PostGIS – How to Draw Lines From Point-to-Point in PostGIS / PostgreSQL

postgispostgresql

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?

Best Answer

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)
Related Question