data_request_sea_ind_areas_ca_20200315

create database data_request_sea_ind_areas_ca_20200315;

drop table if exists data_request_sea_ind_areas_ca_20200315.parcels_in_sea_ind_areas;


create table data_request_sea_ind_areas_ca_20200315.parcels_in_sea_ind_areas
(
parcel_id integer not null
,sea_ind_areas_id integer not null
,primary key (parcel_id)
);

LOAD DATA LOCAL INFILE 'J:/Projects/LandUseVision/LUV.2/Final/Requests/Sea_IndAreas_CA_Mar2020/parcels_in_sea_ind_areas.csv'
INTO TABLE data_request_sea_ind_areas_ca_20200315.parcels_in_sea_ind_areas
FIELDS TERMINATED BY ','
#ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(
parcel_id
,sea_ind_areas_id
);

select * from data_request_sea_ind_areas_ca_20200315.parcels_in_sea_ind_areas;

select
sea_ind_areas_id
,count(parcel_id) as parcels
from data_request_sea_ind_areas_ca_20200315.parcels_in_sea_ind_areas
group by sea_ind_areas_id;


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

drop table if exists data_request_sea_ind_areas_ca_20200315.sea_ind_areas;


create table data_request_sea_ind_areas_ca_20200315.sea_ind_areas
(
sea_ind_areas_id integer not null
,sea_ind_areas_descript varchar(20) not null
,primary key (sea_ind_areas_id)
);

LOAD DATA LOCAL INFILE 'J:/Projects/LandUseVision/LUV.2/Final/Requests/Sea_IndAreas_CA_Mar2020/sea_ind_areas.csv'
INTO TABLE data_request_sea_ind_areas_ca_20200315.sea_ind_areas
FIELDS TERMINATED BY ','
#ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(
sea_ind_areas_id
,sea_ind_areas_descript
);

select * from data_request_sea_ind_areas_ca_20200315.sea_ind_areas;

create table data_request_sea_ind_areas_ca_20200315.parcels_w_sea_ind_areas
SELECT * FROM luv2_lodes_final_outputs_2014.parcels;

alter table data_request_sea_ind_areas_ca_20200315.parcels_w_sea_ind_areas
add primary key(parcel_id)
,add column sea_ind_areas_id integer
;

update data_request_sea_ind_areas_ca_20200315.parcels_w_sea_ind_areas
set sea_ind_areas_id = 0;

update data_request_sea_ind_areas_ca_20200315.parcels_w_sea_ind_areas w
inner join data_request_sea_ind_areas_ca_20200315.parcels_in_sea_ind_areas i
on w.parcel_id = i.parcel_id
set w.sea_ind_areas_id = i.sea_ind_areas_id;

select
sea_ind_areas_id
,count(parcel_id) as parcels
,sum(parcel_sqft)/43560 as acres
from data_request_sea_ind_areas_ca_20200315.parcels_w_sea_ind_areas
group by
sea_ind_areas_id
;