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

[chris mollis]

Hi-

 

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
correctly.

 

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((

st_collect(

    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.

 

Thanks


Chris Mollis

 

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