[postgis-users] ST_DWithin question

[Marco Boeringa]

Hi all,

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 
specific result?

CREATE TABLE <MY_TABLE2> AS SELECT
  a.id AS a_id,
  b.id AS b_id
FROM
  <MY_TABLE1> AS a, <MY_TABLE1> AS b
WHERE
  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
   UNION ALL
   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.

Marco