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?