<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<meta charset="UTF-8">
</head>
<body>
<div> Hi Paul, </div>
<div class="default-style"> </div>
<div class="default-style"> I though about the geodetic issue as
well. But I'm still wondering why the same input geometries
(respectively geographies) deliver different results with
ST_DWithin: (same query as below, just moved the test to the
result set): </div>
<div class="default-style"> </div>
<div class="default-style"> <img class="aspect-ratio"
style="max-width: 100%;"
src="cid:part1.pzYCd81R.RP7sHty9@gmx.net" alt=""> </div>
<div class="default-style"> </div>
<div class="default-style"> As a next step, I modified the search
geometry in different ways: <br>
1) Moving the point in Canada from 124W further East. The problem
persists as long as longitude is more than 90.001W, and disappears
for 90.0W. The Canada-India edge has now a length of 171.9deg,
thus less than 180deg. </div>
<div class="default-style"> 2) Keeping that point at 90.001W and
moving the easternmost point in India further West: at less than
63E (instead of 77E), URWA airport is consistently reported as
within the search polygon. Problem is just that it is now actually
outside. </div>
<div class="default-style"> <img class="aspect-ratio"
style="max-width: 100%;"
src="cid:part2.cZUJ6OAf.0hlvaJgU@gmx.net" alt=""> </div>
<div class="default-style"> 3) Moving the easternmost point further
West to 46.4E: SBAU is now always reported as being in the
polygon, when in fact it is now outside as well. </div>
<div class="default-style"> <img class="aspect-ratio"
style="max-width: 100%;"
src="cid:part3.kwpS3H9k.iswPd0OT@gmx.net" alt=""> </div>
<div class="default-style"> 4) Finally, moving the easternmost point
to lat=0 shows correct results again. </div>
<div class="default-style">So ST_DWithin gets indeed confused with
the inside and outside of this polygon. </div>
<div class="default-style"><br>
</div>
<div class="default-style">The question is how to mitigate this
issue: </div>
<div class="default-style"> Option 1: The search polygon is oriented
clockwise. I don't see any of these issues anymore (at least not
in my data) when I convert the polygon to CCW using
ST_ForcePolygonCCW(). Do you think this is a reliable method? </div>
<div class="default-style"> Option 2 would be to always segmentize
large polygons, but that would have a negative impact on
performance. <br>
</div>
<div class="default-style"><br>
</div>
<div class="default-style">Greetings</div>
<div class="default-style">Christian</div>
<div class="default-style"><br>
</div>
<blockquote type="cite">
<div> On 01/07/2025 8:50 PM CET Paul Ramsey
<a class="moz-txt-link-rfc2396E" href="mailto:pramsey@cleverelephant.ca"><pramsey@cleverelephant.ca></a> wrote: </div>
<div> </div>
<div> </div>
<div> The top line in your “bounds”, when interpreted
geodetically, does some stuff that is probably quite confusing
to the algorithm. I think the core issue is that it breaks the
rule that “no edge should be longer than 180 degrees”, at least
not if you want to interpret it as an edge, since for any vertex
pair the system assumes the desired edge is the shorter one
between the pair. (I’m pretty sure that’s why the
canada->india line takes off around the back of the world,
since that’s the shorter path between them… and that
back-of-the-world path makes it quite hard to visualize what it
is, exactly, that your polygon is actually containing. </div>
<div> </div>
<div> ATB, </div>
<div> P </div>
<div> </div>
<img src="cid:part4.oees3OME.Q5DOBkiE@gmx.net"
alt="PastedGraphic-1.png" width="706" class=""> <br
id="lineBreakAtBeginningOfMessage">
<div> <br>
<blockquote type="cite">
<div> On Jan 7, 2025, at 11:30 AM, Christian Pschierer via
postgis-users <a class="moz-txt-link-rfc2396E" href="mailto:postgis-users@lists.osgeo.org"><postgis-users@lists.osgeo.org></a> wrote: </div>
<br class="Apple-interchange-newline">
<div>
<div>
<div class="default-style">
<div class="default-style"> Hi, </div>
<div class="default-style"> </div>
<div class="default-style"> I see a strange behavior of
ST_DWithin. Below is a sample dataset with 2 airports,
4 versions each, and identical geometries for each
version. </div>
<div class="default-style"> </div>
<div class="default-style"> Running this query returns
all 8 records. <br>
<blockquote> WITH poly AS ( <br>
SELECT ST_SetSRID('Polygon((77.04180277777778
11.026911111111112, -68.90305555555557 -22.5,
-124.14194444444445 51.626111111111115,
77.04180277777778
11.026911111111112))'::geography,4326) AS geom <br>
) <br>
SELECT airportident,version,a.geom,poly.geom FROM
public.airport AS a, poly <br>
WHERE TRUE <br>
-- AND a.version =
'82765120-5874-4598-920c-35ae3379b4b1' <br>
-- AND
ST_DWithin(a.geom::geography,poly.geom::geography,0.0)
<br>
ORDER BY a.airportident </blockquote>
<br>
Adding the ST_DWithin filter should not change the
result as all geometries are within the search polygon
(respectively the result should have 0 or 4 rows if
outside). </div>
<div class="default-style"> But in fact, I get 6
records! The versions with uuid <span
style="font-family: monospace;">718c720b-2ba4-4600-a09a-b51710fb747d
</span>are missing! <br>
<blockquote> <span style="font-family: monospace;">|airportident|version
|</span> <br>
<span style="font-family: monospace;">|------------|------------------------------------|</span>
<br>
<span style="font-family: monospace;">|SBAU
|b25debb4-1d45-4ea8-aed0-634d6b4041fe|</span> <br>
<span style="font-family: monospace;">|SBAU </span><span
style="font-family: monospace;"> </span><span
style="font-family: monospace;">|82765120-5874-4598-920c-35ae3379b4b1|</span>
<br>
<span style="font-family: monospace;">|SBAU </span><span
style="font-family: monospace;"> </span><span
style="font-family: monospace;">|2ece0394-31aa-47c9-99af-ed795bf2c83c|</span>
<br>
<span style="font-family: monospace;">|URWA </span><span
style="font-family: monospace;"> </span><span
style="font-family: monospace;">|b25debb4-1d45-4ea8-aed0-634d6b4041fe|</span>
<br>
<span style="font-family: monospace;">|URWA </span><span
style="font-family: monospace;"> </span><span
style="font-family: monospace;">|82765120-5874-4598-920c-35ae3379b4b1|</span>
<br>
<span style="font-family: monospace;">|URWA </span><span
style="font-family: monospace;"> </span><span
style="font-family: monospace;">|2ece0394-31aa-47c9-99af-ed795bf2c83c|</span>
</blockquote>
</div>
<div class="default-style"> <span
style="font-family: monospace;"> </span> </div>
<div class="default-style"> Adding the version filter as
well should return 2 records, but it is only 1.<span
style="font-family: monospace;"><br>
</span>
<blockquote> <span style="font-family: monospace;">|airportident|version
|</span> <br>
<span style="font-family: monospace;">|------------|------------------------------------|</span>
<br>
<span style="font-family: monospace;">|URWA </span><span
style="font-family: monospace;"> </span><span
style="font-family: monospace;">|82765120-5874-4598-920c-35ae3379b4b1|</span>
</blockquote>
</div>
<div class="default-style"> Do I miss something? My
first thought was it is a corrupt index, but the trick
works without index as well. I can reproduce this
effect with PostGIS 3.4.2 / Postgresql 16.3 on
Windows, as well as 3.3.3/16.4 on MS Azure. </div>
<div class="default-style"> </div>
<div class="default-style"> Greetings </div>
<div class="default-style"> Christian </div>
<div class="default-style"> </div>
<div class="default-style"> Sample Data: </div>
<div class="default-style"> <span
style="font-family: monospace;"> </span> </div>
<div class="default-style">
<div class="default-style"> <span
style="font-family: monospace;">CREATE TABLE
public.airport (<br>
airportident character varying(5) NOT NULL,<br>
version uuid NOT NULL,<br>
geom public.geometry(Point,4326)<br>
);<br>
INSERT INTO public.airport VALUES ('SBAU',
'718c720b-2ba4-4600-a09a-b51710fb747d',
'0101000020E6100000E9933EE9933649C0B5814E1BE82435C0');<br>
INSERT INTO public.airport VALUES ('SBAU',
'b25debb4-1d45-4ea8-aed0-634d6b4041fe',
'0101000020E6100000E9933EE9933649C0B5814E1BE82435C0');<br>
INSERT INTO public.airport VALUES ('SBAU',
'82765120-5874-4598-920c-35ae3379b4b1',
'0101000020E6100000E9933EE9933649C0B5814E1BE82435C0');<br>
INSERT INTO public.airport VALUES ('SBAU',
'2ece0394-31aa-47c9-99af-ed795bf2c83c',
'0101000020E6100000E9933EE9933649C0B5814E1BE82435C0');<br>
INSERT INTO public.airport VALUES ('URWA',
'718c720b-2ba4-4600-a09a-b51710fb747d',
'0101000020E6100000F36AE259D10048404444444444244740');<br>
INSERT INTO public.airport VALUES ('URWA',
'b25debb4-1d45-4ea8-aed0-634d6b4041fe',
'0101000020E6100000F36AE259D10048404444444444244740');<br>
INSERT INTO public.airport VALUES ('URWA',
'82765120-5874-4598-920c-35ae3379b4b1',
'0101000020E6100000F36AE259D10048404444444444244740');<br>
INSERT INTO public.airport VALUES ('URWA',
'2ece0394-31aa-47c9-99af-ed795bf2c83c',
'0101000020E6100000F36AE259D10048404444444444244740');</span>
</div>
</div>
</div>
</div>
</div>
</blockquote>
</div>
</blockquote>
</body>
</html>