[postgis-users] ST_DWithin question

[Marco Boeringa]

Hi Arnaud,

I have now done some preliminary testing with a > 10M records dataset, 
but my first impression is that 'ST_Intersects' is not faster than 
'ST_DWithin' with range 0.

In fact, I have the feeling it may be about 20-25% slower (e.g. 2m48s 
versus for 'ST_Intersects' versus 2m07s for 'ST_DWithin' was one 
result). At least, that is what I am currently seeing when I replace the 
'ST_Intersects' call with 'ST_DWithin' in your optimized query, and this 
difference seems quite consistent when I re-run this multiple times.

So the query below appears to be the most efficient:

SELECT tbl.id
FROM <MYTABLE1> AS tbl
WHERE EXISTS (
     SELECT 1
     FROM <MYTABLE1> AS subq
     WHERE subq.id != tbl.id AND ST_DWithin(tbl.<GEOMETRY_COLUMN> , 
subq.<GEOMETRY_COLUMN>, 0)
);

Marco

Op 22-10-2021 om 14:07 schreef Arnaud L.:
> Le 22/10/2021 à 14:04, Marco Boeringa a écrit :
>>
>> Minor correction to the link provided, which should read:
>>
>> https://gis.stackexchange.com/questions/400700/st-intersects-vs-st-dwithin-behavior-on-polygons-using-postgis
>>
>
> Thanks, I don't know what happened here...
> Also, in the EXISTS (subquery) version you don't have to rejoin to 
> your original table, since you are already querying your original table.
>
> Regards
> --
> Arnaud
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20211022/970583f3/attachment.html>