Creating line from points using PostGIS?

Question

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.

• Micha: 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
• Thomas Becker: 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... ?
• Micha: What are "point 1", "point 2" , "point 3"? How do you recognize them?
• Thomas Becker: 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.
• ThomasG77: 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/…

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
WHERE geom2 IS NOT NULL;

• Mathew Petro: Hi Jakub Kania. This works for me, but I'm facing problem on the intersections, how do I split the points by the intersections.
• Jakub Kania: @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.
• Mathew Petro: 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.
• Jakub Kania: @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.