-
Notifications
You must be signed in to change notification settings - Fork 2
/
Makefile
146 lines (124 loc) · 4.97 KB
/
Makefile
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
#$@ is the file name of the target of the rule.
#example get from s3:aws s3 cp s3://landuse/zoning/match_fields_tables_zoning_2012_source.csv match_fields_tables_zoning_2012_source.csv
get = aws s3 cp s3://landuse/zoning/
get_zoning = aws s3 cp s3://zoning-sf-bay-area/
DBUSERNAME=vagrant
DBPASSWORD=vagrant
DBHOST=localhost
DBPORT=5432
DBNAME=mtc
psql = PGPASSWORD=vagrant psql -p $(DBPORT) -h $(DBHOST) -U $(DBUSERNAME) $(DBNAME)
shp2pgsql = shp2pgsql -t 2D -s 26910 -I
#########################
#####need to review######
#########################
load_zoning_code_additions:
$(psql) -f load/add_missing_codes.sql
fix_string_matching_in_zoning_table: match_fields_tables_zoning_2012_source.csv
$(psql) -f load/zoning_codes_fix_string_matching.sql
###########################
####Join Parcels/Zoning####
###########################
zoning_parcels.csv: zoning_files \
county10_ca.shp \
city10_ba.shp \
parcels_sql_dump
#load postgis extensions
$(psql) -f functions/zoning_functions.sql
$(psql) -f functions/postgis_addons.sql
#load generic zoning assignment table
$(psql) -f load/load-generic-zoning-code-table.sql
#load and assign adminstrative areas to parcels
$(psql) -c "DROP SCHEMA if exists admin_staging CASCADE;"
$(psql) -c "CREATE SCHEMA admin_staging;"
$(shp2pgsql) city10_ba.shp admin_staging.city10_ba | $(psql)
$(shp2pgsql) county10_ca.shp admin_staging.county10_ca | $(psql)
$(psql) -f load/load-zoning-shapefile-metadata.sql
$(psql) -f process/create_jurisdictional_table.sql
$(psql) -f process/assign_city_name_by_county.sql
#load zoning source data shapefiles from 2012
$(psql) -c "DROP SCHEMA IF EXISTS zoning_2012_staging CASCADE"
$(psql) -c "CREATE SCHEMA zoning_2012_staging"
ls jurisdictional/*.shp | cut -d "/" -f2 | sed 's/.shp//' |
xargs -I {} $(shp2pgsql) jurisdictional/{} zoning_2012_staging.{} | $(psql)
#FIX for Napa
$(psql) -c "CREATE TABLE zoning_2012_staging.napacozoning_temp AS SELECT zoning, geom from zoning_2012_staging.napacozoning;"
$(psql) -c "DROP TABLE zoning_2012_staging.napacozoning;"
$(psql) -c "CREATE TABLE zoning_2012_staging.napacozoning AS SELECT * from zoning_2012_staging.napacozoning_temp;"
$(psql) -c "DROP TABLE zoning_2012_staging.napacozoning_temp;"
#FIX for Solano
$(psql) -c "CREATE TABLE zoning_2012_staging.solcogeneral_plan_unincorporated_temp AS SELECT full_name, geom from zoning_2012_staging.solcogeneral_plan_unincorporated;"
$(psql) -c "DROP TABLE zoning_2012_staging.solcogeneral_plan_unincorporated;"
$(psql) -c "CREATE TABLE zoning_2012_staging.solcogeneral_plan_unincorporated AS SELECT * from zoning_2012_staging.solcogeneral_plan_unincorporated_temp;"
$(psql) -c "DROP TABLE zoning_2012_staging.solcogeneral_plan_unincorporated_temp;"
#do 2012 assignment by city
$(psql) -c "SELECT fix_2012_geoms(TRUE);"
$(psql) -c "DROP SCHEMA IF EXISTS zoning_2012_parcel_overlaps CASCADE;"
$(psql) -c "CREATE SCHEMA zoning_2012_parcel_overlaps;"
$(psql) -c "SELECT overlap_2012(TRUE);"
#assign the 2012 zoning data to parcels
$(psql) -f process/assign_2012_zoning_to_parcels.sql
#load zoning source data shapefile from 2006
$(psql) -c "DROP TABLE IF EXISTS zoning.plu06_may2015estimate;"
$(shp2pgsql) data/plu06_may2015estimate.shp zoning.plu06_may2015estimate | $(psql)
$(psql) -f load/add-plu-2006.sql
#clean and homogenize geometries
$(psql) -f process/clean_plu06_geoms.sql
#intersection with 2006:
$(psql) -f process/create_intersection_table_plu06.sql
#output
$(psql) -f output/summarize.sql
python output/fix_zoning_missing_id.py
$(psql) mtc -f output/summarize.sql
bash output/backup_db.sh
bash output/write_db_to_s3.sh
$(psql) -c "\COPY zoning.parcel to 'zoning_parcels_no_dev_as_zero.csv' DELIMITER ',' CSV HEADER;"
##############
##############
####FILES!####
##############
##############
##############
###PARCELS!###
##############
#whats the best way to handle this?
#previously a sql dump, but not sure thats best?
##############
###ZONING!####
##############
legacy_tablenames := $(shell sed 1,1d data/zoning_source_metadata.csv | cut -d ',' -f2 | tr '\n' ' ')
zip_targets = $(addprefix jurisdictional/, $(addsuffix .shp.zip, $(legacy_tablenames)))
shp_targets = $(addprefix jurisdictional/, $(addsuffix .shp, $(legacy_tablenames)))
zoning_files: zoning_files_2012 zoning_file_2006
zoning_files_2012: $(shp_targets)
zoning_file_2006: data/plu06_may2015estimate.shp
data/plu06_may2015estimate.shp: data/plu06_may2015estimate.zip
unzip -o $<
touch $@
$(shp_targets): $(zip_targets)
unzip -d jurisdictional -o [email protected]
touch $@
$(zip_targets):
$(get_zoning)$(@F) \
mv [email protected] $@
#############################
####ADMINISTRATIVE DATA!#####
#############################
county10_ca.shp: county10_ca.zip
unzip -o $<
touch $@
city10_ba.shp: city10_ba.zip
unzip -o $<
city10_ba.zip:
$(get)city10_ba.zip \
mv [email protected] $@
county10_ca.zip:
$(get)county10_ca.zip \
mv [email protected] $@
data/plu06_may2015estimate.zip:
$(get)plu06_may2015estimate.zip \
mv [email protected] $@