processing_capacity_pseudo80_lr60_check

drop table if exists parcels_pseudo80_capacity_netdist;

create table parcels_pseudo80_capacity_netdist
select
parcel_id
,tod_id
,city_id
FROM 2014_parcel_baseyear_core.parcels_80pseudo;


alter table parcels_pseudo80_capacity_netdist
add primary key(parcel_id)
,add column du_base float
,add column du_capacity_50 float
,add column du_capacity_75 float
,add column du_capacity_50_net float
,add column du_capacity_75_net float
,add column jobs_base float
,add column jobs_capacity_50 float
,add column jobs_capacity_75 float
,add column jobs_capacity_50_net float
,add column jobs_capacity_75_net float;

update parcels_pseudo80_capacity_netdist
set
du_base=0
,du_capacity_50=0
,du_capacity_75=0
,du_capacity_50_net=0
,du_capacity_75_net=0
,jobs_base=0
,jobs_capacity_50=0
,jobs_capacity_75=0
,jobs_capacity_50_net=0
,jobs_capacity_75_net=0;

update parcels_pseudo80_capacity_netdist
inner join capacity_indicator_parcel_20190306
on parcels_pseudo80_capacity_netdist.parcel_id = capacity_indicator_parcel_20190306.parcel_id
set
parcels_pseudo80_capacity_netdist.du_base= capacity_indicator_parcel_20190306.DUbase
,parcels_pseudo80_capacity_netdist.du_capacity_50= capacity_indicator_parcel_20190306.DUcapacity50
,parcels_pseudo80_capacity_netdist.du_capacity_75= capacity_indicator_parcel_20190306.DUcapacity75
,parcels_pseudo80_capacity_netdist.jobs_base= capacity_indicator_parcel_20190306.JOBSPbase
,parcels_pseudo80_capacity_netdist.jobs_capacity_50 =capacity_indicator_parcel_20190306.JOBSPcapacity50
,parcels_pseudo80_capacity_netdist.jobs_capacity_75 =capacity_indicator_parcel_20190306.JOBSPcapacity75
;

update parcels_pseudo80_capacity_netdist
set
du_capacity_50_net= du_capacity_50-du_base
,du_capacity_75_net= du_capacity_75-du_base
,jobs_capacity_50_net=jobs_capacity_50-jobs_base
,jobs_capacity_75_net=jobs_capacity_75-jobs_base
;


alter table parcels_pseudo80_capacity_netdist
add column true_city_id int
,add column county_id int
,add column fips_rgs_proposed_id int;

update parcels_pseudo80_capacity_netdist
set
true_city_id =0
,county_id = 0
,fips_rgs_proposed_id = 0;

update parcels_pseudo80_capacity_netdist
set
true_city_id = city_id - 3000
where city_id>3000;


update parcels_pseudo80_capacity_netdist
set
true_city_id = city_id - 2000
where city_id between 2000 and 3000;

update parcels_pseudo80_capacity_netdist
set
true_city_id = city_id
where city_id < 1000;

alter table parcels_pseudo80_capacity_netdist add index (city_id);

update parcels_pseudo80_capacity_netdist
inner join 2014_parcel_baseyear_core.cities_80pseudo
on parcels_pseudo80_capacity_netdist.city_id = 2014_parcel_baseyear_core.cities_80pseudo.city_id
set parcels_pseudo80_capacity_netdist.county_id = 2014_parcel_baseyear_core.cities_80pseudo.county_id
,parcels_pseudo80_capacity_netdist.fips_rgs_proposed_id = 2014_parcel_baseyear_core.cities_80pseudo.fips_rgs_proposed_id
;

update parcels_pseudo80_capacity_netdist
set
du_capacity_50_net= 0
where du_capacity_50_net <0;

update parcels_pseudo80_capacity_netdist
set
du_capacity_75_net= 0
where du_capacity_75_net <0
;

update parcels_pseudo80_capacity_netdist
set
jobs_capacity_50_net= 0
where jobs_capacity_50_net <0
;

update parcels_pseudo80_capacity_netdist
set
jobs_capacity_75_net= 0
where jobs_capacity_75_net <0
;

select
true_city_id
,count(parcel_id) as parcels
,sum(du_capacity_net) as net_du_capacity
,sum(jobs_capacity_net) as net_emp_capacity
from parcels_pseudo80_capacity_netdist
group by true_city_id;


SELECT
count(parcel_id) as parcels
FROM parcels_pseudo80_capacity_netdist p;

SELECT * FROM 2014_parcel_baseyear_core.cities_80pseudo where county_id = 35;