How to draw lines from point-to-point, In PostGIS / PostgreSQL

Question

I have some GPS points in PostGIS, and I’m looking for a way to draw lines from point to point, to illustrate travel routes.

I know how to do one long line, but I need to do small point-to-point lines.

I have a table of GPS points, from tracking a group of vehicles. It looks like this:

dtt |id |geom |
-------------------+-----+---------------------------------------------+
2021年03月12日 00:00:00|13116|POINT (4.428372016319861 0.6945075255173904) |
2021年03月12日 00:00:01|91665|POINT (4.189513573279748 0.9352590120755709) |
2021年03月12日 00:00:01|14532|POINT (4.211658477783203 1.152058821458083) |
2021年03月12日 00:00:01|16531|POINT (4.396507263183594 0.6196146011352539) |
2021年03月12日 00:00:01|17655|POINT (4.452470926138071 0.7759194007286658) |
...

Columns are PostgreSQL types: timestamp, integer and PostGIS Point, restively.

I have +10 mill points per day and need to analyse 6 months’ worth of data, and potentially 5-10 years of historical data later.

Each vehicle has from 1 to ca. 20.000 points per day.

I have reduced the number of points to only one per hour, since that fits my analysis goal, and greatly reduces the number of point. I do that like this:

 -- table with mini-points, and only one per hour, per id
 drop table if exists dmaais.selgeohour cascade;
 create table dmaais.selgeohour as
 select distinct on (date_trunc('hour', dtt), id) *
 from dmaais.selgeo
 order by date_trunc('hour', dtt), id, dtt
 ;

I would like to draw lines, between the hour-point, to represent vehicles movements, i.e. only lines between points from same vehicle, and drawn in time-order.

So far I have tried this: (based on the online help for ST_MakeLine)

 -- draw lines from point-to-point, attributing with temporal and spatial distance
 drop table if exists dmaais.selline cascade;
 create table dmaais.selline as
 SELECT gps.id, ST_MakeLine(gps.geom ORDER BY gps.dtt) as geom, 
 ST_Length(ST_MakeLine(gps.geom ORDER BY gps.dtt)) as leng,
 EXTRACT(EPOCH FROM (max(gps.dtt) - min(gps.dtt))) as dura
 FROM dmaais.selgeohour As gps
 GROUP BY id
 ;

As my data are in geo-coordinates the ‘leng’ fields measures the length in degrees, which is not optimal. But it is a good-enough approximation for distance for my purpose, and calculation speed is important, so I don’t want to include any geo-transformation at this stage.

The ‘dura’ field gives the travel time in seconds, and that is what I seek.

Result data, so far.

This result is not completely different from what I want, but it creates one long line, representing the entire route of the individual vehicle.

How do I get separate line between each pair of points, and the fields ‘leng’ and ‘dura’ to represent the length and duration of traveling only between these two point?

• J. Monticolo: Do you know the LAG() and LEAD() functions ? Maybe they can help you. postgresql.org/docs/current/functions-window.html
• Martin: Will look at LAG() and LEAD() windows functions. Can you point at any good example use?
• J. Monticolo: postgresqltutorial.com/postgresql-lag-function, so you can create make_line with the previous record. The LEAD function is with the next record.

Answer 1

To get multiple segments for each ID, you can't group by ID.

Instead, you can use a window function to partition by ID, order by ddt and at last to access the following row

with src (id, dtt, geom) as (values (1,1,'point(1 1)'),(1,2,'point(1 2)'),(1,3,'point(1 3)'),(2,1,'point(2 1)'),(2,2,'point(2 2)'),(2,3,'point(1 3)'))
SELECT id, dtt, 
 st_asText(
 st_makeLine(geom, lead(geom) OVER(PARTITION BY ID ORDER BY dtt))) 
FROM src;
 id | dtt | st_astext
----+-----+---------------------
 1 | 1 | LINESTRING(1 1,1 2)
 1 | 2 | LINESTRING(1 2,1 3)
 1 | 3 |
 2 | 1 | LINESTRING(2 1,2 2)
 2 | 2 | LINESTRING(2 2,1 3)
 2 | 3 |
(6 rows)

Answer 2

Based on the commnt from @J. Monticolo I have created this that do exactely what I want.

I can't mark a comment as accepted answer, so I'll accept th ansver from JGH, which is similar, but came while I was coding.

-- draw lines from point-to-point, attributing with temporal and spatial distance... ver. 1.1
drop table if exists dmaais.sellegs cascade;
create table dmaais.sellegs as
 with step as (
 select dtt, 
 id, 
 ST_MakeLine(geom, LEAD(geom,1) over (partition by id order by id, dtt)) as geom,
 extract(EPOCH from (LEAD(dtt,1) over (partition by id order by id, dtt) - dtt)) as dura
 from dmaais.selgeohour )
 select *,
 ST_Length(geom) as leng,
 (ST_Length(geom) / step.dura) *60 *60*60 as speed
 -- 60 to go from degrees to arc-minutes, 
 -- and 60*60 to go from seconds to hour.
 -- Arc-minutes/hour is almost like knots 
 from step
;