Find out which polygons that are inside a surrounding polygon

Question

I have a table with a lot of polygons, some polygons are inside a surrounding polygon. I would like to set the table attribute "inside" to "1" if it's a polygon that are surrounded by another polygon AND if the attribute "name" is the same.

If a polygon is a surrounding polygon or doesn't have any "inside" polygons I would like to set "inside" to "2". The "inside" is set to "0" from the beginning.

The picture shows an example of this, I have highlighted a surrounding polygon and this polygon has the same "name" as the polygons inside. The highlighted polygon and the polygons that has no inside polygons shall be set to 2 and the polygons inside the highlighted shall be set to 1 (all the surrounded polygons have the same name as the surrounding polygon, in this example). How do I write an effective SQL command for this?

Answer 1

Run

UPDATE
  <table> AS t
SET
  inside = 2 - EXISTS (
    SELECT
      1
    FROM
      <table> AS _t
    WHERE
      t."name" = _t."name"
    AND
      ST_ContainsProperly(_t.<geom>, t.<geom>)
  )::INT
;

where

• the EXISTS returns a BOOL (that can be cast (::) to INT in range [0,1]) indicating if any other Polygon contains the current one or not
• ST_ContainsProperly is used as a cheap means to make sure that for each Polygon the containment check fails on itself (because every geometry ST_Contains itself) - should even be cheaper than PK exclusion

---

The above UPDATE sets all Polygons to inside = 2 except those that are within a Polygon of the same "name", which are set to inside = 1 instead.

I can think of more granular conditions - e.g. for Polygons that only partially overlap others with the same "name", or with a different "name", or that are completely isolated - that could make sense and where having an initial 0 is useful, but the above seems to be what you ask for.

If you never use the 0 - and the above behavior is correct - consider using [0, 1] as values instead, or use BOOL in the first place.

Thus you could set inside = 1 to those Polygons satisfying your inside condition by running:

UPDATE
  <table> AS t
SET
  inside = 1
WHERE
  EXISTS (
    SELECT
      1
    FROM
      <table> AS _t
    WHERE
      t."name" = _t."name"
    AND
      ST_ContainsProperly(_t.<geom>, t.<geom>)
  )
;

and all others would keep inside = 0; exact same result state, logically, but more in line with a BOOL condition.