Select polygons within polygons in same layer

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.

I tested this and it seems to work. And it is probably is not the most efficient way of accomplishing this task.

SELECT geo_a.* from "public"."footprint" AS geo_a JOIN "public"."footprint" AS geo_b ON st_within(geo_a.geom, geo_b.geom) WHERE st_area(geo_a.geom) < st_area(geo_b.geom);

Essentially, do a self join on features that are within one another (a binary TRUE/FALSE). Finally filtering where the area of the contained feature (geo_a) is less than the containing feature (geo_b).

The contained feature has to be entirely with the containing feature, and all nested features will be still selected.

If this doesn't exactly get you what you need, hopefully it will get you started.

Another solution might be to create a topology and avoid having polygons with in polygons, but this is probably not your data.