<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<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>
</body>
</html>