2

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
3

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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