QGIS – How to Select Polygons Within Polygons in the Same Layer

polygonpostgispostgresqlqgistopology

I have a buildings layer that actually has a flaw. The inner courtyards, drawn as another polygon within the building polygon, should be islands of the building polygons. Instead, they are other polygons inside the buildings polygon (and reside in the same layer).

enter image description here

If I use the geoprocessing tools like Intersect, they don't work well because it also intersects each building with the building itself and not the building just with the inner courtyard.

For the intersection to work correctly, I should select only the polygons that are inside other polygons (inner courtyards) and when using the Intersection tool on the overlay layer check the "Selected objects only" box. This way it works in the tests I've done.

enter image description here

My problem is how to select the polygons (inner courtyards) that are inside other polygons without doing it manually because I have more then 8000 buildings…

I've tried using the "Select by location" tool but always selects all the polygons for the same reason explained before. Same thing happens to me using Postgis functions like ST_Contains or ST_Within…

Best Answer

To SELECT

  • those that are within others (courtyards): 
    SELECT a.*
FROM   <buildings> AS a
WHERE  EXISTS (
  SELECT 1
  FROM   <buildings> AS b
  WHERE  a.<id> <> b.<id>
    AND  ST_Within(a.geom, b.geom)
);
  • those that contain others (houses): 
    SELECT a.*
FROM   <buildings> AS a
WHERE  EXISTS (
  SELECT 1
  FROM   <buildings> AS b
  WHERE  a.<id> <> b.<id>
    AND  ST_Contains(a.geom, b.geom)
);

Note that the EXISTS construct is slightly more performant and more elegant than a JOIN in cases where multiple courtyards exists for a single house.

To UPDATE those that contain others with the footprint of those contained (subtract courtyards from houses; produce Polygons with inner rings):

UPDATE <houses> AS a
  SET geom = (
    SELECT ST_Difference(a.geom, ST_Union(b.geom))
    FROM   <houses> AS b
    WHERE  a.<id> <> b.<id>
      AND  ST_Contains(a.geom, b.geom)
  )
WHERE  EXISTS (
  SELECT 1
  FROM   <buildings> AS b
  WHERE  a.<id> <> b.<id>
    AND  ST_Contains(a.geom, b.geom)
);

Oviously, this will alter data in your table - make sure that is what you want. Note that this fails in cases where the difference results in a multi-part Polygon - this would need to get handled either by changing the geom column definition, or by deleting the originals and reinserting single-part Polygons from a similar query.

To DELETE those that are contained by others (courtyards):

DELETE  --test with SELECT *
FROM   <houses> AS a
WHERE  EXISTS (
  SELECT 1
  FROM   <buildings> AS b
  WHERE  a.<id> <> b.<id>
    AND  ST_Within(a.geom, b.geom)
);

Obviously, this will erase data from your table - so make sure that is what you want before running this query; test a DELETE by running a SELECT * instead and make sure the resulting rows are what you intend to delete!

Finally, to CREATE a new TABLE with only those that contain others, with the footprint of those contained removed:

CREATE TABLE <houses_with_holes> AS (
  SELECT a.<id>,
         a.<column_1>,
         ...
         a.<column_n>,
         ST_SetSRID(ST_Difference(a.geom, ST_Union(b.geom)), <SRID>)::GEOMETRY(POLYGON, <SRID>) AS geom
  FROM   <houses> AS a
  JOIN   <houses> AS b
    ON   a.<id> <> b.<id> AND ST_Contains(a.geom, b.geom)
  GROUP BY
         1, 2, ..., n
);

This is probably the best way to test the outcome. This query can also get improved to handle cases where the difference results in multi-part Polygons.

Related Question