Strange ST_DWithin behavior

Christian Pschierer via postgis-users <[email protected]>
Newsgroups gmane.comp.gis.postgis
Message-ID <[email protected]>
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');
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.