· 6 years ago · Jan 16, 2020, 03:49 AM
1/*
2This is a file that measures how two sets of boundaries compare with each other:
3 a. ESTIMATION/CREATION (California, state FIPS = 6)
4 b. REFERENCE
5 - US Congress
6 - US Counties
7 - US SLDL
8 - US SLDU
9 - US Tab-Blocks
10 - US ZCTA
11 - US State
12 - PDI precincts
13 c. Shows
14 - Containment overlaps & Ratios https://gis.stackexchange.com/questions/289558/finding-combinations-of-polygons-in-postgis
15 - Non-containment overlaps & ratios https://gis.stackexchange.com/questions/187406/how-to-use-st-difference-and-st-intersection-in-case-of-multipolygons-postgis
16 d. Unassigned/Holes
17 - With respect to all of the above named districts :https://gis.stackexchange.com/questions/133134/how-to-detect-gaps-in-a-multipolygon-table-with-postgis
18 - containments and non-containment intersections and ratios for where the holes are, e.g., how much of the district is unsassigned
19 - holes should be defined in two ways
20 - a space where the shapefile itself is NULL, such as a lake in the middle of a park
21 - a space where the equivalent CONSTRUCTION_UNIT (tabulation_block, block_group, census_tract) is not assigned a value for DISTRICT in the plan table
22*/
23
24-- STEP 0: confirm everything is in EPSG: 4269
25
26ALTER TABLE ca_five_districts
27 ALTER COLUMN geom
28 TYPE Geometry(MULTIPOLYGON, 4269)
29 USING ST_Transform(geom, 4269);
30
31ALTER TABLE tl_2019_us_cd_116
32 ALTER COLUMN geom
33 TYPE Geometry(MULTIPOLYGON, 4269)
34 USING ST_Transform(geom, 4269);
35
36
37-- this should be the intersections and it will have the area ranks from each of the composing tables
38-- https://gis.stackexchange.com/questions/289558/finding-combinations-of-polygons-in-postgis
39
40
41-- 1a Congressional Districts
42
43DROP TABLE IF EXISTS
44 plan_cong_all_int_src;
45
46CREATE TABLE plan_cong_all_int_src AS (
47 SELECT ROW_NUMBER() OVER () AS pid,
48 plan.gid AS plan_id,
49 census.gid as cong_id,
50 plan.district AS plan_name,
51 census.namelsad AS cong_name,
52 plan.geom AS plan_geom,
53 census.geom AS cong_geom,
54 ST_MULTI(ST_BUFFER(ST_INTERSECTION(plan.geom, census.geom), 0.0)) AS intersection_geom
55 FROM ca_five_districts AS plan
56 INNER JOIN tl_2019_us_cd_116 AS census
57 ON (ST_INTERSECTS(plan.geom, census.geom))
58 WHERE NOT ST_ISEMPTY(ST_BUFFER(ST_INTERSECTION(plan.geom, census.geom), 0.0)));
59
60CREATE INDEX pln_cong_sdx
61 ON
62 plan_cong_all_int_src
63 USING GIST (intersection_geom);
64
65DROP TABLE IF EXISTS plan_cong_all_int_rep;
66
67CREATE TABLE plan_cong_all_int_rep AS
68 (SELECT src.pid AS pid,
69 src.plan_id AS plan_id,
70 src.cong_id AS cong_id,
71 src.plan_name || ' - ' || src.cong_name AS composing_districts,
72 ST_AREA(src.intersection_geom) / ST_AREA(src.plan_geom) AS overlap_ratio_for_plan,
73 ST_AREA(src.intersection_geom) / ST_AREA(src.cong_geom) AS overlap_ratio_for_congress,
74 src.intersection_geom AS geom
75 FROM plan_cong_all_int_src src);
76
77DROP INDEX IF EXISTS
78 pln_cng_all_int_sdx;
79
80CREATE INDEX IF NOT EXISTS
81 pln_cng_all_int_sdx
82ON
83 plan_cong_all_int_rep
84 USING GIST(geom);
85
86-- Get all parts of the districts that aren't contained by the intersecting CD
87
88DROP TABLE IF EXISTS
89 plan_cong_noncntned_int_src;
90
91CREATE TABLE plan_cong_noncntned_int_src AS (
92 SELECT ROW_NUMBER() OVER () AS pid,
93 plan.gid AS plan_id,
94 census.gid as cong_id,
95 plan.district AS plan_name,
96 census.namelsad AS cong_name,
97 plan.geom AS plan_geom,
98 census.geom AS cong_geom,
99 ST_MULTI(ST_BUFFER(ST_INTERSECTION(plan.geom, census.geom), 0.0)) AS intersection_geom
100 FROM ca_five_districts AS plan
101 INNER JOIN tl_2019_us_cd_116 AS census
102 ON (ST_INTERSECTS(plan.geom, census.geom))
103 WHERE NOT ST_ISEMPTY(ST_BUFFER(ST_INTERSECTION(plan.geom, census.geom), 0.0)));
104
105CREATE INDEX pln_cong_noncntned_sdx
106 ON
107 plan_cong_noncntned_int_src
108 USING GIST (intersection_geom);
109
110DROP TABLE IF EXISTS plan_cong_noncntned_int_rep;
111
112CREATE TABLE plan_cong_noncntned_int_rep AS
113 (SELECT src.pid AS pid,
114 src.plan_id AS plan_id,
115 src.cong_id AS cong_id,
116 src.plan_name || ' - ' || src.cong_name AS composing_districts,
117 ST_AREA(src.intersection_geom) / ST_AREA(src.plan_geom) AS overlap_ratio_for_plan,
118 ST_AREA(src.intersection_geom) / ST_AREA(src.cong_geom) AS overlap_ratio_for_congress,
119 src.intersection_geom AS geom
120 FROM plan_cong_noncntned_int_src src);
121
122DROP INDEX IF EXISTS
123 pln_cong_noncntned_rep_sdx;
124
125CREATE INDEX IF NOT EXISTS
126 pln_cong_noncntned_rep_sdx
127ON
128 plan_cong_noncntned_int_rep
129 USING GIST(geom);
130
131
132-- GET ALL TAB BLOCKS THAT THE INTERSECTION CONTAINS AND AREAS