MySQL_Scripts/data_request_hopelink_sept18 at master · msimonson-psrc/MySQL_Scripts · GitHub

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
reate table data_request_hopelink_j_fredrickson.snoqualmie_buffer_prcl
(
city_name text
,parcel_id integer not null
,primary key (parcel_id)
);

LOAD DATA LOCAL INFILE 'J:/Projects/LandUseVision/LUV.2/Final/Requests/hopelink_j_fredrickson_sept2018/snoqualmie_buffer_prcl.csv'
INTO TABLE data_request_hopelink_j_fredrickson.snoqualmie_buffer_prcl
FIELDS TERMINATED BY ','
#ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
city_name
,parcel_id
);

create table data_request_hopelink_j_fredrickson.snoqualmie_prcl
(
city_name text
,parcel_id integer not null
,primary key (parcel_id)
);

LOAD DATA LOCAL INFILE 'J:/Projects/LandUseVision/LUV.2/Final/Requests/hopelink_j_fredrickson_sept2018/snoqualmie_prcl.csv'
INTO TABLE data_request_hopelink_j_fredrickson.snoqualmie_prcl
FIELDS TERMINATED BY ','
#ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
city_name
,parcel_id
);


alter table luv2_final_outputs_2014.households add column parcel_id integer;
update luv2_final_outputs_2014.households set parcel_id = 0;
alter table luv2_final_outputs_2014.households add index(building_id);
update luv2_final_outputs_2014.households inner join luv2_final_outputs_2014.buildings
on luv2_final_outputs_2014.households.building_id = luv2_final_outputs_2014.buildings.building_id
set luv2_final_outputs_2014.households.parcel_id = luv2_final_outputs_2014.buildings.parcel_id;

alter table luv2_final_outputs_2020.households add column parcel_id integer;
update luv2_final_outputs_2020.households set parcel_id = 0;
alter table luv2_final_outputs_2020.households add index(building_id);
update luv2_final_outputs_2020.households inner join luv2_final_outputs_2020.buildings
on luv2_final_outputs_2020.households.building_id = luv2_final_outputs_2020.buildings.building_id
set luv2_final_outputs_2020.households.parcel_id = luv2_final_outputs_2020.buildings.parcel_id;

alter table luv2_final_outputs_2025.households add column parcel_id integer;
update luv2_final_outputs_2025.households set parcel_id = 0;
alter table luv2_final_outputs_2025.households add index(building_id);
update luv2_final_outputs_2025.households inner join luv2_final_outputs_2025.buildings
on luv2_final_outputs_2025.households.building_id = luv2_final_outputs_2025.buildings.building_id
set luv2_final_outputs_2025.households.parcel_id = luv2_final_outputs_2025.buildings.parcel_id;


##  Not all geographies, just a template I kept re-using

select
count(parcel_id) from
data_request_hopelink_j_fredrickson.snoqualmie_prcl;

select
count(luv2_final_outputs_2014.households.household_id) as households
,sum(luv2_final_outputs_2014.households.persons) as persons
from luv2_final_outputs_2014.households
inner join data_request_hopelink_j_fredrickson.snoqualmie_prcl
on luv2_final_outputs_2014.households.parcel_id = data_request_hopelink_j_fredrickson.snoqualmie_prcl.parcel_id;

select
count(luv2_final_outputs_2020.households.household_id) as households
,sum(luv2_final_outputs_2020.households.persons) as persons
from luv2_final_outputs_2020.households
inner join data_request_hopelink_j_fredrickson.snoqualmie_prcl
on luv2_final_outputs_2020.households.parcel_id = data_request_hopelink_j_fredrickson.snoqualmie_prcl.parcel_id;

select
count(luv2_final_outputs_2025.households.household_id) as households
,sum(luv2_final_outputs_2025.households.persons) as persons
from luv2_final_outputs_2025.households
inner join data_request_hopelink_j_fredrickson.snoqualmie_prcl
on luv2_final_outputs_2025.households.parcel_id = data_request_hopelink_j_fredrickson.snoqualmie_prcl.parcel_id;


select
count(parcel_id) from
data_request_hopelink_j_fredrickson.snoqualmie_buffer_prcl;

select
count(luv2_final_outputs_2014.households.household_id) as households
,sum(luv2_final_outputs_2014.households.persons) as persons
from luv2_final_outputs_2014.households
inner join data_request_hopelink_j_fredrickson.snoqualmie_buffer_prcl
on luv2_final_outputs_2014.households.parcel_id = data_request_hopelink_j_fredrickson.snoqualmie_buffer_prcl.parcel_id;

select
count(luv2_final_outputs_2020.households.household_id) as households
,sum(luv2_final_outputs_2020.households.persons) as persons
from luv2_final_outputs_2020.households
inner join data_request_hopelink_j_fredrickson.snoqualmie_buffer_prcl
on luv2_final_outputs_2020.households.parcel_id = data_request_hopelink_j_fredrickson.snoqualmie_buffer_prcl.parcel_id;

select
count(luv2_final_outputs_2025.households.household_id) as households
,sum(luv2_final_outputs_2025.households.persons) as persons
from luv2_final_outputs_2025.households
inner join data_request_hopelink_j_fredrickson.snoqualmie_buffer_prcl
on luv2_final_outputs_2025.households.parcel_id = data_request_hopelink_j_fredrickson.snoqualmie_buffer_prcl.parcel_id;

select
count(parcel_id) from
data_request_hopelink_j_fredrickson.carnation_prcl;

select
count(luv2_final_outputs_2014.households.household_id) as households
,sum(luv2_final_outputs_2014.households.persons) as persons
from luv2_final_outputs_2014.households
inner join data_request_hopelink_j_fredrickson.carnation_prcl
on luv2_final_outputs_2014.households.parcel_id = data_request_hopelink_j_fredrickson.carnation_prcl.parcel_id;

select
count(luv2_final_outputs_2020.households.household_id) as households
,sum(luv2_final_outputs_2020.households.persons) as persons
from luv2_final_outputs_2020.households
inner join data_request_hopelink_j_fredrickson.carnation_prcl
on luv2_final_outputs_2020.households.parcel_id = data_request_hopelink_j_fredrickson.carnation_prcl.parcel_id;

select
count(luv2_final_outputs_2025.households.household_id) as households
,sum(luv2_final_outputs_2025.households.persons) as persons
from luv2_final_outputs_2025.households
inner join data_request_hopelink_j_fredrickson.carnation_prcl
on luv2_final_outputs_2025.households.parcel_id = data_request_hopelink_j_fredrickson.carnation_prcl.parcel_id;