[postgis-users] ST_DWithin question
arnaud.listes at codata.eu
Fri Oct 22 04:52:33 PDT 2021
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 :
FROM <MYTABLE1> AS tbl
WHERE EXISTS (
FROM <MYTABLE1> AS subq
WHERE subq.id != tbl.id AND ST_Intersects(tbl.<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
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the postgis-users