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.

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?

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
;