def run_transit_proximity(sql_config_dict): calculate_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', source_geometry_column='analysis_geom', target_table_schema=sql_config_dict['vmt_variables_schema'], target_table=sql_config_dict['vmt_variables_table'], target_geometry_column='analysis_geom', target_table_query='du > 0', target_table_pk='id', maximum_distance=403, column='transit_1km' ))
def run_distance_variables_processes(sql_config_dict): 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'])] add_analysis_geom = ''' alter table {schema}.{table} drop column if exists analysis_geom cascade; alter table {schema}.{table} add column analysis_geom geometry; update {schema}.{table} set analysis_geom = st_setSRID(st_transform(wkb_geometry, 3310), 3310); create index {schema}_{table}_analysis_geom on {schema}.{table} using gist (analysis_geom);''' for schema, table in geom_analysis_tables: execute_sql(add_analysis_geom.format(schema=schema, table=table)) ph_distance_calcs = dict( 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_table_pk='id', target_table_query='pop > 0', target_geometry_column='analysis_geom', maximum_distance=2000) calculate_distance( merge( ph_distance_calcs, dict(source_table=sql_config_dict['uf_canvas_schema'] + '.' + sql_config_dict['uf_canvas_table'], source_table_query='emp_education > 0', column='school_distance'))) calculate_distance( merge( ph_distance_calcs, dict(source_table=sql_config_dict['uf_canvas_schema'] + '.' + sql_config_dict['uf_canvas_table'], source_table_query='emp_restaurant > 0', column='restaurant_distance'))) calculate_distance( merge( ph_distance_calcs, dict(source_table=sql_config_dict['uf_canvas_schema'] + '.' + sql_config_dict['uf_canvas_table'], source_table_query='emp_retail_services > 0', column='retail_distance'))) calculate_distance( merge( ph_distance_calcs, dict(source_table=sql_config_dict['source_grid_schema'] + '.' + sql_config_dict['source_grid_table'], source_table_query='acres_parcel_park_open_space > 0', column='park_open_space_distance'))) calculate_distance( merge( ph_distance_calcs, dict(source_table=sql_config_dict['source_grid_schema'] + '.' + sql_config_dict['source_grid_table'], source_table_query='freeway_arterial_length_feet > 0', maximum_distance=500, column='freeway_arterial_any'))) calculate_distance( merge( ph_distance_calcs, 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', column='transit_distance')))
def run_distance_variables_processes(sql_config_dict): 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']) ] add_analysis_geom = ''' alter table {schema}.{table} drop column if exists analysis_geom cascade; alter table {schema}.{table} add column analysis_geom geometry; update {schema}.{table} set analysis_geom = st_setSRID(st_transform(wkb_geometry, 3310), 3310); create index {schema}_{table}_analysis_geom on {schema}.{table} using gist (analysis_geom);''' for schema, table in geom_analysis_tables: execute_sql(add_analysis_geom.format(schema=schema, table=table)) ph_distance_calcs = dict( 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_table_pk='id', target_table_query='pop > 0', target_geometry_column='analysis_geom', maximum_distance=2000 ) calculate_distance(merge(ph_distance_calcs, dict( source_table=sql_config_dict['uf_canvas_schema'] + '.' + sql_config_dict['uf_canvas_table'], source_table_query='emp_education > 0', column='school_distance' ))) calculate_distance(merge(ph_distance_calcs, dict( source_table=sql_config_dict['uf_canvas_schema'] + '.' + sql_config_dict['uf_canvas_table'], source_table_query='emp_restaurant > 0', column='restaurant_distance' ))) calculate_distance(merge(ph_distance_calcs, dict( source_table=sql_config_dict['uf_canvas_schema'] + '.' + sql_config_dict['uf_canvas_table'], source_table_query='emp_retail_services > 0', column='retail_distance' ))) calculate_distance(merge(ph_distance_calcs, dict( source_table=sql_config_dict['source_grid_schema'] + '.' + sql_config_dict['source_grid_table'], source_table_query='acres_parcel_park_open_space > 0', column='park_open_space_distance' ))) calculate_distance(merge(ph_distance_calcs, dict( source_table=sql_config_dict['source_grid_schema'] + '.' + sql_config_dict['source_grid_table'], source_table_query='freeway_arterial_length_feet > 0', maximum_distance=500, column='freeway_arterial_any' ))) calculate_distance(merge(ph_distance_calcs, 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', column='transit_distance' )))