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.

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;