Strange ST_DWithin behavior

Christian Pschierer christian.pschierer at gmx.net
Wed Jan 8 05:39:25 PST 2025 

Hi Paul,
I though about the geodetic issue as well. But I'm still wondering why
the same input geometries (respectively geographies) deliver different
results with ST_DWithin: (same query as below, just moved the test to
the result set):
As a next step, I modified the search geometry in different ways:
1) Moving the point in Canada from 124W further East. The problem
persists as long as longitude is more than 90.001W, and disappears for
90.0W. The Canada-India edge has now a length of 171.9deg, thus less
than 180deg.
2) Keeping that point at 90.001W and moving the easternmost point in
India further West: at less than 63E (instead of 77E), URWA airport is
consistently reported as within the search polygon. Problem is just that
it is now actually outside.
3) Moving the easternmost point further West to 46.4E: SBAU is now
always reported as being in the polygon, when in fact it is now outside
as well.
4) Finally, moving the easternmost point to lat=0 shows correct results
again.
So ST_DWithin gets indeed confused with the inside and outside of this
polygon.

The question is how to mitigate this issue:
Option 1: The search polygon is oriented clockwise. I don't see any of
these issues anymore (at least not in my data) when I convert the
polygon to CCW using ST_ForcePolygonCCW(). Do you think this is a
reliable method?
Option 2 would be to always segmentize large polygons, but that would
have a negative impact on performance.

Greetings
Christian

> On 01/07/2025 8:50 PM CET Paul Ramsey <pramsey at cleverelephant.ca> wrote:
> 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
> PastedGraphic-1.png
>
>> 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/20250108/26629217/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 51984 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20250108/26629217/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 113679 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20250108/26629217/attachment-0001.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 74757 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20250108/26629217/attachment-0002.png>
-------------- 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/20250108/26629217/attachment-0003.png>

More information about the postgis-users mailing list