Example #1
0
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)
Example #2
0
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'])
    ))
Example #3
0
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)
Example #4
0
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'])
    ))
Example #5
0
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))
Example #6
0
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))