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.

  • 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
    Commented May 18, 2013 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... ? Commented May 18, 2013 at 17:45
  • What are "point 1", "point 2" , "point 3"? How do you recognize them?
    – Micha
    Commented May 18, 2013 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. Commented May 18, 2013 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
    Commented May 19, 2013 at 0:15

1 Answer 1


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
  • Hi Jakub Kania. This works for me, but I'm facing problem on the intersections, how do I split the points by the intersections. Commented Jul 12, 2020 at 8:17
  • @MathewPetro Hello. I no longer use this technology so I can't answer the question. Please ask a separate question with good description and examples and someone should be able to help you. Commented Jul 14, 2020 at 10:10
  • Hello Jakub Kania, Sorry for the confusion, I wanted to create a line using the electric poles, whereby I have some T-off(Branch of Electric Lines) and the main-line, What I wanted is to create the lines using the poles on those t-off and when the t-off get at the end, the loop should come and continue from the t-off towards the main-line. Commented Jul 14, 2020 at 21:00
  • @MathewPetro Like I said, please ask a new question (like a new thread, not here in comments) and someone will be able to help you. I haven't touched postgis in years so I really can't.You will have to include how you mark the main line and how you know which pole connects to which. Commented Jul 17, 2020 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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