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)