def run_aggregate_within_variable_distance_processes(sql_config_dict):
drop_table(
'{public_health_variables_schema}.{uf_canvas_table}_variable'.format(
public_health_variables_schema=sql_config_dict[
'public_health_variables_schema'],
uf_canvas_table=sql_config_dict['uf_canvas_table']))
pSql = '''
create table {public_health_variables_schema}.{uf_canvas_table}_variable
as select
a.id, st_transform(a.wkb_geometry, 3310) as wkb_geometry, cast(a.attractions_hbw * 1609.0 as float) as distance,
sum(du * st_area(st_intersection(a.wkb_geometry, b.wkb_geometry)) / st_area(b.wkb_geometry)) as du_variable,
sum(emp * st_area(st_intersection(a.wkb_geometry, b.wkb_geometry)) / st_area(b.wkb_geometry)) as emp_variable
from
(select id, wkb_geometry, attractions_hbw from {trip_lengths_schema}.{trip_lengths_table}) a,
(select wkb_geometry, du, emp from {uf_canvas_schema}.{uf_canvas_table} where du + emp > 0) b
where st_intersects(b.wkb_geometry, a.wkb_geometry) group by a.id, a.wkb_geometry, a.attractions_hbw;
'''.format(public_health_variables_schema=sql_config_dict[
'public_health_variables_schema'],
uf_canvas_schema=sql_config_dict['uf_canvas_schema'],
uf_canvas_table=sql_config_dict['uf_canvas_table'],
trip_lengths_schema=sql_config_dict['trip_lengths_schema'],
trip_lengths_table=sql_config_dict['trip_lengths_table'])
execute_sql(pSql)
add_geom_idx(sql_config_dict['public_health_variables_schema'],
sql_config_dict['uf_canvas_table'] + '_variable')
add_primary_key(sql_config_dict['public_health_variables_schema'],
sql_config_dict['uf_canvas_table'] + '_variable', 'id')
add_analysis_geom(sql_config_dict['public_health_variables_schema'],
sql_config_dict['public_health_variables_table'])
aggregate_within_variable_distance(
dict(source_table=sql_config_dict['public_health_variables_schema'] +
'.' + sql_config_dict['uf_canvas_table'] + '_variable',
source_table_query='id is not null',
target_table_schema=sql_config_dict[
'public_health_variables_schema'],
target_table=sql_config_dict['public_health_variables_table'],
target_table_query='pop > 0',
target_table_pk='id',
suffix='variable',
aggregation_type='sum',
variable_field_list=['du_variable', 'emp_variable']))
drop_table(
'{public_health_variables_schema}.{uf_canvas_table}_variable'.format(
public_health_variables_schema=sql_config_dict[
'public_health_variables_schema'],
uf_canvas_table=sql_config_dict['uf_canvas_table']))
def run_aggregate_within_variable_distance_processes(sql_config_dict):
drop_table('{public_health_variables_schema}.{uf_canvas_table}_variable'.format(
public_health_variables_schema=sql_config_dict['public_health_variables_schema'],
uf_canvas_table=sql_config_dict['uf_canvas_table']))
pSql = '''
create table {public_health_variables_schema}.{uf_canvas_table}_variable
as select
a.id, st_transform(a.wkb_geometry, 3310) as wkb_geometry, cast(a.attractions_hbw * 1609.0 as float) as distance,
sum(du * st_area(st_intersection(a.wkb_geometry, b.wkb_geometry)) / st_area(b.wkb_geometry)) as du_variable,
sum(emp * st_area(st_intersection(a.wkb_geometry, b.wkb_geometry)) / st_area(b.wkb_geometry)) as emp_variable
from
(select id, wkb_geometry, attractions_hbw from {trip_lengths_schema}.{trip_lengths_table}) a,
(select wkb_geometry, du, emp from {uf_canvas_schema}.{uf_canvas_table} where du + emp > 0) b
where st_intersects(b.wkb_geometry, a.wkb_geometry) group by a.id, a.wkb_geometry, a.attractions_hbw;
'''.format(public_health_variables_schema=sql_config_dict['public_health_variables_schema'],
uf_canvas_schema=sql_config_dict['uf_canvas_schema'],
uf_canvas_table=sql_config_dict['uf_canvas_table'],
trip_lengths_schema=sql_config_dict['trip_lengths_schema'],
trip_lengths_table=sql_config_dict['trip_lengths_table'])
execute_sql(pSql)
add_geom_idx(sql_config_dict['public_health_variables_schema'], sql_config_dict['uf_canvas_table'] + '_variable')
add_primary_key(sql_config_dict['public_health_variables_schema'], sql_config_dict['uf_canvas_table'] + '_variable', 'id')
add_analysis_geom(sql_config_dict['public_health_variables_schema'], sql_config_dict['public_health_variables_table'])
aggregate_within_variable_distance(dict(
source_table=sql_config_dict['public_health_variables_schema'] + '.' + sql_config_dict['uf_canvas_table'] + '_variable',
source_table_query='id is not null',
target_table_schema=sql_config_dict['public_health_variables_schema'],
target_table=sql_config_dict['public_health_variables_table'],
target_table_query='pop > 0',
target_table_pk='id',
suffix='variable',
aggregation_type='sum',
variable_field_list=['du_variable', 'emp_variable']
))
drop_table('{public_health_variables_schema}.{uf_canvas_table}_variable'.format(
public_health_variables_schema=sql_config_dict['public_health_variables_schema'],
uf_canvas_table=sql_config_dict['uf_canvas_table']))
def run_vmt_variable_trip_length_buffers(sql_config_dict):
drop_table('{vmt_variables_schema}.{vmt_variables_table}_vmt_variable'.format(
vmt_variables_schema=sql_config_dict['vmt_variables_schema'],
vmt_variables_table=sql_config_dict['vmt_variables_table']))
pSql = '''
create table {vmt_variables_schema}.{vmt_variables_table}_vmt_variable
as select
a.id, st_transform(a.wkb_geometry, 3310) as wkb_geometry, cast(a.attractions_hbw * 1609.0 as float) as distance,
sum(acres_parcel_res) as acres_parcel_res_vb,
sum(acres_parcel_emp) as acres_parcel_emp_vb,
sum(acres_parcel_mixed_use) as acres_parcel_mixed_use_vb,
sum(pop) as pop_vb,
sum(hh) as hh_vb,
sum(du) as du_vb,
sum(du_mf) as du_mf_vb,
sum(emp) as emp_vb,
sum(emp_ret) as emp_ret_vb,
sum(hh_inc_00_10) as hh_inc_00_10_vb,
sum(hh_inc_10_20) as hh_inc_10_20_vb,
sum(hh_inc_20_30) as hh_inc_20_30_vb,
sum(hh_inc_30_40) as hh_inc_30_40_vb,
sum(hh_inc_40_50) as hh_inc_40_50_vb,
sum(hh_inc_50_60) as hh_inc_50_60_vb,
sum(hh_inc_60_75) as hh_inc_60_75_vb,
sum(hh_inc_75_100) as hh_inc_75_100_vb,
sum(hh_inc_100p) as hh_inc_100p_vb,
sum(pop_employed) as pop_employed_vb,
sum(pop_age16_up) as pop_age16_up_vb,
sum(pop_age65_up) as pop_age65_up_vb
from
(select id, wkb_geometry, attractions_hbw from {trip_lengths_schema}.{trip_lengths_table}) a,
(select point, acres_parcel_res, acres_parcel_emp, acres_parcel_mixed_use, pop, hh, du, du_mf, emp, emp_ret,
hh * hh_inc_00_10_rate as hh_inc_00_10,
hh * hh_inc_10_20_rate as hh_inc_10_20,
hh * hh_inc_20_30_rate as hh_inc_20_30,
hh * hh_inc_30_40_rate as hh_inc_30_40,
hh * hh_inc_40_50_rate as hh_inc_40_50,
hh * hh_inc_50_60_rate as hh_inc_50_60,
hh * hh_inc_60_75_rate as hh_inc_60_75,
hh * hh_inc_75_100_rate as hh_inc_75_100,
hh * hh_inc_100p_rate as hh_inc_100p,
pop * pop_age16_up_rate * pop_employed_rate as pop_employed,
pop * pop_age16_up_rate as pop_age16_up,
pop * pop_age65_up_rate as pop_age65_up
from (select st_centroid(wkb_geometry) as point, pop, hh, du, du_mf, emp, emp_ret, acres_parcel_res,
acres_parcel_emp, acres_parcel_mixed_use
from {uf_canvas_schema}.{uf_canvas_table}) a,
(select wkb_geometry, hh_inc_00_10_rate, hh_inc_10_20_rate, hh_inc_20_30_rate,
hh_inc_30_40_rate, hh_inc_40_50_rate, hh_inc_50_60_rate, hh_inc_60_75_rate,
hh_inc_75_100_rate, hh_inc_100_125_rate + hh_inc_125_150_rate + hh_inc_150_200_rate + hh_inc_200p_rate as hh_inc_100p_rate,
pop_employed_rate, pop_age16_up_rate, pop_age65_up_rate from {census_rates_schema}.{census_rates_table}) c
where st_intersects(point, c.wkb_geometry)
) b
where st_intersects(point, a.wkb_geometry) group by a.id, a.wkb_geometry, a.attractions_hbw;
'''.format(vmt_variables_schema=sql_config_dict['vmt_variables_schema'],
vmt_variables_table=sql_config_dict['vmt_variables_table'],
uf_canvas_schema=sql_config_dict['uf_canvas_schema'],
uf_canvas_table=sql_config_dict['uf_canvas_table'],
census_rates_schema=sql_config_dict['census_rates_schema'],
census_rates_table=sql_config_dict['census_rates_table'],
trip_lengths_schema=sql_config_dict['trip_lengths_schema'],
trip_lengths_table=sql_config_dict['trip_lengths_table'])
execute_sql(pSql)
add_geom_idx(sql_config_dict['vmt_variables_schema'], sql_config_dict['vmt_variables_table'] + '_vmt_variable')
add_primary_key(sql_config_dict['vmt_variables_schema'], sql_config_dict['vmt_variables_table'] + '_vmt_variable', 'id')
aggregate_within_variable_distance(dict(
source_table=sql_config_dict['vmt_variables_schema'] + '.' + sql_config_dict['vmt_variables_table'] + '_vmt_variable',
source_table_query='du_vb + emp_vb > 0',
target_table_schema=sql_config_dict['vmt_variables_schema'],
target_table=sql_config_dict['vmt_variables_table'],
target_table_query='id is not null',
target_table_pk='id',
suffix='vmt_vb',
aggregation_type='sum',
variable_field_list=['acres_parcel_res_vb', 'acres_parcel_emp_vb', 'acres_parcel_mixed_use_vb', 'du_vb', 'pop_vb',
'emp_vb', 'emp_ret_vb', 'hh_vb', 'du_mf_vb', 'hh_inc_00_10_vb', 'hh_inc_10_20_vb',
'hh_inc_20_30_vb', 'hh_inc_30_40_vb', 'hh_inc_40_50_vb', 'hh_inc_50_60_vb',
'hh_inc_60_75_vb', 'hh_inc_75_100_vb', 'hh_inc_100p_vb', 'pop_employed_vb',
'pop_age16_up_vb', 'pop_age65_up_vb']
))
pSql = '''DROP INDEX {schema}.{schema}_{table}_analysis_geom;
Alter Table {schema}.{table} drop column analysis_geom;'''.format(schema=sql_config_dict['vmt_variables_schema'],
table=sql_config_dict['vmt_variables_table'])
execute_sql(pSql)
def run_vmt_variable_trip_length_buffers(sql_config_dict):
drop_table('{vmt_variables_schema}.{vmt_variables_table}_vmt_variable'.format(
vmt_variables_schema=sql_config_dict['vmt_variables_schema'],
vmt_variables_table=sql_config_dict['vmt_variables_table']))
pSql = '''
create table {vmt_variables_schema}.{vmt_variables_table}_vmt_variable
as select
a.id, st_transform(a.wkb_geometry, 3310) as wkb_geometry, cast(a.attractions_hbw * 1609.0 as float) as distance,
sum(acres_parcel_res) as acres_parcel_res_vb,
sum(acres_parcel_emp) as acres_parcel_emp_vb,
sum(acres_parcel_mixed_use) as acres_parcel_mixed_use_vb,
sum(pop) as pop_vb,
sum(hh) as hh_vb,
sum(du) as du_vb,
sum(du_mf) as du_mf_vb,
sum(emp) as emp_vb,
sum(emp_ret) as emp_ret_vb,
sum(hh_inc_00_10) as hh_inc_00_10_vb,
sum(hh_inc_10_20) as hh_inc_10_20_vb,
sum(hh_inc_20_30) as hh_inc_20_30_vb,
sum(hh_inc_30_40) as hh_inc_30_40_vb,
sum(hh_inc_40_50) as hh_inc_40_50_vb,
sum(hh_inc_50_60) as hh_inc_50_60_vb,
sum(hh_inc_60_75) as hh_inc_60_75_vb,
sum(hh_inc_75_100) as hh_inc_75_100_vb,
sum(hh_inc_100p) as hh_inc_100p_vb,
sum(pop_employed) as pop_employed_vb,
sum(pop_age16_up) as pop_age16_up_vb,
sum(pop_age65_up) as pop_age65_up_vb
from
(select id, wkb_geometry, attractions_hbw from {trip_lengths_schema}.{trip_lengths_table}) a,
(select point, acres_parcel_res, acres_parcel_emp, acres_parcel_mixed_use, pop, hh, du, du_mf, emp, emp_ret,
hh * hh_inc_00_10_rate as hh_inc_00_10,
hh * hh_inc_10_20_rate as hh_inc_10_20,
hh * hh_inc_20_30_rate as hh_inc_20_30,
hh * hh_inc_30_40_rate as hh_inc_30_40,
hh * hh_inc_40_50_rate as hh_inc_40_50,
hh * hh_inc_50_60_rate as hh_inc_50_60,
hh * hh_inc_60_75_rate as hh_inc_60_75,
hh * hh_inc_75_100_rate as hh_inc_75_100,
hh * hh_inc_100p_rate as hh_inc_100p,
pop * pop_age16_up_rate * pop_employed_rate as pop_employed,
pop * pop_age16_up_rate as pop_age16_up,
pop * pop_age65_up_rate as pop_age65_up
from (select st_centroid(wkb_geometry) as point, pop, hh, du, du_mf, emp, emp_ret, acres_parcel_res,
acres_parcel_emp, acres_parcel_mixed_use
from {uf_canvas_schema}.{uf_canvas_table}) a,
(select wkb_geometry, hh_inc_00_10_rate, hh_inc_10_20_rate, hh_inc_20_30_rate,
hh_inc_30_40_rate, hh_inc_40_50_rate, hh_inc_50_60_rate, hh_inc_60_75_rate,
hh_inc_75_100_rate, hh_inc_100_125_rate + hh_inc_125_150_rate + hh_inc_150_200_rate + hh_inc_200p_rate as hh_inc_100p_rate,
pop_employed_rate, pop_age16_up_rate, pop_age65_up_rate from {census_rates_schema}.{census_rates_table}) c
where st_intersects(point, c.wkb_geometry)
) b
where st_intersects(point, a.wkb_geometry) group by a.id, a.wkb_geometry, a.attractions_hbw;
'''.format(vmt_variables_schema=sql_config_dict['vmt_variables_schema'],
vmt_variables_table=sql_config_dict['vmt_variables_table'],
uf_canvas_schema=sql_config_dict['uf_canvas_schema'],
uf_canvas_table=sql_config_dict['uf_canvas_table'],
census_rates_schema=sql_config_dict['census_rates_schema'],
census_rates_table=sql_config_dict['census_rates_table'],
trip_lengths_schema=sql_config_dict['trip_lengths_schema'],
trip_lengths_table=sql_config_dict['trip_lengths_table'])
execute_sql(pSql)
add_geom_idx(sql_config_dict['vmt_variables_schema'], sql_config_dict['vmt_variables_table'] + '_vmt_variable')
add_primary_key(sql_config_dict['vmt_variables_schema'], sql_config_dict['vmt_variables_table'] + '_vmt_variable', 'id')
aggregate_within_variable_distance(dict(
source_table=sql_config_dict['vmt_variables_schema'] + '.' + sql_config_dict['vmt_variables_table'] + '_vmt_variable',
source_table_query='du_vb + emp_vb > 0',
target_table_schema=sql_config_dict['vmt_variables_schema'],
target_table=sql_config_dict['vmt_variables_table'],
target_table_query='id is not null',
target_table_pk='id',
suffix='vmt_vb',
aggregation_type='sum',
variable_field_list=['acres_parcel_res_vb', 'acres_parcel_emp_vb', 'acres_parcel_mixed_use_vb', 'du_vb', 'pop_vb',
'emp_vb', 'emp_ret_vb', 'hh_vb', 'du_mf_vb', 'hh_inc_00_10_vb', 'hh_inc_10_20_vb',
'hh_inc_20_30_vb', 'hh_inc_30_40_vb', 'hh_inc_40_50_vb', 'hh_inc_50_60_vb',
'hh_inc_60_75_vb', 'hh_inc_75_100_vb', 'hh_inc_100p_vb', 'pop_employed_vb',
'pop_age16_up_vb', 'pop_age65_up_vb']
))
pSql = '''DROP INDEX {schema}.{schema}_{table}_analysis_geom;
Alter Table {schema}.{table} drop column analysis_geom;'''.format(schema=sql_config_dict['vmt_variables_schema'],
table=sql_config_dict['vmt_variables_table'])
execute_sql(pSql)
