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:
- Tabulation blocks whose geometries in the district do not have a
district
value assigned - 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