<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Would using a MultiPoint (or GeometryCollection with Points) for
the GPS locations rather than a Linestring be possible?</p>
<p>Simon<br>
</p>
<div class="moz-cite-prefix">On 17/10/2022 10:10 am, <a class="moz-txt-link-abbreviated" href="mailto:magog002@web.de">magog002@web.de</a>
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:trinity-26a9983e-6d84-4cab-b20c-584e55eafd81-1665961831247@3c-app-webde-bap37">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<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><a class="moz-txt-link-freetext" href="https://stackoverflow.com/questions/62090829/why-st-intersection-from-postgis-returns-multilinestring-for-self-intersecting-l">https://stackoverflow.com/questions/62090829/why-st-intersection-from-postgis-returns-multilinestring-for-self-intersecting-l</a></div>
<div> </div>
<div>Many thanks in advance for hints to solve the problem.</div>
<div> </div>
<div>Juergen</div>
<div> </div>
</div>
<br>
<fieldset class="moz-mime-attachment-header"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<pre class="moz-signature" cols="72">--
Simon Greener
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia
(m) +61 418 396 391
(w) <a class="moz-txt-link-abbreviated" href="http://www.spdba.com.au">www.spdba.com.au</a>
(m) <a class="moz-txt-link-abbreviated" href="mailto:simon@spdba.com.au">simon@spdba.com.au</a></pre>
</body>
</html>