10

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

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;
| improve this answer | |
  • Hi Jakub Kania. This works for me, but I'm facing problem on the intersections, how do I split the points by the intersections. – Mathew Petro Jul 12 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. – Jakub Kania Jul 14 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. – Mathew Petro Jul 14 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. – Jakub Kania Jul 17 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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