· 8 years ago · Jul 03, 2018, 03:40 PM
1-- Create some indices in state plane on the census geometry
2CREATE INDEX idx_bg_geom_state_plane on geom.blockgroup USING gist (ST_Transform(geom, 102719));
3CREATE INDEX idx_tract_geom_state_plane on geom.tract USING gist (ST_Transform(geom, 102719));
4
5-- Import data from staging into final tables.
6
7-- Create table of dwelling units per blockgroup / per tract
8CREATE MATERIALIZED VIEW nbhd_correlates.dwelling_units__blockgroup__2017 AS
9SELECT bg.gid as gid, bg.geom as geom, bg.fips as fips, sum(du.sum_du) as dwelling_units FROM geom.blockgroup bg JOIN
10 staging.parcels_2018_dwellingunits du ON st_intersects(st_transform(bg.geom, 102719), du.geom)
11 group by bg.gid, bg.fips, bg.geom;
12
13-- Basic format of the query
14SELECT bg.geom as geom, bg.fips as fips, sum(du.sum_du) as r_prox_clinic FROM geom.blockgroup bg JOIN
15 (SELECT DISTINCT ON (parcels.gid) parcels.gid, parcels.sum_du as sum_du, parcels.geom as geom FROM staging.parcels_2018_dwellingunits parcels JOIN staging.clinics_532018 clinics
16 ON st_dwithin(parcels.geom, clinics.geom, 1320)) du
17 ON ST_Intersects(ST_Transform(bg.geom,102719), du.geom) group by bg.fips, bg.geom;
18
19-- Alternate form which is more easily reworked as function
20SELECT bg.geom as geom, bg.fips as fips, (SELECT sum(du.sum_du) FROM (
21 SELECT DISTINCT ON (parcels.gid) parcels.gid, parcels.sum_du as sum_du, parcels.geom as geom FROM staging.parcels_2018_dwellingunits parcels, staging.pharmacies_2018 pharmacies
22 WHERE ST_Intersects(ST_Transform(bg.geom,102719), parcels.geom) AND ST_Intersects(ST_Transform(bg.geom,102719), st_buffer(pharmacies.geom, 1320)) and st_dwithin(parcels.geom, pharmacies.geom, 1320)) du) as prox_pharmacy FROM geom.blockgroup bg;
23
24-- Copy dwelling units into their own table
25DROP TABLE IF EXISTS geom.parcels;
26CREATE TABLE geom.parcels (
27 gid SERIAL PRIMARY KEY,
28 geom geometry(geometry, 102719),
29 data_year integer,
30 parcel_id varchar(25),
31 pin varchar(20),
32 dwelling_units bigint
33);
34INSERT INTO geom.parcels(geom, data_year, parcel_id, pin, dwelling_units)
35 SELECT geom, 2018, parcel_id, pin, sum_du FROM staging.parcels_2018_dwellingunits;
36
37CREATE INDEX idx_parcels_geom on geom.parcels USING gist(geom);
38COMMENT ON TABLE geom.parcels IS 'Durham parcels dataset. Does not contain full attribute information, that information will be stored elsewhere.';
39COMMENT ON COLUMN geom.parcels.dwelling_units IS 'Number of dwelling units on this parcel.';
40
41-- Functional version of query which can be reused
42CREATE OR REPLACE FUNCTION nbhd_correlates.dwelling_units__proximity_count(poly geometry, points_tbl regclass, dist numeric, year bigint DEFAULT 2018, OUT result numeric)
43 RETURNS numeric AS $$
44 BEGIN
45 EXECUTE 'SELECT sum(du.dwelling_units) FROM ' ||
46 '(SELECT DISTINCT ON (parcels.gid) parcels.gid, parcels.dwelling_units as dwelling_units, parcels.geom as geom ' ||
47 'FROM geom.parcels parcels, ' ||
48 points_tbl || ' points WHERE points.data_year=$3 AND parcels.data_year=$3 ' ||
49 'AND ST_Intersects(ST_Transform($1, 102719), parcels.geom) ' ||
50 'AND ST_Intersects(ST_Transform($1, 102719), ST_Buffer(points.geom, $2)) ' ||
51 'AND st_dwithin(parcels.geom, points.geom, $2)) du ' ||
52 ''
53 INTO result USING poly, dist, year;
54end;
55$$ LANGUAGE plpgsql;
56
57-- Functional version of query which gives total number of dwelling units
58CREATE OR REPLACE FUNCTION nbhd_correlates.dwelling_units__count(poly geometry, year bigint DEFAULT 2018, OUT result numeric)
59 RETURNS numeric AS $$
60 BEGIN
61 EXECUTE 'SELECT sum(du.dwelling_units) FROM ' ||
62 '(SELECT * ' ||
63 'FROM geom.parcels parcels ' ||
64 'WHERE ST_Intersects(ST_Transform($1, 102719), parcels.geom) ' ||
65 'AND parcels.data_year=$2 ) du '
66 INTO result USING poly, year;
67end;
68$$ LANGUAGE plpgsql;
69
70CREATE SCHEMA point_features;
71GRANT ALL ON SCHEMA point_features TO staff;
72GRANT USAGE ON SCHEMA point_features TO public;
73GRANT ALL ON ALL TABLES IN SCHEMA point_features TO staff;
74GRANT SELECT on ALL TABLES in SCHEMA point_features to public;
75ALTER DEFAULT PRIVILEGES IN SCHEMA point_features GRANT ALL ON TABLES TO staff;
76ALTER DEFAULT PRIVILEGES IN SCHEMA point_features GRANT SELECT ON TABLES TO public;
77ALTER DEFAULT PRIVILEGES FOR ROLE tim IN SCHEMA point_features GRANT SELECT ON TABLES TO PUBLIC;
78ALTER DEFAULT PRIVILEGES FOR ROLE john IN SCHEMA point_features GRANT SELECT ON TABLES TO PUBLIC;
79
80-- Add point features from John's data
81DROP TABLE IF EXISTS point_features.clinics;
82CREATE TABLE point_features.clinics (
83 gid SERIAL PRIMARY KEY,
84 geom geometry(geometry, 102719),
85 data_year integer,
86 name varchar(254),
87 type varchar(254),
88 street_address varchar(254),
89 street_address_2 varchar(254),
90 city varchar(254),
91 state varchar(254),
92 zip bigint,
93 phone varchar(254),
94 affiliation varchar(254)
95);
96
97INSERT INTO point_features.clinics(geom, data_year, name, type, street_address, street_address_2, city, state, zip, phone, affiliation)
98SELECT
99geom, '2018', name, type_, streetadd, office, city, state, zip, phone, affiliatio
100FROM staging.clinics_532018;
101
102CREATE INDEX idx_clinics_geom ON point_features.clinics USING gist(geom);
103
104COMMENT ON TABLE point_features.clinics IS 'Locations of clinics and urgent care facilities in Durham County, as provided by ';
105COMMENT ON COLUMN point_features.clinics.type IS 'Health system which this facility is associated with, or Independent for unaffiliated clinics.';
106
107DROP TABLE IF EXISTS point_features.pharmacies;
108CREATE TABLE point_features.pharmacies (
109 gid SERIAL PRIMARY KEY,
110 geom geometry(geometry, 102719),
111 data_year integer,
112 name varchar(254),
113 street_address varchar(254),
114 city varchar(254),
115 state varchar(2),
116 zip bigint,
117 contact_name varchar(254),
118 contact_phone varchar(15),
119 contact_title varchar(254),
120 notes varchar(254),
121 naics_code numeric,
122 naics_description varchar(254),
123 sic_code numeric,
124 sic_description varchar(254)
125);
126
127INSERT INTO point_features.pharmacies(geom, data_year, name, street_address, city, state, zip, contact_name, contact_phone, contact_title, notes, naics_code, naics_description, sic_code, sic_description)
128SELECT geom, 2018, coname as name, coaddr_1 as street_address, cocity_1 as city, 'NC', cozip_1 as zip, contact_na as contact_name, contact_ph as contact_phone, title_desc as contact_title, notes1 as notes, naics_code, naics_desc, sic_code, sic_desc
129FROM staging.pharmacies_2018;
130
131CREATE INDEX idx_pharmacies_geom ON point_features.pharmacies USING gist(geom);
132
133COMMENT ON TABLE point_features.pharmacies IS 'Location of pharmacies in Durham County, as provided by ____';
134
135DROP TABLE IF EXISTS point_features.bus_stops;
136CREATE TABLE point_features.bus_stops (
137 gid SERIAL PRIMARY KEY,
138 geom geometry(geometry, 102719),
139 data_year integer,
140 name varchar(254),
141 onstreet varchar(254),
142 atstreet varchar(254),
143 city varchar(254),
144 state varchar(10),
145 zip varchar(15),
146 system varchar(100),
147 bus_line_id bigint,
148 bus_line_number varchar(25),
149 bus_line_name varchar(254),
150 has_bench boolean,
151 has_shelter boolean,
152 has_lighting boolean,
153 has_garbage_can boolean,
154 has_telephone boolean,
155 has_bicycle_rack boolean
156);
157
158INSERT INTO point_features.bus_stops(geom, data_year, name, onstreet, atstreet, city, state, zip, system, bus_line_id, bus_line_number, bus_line_name, has_bench, has_shelter, has_lighting, has_garbage_can, has_telephone, has_bicycle_rack)
159SELECT geom, 2018, stopname as name, onstreet, atstreet, city, state, zipcode as zip, 'GoDurham'::varchar(100) as system, lineid as bus_line_id, lineabbr as bus_line_number, linename as bus_line_name,
160 bench::boolean as has_bench, shelter::boolean as has_shelter, lighting::boolean as has_lighting, garbage::boolean as has_garbage_can,
161 telephone::boolean as has_telephone, bicycle::boolean as has_bicycle_rack
162FROM staging.godurham_stops20180201;
163
164CREATE INDEX idx_bus_stops_geom ON point_features.bus_stops USING gist(geom);
165
166INSERT INTO point_features.bus_stops(data_year, geom, name, onstreet, atstreet, city, state, zip, system, bus_line_id, bus_line_number, bus_line_name, has_bench, has_shelter, has_lighting, has_garbage_can, has_telephone, has_bicycle_rack)
167 SELECT 2018, geom, stopname, onstreet, atstreet, city, state, zipcode, 'GoTriangle', lineid, lineabbr, linename, bench::boolean, shelter::boolean, lighting::boolean, garbage::boolean, telephone::boolean, bicycle::boolean FROM staging.gotriangle_stops20180201;
168
169-- Add function to substitute zero for null values
170CREATE OR REPLACE FUNCTION numval_or_zero(v_input numeric)
171RETURNS numeric AS $$
172BEGIN
173IF v_input IS NULL THEN
174RETURN 0;
175ELSE
176RETURN v_input;
177END IF;
178END;
179$$ LANGUAGE plpgsql IMMUTABLE;
180
181DROP MATERIALIZED VIEW IF EXISTS nbhd_correlates.healthcare_access__tract__2018;
182CREATE MATERIALIZED VIEW nbhd_correlates.healthcare_access__tract__2018 AS
183SELECT gid, geom, fips, numval_or_zero(clinic_proximity)/nullif(du,0) as Clinic_Proximity, numval_or_zero(bus_stop_proximity)/nullif(du,0) as Bus_Stop_Proximity, numval_or_zero(pharmacy_proximity)/nullif(du, 0) AS Pharmacy_Proximity FROM
184 (SELECT gid, geom, fips,
185 nbhd_correlates.dwelling_units__count(geom) as du,
186 nbhd_correlates.dwelling_units__proximity_count(geom, 'point_features.clinics', 1320) as clinic_proximity,
187 nbhd_correlates.dwelling_units__proximity_count(geom, 'point_features.bus_stops', 1320) as bus_stop_proximity,
188 nbhd_correlates.dwelling_units__proximity_count(geom, 'point_features.pharmacies', 1320) as pharmacy_proximity
189 FROM geom.tract) AS c;
190
191DROP TABLE IF EXISTS point_features.epa_registered_air_emitters;
192CREATE TABLE point_features.epa_registered_air_emitters (
193 gid SERIAL PRIMARY KEY,
194 geom geometry(geometry, 102719),
195 data_year integer,
196 frs_id numeric,
197 name varchar(254),
198 street_address varchar(254),
199 city varchar(254),
200 state varchar(2),
201 zip varchar(15),
202 site_type varchar(50),
203 epa_programs varchar(254),
204 epa_interests varchar(254),
205 naics_codes varchar(254),
206 naics_descriptions varchar(254),
207 sic_codes varchar(254),
208 sic_descriptions varchar(254),
209 date_created date,
210 date_updated date
211);
212INSERT INTO point_features.epa_registered_air_emitters(geom, data_year, frs_id, name, street_address, city, state, zip, site_type, epa_programs, epa_interests, naics_codes, naics_descriptions, sic_codes, sic_descriptions, date_created, date_updated)
213 SELECT geom, 2018, registry_i, primary_na, location_a, city_name, state_code, postal_cod, site_type_, pgm_sys_ac, interest_t, naics_code, naics_co_1, sic_codes, sic_code_d, create_dat, update_dat
214FROM staging.durhamcounty_air_epa_2018 WHERE site_type_ <> 'MONITORING STATION';
215CREATE INDEX idx_epa_registered_air_emitters_geom ON point_features.epa_registered_air_emitters USING gist(geom);
216
217COMMENT ON TABLE point_features.epa_registered_air_emitters IS 'FRS-registered facilities reporting air emissions according to EPA dataset';
218COMMENT ON COLUMN point_features.epa_registered_air_emitters.date_created IS 'created_date from EPA source data';
219COMMENT ON COLUMN point_features.epa_registered_air_emitters.date_updated IS 'updated_date from EPA source data';
220
221DROP MATERIALIZED VIEW IF EXISTS nbhd_correlates.environmental_stress__tract__2018;
222CREATE MATERIALIZED VIEW nbhd_correlates.environmental_stress__tract__2018 AS
223SELECT gid, geom, fips, numval_or_zero(air_proximity)/nullif(du,0) as Air_Polluter_Proximity FROM
224 (SELECT gid, geom, fips,
225 nbhd_correlates.dwelling_units__count(geom) as du,
226 nbhd_correlates.dwelling_units__proximity_count(geom, 'point_features.epa_registered_air_emitters', 2640) as air_proximity
227 FROM geom.tract) AS c;
228
229CREATE VIEW staging.air_polluter_proximity AS SELECT gid, geom, fips,
230 nbhd_correlates.dwelling_units__count(geom) as du,
231 nbhd_correlates.dwelling_units__proximity_count(geom, 'point_features.epa_registered_air_emitters', 2640) as air_proximity
232 FROM geom.tract;