[postgis-users] Question about calculating total distance from a list of points

chris mollis cmollis at objectlab.com
Wed Jul 2 06:41:59 PDT 2008



I have list of lon, lat points that users record and upload to our database.
These are recorded and logged in time order.  Presumably iterating over
these in the same order would provide a way to calculate the distance and
direction traveled by the user.  I'd like to calculate and store the overall
distance traveled, but I'm not sure that I'm actually calculating this


Basically, I collect all of the recorded points in time order (which yields
a multipoint geometry), which I then convert to a linestring, from which I
calculate the distance.


select (ST_length2D((ST_linefrommultipoint((


    transform(PointFromText('POINT(' || 

    to_char(pts.longitude, '999D999999') || ' ' || to_char(pts.latitude,
'999D999999') || ')', 4269), 32661)

 ) ) ) ) ) ) / 1000

from points pts 

order by pts.point_id


Note: lon and lat are in this form POINT(-75.16050 39.959662)  which, I
believe, is spatial reference system 4269.  My understanding is that I need
to convert to spatial reference system determined in meters to get the
appropriate result.


The distances that I calculate seem far to large.. so I suspect that I'm
doing something wrong.



Chris Mollis


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080702/561ba090/attachment.html>

More information about the postgis-users mailing list