Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I am looking for an SQL query that sums the distances between a series of points using PostGIS.

The situation is as follows:

I have 2 tables: tracks and points. Points belong to a track and thus each points record has a track_id field. Points also have geom field (in WGS84 = GPS coordinates) and a timestamp.

What I would like to do is calculate the total (geographically correct) distance for each track.

In orther words, I need a query that, when given a certain track_id, looks up all the points for that track, orders them by timestamp and than calculates the distance between each consecutive pair of points and sums these distances to give me the total distances of the track in question.

Thanks in advance for any ideas.

share|improve this question

2 Answers 2

up vote 7 down vote accepted

It is fairly straightforward to write a single query for this, you just need to break it down into logical blocks. To start with, you need your point geometry sorted by track and time:

SELECT track_id, gpstime, ST_AsText(the_geom) FROM points ORDER BY track_id, gpstime;

Here I assume you have your points in a geometry column. A geography column in this case would achieve the same thing, and actually save a bit of faff in the next step.

(Note here I've used ST_AsText() to make things readable. You should remove this in the final query).

With my simple database of 2 tracks and 6 points, that query gives me:

 track_id |            gpstime            |         st_astext
----------+-------------------------------+----------------------------
        1 | 2012-01-27 14:36:26.354665+00 | POINT(-71.064544 42.28787)
        1 | 2012-01-27 14:36:53.608661+00 | POINT(-71.063544 42.28987)
        1 | 2012-01-27 14:37:10.841856+00 | POINT(-71.066544 42.3)
        2 | 2012-01-27 14:37:46.400954+00 | POINT(-0.8 53.2)
        2 | 2012-01-27 14:37:55.752122+00 | POINT(-0.81 53.21)
        2 | 2012-01-27 14:38:07.773077+00 | POINT(-0.82 53.22)

The next step is to aggregate those points into linestrings. This is done with the ST_MakeLine() function:

SELECT gps.track_id, ST_AsText(ST_MakeLine(gps.the_geom)) AS track_line
    FROM (SELECT track_id, gpstime, the_geom FROM points ORDER BY track_id, gpstime) as gps
    GROUP BY gps.track_id
    ORDER BY gps.track_id;

Which yields:

 track_id |                             track_line
----------+---------------------------------------------------------------------
        1 | LINESTRING(-71.064544 42.28787,-71.063544 42.28987,-71.066544 42.3)
        2 | LINESTRING(-0.8 53.2,-0.81 53.21,-0.82 53.22)

Finally you need to get the length of that line over the spheroid of your choice. If you're using GPS then inevitably it'll be the WGS84 spheroid. The function to use is ST_Length_Spheroid().

So putting that all together, my final query ends up looking like this:

SELECT gps.track_id, tracks.name, ST_Length_Spheroid(ST_MakeLine(gps.the_geom), 'SPHEROID["WGS 84",6378137,298.257223563]') AS track_len
    FROM (SELECT track_id, gpstime, the_geom FROM points ORDER BY track_id, gpstime) as gps, tracks
    WHERE gps.track_id = tracks.track_id
    GROUP BY gps.track_id, tracks.name
    ORDER BY gps.track_id;

And I get these results:

 track_id | name  |    track_len
----------+-------+------------------
        1 | Larry | 1389.08015675763
        2 | Curly | 2596.09131911171

If you want to limit it to a specific track, change your WHERE clause to something like this:

 WHERE gps.track_id = tracks.track_id AND tracks.name = 'Larry'

If you're using geography columns instead of geometry, you can eliminate the spheroid part and just use ST_Length(). Note that the second parameter of ST_Length() when using geography columns should be set to TRUE if you want more accurate but slower calculations. The only drawback is that geography columns are limited to the WGS84 spheroid.

share|improve this answer
1  
That's a better answer than mine. I didn't think about joining them up, doh! It's Friday :) –  MappaGnosis Jan 27 '12 at 15:35
2  
What a wonderfully clear, detailed solution! –  whuber Jan 27 '12 at 17:40
    
When I run the final query with PostgreSQL 9.1.13 I get the following error: ERROR: column tracks.track_id does not exist / LINE 1: ...k_id, gpstime) as gps, tracks WHERE gps.track_id = tracks.tra.... The second query prints out many empty lines. The first query prints the table as in your example. –  JJD May 5 at 15:30

I'm not sure I would want to try doing it as a single-step SQL query. I would probably write a script (I use Python - other languages are available) along the following lines:

  1. Initaialise a total distance variable to zero
  2. create an SQL query that returns a selection of points for a given track id ordered by time-stamp.
  3. Create a cursor to iterate over the selection.
  4. Initialize 'from_point' to the geometry first feature and 'to_point' as the next feature.
  5. LOOP starts here:
  6. Calculate great circle distance using Haversine or some function depending on what libraries you are using (examples here).
  7. Add the result to the Total_Distance variable
  8. Set your 'from-point' equal to the geometry of your current 'to-point'
  9. Get the next geometry from your selection with your cursor and set your 'to_point' equal to it.
  10. Rinse and repeat until the cursor hits the end of your selection.
  11. Report the value of your Total_Distance variable.
share|improve this answer
    
+1: It works and the important details are explicitly presented. (Those not initiated in the pitfalls of cursors and looping over data will want to make sure that the very last segment is included in the loop, though! Good testing would therefore include two-point tracks (having just one segment) and one-point tracks (with zero segments).) –  whuber Jan 27 '12 at 17:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.