data_request_rgs_luv2_generic_march_2022

create database data_request_kingmetro_ireland_rt_36_march2022;

## LUV2 Parcels

drop table if exists data_request_kingmetro_ireland_rt_36_march2022.luv2_parcels_in_sa;

create table data_request_kingmetro_ireland_rt_36_march2022.luv2_parcels_in_sa
(
parcel_id integer not null
,x_coord_sp integer not null
,y_coord_sp integer not null
,sa_id integer not null
,primary key (parcel_id)
);

LOAD DATA LOCAL INFILE 'J:/Projects/LandUseVision/LUV.2/Final/Requests/KingMetro_Ireland_Mar2022/luv2_parcels_in_sa.csv'
INTO TABLE data_request_kingmetro_ireland_rt_36_march2022.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
,sa_id
);

select * from data_request_kingmetro_ireland_rt_36_march2022.luv2_parcels_in_sa;

## RGS parcels

drop table if exists data_request_kingmetro_ireland_rt_36_march2022.rgs_parcels_in_sa;

create table data_request_kingmetro_ireland_rt_36_march2022.rgs_parcels_in_sa
(
parcel_id integer not null
,x_coord_sp integer not null
,y_coord_sp integer not null
,sa_id integer not null
,primary key (parcel_id)
);

LOAD DATA LOCAL INFILE 'J:/Projects/LandUseVision/LUV.2/Final/Requests/KingMetro_Ireland_Mar2022/rgs_parcels_in_sa.csv'
INTO TABLE data_request_kingmetro_ireland_rt_36_march2022.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
,sa_id
);

select * from data_request_kingmetro_ireland_rt_36_march2022.rgs_parcels_in_sa;


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

drop table if exists data_request_kingmetro_ireland_rt_36_march2022.sa;


create table data_request_kingmetro_ireland_rt_36_march2022.sa
(
sa_id integer not null
,descript varchar(30) not null
,primary key (sa_id)
);

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

select * from data_request_kingmetro_ireland_rt_36_march2022.sa;

## LUV2 parcel combine

drop table if exists data_request_kingmetro_ireland_rt_36_march2022.luv2_parcels_w_sa;

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

alter table data_request_kingmetro_ireland_rt_36_march2022.luv2_parcels_w_sa
add primary key(parcel_id)
,add column sa_id integer
;

update data_request_kingmetro_ireland_rt_36_march2022.luv2_parcels_w_sa
set sa_id = 1;

update data_request_kingmetro_ireland_rt_36_march2022.luv2_parcels_w_sa w
inner join data_request_kingmetro_ireland_rt_36_march2022.luv2_parcels_in_sa i
on w.parcel_id = i.parcel_id
set w.sa_id = i.sa_id;

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

## RGS parcel combine

drop table if exists data_request_kingmetro_ireland_rt_36_march2022.rgs_parcels_w_sa;

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

alter table data_request_kingmetro_ireland_rt_36_march2022.rgs_parcels_w_sa
add primary key(parcel_id)
,add column sa_id integer
;

update data_request_kingmetro_ireland_rt_36_march2022.rgs_parcels_w_sa
set sa_id = 1;

update data_request_kingmetro_ireland_rt_36_march2022.rgs_parcels_w_sa w
inner join data_request_kingmetro_ireland_rt_36_march2022.rgs_parcels_in_sa i
on w.parcel_id = i.parcel_id
set w.sa_id = i.sa_id;

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