Strange ST_DWithin behavior

[Christian Pschierer]

Hi,

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.

Running this query returns all 8 records.

    WITH poly AS (
         SELECT ST_SetSRID('Polygon((77.04180277777778
    11.026911111111112, -68.90305555555557 -22.5, -124.14194444444445
    51.626111111111115, 77.04180277777778
    11.026911111111112))'::geography,4326) AS geom
    )
    SELECT airportident,version,a.geom,poly.geom FROM public.airport AS
    a, poly
    WHERE TRUE
    -- AND a.version = '82765120-5874-4598-920c-35ae3379b4b1'
    -- AND ST_DWithin(a.geom::geography,poly.geom::geography,0.0)
    ORDER BY a.airportident


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).
But in fact, I get 6 records! The versions with uuid
718c720b-2ba4-4600-a09a-b51710fb747d are missing!

    |airportident|version |
    |------------|------------------------------------|
    |SBAU |b25debb4-1d45-4ea8-aed0-634d6b4041fe|
    |SBAU |82765120-5874-4598-920c-35ae3379b4b1|
    |SBAU |2ece0394-31aa-47c9-99af-ed795bf2c83c|
    |URWA |b25debb4-1d45-4ea8-aed0-634d6b4041fe|
    |URWA |82765120-5874-4598-920c-35ae3379b4b1|
    |URWA |2ece0394-31aa-47c9-99af-ed795bf2c83c|


Adding the version filter as well should return 2 records, but it is only 1.

    |airportident|version |
    |------------|------------------------------------|
    |URWA |82765120-5874-4598-920c-35ae3379b4b1|

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.

Greetings
Christian

Sample Data:

CREATE TABLE public.airport (
airportident character varying(5) NOT NULL,
version uuid NOT NULL,
geom public.geometry(Point,4326)
);
INSERT INTO public.airport VALUES ('SBAU',
'718c720b-2ba4-4600-a09a-b51710fb747d',
'0101000020E6100000E9933EE9933649C0B5814E1BE82435C0');
INSERT INTO public.airport VALUES ('SBAU',
'b25debb4-1d45-4ea8-aed0-634d6b4041fe',
'0101000020E6100000E9933EE9933649C0B5814E1BE82435C0');
INSERT INTO public.airport VALUES ('SBAU',
'82765120-5874-4598-920c-35ae3379b4b1',
'0101000020E6100000E9933EE9933649C0B5814E1BE82435C0');
INSERT INTO public.airport VALUES ('SBAU',
'2ece0394-31aa-47c9-99af-ed795bf2c83c',
'0101000020E6100000E9933EE9933649C0B5814E1BE82435C0');
INSERT INTO public.airport VALUES ('URWA',
'718c720b-2ba4-4600-a09a-b51710fb747d',
'0101000020E6100000F36AE259D10048404444444444244740');
INSERT INTO public.airport VALUES ('URWA',
'b25debb4-1d45-4ea8-aed0-634d6b4041fe',
'0101000020E6100000F36AE259D10048404444444444244740');
INSERT INTO public.airport VALUES ('URWA',
'82765120-5874-4598-920c-35ae3379b4b1',
'0101000020E6100000F36AE259D10048404444444444244740');
INSERT INTO public.airport VALUES ('URWA',
'2ece0394-31aa-47c9-99af-ed795bf2c83c',
'0101000020E6100000F36AE259D10048404444444444244740');
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20250107/c470e0d2/attachment.htm>