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');
