Gaps and Islands problem in PostGIS for GPS traces

Question

I'm using ST_MakeLine to create traces of ship movements. The query below yields all the GPS points on a certain day, and I'm using ST_Within to only find ships in the navigable portions of the river.

SELECT gps.basedatetime, gps.geom
from ais_2018 as gps
join dissolved_nav_waterway dnw  
on st_within(gps.geom, dnw.wkb_geometry)
where status = 0
and vesselname = 'HIGH ROLLER'
and basedatetime::date = '2018-08-25'
group by gps.basedatetime, gps.geom 
order by gps.basedatetime

However, if I try to use ST_Makeline, I get an erroneous straight line over the land, which I determined is because the GPS data has a gap where it wasn't being recorded for several hours. Query below.

select st_makeline(geom)
from problem p 

Is there any way I can split the line into two segments at this gap or otherwise remove the erroneous straight line? I've read a bit about the "gaps and islands" problem but I can't seem to find a solution. Photo attached, the straight line is the error, which represents the ship moving northeast without its GPS on, turning GPS on, and then moving south to southwest.

Answer 1

You will have to delineate a dimensional threshold in order to solve the Islands and Gaps problem; you could e.g. define an upper limit for the GPS check-in interval, and split the sequence accordingly:

SELECT  ST_MakeLine(geom ORDER BY basedatetime) AS geom
FROM    (
    SELECT *,
           SUM(__isl) OVER(ORDER BY basedatetime) AS _mkl
    FROM   (
        SELECT gps.*,
               COALESCE((gps.basedatetime - LAG(gps.basedatetime) OVER(ORDER BY gps.basedatetime) > <TIME_THRESHOLD>)::INT, 0) AS __isl
        FROM   ais_2018 AS gps
        JOIN   dissolved_nav_waterway AS dnw  
          ON   ST_Intersects(gps.geom, dnw.wkb_geometry)
        WHERE  gps.status = 0
          AND  gps.vesselname = 'HIGH ROLLER'
          AND  gps.basedatetime::DATE = '2018-08-25'
    ) sq
) q
GROUP BY
        _mkl
;

This should return a separate LineString for all parts of a trace spearated by more than <TIME_THRESOLD>.

---

Note that you could use the Window Functions to PARTITION BY your attributes, so that e.g. the query creates these paths in batch, if needed, for all unique combinations of gps.status and/or gps.vesselname and/or gps.basedatetime::DATE:

SELECT  status,
        vesselname,
        basedatetime::DATE,
        ST_MakeLine(geom ORDER BY basedatetime) AS geom
FROM    (
    SELECT *,
           SUM(__isl) OVER(PARTITION BY gps.status, gps.vesselname, gps.basedatetime::DATEORDER BY basedatetime) AS _mkl
    FROM   (
        SELECT gps.*,
               COALESCE((gps.basedatetime - LAG(gps.basedatetime) OVER(PARTITION BY gps.status, gps.vesselname, gps. ORDER BY gps.basedatetime) > <TIME_THRESHOLD>)::INT, 0) AS __isl
        FROM   ais_2018 AS gps
        JOIN   dissolved_nav_waterway AS dnw  
          ON   ST_Intersects(gps.geom, dnw.wkb_geometry)
    ) sq
) q
GROUP BY
        status, vesselname, basedatetime::DATE, _mkl
;