# create lines from points table

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:

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?

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;
``````
• Before to run your query should I have to add a field linegeom in the test_st_12_v1 table point? Commented Jun 3, 2020 at 15:35
• This query updates the source table, so yes. But it is easy to transform it to a `select`
– JGH
Commented Jun 3, 2020 at 15:37
• How coul I transform it? I would simply run this before: ALTER TABLE test_st_12_v1 ADD COLMUN linegeom geometry (MultiLineString, 3035) Commented Jun 3, 2020 at 15:50
• see edit for a `select` version. If you want to add a column, I believe it should rather be of type `geometry(linestring,3035)`
– JGH
Commented Jun 3, 2020 at 16:04
• 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 Commented Jun 5, 2020 at 8:57