[postgis-users] ST_DWithin question
marco at boeringa.demon.nl
Fri Oct 22 04:31:12 PDT 2021
I have a specific requirement where I would like to find all unique
'IDs' of polygon geometries having a 'neighbor', that is, another
polygon bordering them (sharing a segment). For my requirements, it
doesn't actually have to be a really exact result, e.g., if 'touching'
at one point neighbors are found or not is irrelevant. I just want the
most efficient and fast way to separate out those polygons having a
'neighbor' from 'single' disjunct polygons (e.g. think terraced housing
versus stand alone buildings).
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.
This works quite well, however, especially the first step can still be
quite time consuming on very large datasets (note that there is a proper
GiST spatial index on the dataset). 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.
Or does someone potentially know a more efficient way to achieve this
CREATE TABLE <MY_TABLE2> AS SELECT
a.id AS a_id,
b.id AS b_id
<MY_TABLE1> AS a, <MY_TABLE1> AS b
a.id < b.id AND ST_DWithin(a.<GEOMETRY_COLUMN> , b.<GEOMETRY_COLUMN> , 0)
ORDER BY a.id, b.id
I then de-duplicate the ids using:
SELECT DISTINCT c.id FROM (
SELECT DISTINCT a_id AS id FROM <MY_TABLE2> a
SELECT DISTINCT b_id AS id FROM <MY_TABLE2> b) c ORDER BY c.id
which I can subsequently join back to the original table.
More information about the postgis-users