Strange ST_DWithin behavior

[Paul Ramsey]

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.

ATB,
P



> On Jan 7, 2025, at 11:30 AM, Christian Pschierer via postgis-users <postgis-users at lists.osgeo.org> wrote:
> 
> 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/31e646d7/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: PastedGraphic-1.png
Type: image/png
Size: 176731 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20250107/31e646d7/attachment.png>