Finding holes in proposed districts (Polygons/Multipolygons)

Question

Thinking through something that I seek some SQL on:

One of the ideas in redistricting is that a proposed district boundary should have no holes in it, i.e., a chunk of land with no district_id attached to it, where each chunk is made up of a tabulation_block. Each row in the proposed_districts table would have a pid, district_id, geom , with the geometry created to represent the UNION of every tabulation_block (separate table) assigned a district_id.

There are two things that I am looking for in a SQL query:

1. Tabulation blocks whose geometries in the district do not have a district value assigned
2. Naturally existing interior rings.

I have appended the DESCRIBE TABLE results for the two tables, tab blocks and a district. I suspect that the latter involves something involving ST_DUMPRINGS(), but I cannot quite put it together.

Intended result: Table with each row

primary_key, district_id, geom_of_hole

   Column   |            Type             | Collation | Nullable |                      Default
------------+-----------------------------+-----------+----------+---------------------------------------------------
 gid        | integer                     |           | not null | nextval('rapis_five_districts_gid_seq'::regclass)
 district   | character varying(50)       |           |          |
 population | integer                     |           |          |
 geom       | geometry(MultiPolygon,4269) |           |          |
Indexes:
    "rapis_five_districts_pkey" PRIMARY KEY, btree (gid)
    "rapis_five_districts_geom_idx" gist (geom)```


```                                         Table "public.tl_2019_06_tabblock10"
   Column   |            Type             | Collation | Nullable |                      Default
------------+-----------------------------+-----------+----------+----------------------------------------------------
 gid        | integer                     |           | not null | nextval('tl_2019_06_tabblock10_gid_seq'::regclass)
 statefp10  | character varying(2)        |           |          |
 countyfp10 | character varying(3)        |           |          |
 tractce10  | character varying(6)        |           |          |
 blockce10  | character varying(4)        |           |          |
 geoid10    | character varying(15)       |           |          |
 name10     | character varying(10)       |           |          |
 mtfcc10    | character varying(5)        |           |          |
 ur10       | character varying(1)        |           |          |
 uace10     | character varying(5)        |           |          |
 uatype     | character varying(1)        |           |          |
 funcstat10 | character varying(1)        |           |          |
 aland10    | double precision            |           |          |
 awater10   | double precision            |           |          |
 intptlat10 | character varying(11)       |           |          |
 intptlon10 | character varying(12)       |           |          |
 geom       | geometry(MultiPolygon,4269) |           |          |
Indexes:
    "tl_2019_06_tabblock10_pkey" PRIMARY KEY, btree (gid)
    "tl_2019_06_tabblock10_geom_idx" gist (geom)```


  [1]: https://postgis.net/docs/ST_DumpRings.html

Answer 1

This SQL will find polygonal geometries (Polygons OR MultiPolygons) which contain holes from geometries in a table:

SELECT geom FROM data WHERE ST_NRings(geom) - ST_NumGeometries(geom) > 0;

There's a another approach given in the doc for ST_NumInteriorRings, but it's more complicated.

Answer 2

Not the complete answer, I'm sure, but If I was looking for school boundary polygons that had multiple parts, and which of our planning regions they intersected, I would use this SQL:

select
    e.elem_name
    , r.region
    , st_numgeometries(e.geom) as number_of_parts
from
    dpsdata.schoolboundaries_elem as e
join dpsdata."Regions" as r on
    ST_Intersects(ST_Centroid(e.geom)
    , r.geom)
where
    1 = 1
    and st_numgeometries(e.geom) > 1

I've used ST_Numgeometries in a few spots. Again, not sure if this solves your issue, but some ideas of how to use spatial functions on a problem like this.