[postgis-users] ST_DWithin question

[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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20211022/e2b35159/attachment.html>