<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>Hi,</div>
<div> </div>
<div>I've got an issue with an SQL PostGIS query and hope to get a solution here.</div>
<div> </div>
<div>First some details.</div>
<div>I'm storing GPS locations of vessels by their MMSI identification number (AIS) that are received in 3 Sek to 3 Minutes ranges in a table.</div>
<div>As this becomes quite unhandy in a very short time, each day the basic details mmsi, received_at (Timestamp), lat, long are converted into a Points with the Timestamp integrated and this is then converted into a LineString for each MMSI, per day, with the first and last timestamp of the day for which a point exists as direct columns.</div>
<div> </div>
<div>Now I want to know when some vessel is/was inside a specific area (harbor, berth) so I have a Polygon which the LineString has to intersect.</div>
<div>So far so good.</div>
<div>This works but can create false positives (two points outside the polygon are connected and this created line goes through the Polygon but there is no actual Point of that line in the Polygon)...so I need to re-check again by pulling out the points from my LineString.</div>
<div> </div>
<div> </div>
<div>Here the basic search query that basically works:</div>
<div>================================</div>
<div>--<br/>
-- Return all vessels (MMSI) found in the selected timeframe where the<br/>
-- LineString Intersects the Polygon given as parameter to search.<br/>
--<br/>
SELECT ais_trajectory_mmsi_daily.mmsi<br/>
,ais_trajectory_mmsi_daily.min_created_on<br/>
,ais_trajectory_mmsi_daily.max_created_on<br/>
,ST_SetSRID(ais_trajectory_mmsi_daily.geo_lat_long_line, 4326) AS geo_lat_long_line<br/>
FROM ais_trajectory_mmsi_daily<br/>
WHERE ST_Intersects(ST_SetSRID(ais_trajectory_mmsi_daily.geo_lat_long_line, 4326),<br/>
ST_GeometryFromText('POLYGON((7.1084729427821 50.7352828020046,7.1089771980769 50.7353778664669,7.10848259899649 50.7365542732199,7.10801482200623 50.7364673592064,7.1084729427821 50.7352828020046))', 4326) )<br/>
AND min_created_on >= CAST('2022-10-01' AS TIMESTAMPTZ)<br/>
AND max_created_on < CAST('2022-10-15' AS TIMESTAMPTZ)<br/>
;</div>
<div> </div>
<div> </div>
<div>Here is now the query I use with ST_Intersection and where it starts to go wrong.</div>
<div>When a vessel stays a the same place the GPS coordinates are the same + drift.</div>
<div> </div>
<div>As those points are all added to the LineString the line sections are running over themself and it looks like a "woolen noodle".</div>
<div>This seems to cause issues with ST_Intersection as I use it because instead of getting only the entering and exiting details as normally 1 row where the LineString hits my Polygon with times I now get hundreds of rows as a result and the visual result shown on the map is weird and sometimes wrong (the line goes out of the polygon in a way the original LineString did never exist).</div>
<div> </div>
<div>Here is the query in question (basically the part above) with ST_Dump and ST_Intersection in the SELECT part + plus an outer query to get more details on what was the first and last time there was an intersection.</div>
<div> </div>
<div>--<br/>
-- Outer query with union to visualize the Polygon and the <br/>
-- data received from the LineString that Intersects<br/>
-- with the polygon via e.g. pgAdmin.<br/>
--<br/>
SELECT NULL AS mmsi<br/>
,NULL AS created_on_date<br/>
,NULL AS min_created_on<br/>
,NULL AS max_created_on<br/>
,ST_GeometryFromText('POLYGON((7.1084729427821 50.7352828020046,7.1089771980769 50.7353778664669,7.10848259899649 50.7365542732199,7.10801482200623 50.7364673592064,7.1084729427821 50.7352828020046))', 4326) AS geom<br/>
,NULL AS geom_start<br/>
,NULL AS geom_end<br/>
UNION<br/>
-- The actual data of interest<br/>
SELECT intersection.mmsi<br/>
,intersection.created_on_date<br/>
,intersection.min_created_on<br/>
,intersection.max_created_on<br/>
,intersection.geom<br/>
,MIN(DATE_TRUNC('second', TO_TIMESTAMP(ST_InterpolatePoint(intersection.geo_lat_long_line, ST_StartPoint(intersection.geom)) )::timestamptz )) AS geom_start<br/>
,MAX(DATE_TRUNC('second', TO_TIMESTAMP(ST_InterpolatePoint(intersection.geo_lat_long_line, ST_EndPoint(intersection.geom)) )::timestamptz )) AS geom_end<br/>
FROM (<br/>
SELECT ais_trajectory_mmsi_daily.mmsi<br/>
,CAST(ais_trajectory_mmsi_daily.min_created_on AS date) AS created_on_date <br/>
,ais_trajectory_mmsi_daily.min_created_on<br/>
,ais_trajectory_mmsi_daily.max_created_on<br/>
,ST_SetSRID(ais_trajectory_mmsi_daily.geo_lat_long_line, 4326) AS geo_lat_long_line</div>
<div> --</div>
<div> -- Here seems to be the problem...</div>
<div> --<br/>
,(ST_Dump(ST_Intersection(ST_GeometryFromText('POLYGON((7.1084729427821 50.7352828020046,7.1089771980769 50.7353778664669,7.10848259899649 50.7365542732199,7.10801482200623 50.7364673592064,7.1084729427821 50.7352828020046))', 4326)<br/>
,ST_SetSRID(ais_trajectory_mmsi_daily.geo_lat_long_line, 4326)<br/>
)<br/>
)).geom AS geom<br/>
FROM ais_trajectory_mmsi_daily<br/>
WHERE ST_Intersects(ST_SetSRID(ais_trajectory_mmsi_daily.geo_lat_long_line, 4326),<br/>
ST_GeometryFromText('POLYGON((7.1084729427821 50.7352828020046,7.1089771980769 50.7353778664669,7.10848259899649 50.7365542732199,7.10801482200623 50.7364673592064,7.1084729427821 50.7352828020046))', 4326) )<br/>
AND min_created_on >= CAST('2022-10-01' AS TIMESTAMPTZ)<br/>
AND max_created_on < CAST('2022-10-02' AS TIMESTAMPTZ)<br/>
) AS intersection<br/>
GROUP BY intersection.mmsi<br/>
,intersection.created_on_date<br/>
,intersection.min_created_on<br/>
,intersection.max_created_on<br/>
,intersection.geom<br/>
ORDER BY created_on_date, mmsi, geom_start<br/>
;</div>
<div> </div>
<div>If needed I can try to create some sample with data to put on sqlfiddle.</div>
<div> </div>
<div>On Stackoverflow I found this which seems to be related to the same issue:</div>
<div>https://stackoverflow.com/questions/62090829/why-st-intersection-from-postgis-returns-multilinestring-for-self-intersecting-l</div>
<div> </div>
<div>Many thanks in advance for hints to solve the problem.</div>
<div> </div>
<div>Juergen</div>
<div> </div></div></body></html>