-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathsc_correspondence_file_update_work
More file actions
127 lines (109 loc) · 4.16 KB
/
sc_correspondence_file_update_work
File metadata and controls
127 lines (109 loc) · 4.16 KB
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
## Exploratory Work
SELECT * FROM 2014_parcel_baseyear_core.cities_king_paa where city_id = 66;
SELECT * FROM 2014_parcel_baseyear_core_archive.cities_king_paa_20191107 where city_id in (66);
SELECT * FROM 2014_parcel_baseyear_core_archive.cities_20191107 where city_id in (66);
SELECT * FROM 2014_parcel_baseyear_core_archive.cities_king_paa_20191107 where city_id in (66,73,67,72,170,171,172,173);
SELECT * FROM 2014_parcel_baseyear_core_archive.cities_king_paa_20190701 order by city_id;
SELECT * FROM 2014_parcel_baseyear_core_archive.cities_king_paa_20191107 order by city_id;
SELECT * FROM 2014_parcel_baseyear_core_archive.cities_20191107 order by city_id;
create database v2050_sc_parcel_geog_corres;
## Import the Current Version of the Parcels File
create table v2050_sc_parcel_geog_corres.parcel_tract_county_orig_circa_aug2018
(
parcel_id integer not null
,census_tract bigint not null
,city_id integer not null
,county_id integer not null
,city_name varchar(50) not null
,fips_rgs_id integer not null
,rgs_id integer not null
,rg_proposed varchar(15) not null
,primary key (parcel_id)
);
LOAD DATA LOCAL INFILE 'L:/vision2050/soundcast/setup/parcel_tract_county.csv'
INTO TABLE v2050_sc_parcel_geog_corres.parcel_tract_county_orig_circa_aug2018
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(
parcel_id
,census_tract
,city_id
,county_id
,city_name
,fips_rgs_id
,rgs_id
,rg_proposed
);
SELECT
city_id
,city_name
,county_id
,rg_proposed
,count(parcel_id) as parcels
FROM v2050_sc_parcel_geog_corres.parcel_tract_county_orig_circa_aug2018
group by
city_id
,city_name
,county_id
,rg_proposed
;
## Create the SD Version - TFG, RUG and STC & the original DSEIS geographies
create table v2050_sc_parcel_geog_corres.parcel_tract_county_sd
SELECT
2014_parcel_baseyear_core_archive.parcels_king_paa_20191107.parcel_id
#,2014_parcel_baseyear_core_archive.parcels_king_paa_20191107.x_coord_sp
#,2014_parcel_baseyear_core_archive.parcels_king_paa_20191107.y_coord_sp
#,2014_parcel_baseyear_core_archive.parcels_king_paa_20191107.zone_id
,2014_parcel_baseyear_core.census_tract.geoid10 as census_tract
,2014_parcel_baseyear_core_archive.parcels_king_paa_20191107.city_id
,2014_parcel_baseyear_core_archive.cities_king_paa_20190701.county_id
,2014_parcel_baseyear_core_archive.cities_king_paa_20190701.city_name
,2014_parcel_baseyear_core_archive.cities_king_paa_20190701.fips_rgs_id
,2014_parcel_baseyear_core_archive.cities_king_paa_20190701.rgs_id
,2014_parcel_baseyear_core_archive.cities_king_paa_20190701.rg_proposed
FROM 2014_parcel_baseyear_core_archive.parcels_king_paa_20191107
Inner Join 2014_parcel_baseyear_core_archive.cities_king_paa_20190701
on 2014_parcel_baseyear_core_archive.parcels_king_paa_20191107.city_id = 2014_parcel_baseyear_core_archive.cities_king_paa_20190701.city_id
Inner join 2014_parcel_baseyear_core.census_tract
On 2014_parcel_baseyear_core_archive.parcels_king_paa_20191107.census_tract_id = 2014_parcel_baseyear_core.census_tract.census_tract_id;
SELECT
city_id
,city_name
,count(parcel_id) as parcels
,rg_proposed
FROM parcel_tract_county_sd
group by
city_id
,city_name
,rg_proposed
;
## Create the current PA Version - w Kitsap and Pierce Changes
create table v2050_sc_parcel_geog_corres.parcel_tract_county_pa
SELECT
2014_parcel_baseyear_core.parcels_king_paa.parcel_id
,2014_parcel_baseyear_core.census_tract.geoid10 as census_tract
,2014_parcel_baseyear_core.parcels_king_paa.city_id
,2014_parcel_baseyear_core.cities_king_paa.county_id
,2014_parcel_baseyear_core.cities_king_paa.city_name
,2014_parcel_baseyear_core.cities_king_paa.fips_rgs_id
,2014_parcel_baseyear_core.cities_king_paa.rgs_id
,2014_parcel_baseyear_core.cities_king_paa.rg_proposed
FROM 2014_parcel_baseyear_core.parcels_king_paa
Inner Join 2014_parcel_baseyear_core.cities_king_paa
on 2014_parcel_baseyear_core.parcels_king_paa.city_id = 2014_parcel_baseyear_core.cities_king_paa.city_id
Inner join 2014_parcel_baseyear_core.census_tract
On 2014_parcel_baseyear_core.parcels_king_paa.census_tract_id = 2014_parcel_baseyear_core.census_tract.census_tract_id
;
SELECT
city_id
,city_name
,count(parcel_id) as parcels
,rg_proposed
FROM parcel_tract_county_pa
group by
city_id
,city_name
,rg_proposed
;