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_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'
)))