create lines from points table

Question

I have a point table I have sorted by mmsi (it is the identifier) and by datetime columns.

 mmsi numeric,
 x1 double precision,
 y1 double precision,
 datetime timestamp without time zone,
 geom geometry(Point,3035)
 linetime2 (interval)

For each mmsi I would like to create a set of segments every 2 consecutive rows/points, skipping segments which have more then 6 hours of difference between the starting and the ending point AND more then 30 kilometers of distance between the starting and the ending points. the data are as follow:

enter image description here

Concerning the maximum distance between two consectutive points of the same mmsi I could calculate the line length in a second step and then delete lines longer than 30 km.

Concerning the maximun time interval I have calculted the time difference (column linetime2) between two consecutive points/rows using a lag function:

select mmsi, 
 datetime,
 datetime - lag(datetime) over (order by mmsi, datetime) as linetime
from test_st_12_v1;
with cte as (select mmsi, x1, y1, datetime, datetime - lag(datetime) over (partition by mmsi order by mmsi, datetime) as linetime
from test_st_12_v1)
update test_st_12_v1 lt
set linetime2 = ct.linetime
from cte ct
where lt.mmsi = ct.mmsi
and lt.datetime= ct.datetime;

Now, how can I create lines every two consecutive points with the same id (mmsi) skipping segments with a linetime2> 6 hours? E.g. in the example I dont' want a segnemt between the points 9 and 10, 15 and 16, 83 and 84, and of course between 84 and 85 because I have another id number (mmsi). Any idea?

Answer 1

You can also use the lag() to find the next point, construct a line and compute its length

WITH cte AS (
 SELECT mmsi, x1, y1, datetime, 
 datetime - lag(datetime) OVER (PARTITION BY mmsi ORDER BY mmsi, datetime) as linetime,
 ST_MAKELINE(geom , lag(geom ) OVER (PARTITION BY mmsi ORDER BY mmsi, datetime)) as linegeom
 FROM test_st_12_v1)
UPDATE test_st_12_v1 lt
SET linetime2 = ct.linetime,
 linegeom = ct.linegeom
FROM cte ct
WHERE lt.mmsi = ct.mmsi
 AND lt.datetime= ct.datetime
 AND ct.linetime < interval '6 hour' 
 AND st_length(ct.linegeom) < 30000;

To just select the lines, you can do the following. Let's note that having a unique segmentid could help joining back the line to the original table

WITH cte AS (
 SELECT mmsi, x1, y1, datetime, 
 datetime - lag(datetime) OVER (PARTITION BY mmsi ORDER BY mmsi, datetime) as linetime,
 ST_MAKELINE(geom , lag(geom ) OVER (PARTITION BY mmsi ORDER BY mmsi, datetime)) as linegeom
 FROM test_st_12_v1)
SELECT *
FROM cte ct
WHERE ct.linetime < interval '6 hour' 
 AND st_length(linegeom) < 30000;

• Luigi Falco: Before to run your query should I have to add a field linegeom in the test_st_12_v1 table point?
• JGH: This query updates the source table, so yes. But it is easy to transform it to a select
• Luigi Falco: How coul I transform it? I would simply run this before: ALTER TABLE test_st_12_v1 ADD COLMUN linegeom geometry (MultiLineString, 3035)
• JGH: see edit for a select version. If you want to add a column, I believe it should rather be of type geometry(linestring,3035)
• Luigi Falco: thank you for the help. I have just to figure out how to edit the existing table, from point to line, avoiding to add and delete geometry columns