def run_vmt_quarter_mile_buffers(sql_config_dict): aggregate_within_distance(dict( source_table=sql_config_dict['uf_canvas_schema'] + '.' + sql_config_dict['uf_canvas_table'], source_table_query='pop + emp > 0', source_geometry_column='analysis_geom', target_table_schema=sql_config_dict['vmt_variables_schema'], target_table=sql_config_dict['vmt_variables_table'], target_table_query='du + emp > 0', target_geometry_column='analysis_geom', target_table_pk='id', distance=403, suffix='qtr_mi', aggregation_type='sum', variable_field_dict=dict( acres_parcel_res_qtrmi=['acres_parcel_res'], acres_parcel_emp_qtrmi=['acres_parcel_emp'], acres_parcel_mixed_use_qtrmi=['acres_parcel_mixed_use'], du_qtrmi=['du'], pop_qtrmi=['pop'], emp_qtrmi=['emp'], emp_ret_qtrmi=['emp_ret']) )) pSql = '''DROP INDEX {schema}.{schema}_{table}_analysis_geom; Alter Table {schema}.{table} drop column analysis_geom;'''.format(schema=sql_config_dict['transit_stop_schema'], table=sql_config_dict['transit_stop_table']) execute_sql(pSql)
def run_vmt_one_mile_buffers(sql_config_dict): add_analysis_geom(sql_config_dict['uf_canvas_schema'], sql_config_dict['uf_canvas_table']) add_analysis_geom(sql_config_dict['vmt_variables_schema'], sql_config_dict['vmt_variables_table']) add_analysis_geom(sql_config_dict['transit_stop_schema'], sql_config_dict['transit_stop_table']) pSql = ''' update {vmt_variables_schema}.{vmt_variables_table} a set du = b.du, emp = b.emp from (select id, du, emp from {uf_canvas_schema}.{uf_canvas_table}) b where a.id = b.id; '''.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']) execute_sql(pSql) aggregate_within_distance(dict( source_table=sql_config_dict['uf_canvas_schema'] + '.' + sql_config_dict['uf_canvas_table'], source_table_query='emp > 0', source_geometry_column='analysis_geom', target_table_schema=sql_config_dict['vmt_variables_schema'], target_table=sql_config_dict['vmt_variables_table'], target_table_query='du + emp > 0', target_geometry_column='analysis_geom', target_table_pk='id', distance=1609, suffix='one_mi', aggregation_type='sum', variable_field_dict=dict( emp_1mile=['emp']) ))
def run_aggregate_within_distance_processes(sql_config_dict): aggregate_within_distance( dict(source_table=sql_config_dict['uf_canvas_schema'] + '.' + sql_config_dict['uf_canvas_table'], source_table_query='du + emp > 0', source_geometry_column='analysis_geom', target_table_schema=sql_config_dict[ 'public_health_variables_schema'], target_table=sql_config_dict['public_health_variables_table'], target_geometry_column='analysis_geom', target_table_query='pop > 0', target_table_pk='id', distance=1000, suffix='bldg_sqft', aggregation_type='sum', variable_field_dict=dict( bldg_sqft_res=[ 'bldg_sqft_detsf_sl', 'bldg_sqft_detsf_ll', 'bldg_sqft_attsf', 'bldg_sqft_mf' ], bldg_sqft_ret1=[ 'bldg_sqft_retail_services', 'bldg_sqft_restaurant', 'bldg_sqft_accommodation', 'bldg_sqft_arts_entertainment', 'bldg_sqft_other_services' ], bldg_sqft_ret=[ 'bldg_sqft_retail_services', 'bldg_sqft_restaurant', 'bldg_sqft_arts_entertainment', 'bldg_sqft_other_services' ], bldg_sqft_off=[ 'bldg_sqft_office_services', 'bldg_sqft_public_admin', 'bldg_sqft_education', 'bldg_sqft_medical_services' ], b1=[ 'bldg_sqft_detsf_sl', 'bldg_sqft_detsf_ll', 'bldg_sqft_attsf', 'bldg_sqft_mf' ], b2=['bldg_sqft_retail_services', 'bldg_sqft_other_services'], b3=['bldg_sqft_restaurant', 'bldg_sqft_arts_entertainment'], b4=['bldg_sqft_office_services'], b5=['bldg_sqft_public_admin'], du_1km_tr=['du'], resmix_dens=['acres_parcel_res', 'acres_parcel_mixed_use'], far_nonres=['acres_parcel_emp', 'acres_parcel_mixed_use']))) aggregate_within_distance( dict( source_table=sql_config_dict['source_grid_schema'] + '.' + sql_config_dict['source_grid_table'], source_table_query= 'local_roads_length_feet + secondary_roads_length_feet + freeway_arterial_length_feet + acres_parcel_park_open_space > 0', source_geometry_column='analysis_geom', target_table_schema=sql_config_dict[ 'public_health_variables_schema'], target_table=sql_config_dict['public_health_variables_table'], target_geometry_column='analysis_geom', target_table_query='pop > 0', target_table_pk='id', distance=1000, suffix='grid', aggregation_type='sum', variable_field_dict=dict( local_street=[ 'local_roads_length_feet', 'secondary_roads_length_feet' ], major_street=['freeway_arterial_length_feet'], acres_parcel_park_open_space=['acres_parcel_park_open_space' ]))) aggregate_within_distance( dict( source_table=sql_config_dict['transit_stop_schema'] + '.' + sql_config_dict['transit_stop_table'], source_table_query= 'route_type = 0 or route_type = 1 or route_type = 2 or route_type = 3', source_geometry_column='analysis_geom', target_table_schema=sql_config_dict[ 'public_health_variables_schema'], target_table=sql_config_dict['public_health_variables_table'], target_geometry_column='analysis_geom', target_table_query='pop > 0', target_table_pk='id', distance=1000, suffix='transit', aggregation_type='count', variable_field_dict=dict(transit_count=['wkb_geometry']))) geom_analysis_tables = [(sql_config_dict['uf_canvas_schema'], sql_config_dict['uf_canvas_table']), (sql_config_dict['source_grid_schema'], sql_config_dict['source_grid_table']), (sql_config_dict['transit_stop_schema'], sql_config_dict['transit_stop_table']), (sql_config_dict['public_health_variables_schema'], sql_config_dict['public_health_variables_table'])] for schema, table in geom_analysis_tables: execute_sql('''DROP INDEX {schema}.{schema}_{table}_analysis_geom; Alter Table {schema}.{table} drop column analysis_geom;'''.format( schema=schema, table=table))
def run_aggregate_within_distance_processes(sql_config_dict): aggregate_within_distance(dict( source_table=sql_config_dict['uf_canvas_schema'] + '.' + sql_config_dict['uf_canvas_table'], source_table_query='du + emp > 0', source_geometry_column='analysis_geom', target_table_schema=sql_config_dict['public_health_variables_schema'], target_table=sql_config_dict['public_health_variables_table'], target_geometry_column='analysis_geom', target_table_query='pop > 0', target_table_pk='id', distance=1000, suffix='bldg_sqft', aggregation_type='sum', variable_field_dict=dict( bldg_sqft_res=['bldg_sqft_detsf_sl', 'bldg_sqft_detsf_ll', 'bldg_sqft_attsf', 'bldg_sqft_mf'], bldg_sqft_ret1=['bldg_sqft_retail_services', 'bldg_sqft_restaurant', 'bldg_sqft_accommodation', 'bldg_sqft_arts_entertainment', 'bldg_sqft_other_services'], bldg_sqft_ret=['bldg_sqft_retail_services', 'bldg_sqft_restaurant', 'bldg_sqft_arts_entertainment', 'bldg_sqft_other_services'], bldg_sqft_off=['bldg_sqft_office_services', 'bldg_sqft_public_admin', 'bldg_sqft_education', 'bldg_sqft_medical_services'], b1=['bldg_sqft_detsf_sl', 'bldg_sqft_detsf_ll', 'bldg_sqft_attsf', 'bldg_sqft_mf'], b2=['bldg_sqft_retail_services', 'bldg_sqft_other_services'], b3=['bldg_sqft_restaurant', 'bldg_sqft_arts_entertainment'], b4=['bldg_sqft_office_services'], b5=['bldg_sqft_public_admin'], du_1km_tr=['du'], resmix_dens=['acres_parcel_res', 'acres_parcel_mixed_use'], far_nonres=['acres_parcel_emp', 'acres_parcel_mixed_use']) )) aggregate_within_distance(dict( source_table=sql_config_dict['source_grid_schema'] + '.' + sql_config_dict['source_grid_table'], source_table_query='local_roads_length_feet + secondary_roads_length_feet + freeway_arterial_length_feet + acres_parcel_park_open_space > 0', source_geometry_column='analysis_geom', target_table_schema=sql_config_dict['public_health_variables_schema'], target_table=sql_config_dict['public_health_variables_table'], target_geometry_column='analysis_geom', target_table_query='pop > 0', target_table_pk='id', distance=1000, suffix='grid', aggregation_type='sum', variable_field_dict=dict( local_street=['local_roads_length_feet', 'secondary_roads_length_feet'], major_street=['freeway_arterial_length_feet'], acres_parcel_park_open_space=['acres_parcel_park_open_space']) )) aggregate_within_distance(dict( source_table=sql_config_dict['transit_stop_schema'] + '.' + sql_config_dict['transit_stop_table'], source_table_query='route_type = 0 or route_type = 1 or route_type = 2 or route_type = 3', source_geometry_column='analysis_geom', target_table_schema=sql_config_dict['public_health_variables_schema'], target_table=sql_config_dict['public_health_variables_table'], target_geometry_column='analysis_geom', target_table_query='pop > 0', target_table_pk='id', distance=1000, suffix='transit', aggregation_type='count', variable_field_dict=dict( transit_count=['wkb_geometry']) )) geom_analysis_tables = [ (sql_config_dict['uf_canvas_schema'], sql_config_dict['uf_canvas_table']), (sql_config_dict['source_grid_schema'], sql_config_dict['source_grid_table']), (sql_config_dict['transit_stop_schema'], sql_config_dict['transit_stop_table']), (sql_config_dict['public_health_variables_schema'], sql_config_dict['public_health_variables_table']) ] for schema, table in geom_analysis_tables: execute_sql('''DROP INDEX {schema}.{schema}_{table}_analysis_geom; Alter Table {schema}.{table} drop column analysis_geom;'''.format(schema=schema, table=table))