I have a PostGIS table with position data of numerous vehicles and want to create lines from these points. Data can be selected by vehicle id and ordered by timestamp but how to create lines from the result? What I basically need is one line segment from point 1 to point 2, finalize the line and then again from point 2 to point 3. Of course all that under consideration of the vehicle id...

The latter one is needed because I want to calculate the cruise direction and speed of the vehicle from one point to the next.

Would be really nice to hear from you. Cheers Tom

  • 1
    The ST_MakeLine() function will do this, once you have your GROUP BY vehicle_id and ORDER BY timestamp worked out. See: postgis.refractions.net/docs/ST_MakeLine.html – Micha May 18 '13 at 16:20
  • Alright, I simply gave it a try and issued the following statement: SELECT ais_data.mmsi, ST_MakeLine(ais_data.geom) AS newgeom INTO ais_lines FROM (SELECT * FROM ais_data ORDER BY ais_data.mmsi, ais_data.bs_ts ASC) AS ais_data GROUP BY ais_data.mmsi; That will give me the track of every vehicle, and is not exactly what I need. How to tell ST_MakeLine() to create a line from point 1 to point 2, finalize the line and start a new one from point 2 to point 3... ? – Thomas Becker May 18 '13 at 17:45
  • What are "point 1", "point 2" , "point 3"? How do you recognize them? – Micha May 18 '13 at 19:01
  • I thought to recognise them via the ordering of the timestamp... ORDER BY ais_data.bs_ts - is it possible? So, point 1, point 2 and so forth are basically the point information given in each line as the result of the Select statement. – Thomas Becker May 18 '13 at 19:36
  • You can in a first step generate one line per vehicle and after generate vertex from this line using tips from mailing list postgis postgis.17.x6.nabble.com/… – ThomasG77 May 19 '13 at 0:15

It can be done in a few ways, using self-joins or correlated subqueries but using window functions is probably the easiest way.

The function lead() returns a value that's ahead in the given partition and our partition is (PARTITION BY <vehicle_id> ORDER BY <timestamp>)

This query gives us the vehicle number, the position of that point in partition (which is equal to the position of line starting with it) and the two geometries that will make the line. Of course it returns NULL geom2 for last point so we need to check for that in the outer query.

SELECT mmsi, num, ST_MAKELINE(geom,geom2) FROM (
  SELECT mmsi, row_number() OVER w AS num, geom, lead(geom) OVER w AS geom2
  FROM ais_data WINDOW w AS (PARTITION BY mmsi ORDER BY bs_ts) ) as q
  • 2
    That's absolutely fantastic! :) I'm getting more and more convinced that PostGIS is really what I should focus on! – Thomas Becker May 20 '13 at 10:45

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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