<html><head><meta http-equiv="content-type" content="text/html; charset=utf-8"></head><body style="overflow-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;"><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><br></div><div>ATB,</div><div>P</div><div><br></div><img width="706" alt="PastedGraphic-1.png" src="cid:3F41BA4E-4410-4B8E-9942-3C5AE12E8659"><br id="lineBreakAtBeginningOfMessage"><div><br><blockquote type="cite"><div>On Jan 7, 2025, at 11:30 AM, Christian Pschierer via postgis-users <postgis-users@lists.osgeo.org> wrote:</div><br class="Apple-interchange-newline"><div>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div>
<div class="default-style">
<div class="default-style">Hi,</div>
<div class="default-style"><br>
</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"><br>
</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<br>
</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 <font face="monospace">718c720b-2ba4-4600-a09a-b51710fb747d
</font>are missing!<br>
<blockquote><font face="monospace">|airportident|version |</font><br>
<font face="monospace">|------------|------------------------------------|</font><br>
<font face="monospace">|SBAU
|b25debb4-1d45-4ea8-aed0-634d6b4041fe|</font><br>
<font face="monospace">|SBAU </font><font face="monospace">
</font><font face="monospace">|82765120-5874-4598-920c-35ae3379b4b1|</font><br>
<font face="monospace">|SBAU </font><font face="monospace">
</font><font face="monospace">|2ece0394-31aa-47c9-99af-ed795bf2c83c|</font><br>
<font face="monospace">|URWA </font><font face="monospace">
</font><font face="monospace">|b25debb4-1d45-4ea8-aed0-634d6b4041fe|</font><br>
<font face="monospace">|URWA </font><font face="monospace">
</font><font face="monospace">|82765120-5874-4598-920c-35ae3379b4b1|</font><br>
<font face="monospace">|URWA </font><font face="monospace">
</font><font face="monospace">|2ece0394-31aa-47c9-99af-ed795bf2c83c|</font></blockquote>
</div>
<div class="default-style"><font face="monospace"><br>
</font></div>
<div class="default-style">Adding the version filter as well
should return 2 records, but it is only 1.<font face="monospace"><br>
</font>
<blockquote><font face="monospace">|airportident|version |</font><br>
<font face="monospace">|------------|------------------------------------|</font><br>
<font face="monospace">|URWA </font><font face="monospace">
</font><font face="monospace">|82765120-5874-4598-920c-35ae3379b4b1|</font></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"><br>
</div>
<div class="default-style">Greetings<br>
</div>
<div class="default-style">Christian</div>
<div class="default-style"><br>
</div>
<div class="default-style">Sample Data:<br>
</div>
<div class="default-style"><font face="monospace"><br>
</font></div>
<div class="default-style">
<div class="default-style"><font face="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');</font></div>
<br>
</div>
</div>
</div>
</div></blockquote></div><br></body></html>