I have a map of buildings, I'm looking to identify only those buildings which are detached (no neighbours). I can run select by location touches in QGIS and invert this but it does not seem the most efficient way. Is there a PostGIS or QGIS method that will be quicker?

Edit; I'm testing this approach. https://geospatial.commons.gc.cuny.edu/2013/11/21/finding-islands-or-the-converse/


You can now use ST_ClusterDBSCAN for this purpose, with a minimum cluster size of 1 and a distance parameter of 0. This will cluster objects that are no more than a specified distance away from each other -- in this case, touching, as distance = 0 -- and, as it is a window function, you can easily get back the IDs of any geometries in each cluster as an array, using the array_agg function. Then all you need to do is select those clusters which only have one ID in the returned array that represents the ids per cluster, using the array_length function. Clusters of size one are your detached buildings.

Modifying the example query from the docs somewhat:

WITH clusters (cid, ids_in_cluster) AS ( 
 SELECT cid, array_agg(building_id) AS ids_in_cluster 
   FROM (
          ST_ClusterDBSCAN(geom, eps := 0, minpoints := 1) OVER () AS cid
        FROM some_table
       ) foo
  GROUP BY cid
SELECT cid, ids_in_cluster
  FROM clusters
 WHERE array_length(ids_in_cluster, 1) = 1;

This version of ST_ClusterDBSCAN, with distance 0, and minpoints 1, is essentially identical to ST_ClusterIntersecting, which returns a set of geometries, which makes it more painful to infer which IDs/geometries are in each cluster. As DBSCAN is a window function, it allows you more flexibility with the results.

There is nothing wrong with the approach in your question. However, as it involves a spatial self-join, it is likely to be slower than the clustering approach.

  • Have you got any comments on this. There may be a syntax error, but I have run similar queries successfully on my own datasets. – John Powell Jan 9 at 13:07
  • Many thanks John, this is for a personal project which I will hopefully get back onto this weekend post deadline – mapping dom Jan 11 at 9:57
  • No worries. I know how that feels :D – John Powell Jan 11 at 10:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.