MySQL_Scripts/data_request_bhc_clearview_mar22 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
138
139
140
141
142
143
144
145
146
147
create database data_request_bhc_clearviewwsa_tittelfitz_20220311;

## LUV2 Parcels

drop table if exists data_request_bhc_clearviewwsa_tittelfitz_20220311.luv2_parcels_in_sa;

create table data_request_bhc_clearviewwsa_tittelfitz_20220311.luv2_parcels_in_sa
(
parcel_id integer not null
,x_coord_sp integer not null
,y_coord_sp integer not null
,descript varchar(30) not null
,clearview_id integer not null
,primary key (parcel_id)
);

LOAD DATA LOCAL INFILE 'J:/Projects/LandUseVision/LUV.2/Final/Requests/BHC_ClearviewWSA_Tittelfitz_Mar2022/luv2_parcels_in_sa.csv'
INTO TABLE data_request_bhc_clearviewwsa_tittelfitz_20220311.luv2_parcels_in_sa
FIELDS TERMINATED BY ','
#ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(
parcel_id
,x_coord_sp
,y_coord_sp
,descript
,clearview_id
);

select * from data_request_bhc_clearviewwsa_tittelfitz_20220311.luv2_parcels_in_sa;

## RGS parcels

drop table if exists data_request_bhc_clearviewwsa_tittelfitz_20220311.rgs_parcels_in_sa;

create table data_request_bhc_clearviewwsa_tittelfitz_20220311.rgs_parcels_in_sa
(
parcel_id integer not null
,x_coord_sp integer not null
,y_coord_sp integer not null
,descript varchar(30) not null
,clearview_id integer not null
,primary key (parcel_id)
);

LOAD DATA LOCAL INFILE 'J:/Projects/LandUseVision/LUV.2/Final/Requests/BHC_ClearviewWSA_Tittelfitz_Mar2022/rgs_parcels_in_sa.csv'
INTO TABLE data_request_bhc_clearviewwsa_tittelfitz_20220311.rgs_parcels_in_sa
FIELDS TERMINATED BY ','
#ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(
parcel_id
,x_coord_sp
,y_coord_sp
,descript
,clearview_id
);

select * from data_request_bhc_clearviewwsa_tittelfitz_20220311.rgs_parcels_in_sa;


####
# vv_basin_id	vv_basin_name	vv_major_basin	vv_basin_full_class
####

drop table if exists data_request_bhc_clearviewwsa_tittelfitz_20220311.clearview;


create table data_request_bhc_clearviewwsa_tittelfitz_20220311.clearview
(
clearview_id integer not null
,descript varchar(30) not null
,primary key (clearview_id)
);

LOAD DATA LOCAL INFILE 'J:/Projects/LandUseVision/LUV.2/Final/Requests/BHC_ClearviewWSA_Tittelfitz_Mar2022/clearview.csv'
INTO TABLE data_request_bhc_clearviewwsa_tittelfitz_20220311.clearview
FIELDS TERMINATED BY ','
#ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(
clearview_id
,descript
);

select * from data_request_bhc_clearviewwsa_tittelfitz_20220311.clearview;

## LUV2 parcel combine

drop table if exists data_request_bhc_clearviewwsa_tittelfitz_20220311.luv2_parcels_w_sa;

create table data_request_bhc_clearviewwsa_tittelfitz_20220311.luv2_parcels_w_sa
SELECT * FROM luv2_lodes_final_outputs_2014.parcels;

alter table data_request_bhc_clearviewwsa_tittelfitz_20220311.luv2_parcels_w_sa
add primary key(parcel_id)
,add column clearview_id integer
;

update data_request_bhc_clearviewwsa_tittelfitz_20220311.luv2_parcels_w_sa
set clearview_id = 1;

update data_request_bhc_clearviewwsa_tittelfitz_20220311.luv2_parcels_w_sa w
inner join data_request_bhc_clearviewwsa_tittelfitz_20220311.luv2_parcels_in_sa i
on w.parcel_id = i.parcel_id
set w.clearview_id = i.clearview_id;

select
clearview_id
,count(parcel_id) as parcels
,sum(parcel_sqft)/43560 as acres
from data_request_bhc_clearviewwsa_tittelfitz_20220311.luv2_parcels_w_sa
group by
clearview_id
;

## RGS parcel combine

drop table if exists data_request_bhc_clearviewwsa_tittelfitz_20220311.rgs_parcels_w_sa;

create table data_request_bhc_clearviewwsa_tittelfitz_20220311.rgs_parcels_w_sa
SELECT * FROM v2050_rgs_major_bludd_2014.parcels;

alter table data_request_bhc_clearviewwsa_tittelfitz_20220311.rgs_parcels_w_sa
add primary key(parcel_id)
,add column clearview_id integer
;

update data_request_bhc_clearviewwsa_tittelfitz_20220311.rgs_parcels_w_sa
set clearview_id = 1;

update data_request_bhc_clearviewwsa_tittelfitz_20220311.rgs_parcels_w_sa w
inner join data_request_bhc_clearviewwsa_tittelfitz_20220311.rgs_parcels_in_sa i
on w.parcel_id = i.parcel_id
set w.clearview_id = i.clearview_id;

select
clearview_id
,count(parcel_id) as parcels
,sum(parcel_sqft)/43560 as acres
from data_request_bhc_clearviewwsa_tittelfitz_20220311.rgs_parcels_w_sa
group by
clearview_id
;