5

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...

4
  • 2
    do they have different ids? if so some thing like a.id!=b.id and st_within(a.geom. b.geom) should work
    – Ian Turton
    Jan 19, 2022 at 14:11
  • 2
    use select by expression with overlay_within(@layer)
    – LM10
    Jan 19, 2022 at 14:16
  • @LM10 you should post that as an answer
    – BERA
    Jan 19, 2022 at 18:19
  • When you say you want the courtyards to be islands do you mean removed or just cut into and part of the building layer? If you want to remove them you could run either the SAGA Polygon self intersection tool (which works differently than the standard intersection) or the GRASS v.clean tool, followed by the Delete Duplicate Geometries tool.
    – John
    Jan 26, 2022 at 18:01

3 Answers 3

9

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.

7

Use "Select by expression" with overlay_within(@layer).

3

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.

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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