[postgis-users] ST_DWithin question

[Marco Boeringa]

Thanks Arnaud,

This is a very useful answer. I will try the ST_Intersects option you 
propose.

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

Marco

Op 22-10-2021 om 13:52 schreef Arnaud L.:
> Hi Marco
>
> Le 22/10/2021 à 13:31, Marco Boeringa a écrit :
>> I currently use the following two queries. This first query creates a
>> table with all unique 'pairs' of polygons as identified by their ID
>> using 'ST_DWithin with a 0 range from the <MY_TABLE1> original polygon
>> table. The second one deduplicates the two columns of the first table to
>> create a unique list of IDs.
> I think I would write this in a single query, using an EXISTS subquery 
> instead of a join step and a dedup step :
>
> SELECT tbl.id
> FROM <MYTABLE1> AS tbl
> WHERE EXISTS (
>     SELECT 1
>     FROM <MYTABLE1> AS subq
>     WHERE subq.id != tbl.id AND ST_Intersects(tbl.<GEOMETRY_COLUMN> , 
> subq.<GEOMETRY_COLUMN>)
> );
>
> No need to DISTINCT or anything here, the result should already be 
> correct.
>
>>  From the documentation of
>> ST_DWithin, it is clear it is already optimized to use the spatial index
>> and bounding boxes to speed up the search, so I guess there is nothing
>> more I can do. It is properly using multiple parallel workers in
>> PostgreSQL as well for creating the table from a look in PgAdmin.
>
> ST_DWithin(a,b,0) is logically equivalent to ST_Intersects(a,b), but 
> as per this post on SE, ST_Intersects might be faster due to internal 
> optimizations :
> https://gis.stackexchange.com/questions/400700/st-intersects-vs-st-dwithin-behavior-on-polygons-using-postgiseo.org/mailman/listinfo/postgis-users
>
>
> 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/b7ad378f/attachment.html>