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.