def aggregate_results_to_outcomes_summary_table(self, sql_config_dict):
"""
Aggregates the result table (at grid scale) to a standardized summary table
"""
attribute_list = filter(lambda x: x not in ['id', 'hh', 'pop', 'pop_adult', 'pop_adult_high', 'pop_adult_med',
'pop_adult_low', 'pop_senior', 'pop_teen', 'pop_children'],
self.outcome_fields)
output_list = filter(lambda x: x not in ['id'],
self.outcome_fields)
field_calculations = ''',
'''.join([
"case when SUM(pop) > 0 then SUM({field} * pop) / SUM(pop) else 0 end as {field}".format(
field=field)
for field in attribute_list
])
truncate_table('{outcome_summary_schema}.{outcome_summary_table}'.format(**sql_config_dict))
pSql = """
SELECT
SUM(hh) as hh,
SUM(pop) as pop,
SUM(pop_adult) as pop_adult,
SUM(pop_adult_high) as pop_adult_high,
SUM(pop_adult_med) as pop_adult_med,
SUM(pop_adult_low) as pop_adult_low,
SUM(pop_senior) as pop_senior,
SUM(pop_teen) as pop_teen,
SUM(pop_children) as pop_children,
{field_calculations}
FROM {grid_outcome_schema}.{grid_outcome_table};
""".format(field_calculations=field_calculations, **sql_config_dict)
summary_results = flatten(report_sql_values(pSql, 'fetchall'))
index_id = 0
for result in output_list:
source = 'ref'
model_name = result
ph_models = self.list_UD4H_models()
if '_minutes' in model_name:
model_name = model_name[:-8] + '_min'
for model in ph_models:
ph_model_name = "".join(model[0]['name'].split())[:-4].lower()
if ph_model_name == model_name:
source = model[0]['source']
pSql = '''
insert into {outcome_summary_schema}.{outcome_summary_table} values ({index_id}, '{column_name}', '{source}', {model_output});
'''.format(index_id=index_id, column_name=str(result), source=str(source), model_output=summary_results[index_id], **sql_config_dict)
execute_sql(pSql)
index_id += 1
def populate_public_health_grid_relation_table(sql_config_dict):
join_exists = table_exists(
sql_config_dict['source_grid_table'] + '_join',
sql_config_dict['public_health_variables_schema'])
if not join_exists:
pSql = '''
create table {public_health_variables_schema}.{source_grid_table}_join
as select
a.id as grid_id, b.id as primary_id, c.id as census_id,
st_area(st_intersection(a.wkb_geometry, b.wkb_geometry)) / st_area(b.wkb_geometry) as primary_proportion
from
{source_grid_schema}.{source_grid_table} a,
{uf_canvas_schema}.{uf_canvas_table} b,
{census_rate_schema}.{census_rate_table} c
where
st_intersects(a.wkb_geometry, b.wkb_geometry) and
st_intersects(st_centroid(a.wkb_geometry), c.wkb_geometry);
'''.format(**sql_config_dict)
execute_sql(pSql)
add_attribute_idx(sql_config_dict['public_health_variables_schema'],
sql_config_dict['source_grid_table'] + '_join',
'grid_id')
add_attribute_idx(sql_config_dict['public_health_variables_schema'],
sql_config_dict['source_grid_table'] + '_join',
'primary_id')
add_attribute_idx(sql_config_dict['public_health_variables_schema'],
sql_config_dict['source_grid_table'] + '_join',
'census_id')
def populate_public_health_grid_relation_table(sql_config_dict):
join_exists = table_exists(sql_config_dict['source_grid_table'] + '_join',
sql_config_dict['public_health_variables_schema'])
if not join_exists:
pSql = '''
create table {public_health_variables_schema}.{source_grid_table}_join
as select
a.id as grid_id, b.id as primary_id, c.id as census_id,
st_area(st_intersection(a.wkb_geometry, b.wkb_geometry)) / st_area(b.wkb_geometry) as primary_proportion
from
{source_grid_schema}.{source_grid_table} a,
{uf_canvas_schema}.{uf_canvas_table} b,
{census_rate_schema}.{census_rate_table} c
where
st_intersects(a.wkb_geometry, b.wkb_geometry) and
st_intersects(st_centroid(a.wkb_geometry), c.wkb_geometry);
'''.format(**sql_config_dict)
execute_sql(pSql)
add_attribute_idx(sql_config_dict['public_health_variables_schema'],
sql_config_dict['source_grid_table'] + '_join', 'grid_id')
add_attribute_idx(sql_config_dict['public_health_variables_schema'],
sql_config_dict['source_grid_table'] + '_join', 'primary_id')
add_attribute_idx(sql_config_dict['public_health_variables_schema'],
sql_config_dict['source_grid_table'] + '_join', 'census_id')
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_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_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 aggregate_results_to_block_group(self, sql_config_dict):
"""
Aggregates the result table (at grid scale) to a Census Block Group result
"""
attribute_list = filter(
lambda x: x not in [
'id', 'hh', 'pop', 'pop_adult', 'pop_adult_high',
'pop_adult_med', 'pop_adult_low', 'pop_senior', 'pop_teen',
'pop_children'
], self.outcome_fields)
field_calculations = ''',
'''.join([
"case when SUM(grid.pop) > 0 then SUM(grid.{field} * grid.pop) / SUM(grid.pop) else 0 end as {field}"
.format(field=field) for field in attribute_list
])
drop_table(
'{block_group_outcome_schema}.{block_group_outcome_table}'.format(
**sql_config_dict))
create_blockgroup_results = """
CREATE TABLE {block_group_outcome_schema}.{block_group_outcome_table} AS SELECT
bg.id as id,
bg.blockgroup as blockgroup,
bg.tract as tract,
bg.county_name as county_name,
SUM(grid.pop) as pop,
SUM(grid.hh) as hh,
SUM(grid.pop_adult) as pop_adult,
SUM(grid.pop_adult_high) as pop_adult_high,
SUM(grid.pop_adult_med) as pop_adult_med,
SUM(grid.pop_adult_low) as pop_adult_low,
SUM(grid.pop_senior) as pop_senior,
SUM(grid.pop_teen) as pop_teen,
SUM(grid.pop_children) as pop_children,
{field_calculations},
bg.wkb_geometry as wkb_geometry
FROM (select grid_id, census_id from {public_health_variables_schema}.{source_grid_table}_join group by grid_id, census_id) grid_portions
inner join {grid_outcome_schema}.{grid_outcome_table} grid on grid_portions.grid_id = grid.id
inner join {census_rate_schema}.{census_rate_table} bg on grid_portions.census_id = bg.id
group by bg.id, bg.blockgroup, tract, bg.county_name, bg.wkb_geometry;
""".format(field_calculations=field_calculations, **sql_config_dict)
execute_sql(create_blockgroup_results)
def run_analysis(self, **kwargs):
start_time = time.time()
self.report_progress(0.1, **kwargs)
self.climate_zone_class = self.config_entity.db_entity_feature_class(DbEntityKey.CLIMATE_ZONES)
self.energy_class = self.config_entity.db_entity_feature_class(DbEntityKey.ENERGY)
self.base_class = self.config_entity.db_entity_feature_class(DbEntityKey.BASE_CANVAS)
self.rel_table = parse_schema_and_table(self.energy_class._meta.db_table)[1]
self.rel_column = self.energy_class._meta.parents.values()[0].column
options = dict(
energy_result_table=self.energy_class.db_entity_key,
energy_schema=parse_schema_and_table(self.energy_class._meta.db_table)[0],
base_table=self.base_class.db_entity_key,
base_schema=parse_schema_and_table(self.base_class._meta.db_table)[0],
)
logger.info("Running Energy Updater Tool with options %s" % options)
if isinstance(self.config_entity.subclassed, FutureScenario):
self.end_state_class = self.config_entity.db_entity_feature_class(DbEntityKey.END_STATE)
logger.info("Running Future Calculations")
energy_output_list, options = self.run_future_calculations(options, **kwargs)
else:
logger.info("Running Base Calculations")
energy_output_list, options = self.run_base_calculations(options, **kwargs)
logger.info("Writing to feature table {energy_schema}.{energy_result_table}".format(**options))
self.write_results_to_database(options, energy_output_list)
logger.info("Writing to relative table {energy_schema}.{rel_table}".format(
energy_schema=options['energy_schema'],
rel_table=self.rel_table))
updated = datetime.datetime.now()
truncate_table(options['energy_schema'] + '.' + self.rel_table)
pSql = '''
insert into {energy_schema}.{rel_table} ({rel_column}, updated)
select id, '{updated}' from {energy_schema}.{energy_result_table};'''.format(
energy_schema=options['energy_schema'],
energy_result_table=options['energy_result_table'],
rel_table=self.rel_table,
rel_column=self.rel_column,
updated=updated)
execute_sql(pSql)
from footprint.main.publishing.data_import_publishing import create_and_populate_relations
create_and_populate_relations(self.config_entity, self.config_entity.computed_db_entities(key=DbEntityKey.ENERGY)[0])
self.report_progress(0.2, **kwargs)
logger.info('Finished: ' + str(time.time() - start_time))
def run_analysis(self, **kwargs):
start_time = time.time()
self.report_progress(0.1, **kwargs)
self.climate_zone_class = self.config_entity.db_entity_feature_class(DbEntityKey.CLIMATE_ZONES)
self.energy_class = self.config_entity.db_entity_feature_class(DbEntityKey.ENERGY)
self.base_class = self.config_entity.db_entity_feature_class(DbEntityKey.BASE_CANVAS)
self.rel_table = parse_schema_and_table(self.energy_class._meta.db_table)[1]
self.rel_column = self.energy_class._meta.parents.values()[0].column
options = dict(
energy_result_table=self.energy_class.db_entity_key,
energy_schema=parse_schema_and_table(self.energy_class._meta.db_table)[0],
base_table=self.base_class.db_entity_key,
base_schema=parse_schema_and_table(self.base_class._meta.db_table)[0],
)
logger.info("Running Energy Updater Tool with options %s" % options)
if isinstance(self.config_entity.subclassed, FutureScenario):
self.end_state_class = self.config_entity.db_entity_feature_class(DbEntityKey.END_STATE)
logger.info("Running Future Calculations")
energy_output_list, options = self.run_future_calculations(options, **kwargs)
else:
logger.info("Running Base Calculations")
energy_output_list, options = self.run_base_calculations(options, **kwargs)
logger.info("Writing to feature table {energy_schema}.{energy_result_table}".format(**options))
self.write_results_to_database(options, energy_output_list)
logger.info("Writing to relative table {energy_schema}.{rel_table}".format(
energy_schema=options['energy_schema'],
rel_table=self.rel_table))
updated = datetime.datetime.now()
truncate_table(options['energy_schema'] + '.' + self.rel_table)
pSql = '''
insert into {energy_schema}.{rel_table} ({rel_column}, updated)
select id, '{updated}' from {energy_schema}.{energy_result_table};'''.format(
energy_schema=options['energy_schema'],
energy_result_table=options['energy_result_table'],
rel_table=self.rel_table,
rel_column=self.rel_column,
updated=updated)
execute_sql(pSql)
from footprint.main.publishing.data_import_publishing import create_and_populate_relations
create_and_populate_relations(self.config_entity, self.config_entity.computed_db_entities(key=DbEntityKey.ENERGY)[0])
self.report_progress(0.2, **kwargs)
logger.info('Finished: ' + str(time.time() - start_time))
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 aggregate_results_to_block_group(self, sql_config_dict):
"""
Aggregates the result table (at grid scale) to a Census Block Group result
"""
attribute_list = filter(lambda x: x not in ['id', 'hh', 'pop', 'pop_adult', 'pop_adult_high', 'pop_adult_med',
'pop_adult_low', 'pop_senior', 'pop_teen', 'pop_children'],
self.outcome_fields)
field_calculations = ''',
'''.join([
"case when SUM(grid.pop) > 0 then SUM(grid.{field} * grid.pop) / SUM(grid.pop) else 0 end as {field}".format(
field=field)
for field in attribute_list
])
drop_table('{block_group_outcome_schema}.{block_group_outcome_table}'.format(**sql_config_dict))
create_blockgroup_results = """
CREATE TABLE {block_group_outcome_schema}.{block_group_outcome_table} AS SELECT
bg.id as id,
bg.blockgroup as blockgroup,
bg.tract as tract,
bg.county_name as county_name,
SUM(grid.pop) as pop,
SUM(grid.hh) as hh,
SUM(grid.pop_adult) as pop_adult,
SUM(grid.pop_adult_high) as pop_adult_high,
SUM(grid.pop_adult_med) as pop_adult_med,
SUM(grid.pop_adult_low) as pop_adult_low,
SUM(grid.pop_senior) as pop_senior,
SUM(grid.pop_teen) as pop_teen,
SUM(grid.pop_children) as pop_children,
{field_calculations},
bg.wkb_geometry as wkb_geometry
FROM (select grid_id, census_id from {public_health_variables_schema}.{source_grid_table}_join group by grid_id, census_id) grid_portions
inner join {grid_outcome_schema}.{grid_outcome_table} grid on grid_portions.grid_id = grid.id
inner join {census_rate_schema}.{census_rate_table} bg on grid_portions.census_id = bg.id
group by bg.id, bg.blockgroup, tract, bg.county_name, bg.wkb_geometry;
""".format(field_calculations=field_calculations, **sql_config_dict)
execute_sql(create_blockgroup_results)
def run_water_calculations(self, **kwargs):
self.format_policy_inputs()
self.water_class = self.config_entity.db_entity_feature_class(
DbEntityKey.WATER)
self.base_class = self.config_entity.db_entity_feature_class(
DbEntityKey.BASE_CANVAS)
self.climate_zone_class = self.config_entity.db_entity_feature_class(
DbEntityKey.CLIMATE_ZONES)
self.rel_table = parse_schema_and_table(
self.water_class._meta.db_table)[1]
self.rel_column = self.water_class._meta.parents.values()[0].column
if isinstance(self.config_entity.subclassed, FutureScenario):
self.report_progress(0.2, **kwargs)
self.end_state_class = self.config_entity.db_entity_feature_class(
DbEntityKey.END_STATE)
water_output_list, options = self.run_future_water_calculations(
**kwargs)
else:
self.report_progress(0.2, **kwargs)
water_output_list, options = self.run_base_water_calculations()
self.report_progress(0.7, **kwargs)
self.write_water_results_to_database(options, water_output_list)
updated = datetime.datetime.now()
truncate_table(options['water_schema'] + '.' + self.rel_table)
pSql = '''
insert into {water_schema}.{rel_table} ({rel_column}, updated) select id, '{updated}' from {water_schema}.{water_result_table};'''.format(
water_schema=options['water_schema'],
water_result_table=options['water_result_table'],
rel_table=self.rel_table,
rel_column=self.rel_column,
updated=updated)
execute_sql(pSql)
from footprint.main.publishing.data_import_publishing import create_and_populate_relations
create_and_populate_relations(
self.config_entity,
self.config_entity.computed_db_entities(key=DbEntityKey.WATER)[0])
self.report_progress(0.10000001, **kwargs)
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 update_result_table_geometry_column(self, options):
pSql = '''alter table {vmt_schema}.{vmt_result_table} add column wkb_geometry geometry (GEOMETRY, 4326);
'''.format(**options)
execute_sql(pSql)
pSql = '''
update {vmt_schema}.{vmt_result_table} b set wkb_geometry = a.wkb_geometry
from (select id, wkb_geometry from {uf_canvas_schema}.{uf_canvas_table}) a
where cast(a.id as int) = cast(b.id as int);
CREATE INDEX ON {vmt_schema}.{vmt_result_table} USING gist (wkb_geometry);
'''.format(**options)
execute_sql(pSql)
updated = datetime.datetime.now()
truncate_table(options['vmt_schema'] + '.' + options['vmt_rel_table'])
pSql = '''
insert into {vmt_schema}.{rel_table} ({rel_column}, updated)
select id, '{updated}' from {vmt_schema}.{vmt_result_table};'''.format(
vmt_schema=options['vmt_schema'],
vmt_result_table=options['vmt_result_table'],
rel_table=options['vmt_rel_table'],
rel_column=options['vmt_rel_column'],
updated=updated)
execute_sql(pSql)
from footprint.main.publishing.data_import_publishing import create_and_populate_relations
create_and_populate_relations(
options['config_entity'],
options['config_entity'].computed_db_entities(
key=DbEntityKey.VMT)[0])
def update_result_table_geometry_column(self, options):
pSql = '''alter table {vmt_schema}.{vmt_result_table} add column wkb_geometry geometry (GEOMETRY, 4326);
'''.format(**options)
execute_sql(pSql)
pSql = '''
update {vmt_schema}.{vmt_result_table} b set wkb_geometry = a.wkb_geometry
from (select id, wkb_geometry from {uf_canvas_schema}.{uf_canvas_table}) a
where cast(a.id as int) = cast(b.id as int);
CREATE INDEX ON {vmt_schema}.{vmt_result_table} USING gist (wkb_geometry);
'''.format(**options)
execute_sql(pSql)
updated = datetime.datetime.now()
truncate_table(options['vmt_schema'] + '.' + options['vmt_rel_table'])
pSql = '''
insert into {vmt_schema}.{rel_table} ({rel_column}, updated)
select id, '{updated}' from {vmt_schema}.{vmt_result_table};'''.format(
vmt_schema=options['vmt_schema'],
vmt_result_table=options['vmt_result_table'],
rel_table=options['vmt_rel_table'],
rel_column=options['vmt_rel_column'],
updated=updated)
execute_sql(pSql)
from footprint.main.publishing.data_import_publishing import create_and_populate_relations
create_and_populate_relations(options['config_entity'], options['config_entity'].computed_db_entities(key=DbEntityKey.VMT)[0])
def write_results_to_database(self, options, public_health_output_list):
drop_table(
'{grid_outcome_schema}.{grid_outcome_table}'.format(**options))
attribute_list = filter(lambda x: x != 'id', self.outcome_fields)
options['output_field_syntax'] = 'id int, ' + \
create_sql_calculations(attribute_list, '{0} numeric(20,8)')
execute_sql(
"create table {grid_outcome_schema}.{grid_outcome_table} ({output_field_syntax});"
.format(**options))
output_textfile = StringIO("")
for row in public_health_output_list:
stringrow = []
for item in row:
if isinstance(item, int):
stringrow.append(str(item))
else:
stringrow.append(str(round(item, 8)))
output_textfile.write("\t".join(stringrow) + "\n")
output_textfile.seek(os.SEEK_SET)
#copy text file output back into Postgres
copy_from_text_to_db(
output_textfile,
'{grid_outcome_schema}.{grid_outcome_table}'.format(**options))
output_textfile.close()
##---------------------------
pSql = '''alter table {grid_outcome_schema}.{grid_outcome_table}
add column wkb_geometry geometry (GEOMETRY, 4326);'''.format(
**options)
execute_sql(pSql)
pSql = '''update {grid_outcome_schema}.{grid_outcome_table} b set
wkb_geometry = st_setSRID(a.wkb_geometry, 4326)
from (select id, wkb_geometry from {source_grid_schema}.{source_grid_table}) a
where cast(a.id as int) = cast(b.id as int);
'''.format(**options)
execute_sql(pSql)
add_geom_idx(options['grid_outcome_schema'],
options['grid_outcome_table'], 'wkb_geometry')
add_primary_key(options['grid_outcome_schema'],
options['grid_outcome_table'], 'id')
# Since not every grid cell results in a grid_outcome, we need to wipe out the rel
# table and recreate it to match the base grid_coutcome table. Otherwise there will
# be to many rel table rows and cloning the DbEntity or ConfigEntity will fail
logger.info(
"Writing to relative table {grid_outcome_schema}.{grid_outcome_table}rel"
.format(**options))
truncate_table(
"{grid_outcome_schema}.{grid_outcome_table}rel".format(**options))
from footprint.main.publishing.data_import_publishing import create_and_populate_relations
create_and_populate_relations(
self.config_entity,
self.config_entity.computed_db_entities(
key=DbEntityKey.PH_GRID_OUTCOMES)[0])
def run_water_calculations(self, **kwargs):
self.format_policy_inputs()
self.water_class = self.config_entity.db_entity_feature_class(DbEntityKey.WATER)
self.base_class = self.config_entity.db_entity_feature_class(DbEntityKey.BASE_CANVAS)
self.climate_zone_class = self.config_entity.db_entity_feature_class(DbEntityKey.CLIMATE_ZONES)
self.rel_table = parse_schema_and_table(self.water_class._meta.db_table)[1]
self.rel_column = self.water_class._meta.parents.values()[0].column
if isinstance(self.config_entity.subclassed, FutureScenario):
self.report_progress(0.2, **kwargs)
self.end_state_class = self.config_entity.db_entity_feature_class(DbEntityKey.END_STATE)
water_output_list, options = self.run_future_water_calculations(**kwargs)
else:
self.report_progress(0.2, **kwargs)
water_output_list, options = self.run_base_water_calculations()
self.report_progress(0.7, **kwargs)
self.write_water_results_to_database(options, water_output_list)
updated = datetime.datetime.now()
truncate_table(options['water_schema'] + '.' + self.rel_table)
pSql = '''
insert into {water_schema}.{rel_table} ({rel_column}, updated) select id, '{updated}' from {water_schema}.{water_result_table};'''.format(
water_schema=options['water_schema'],
water_result_table=options['water_result_table'],
rel_table=self.rel_table,
rel_column=self.rel_column,
updated=updated)
execute_sql(pSql)
from footprint.main.publishing.data_import_publishing import create_and_populate_relations
create_and_populate_relations(self.config_entity, self.config_entity.computed_db_entities(key=DbEntityKey.WATER)[0])
self.report_progress(0.10000001, **kwargs)
def write_results_to_database(self, options, energy_output_list):
drop_table('{energy_schema}.{energy_result_table}'.format(**options))
attribute_list = filter(
lambda x: x not in ['id', 'title24_zone', 'fcz_zone'],
self.output_fields)
output_field_syntax = 'id int, title24_zone int, fcz_zone int, ' + create_sql_calculations(
attribute_list, '{0} numeric(14, 4)')
pSql = '''
create table {energy_schema}.{energy_result_table} ({output_field_syntax});'''.format(
output_field_syntax=output_field_syntax, **options)
execute_sql(pSql)
output_textfile = StringIO("")
for row in energy_output_list:
stringrow = []
for item in row:
if isinstance(item, int):
stringrow.append(str(item))
else:
stringrow.append(str(round(item, 4)))
output_textfile.write("\t".join(stringrow) + "\n")
output_textfile.seek(os.SEEK_SET)
#copy text file output back into Postgres
copy_from_text_to_db(
output_textfile,
'{energy_schema}.{energy_result_table}'.format(**options))
output_textfile.close()
pSql = '''alter table {energy_schema}.{energy_result_table} add column wkb_geometry geometry (GEOMETRY, 4326);'''.format(
**options)
execute_sql(pSql)
pSql = '''update {energy_schema}.{energy_result_table} b set
wkb_geometry = st_setSRID(a.wkb_geometry, 4326)
from (select id, wkb_geometry from {base_schema}.{base_table}) a
where cast(a.id as int) = cast(b.id as int);
'''.format(**options)
execute_sql(pSql)
add_geom_idx(options['energy_schema'], options['energy_result_table'],
'wkb_geometry')
add_primary_key(options['energy_schema'],
options['energy_result_table'], 'id')
add_attribute_idx(options['energy_schema'],
options['energy_result_table'],
'annual_million_btus_per_unit')
def write_results_to_database(self, options, public_health_output_list):
drop_table('{grid_outcome_schema}.{grid_outcome_table}'.format(**options))
attribute_list = filter(lambda x: x != 'id', self.outcome_fields)
options['output_field_syntax'] = 'id int, ' + \
create_sql_calculations(attribute_list, '{0} numeric(20,8)')
execute_sql("create table {grid_outcome_schema}.{grid_outcome_table} ({output_field_syntax});".format(
**options))
output_textfile = StringIO("")
for row in public_health_output_list:
stringrow = []
for item in row:
if isinstance(item, int):
stringrow.append(str(item))
else:
stringrow.append(str(round(item, 8)))
output_textfile.write("\t".join(stringrow) + "\n")
output_textfile.seek(os.SEEK_SET)
#copy text file output back into Postgres
copy_from_text_to_db(output_textfile, '{grid_outcome_schema}.{grid_outcome_table}'.format(**options))
output_textfile.close()
##---------------------------
pSql = '''alter table {grid_outcome_schema}.{grid_outcome_table}
add column wkb_geometry geometry (GEOMETRY, 4326);'''.format(**options)
execute_sql(pSql)
pSql = '''update {grid_outcome_schema}.{grid_outcome_table} b set
wkb_geometry = st_setSRID(a.wkb_geometry, 4326)
from (select id, wkb_geometry from {source_grid_schema}.{source_grid_table}) a
where cast(a.id as int) = cast(b.id as int);
'''.format(**options)
execute_sql(pSql)
add_geom_idx(options['grid_outcome_schema'], options['grid_outcome_table'], 'wkb_geometry')
add_primary_key(options['grid_outcome_schema'], options['grid_outcome_table'], 'id')
# Since not every grid cell results in a grid_outcome, we need to wipe out the rel
# table and recreate it to match the base grid_coutcome table. Otherwise there will
# be to many rel table rows and cloning the DbEntity or ConfigEntity will fail
logger.info("Writing to relative table {grid_outcome_schema}.{grid_outcome_table}rel".format(**options))
truncate_table("{grid_outcome_schema}.{grid_outcome_table}rel".format(**options))
from footprint.main.publishing.data_import_publishing import create_and_populate_relations
create_and_populate_relations(
self.config_entity,
self.config_entity.computed_db_entities(key=DbEntityKey.PH_GRID_OUTCOMES)[0])
def write_results_to_database(self, options, energy_output_list):
drop_table('{energy_schema}.{energy_result_table}'.format(**options))
attribute_list = filter(lambda x: x not in ['id', 'title24_zone', 'fcz_zone'], self.output_fields)
output_field_syntax = 'id int, title24_zone int, fcz_zone int, ' + create_sql_calculations(attribute_list, '{0} numeric(14, 4)')
pSql = '''
create table {energy_schema}.{energy_result_table} ({output_field_syntax});'''.format(output_field_syntax=output_field_syntax, **options)
execute_sql(pSql)
output_textfile = StringIO("")
for row in energy_output_list:
stringrow = []
for item in row:
if isinstance(item, int):
stringrow.append(str(item))
else:
stringrow.append(str(round(item, 4)))
output_textfile.write("\t".join(stringrow) + "\n")
output_textfile.seek(os.SEEK_SET)
#copy text file output back into Postgres
copy_from_text_to_db(output_textfile, '{energy_schema}.{energy_result_table}'.format(**options))
output_textfile.close()
pSql = '''alter table {energy_schema}.{energy_result_table} add column wkb_geometry geometry (GEOMETRY, 4326);'''.format(**options)
execute_sql(pSql)
pSql = '''update {energy_schema}.{energy_result_table} b set
wkb_geometry = st_setSRID(a.wkb_geometry, 4326)
from (select id, wkb_geometry from {base_schema}.{base_table}) a
where cast(a.id as int) = cast(b.id as int);
'''.format(**options)
execute_sql(pSql)
add_geom_idx(options['energy_schema'], options['energy_result_table'], 'wkb_geometry')
add_primary_key(options['energy_schema'], options['energy_result_table'], 'id')
add_attribute_idx(options['energy_schema'], options['energy_result_table'], 'annual_million_btus_per_unit')
def add_analysis_geom(schema, table):
pSql = '''
DROP INDEX {schema}.{schema}_{table}_analysis_geom;
Alter Table {schema}.{table} drop column analysis_geom;'''.format(schema=schema, table=table)
try:
execute_sql(pSql)
except:
print "analysis column did not exist... removing"
pSql = '''alter table {schema}.{table} add column analysis_geom geometry;'''.format(schema=schema, table=table)
try:
execute_sql(pSql)
except:
print "analysis column already exists... continuing"
pSql = '''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);
'''.format(schema=schema, table=table)
execute_sql(pSql)
def add_analysis_geom(schema, table):
pSql = '''
DROP INDEX {schema}.{schema}_{table}_analysis_geom;
Alter Table {schema}.{table} drop column analysis_geom;'''.format(schema=schema, table=table)
try:
execute_sql(pSql)
except:
print "analysis column did not exist... removing"
pSql = '''alter table {schema}.{table} add column analysis_geom geometry;'''.format(schema=schema, table=table)
try:
execute_sql(pSql)
except:
print "analysis column already exists... continuing"
pSql = '''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);
'''.format(schema=schema, table=table)
execute_sql(pSql)
def aggregate_within_distance(distance_options):
thread_count = count_cores()
queue = queue_process()
source_table_column_list = []
for key, value in distance_options['variable_field_dict'].items():
source_table_column_list += value
source_table_column_list = list(set(source_table_column_list))
sql_format = 'out {formatter} float'.format(formatter="{0}")
output_field_format = create_sql_calculations(source_table_column_list, sql_format, ', ')
sql_format = 'cast({aggregation_type}({formatter}) as float) as {formatter}_{suffix}'.format(formatter="{0}", **distance_options)
sql_calculations_format = create_sql_calculations(source_table_column_list, sql_format, ', ')
pSql = '''drop function if exists aggregate_within_distance_tool(
in_id int,
in_wkb_geometry geometry,
out id int,
{output_field_format}) cascade;'''.format(
output_field_format=output_field_format)
execute_sql(pSql)
pSql = '''
CREATE OR REPLACE FUNCTION aggregate_within_distance_tool(
in_id int,
in_wkb_geometry geometry,
out id int,
{output_field_format})
AS
$$
select
$1 as id,
{sql_calculations_format}
from (select *, {source_geometry_column} as geometry from {source_table}) ref
where ST_DWITHIN( $2, ref.geometry, {distance}) and (ref.{source_table_query});
$$
COST 10000
language SQL STABLE strict;
'''.format(source_table=distance_options['source_table'],
source_table_query=distance_options['source_table_query'],
distance=distance_options['distance'],
source_geometry_column=distance_options['source_geometry_column'],
output_field_format=output_field_format,
sql_calculations_format=sql_calculations_format)
execute_sql(pSql)
drop_table('{target_table_schema}.{target_table}_{suffix}'.format(
target_table_schema=distance_options['target_table_schema'],
target_table=distance_options['target_table'], suffix=distance_options['suffix']))
sql_format = '{formatter}_{suffix} float'.format(formatter="{0}", **distance_options)
output_table_field_format = create_sql_calculations(source_table_column_list, sql_format, ', ')
pSql = '''create table {target_table_schema}.{target_table}_{suffix} (id int, {output_table_field_format});'''.format(
target_table_schema=distance_options['target_table_schema'], target_table=distance_options['target_table'],
suffix=distance_options['suffix'], output_table_field_format=output_table_field_format)
execute_sql(pSql)
pSql = 'select cast({target_table_pk} as int) from {target_table_schema}.{target_table} where {target_table_query} order by {target_table_pk}'.format(
target_table_schema=distance_options['target_table_schema'],
target_table=distance_options['target_table'],
target_table_pk=distance_options['target_table_pk'],
target_table_query=distance_options['target_table_query'])
id_list = flatten(report_sql_values(pSql, 'fetchall'))
insert_sql = '''
insert into {target_table_schema}.{target_table}_{suffix}
select (f).* from (
select aggregate_within_distance_tool({target_table_pk}, {target_geometry_column}) as f
from {target_table_schema}.{target_table}
where
{target_table_pk} >= {bottom_range_id} and
{target_table_pk} <= {top_range_id} and
{target_table_query}
offset 0) s
where (f).id is not null;
'''.format(
target_table_schema=distance_options['target_table_schema'],
target_table=distance_options['target_table'],
target_table_query=distance_options['target_table_query'],
target_geometry_column=distance_options['target_geometry_column'],
source_table=distance_options['source_table'],
suffix=distance_options['suffix'],
target_table_pk=distance_options['target_table_pk'],
bottom_range_id="{start_id}",
top_range_id="{end_id}")
for i in range(thread_count):
t = MultithreadProcess(queue, insert_sql)
t.setDaemon(True)
t.start()
#populate queue with data
rows_per_thread = len(id_list) / thread_count
offset = 0
for i in range(thread_count):
if i == thread_count - 1:
## last bucket gets any remainder, too
last_thread = len(id_list) - 1
else:
last_thread = offset + rows_per_thread - 1
rows_to_process = {
'start_id': id_list[offset],
'end_id': id_list[last_thread]
}
offset += rows_per_thread
queue.put(rows_to_process)
#wait on the queue until everything has been processed
queue.join()
add_attribute_idx(distance_options['target_table_schema'],
'{target_table}_{suffix}'.format(target_table=distance_options['target_table'],
suffix=distance_options['suffix']), 'id')
count = 1
update_sql_format = ''
if len(distance_options['variable_field_dict']) > 0:
for key, value in distance_options['variable_field_dict'].items():
update_table_field_format = create_sql_calculations(value, '{formatter}_{suffix}'.format(formatter='b.{0}',
**distance_options), ' + ')
if count == 1:
update_sql_format += key + ' = ' + "(case when {0} is null then 0 else {0} end)".format(update_table_field_format)
else:
update_sql_format += ', ' + key + ' = ' + "(case when {0} is null then 0 else {0} end)".format(update_table_field_format)
count +=1
pSql = '''
update {target_table_schema}.{target_table} a set {update_sql_format}
from (select * from {target_table_schema}.{target_table}_{suffix}) b
where a.{target_table_pk} = b.id and {target_table_query}
'''.format(
target_table_schema=distance_options['target_table_schema'],
target_table=distance_options['target_table'],
target_table_query=distance_options['target_table_query'],
target_table_pk=distance_options['target_table_pk'],
update_sql_format=update_sql_format,
suffix=distance_options['suffix']
)
execute_sql(pSql)
drop_table('{target_table_schema}.{target_table}_{suffix}'.format(
target_table_schema=distance_options['target_table_schema'],
target_table=distance_options['target_table'],
suffix=distance_options['suffix']))
def update(self, **kwargs):
"""
This function handles the update or creation on the environmental constraints geography producing the area
for each layer with the environmental constraint behavior. This function will both add and remove
constraints and produce the final constraints layer in the primary geography of the active scenario
"""
# TODO : remove hard-coded 3310 (only works in CA), need to set an "analysis projection" in the Region
start_time = time.time()
current_db_entities = \
set(self.config_entity.db_entities_having_behavior_key(BehaviorKey.Fab.ricate('environmental_constraint')))
base_feature_class = self.config_entity.db_entity_feature_class(
DbEntityKey.BASE_CANVAS)
options = dict(project_schema=parse_schema_and_table(
base_feature_class._meta.db_table)[0],
base_table=base_feature_class.db_entity_key)
logger.info('Inserting raw geographies into the environmental constraint geographies table for DbEntities: %s' % \
', '.join(map(lambda db_entity: db_entity.name, current_db_entities)))
drop_table(
'{project_schema}.environmental_constraint_geographies_table'.
format(project_schema=options['project_schema']))
current_environmental_constraints = []
for db_entity in current_db_entities:
constraint_class = self.config_entity.db_entity_feature_class(
db_entity.key)
current_environmental_constraints.append(
constraint_class.db_entity_key)
create_id_field_format = create_sql_calculations(
current_environmental_constraints, '{0}_id int')
insert_id_field_format = create_sql_calculations(
current_environmental_constraints, '{0}_id')
pSql = '''
create table {project_schema}.environmental_constraint_geographies_table
(primary_id integer, wkb_geometry geometry, {create_id_field_format});
SELECT UpdateGeometrySRID('{project_schema}', 'environmental_constraint_geographies_table', 'wkb_geometry', 3310)
'''.format(project_schema=options['project_schema'],
create_id_field_format=create_id_field_format)
execute_sql(pSql)
for db_entity in current_db_entities:
logger.info(
'Inserting into environmental constraint geographies table for DbEntity: %s'
% db_entity.full_name)
constraint_class = self.config_entity.db_entity_feature_class(
db_entity.key)
pSql = '''
insert into {project_schema}.environmental_constraint_geographies_table (primary_id, wkb_geometry, {constraint_db_entity_key}_id) select
cast(primary_id as int), wkb_geometry, {constraint_db_entity_key}_id from (
select
id as primary_id,
{constraint_db_entity_id} as {constraint_db_entity_key}_id,
st_setSRID(st_transform(st_buffer((st_dump(wkb_geometry)).geom, 0), 3310), 3310) as wkb_geometry
from (
select b.id, st_intersection(a.wkb_geometry, b.wkb_geometry) as wkb_geometry
from {constraint_schema}.{constraint_db_entity_key} a,
{project_schema}.{base_table} b
where st_intersects(a.wkb_geometry, b.wkb_geometry)) as intersection
) as polygons;
'''.format(
project_schema=options['project_schema'],
base_table=options['base_table'],
constraint_schema=parse_schema_and_table(
constraint_class._meta.db_table)[0],
constraint_db_entity_key=constraint_class.db_entity_key,
constraint_db_entity_id=db_entity.id)
execute_sql(pSql)
logger.info(
'finished inserting db_entity: {db_entity} {time} elapsed'.
format(time=time.time() - start_time,
db_entity=constraint_class.db_entity_key))
#only regenerate the merged environmental constraint whenever an envrionmental constraint is added or removed
# from the layer
add_geom_idx(options['project_schema'],
'environmental_constraint_geographies_table')
logger.info('Unioning all environmental constraint geographies')
drop_table(
'{project_schema}.environmental_constraint_geographies_table_unioned'
.format(project_schema=options['project_schema']))
pSql = '''
CREATE TABLE {project_schema}.environmental_constraint_geographies_table_unioned
(id serial, wkb_geometry geometry, acres float, primary_id int, {create_id_field_format});
SELECT UpdateGeometrySRID('{project_schema}', 'environmental_constraint_geographies_table_unioned', 'wkb_geometry', 3310);
'''.format(project_schema=options['project_schema'],
create_id_field_format=create_id_field_format)
execute_sql(pSql)
pSql = '''
insert into {project_schema}.environmental_constraint_geographies_table_unioned (wkb_geometry, acres, primary_id, {insert_id_field_format})
SELECT
st_buffer(wkb_geometry, 0) as wkb_geometry,
st_area(st_buffer(wkb_geometry, 0)) * 0.000247105 as acres,
primary_id, {insert_id_field_format}
FROM (
SELECT
(ST_Dump(wkb_geometry)).geom as wkb_geometry,
primary_id, {insert_id_field_format}
FROM (
SELECT ST_Polygonize(wkb_geometry) AS wkb_geometry, primary_id, {insert_id_field_format} FROM (
SELECT ST_Collect(wkb_geometry) AS wkb_geometry, primary_id, {insert_id_field_format} FROM (
SELECT ST_ExteriorRing(wkb_geometry) AS wkb_geometry, primary_id, {insert_id_field_format}
FROM {project_schema}.environmental_constraint_geographies_table) AS lines
group by primary_id, {insert_id_field_format}) AS noded_lines
group by primary_id, {insert_id_field_format}) as polygons
) as final
WHERE st_area(st_buffer(wkb_geometry, 0)) > 5;'''.format(
project_schema=options['project_schema'],
insert_id_field_format=insert_id_field_format)
execute_sql(pSql)
logger.info('finished unioning env constraints: {time} elapsed'.format(
time=time.time() - start_time))
#reproject table back to 4326 for integration with web viewing
pSql = '''
SELECT UpdateGeometrySRID('{project_schema}', 'environmental_constraint_geographies_table_unioned', 'wkb_geometry', 4326);
update {project_schema}.environmental_constraint_geographies_table_unioned a set wkb_geometry = st_transform(st_buffer(wkb_geometry, 0), 4326);
'''.format(project_schema=options['project_schema'])
execute_sql(pSql)
add_geom_idx(options['project_schema'],
'environmental_constraint_geographies_table_unioned')
logger.info('Env Union Finished: %s' % str(time.time() - start_time))
def aggregate_results_to_outcomes_summary_table(self, sql_config_dict):
"""
Aggregates the result table (at grid scale) to a standardized summary table
"""
attribute_list = filter(
lambda x: x not in [
'id', 'hh', 'pop', 'pop_adult', 'pop_adult_high',
'pop_adult_med', 'pop_adult_low', 'pop_senior', 'pop_teen',
'pop_children'
], self.outcome_fields)
output_list = filter(lambda x: x not in ['id'], self.outcome_fields)
field_calculations = ''',
'''.join([
"case when SUM(pop) > 0 then SUM({field} * pop) / SUM(pop) else 0 end as {field}"
.format(field=field) for field in attribute_list
])
truncate_table(
'{outcome_summary_schema}.{outcome_summary_table}'.format(
**sql_config_dict))
pSql = """
SELECT
SUM(hh) as hh,
SUM(pop) as pop,
SUM(pop_adult) as pop_adult,
SUM(pop_adult_high) as pop_adult_high,
SUM(pop_adult_med) as pop_adult_med,
SUM(pop_adult_low) as pop_adult_low,
SUM(pop_senior) as pop_senior,
SUM(pop_teen) as pop_teen,
SUM(pop_children) as pop_children,
{field_calculations}
FROM {grid_outcome_schema}.{grid_outcome_table};
""".format(field_calculations=field_calculations, **sql_config_dict)
summary_results = flatten(report_sql_values(pSql, 'fetchall'))
index_id = 0
for result in output_list:
source = 'ref'
model_name = result
ph_models = self.list_UD4H_models()
if '_minutes' in model_name:
model_name = model_name[:-8] + '_min'
for model in ph_models:
ph_model_name = "".join(model[0]['name'].split())[:-4].lower()
if ph_model_name == model_name:
source = model[0]['source']
pSql = '''
insert into {outcome_summary_schema}.{outcome_summary_table} values ({index_id}, '{column_name}', '{source}', {model_output});
'''.format(index_id=index_id,
column_name=str(result),
source=str(source),
model_output=summary_results[index_id],
**sql_config_dict)
execute_sql(pSql)
index_id += 1
def update(self, **kwargs):
logger.debug("Executing Environmental Constraints using {0}".format(
self.config_entity))
config_entity = self.config_entity
end_state_feature_class = config_entity.db_entity_feature_class(
DbEntityKey.END_STATE)
base_table = config_entity.db_entity_feature_class(
DbEntityKey.BASE_CANVAS)
options = dict(project_schema=parse_schema_and_table(
base_table._meta.db_table)[0],
scenario_schema=parse_schema_and_table(
end_state_feature_class._meta.db_table)[0],
end_state_table=end_state_feature_class.db_entity_key)
current_db_entities, db_entities_to_add, db_entities_to_delete = \
self.update_or_create_environmental_constraint_percents(config_entity)
current_environmental_constraints = []
logger.debug("Current db_entities {0}".format(current_db_entities))
for db_entity in current_db_entities:
logger.debug("Active db_entity {0}".format(db_entity.key))
constraint_class = config_entity.db_entity_feature_class(
db_entity.key)
environmental_constraint_percent = EnvironmentalConstraintPercent.objects.filter(
db_entity_id=db_entity.id, analysis_tool_id=self.id)[0]
current_environmental_constraints.append(
dict(key=constraint_class.db_entity_key,
priority=environmental_constraint_percent.priority,
percent=environmental_constraint_percent.percent))
pSql = '''
DO $$
BEGIN
BEGIN
ALTER TABLE {project_schema}.environmental_constraint_geographies_table_unioned ADD COLUMN constraint_acres_{config_entity_id} float;
EXCEPTION
WHEN duplicate_column
THEN -- do nothing;
END;
END;
$$'''.format(project_schema=options['project_schema'],
config_entity_id=self.config_entity.id)
execute_sql(pSql)
logger.info(
'Calculate constraint acreage for the active scenario end state feature'
)
for db_entity in current_db_entities:
constraint_class = self.config_entity.db_entity_feature_class(
db_entity.key)
environmental_constraint_percent = EnvironmentalConstraintPercent.objects.filter(
db_entity_id=db_entity.id, analysis_tool_id=self.id)[0]
constraint_percent = environmental_constraint_percent.percent
active_constraint = filter(
lambda dct: constraint_class.db_entity_key in dct['key'],
current_environmental_constraints)[0]
priority_constraints = filter(
lambda dct: dct['priority'] < active_constraint['priority'] or
(dct['priority'] == active_constraint['priority'] and dct[
'percent'] > active_constraint['percent']),
current_environmental_constraints)
priority_key_list = []
for constraint in priority_constraints:
priority_key_list.append(constraint['key'])
priority_query = create_sql_calculations(
priority_key_list, ' and {0}_id is null',
' and a.primary_id is not null')
pSql = '''
update {project_schema}.environmental_constraint_geographies_table_unioned a set
constraint_acres_{config_entity_id} = acres * {percent} where {constraint}_id = {constraint_id} {priority_query};
'''.format(project_schema=options['project_schema'],
constraint=constraint_class.db_entity_key,
constraint_id=db_entity.id,
percent=constraint_percent,
priority_query=priority_query,
config_entity_id=self.config_entity.id)
execute_sql(pSql)
pSql = '''
update {scenario_schema}.{end_state_table} a set
acres_developable = a.acres_gross - b.constraint_acres
FROM
(select primary_id,
sum(constraint_acres_{config_entity_id}) as constraint_acres
from {project_schema}.environmental_constraint_geographies_table_unioned
where constraint_acres_{config_entity_id} is not null group by primary_id) b
where a.id= b.primary_id;
'''.format(scenario_schema=options['scenario_schema'],
project_schema=options['project_schema'],
end_state_table=options['end_state_table'],
config_entity_id=self.config_entity.id)
execute_sql(pSql)
pSql = '''
update {scenario_schema}.{end_state_table}
set developable_proportion = (
case when acres_gross > 0 then acres_developable / acres_gross else 0 end
)
'''.format(scenario_schema=options['scenario_schema'],
end_state_table=options['end_state_table'])
execute_sql(pSql)
def aggregate_within_distance(distance_options):
thread_count = count_cores()
queue = queue_process()
source_table_column_list = []
for key, value in distance_options['variable_field_dict'].items():
source_table_column_list += value
source_table_column_list = list(set(source_table_column_list))
sql_format = 'out {formatter} float'.format(formatter="{0}")
output_field_format = create_sql_calculations(source_table_column_list, sql_format, ', ')
sql_format = 'cast({aggregation_type}({formatter}) as float) as {formatter}_{suffix}'.format(formatter="{0}", **distance_options)
sql_calculations_format = create_sql_calculations(source_table_column_list, sql_format, ', ')
pSql = '''drop function if exists aggregate_within_distance_tool(
in_id int,
in_wkb_geometry geometry,
out id int,
{output_field_format}) cascade;'''.format(
output_field_format=output_field_format)
execute_sql(pSql)
pSql = '''
CREATE OR REPLACE FUNCTION aggregate_within_distance_tool(
in_id int,
in_wkb_geometry geometry,
out id int,
{output_field_format})
AS
$$
select
$1 as id,
{sql_calculations_format}
from (select *, {source_geometry_column} as geometry from {source_table}) ref
where ST_DWITHIN( $2, ref.geometry, {distance}) and (ref.{source_table_query});
$$
COST 10000
language SQL STABLE strict;
'''.format(source_table=distance_options['source_table'],
source_table_query=distance_options['source_table_query'],
distance=distance_options['distance'],
source_geometry_column=distance_options['source_geometry_column'],
output_field_format=output_field_format,
sql_calculations_format=sql_calculations_format)
execute_sql(pSql)
drop_table('{target_table_schema}.{target_table}_{suffix}'.format(
target_table_schema=distance_options['target_table_schema'],
target_table=distance_options['target_table'], suffix=distance_options['suffix']))
sql_format = '{formatter}_{suffix} float'.format(formatter="{0}", **distance_options)
output_table_field_format = create_sql_calculations(source_table_column_list, sql_format, ', ')
pSql = '''create table {target_table_schema}.{target_table}_{suffix} (id int, {output_table_field_format});'''.format(
target_table_schema=distance_options['target_table_schema'], target_table=distance_options['target_table'],
suffix=distance_options['suffix'], output_table_field_format=output_table_field_format)
execute_sql(pSql)
pSql = 'select cast({target_table_pk} as int) from {target_table_schema}.{target_table} where {target_table_query} order by {target_table_pk}'.format(
target_table_schema=distance_options['target_table_schema'],
target_table=distance_options['target_table'],
target_table_pk=distance_options['target_table_pk'],
target_table_query=distance_options['target_table_query'])
id_list = flatten(report_sql_values(pSql, 'fetchall'))
insert_sql = '''
insert into {target_table_schema}.{target_table}_{suffix}
select (f).* from (
select aggregate_within_distance_tool({target_table_pk}, {target_geometry_column}) as f
from {target_table_schema}.{target_table}
where
{target_table_pk} >= {bottom_range_id} and
{target_table_pk} <= {top_range_id} and
{target_table_query}
offset 0) s
where (f).id is not null;
'''.format(
target_table_schema=distance_options['target_table_schema'],
target_table=distance_options['target_table'],
target_table_query=distance_options['target_table_query'],
target_geometry_column=distance_options['target_geometry_column'],
source_table=distance_options['source_table'],
suffix=distance_options['suffix'],
target_table_pk=distance_options['target_table_pk'],
bottom_range_id="{start_id}",
top_range_id="{end_id}")
for i in range(thread_count):
t = MultithreadProcess(queue, insert_sql)
t.setDaemon(True)
t.start()
#populate queue with data
rows_per_thread = len(id_list) / thread_count
offset = 0
for i in range(thread_count):
if i == thread_count - 1:
## last bucket gets any remainder, too
last_thread = len(id_list) - 1
else:
last_thread = offset + rows_per_thread - 1
rows_to_process = {
'start_id': id_list[offset],
'end_id': id_list[last_thread]
}
offset += rows_per_thread
queue.put(rows_to_process)
#wait on the queue until everything has been processed
queue.join()
add_attribute_idx(distance_options['target_table_schema'],
'{target_table}_{suffix}'.format(target_table=distance_options['target_table'],
suffix=distance_options['suffix']), 'id')
count = 1
update_sql_format = ''
if len(distance_options['variable_field_dict']) > 0:
for key, value in distance_options['variable_field_dict'].items():
update_table_field_format = create_sql_calculations(value, '{formatter}_{suffix}'.format(formatter='b.{0}',
**distance_options), ' + ')
if count == 1:
update_sql_format += key + ' = ' + "(case when {0} is null then 0 else {0} end)".format(update_table_field_format)
else:
update_sql_format += ', ' + key + ' = ' + "(case when {0} is null then 0 else {0} end)".format(update_table_field_format)
count +=1
pSql = '''
update {target_table_schema}.{target_table} a set {update_sql_format}
from (select * from {target_table_schema}.{target_table}_{suffix}) b
where a.{target_table_pk} = b.id and {target_table_query}
'''.format(
target_table_schema=distance_options['target_table_schema'],
target_table=distance_options['target_table'],
target_table_query=distance_options['target_table_query'],
target_table_pk=distance_options['target_table_pk'],
update_sql_format=update_sql_format,
suffix=distance_options['suffix']
)
execute_sql(pSql)
drop_table('{target_table_schema}.{target_table}_{suffix}'.format(
target_table_schema=distance_options['target_table_schema'],
target_table=distance_options['target_table'],
suffix=distance_options['suffix']))
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_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 populate_grid_census_rate_attributes(sql_config_dict):
pSql = '''
update {public_health_variables_schema}.{public_health_variables_table} a set
pop = b.pop,
hh = b.hh,
intersection_density_sqmi = b.intersection_density_sqmi,
gender2 = (case when b.pop > 0 then b.pop_female_rate else 0 end),
age_children = (case when b.pop > 0 then b.pop_avg_age5_11 else 0 end),
age_teens = (case when b.pop > 0 then b.pop_avg_age12_17 else 0 end),
age_adult = (case when b.pop > 0 then b.pop_avg_age18_64 else 0 end),
age_seniors = (case when b.pop > 0 then b.pop_avg_age65_up else 0 end),
racehisp1 = (case when b.pop > 0 then b.pop_white_rate else 0 end),
racehisp2 = (case when b.pop > 0 then b.pop_black_rate else 0 end),
racehisp4 = (case when b.pop > 0 then b.pop_asian_rate else 0 end),
racehisp97 = (case when b.pop > 0 then b.pop_american_indian_rate + b.pop_hawaiian_islander_rate + b.pop_other_ethnicity_rate else 0 end),
emply2 = (case when b.pop > 0 then b.pop_age16_up_rate * b.pop_unemployed_rate else 0 end),
educa2 = (case when b.pop > 0 then b.pop_hs_diploma_rate else 0 end),
educa3 = (case when b.pop > 0 then b.pop_assoc_some_coll_rate else 0 end),
educa4 = (case when b.pop > 0 then b.pop_coll_degree_rate else 0 end),
educa5 = (case when b.pop > 0 then b.pop_grad_degree_rate else 0 end),
own2 = (case when b.hh > 0 then b.hh_rent_occ_rate else 0 end),
hhveh = (case when b.hh > 0 then b.hh_agg_veh_rate else 0 end),
hhsize = (case when b.hh > 0 then b.pop / b.hh else 0 end),
incom2 = (case when b.hh > 0 then b.hh_inc_10_20_rate + b.hh_inc_20_30_rate + b.hh_inc_30_40_rate / 2 else 0 end),
incom3 = (case when b.hh > 0 then hh_inc_30_40_rate / 2 + b.hh_inc_40_50_rate else 0 end),
incom4 = (case when b.hh > 0 then hh_inc_50_60_rate + b.hh_inc_60_75_rate else 0 end),
incom5 = (case when b.hh > 0 then hh_inc_75_100_rate else 0 end),
incom6 = (case when b.hh > 0 then hh_inc_100_125_rate + b.hh_inc_125_150_rate else 0 end),
incom7 = (case when b.hh > 0 then hh_inc_150_200_rate + b.hh_inc_200p_rate else 0 end),
child_any1 = (case when b.hh > 0 then b.hh_with_children_under_18yr_rate else 0 end),
disabled1_children = (case when b.pop > 0 then (b.pop_age5_17_disability_rate - b.pop_age5_17_ambulatory_disability_rate) / 2 else 0 end),
disabled2_children = (case when b.pop > 0 then b.pop_age5_17_ambulatory_disability_rate / 2 else 0 end),
disabled1_teens = (case when b.pop > 0 then (b.pop_age5_17_disability_rate - b.pop_age5_17_ambulatory_disability_rate) / 2 else 0 end),
disabled2_teens = (case when b.pop > 0 then b.pop_age5_17_ambulatory_disability_rate / 2 else 0 end),
disabled1_adult = (case when b.pop > 0 then b.pop_age18_64_disability_rate - b.pop_age18_64_ambulatory_disability_rate else 0 end),
disabled2_adult = (case when b.pop > 0 then b.pop_age18_64_ambulatory_disability_rate else 0 end),
disabled1_seniors = (case when b.pop > 0 then b.pop_age65up_disability_rate - b.pop_age65up_ambulatory_disability_rate else 0 end),
disabled2_seniors = (case when b.pop > 0 then b.pop_age65up_ambulatory_disability_rate else 0 end),
emply_hh = (case when b.hh > 0 then b.pop / b.hh * b.pop_employed_rate else 0 end),
educa_hh2 = (case when b.pop > 0 then b.pop_hs_diploma_rate else 0 end),
educa_hh3 = (case when b.pop > 0 then b.pop_assoc_some_coll_rate else 0 end),
educa_hh4 = (case when b.pop > 0 then b.pop_coll_degree_rate else 0 end),
educa_hh5 = (case when b.pop > 0 then b.pop_grad_degree_rate else 0 end),
pop_age_children = (case when b.pop > 0 then pop_age_5_11_rate else 0 end),
pop_age_teens = (case when b.pop > 0 then pop_age_12_17_rate else 0 end),
pop_age_adult = (case when b.pop > 0 then pop_age_18_64_rate else 0 end),
pop_age_seniors = (case when b.pop > 0 then pop_age65_up_rate else 0 end),
-- unlike the fields above, these describe population counts for the analysis groups
pop_children = (case when b.pop > 0 then b.pop * pop_age_5_11_rate else 0 end),
pop_teen = (case when b.pop > 0 then b.pop * pop_age_12_17_rate else 0 end),
pop_senior = (case when b.pop > 0 then b.pop * pop_age65_up_rate else 0 end),
pop_adult = (case when b.pop > 0 then b.pop * pop_age_18_64_rate else 0 end),
pop_adult_low = (case when b.pop > 0 then b.pop * pop_age_18_64_rate * (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) else 0 end),
pop_adult_med = (case when b.pop > 0 then b.pop * pop_age_18_64_rate * (hh_inc_50_60_rate + hh_inc_60_75_rate + hh_inc_75_100_rate) else 0 end),
pop_adult_high = (case when b.pop > 0 then b.pop * pop_age_18_64_rate * (hh_inc_100_125_rate + hh_inc_125_150_rate + hh_inc_150_200_rate + hh_inc_200p_rate) else 0 end)
from (
select a.grid_id, pop, hh, intersection_density_sqmi, c.*
from (select grid_id, census_id, sum(pop * primary_proportion) as pop, sum(hh * primary_proportion) as hh,
avg(intersection_density_sqmi) as intersection_density_sqmi
from {public_health_variables_schema}.{source_grid_table}_join a
left join {uf_canvas_schema}.{uf_canvas_table} b on a.primary_id = b.id group by grid_id, census_id) a
LEFT JOIN {census_rate_schema}.{census_rate_table} c on a.census_id = c.id
) b where b.grid_id = a.id;
'''.format(**sql_config_dict)
execute_sql(pSql)
def update(self, **kwargs):
logger.debug("Executing Environmental Constraints using {0}".format(self.config_entity))
config_entity = self.config_entity
end_state_feature_class = config_entity.db_entity_feature_class(DbEntityKey.END_STATE)
base_table = config_entity.db_entity_feature_class(DbEntityKey.BASE_CANVAS)
options = dict(
project_schema=parse_schema_and_table(base_table._meta.db_table)[0],
scenario_schema=parse_schema_and_table(end_state_feature_class._meta.db_table)[0],
end_state_table=end_state_feature_class.db_entity_key
)
current_db_entities, db_entities_to_add, db_entities_to_delete = \
self.update_or_create_environmental_constraint_percents(config_entity)
current_environmental_constraints = []
logger.debug("Current db_entities {0}".format(current_db_entities))
for db_entity in current_db_entities:
logger.debug("Active db_entity {0}".format(db_entity.key))
constraint_class = config_entity.db_entity_feature_class(db_entity.key)
environmental_constraint_percent = EnvironmentalConstraintPercent.objects.filter(
db_entity_id=db_entity.id,
analysis_tool_id=self.id)[0]
current_environmental_constraints.append(
dict(
key=constraint_class.db_entity_key,
priority=environmental_constraint_percent.priority,
percent=environmental_constraint_percent.percent
)
)
pSql = '''
DO $$
BEGIN
BEGIN
ALTER TABLE {project_schema}.environmental_constraint_geographies_table_unioned ADD COLUMN constraint_acres_{config_entity_id} float;
EXCEPTION
WHEN duplicate_column
THEN -- do nothing;
END;
END;
$$'''.format(
project_schema=options['project_schema'],
config_entity_id=self.config_entity.id
)
execute_sql(pSql)
logger.info('Calculate constraint acreage for the active scenario end state feature')
for db_entity in current_db_entities:
constraint_class = self.config_entity.db_entity_feature_class(db_entity.key)
environmental_constraint_percent = EnvironmentalConstraintPercent.objects.filter(
db_entity_id=db_entity.id,
analysis_tool_id=self.id)[0]
constraint_percent = environmental_constraint_percent.percent
active_constraint = filter(lambda dct: constraint_class.db_entity_key in dct['key'], current_environmental_constraints)[0]
priority_constraints = filter(lambda dct: dct['priority'] < active_constraint['priority'] or (dct['priority'] == active_constraint['priority'] and dct['percent'] > active_constraint['percent']), current_environmental_constraints)
priority_key_list = []
for constraint in priority_constraints:
priority_key_list.append(constraint['key'])
priority_query = create_sql_calculations(priority_key_list, ' and {0}_id is null', ' and a.primary_id is not null')
pSql = '''
update {project_schema}.environmental_constraint_geographies_table_unioned a set
constraint_acres_{config_entity_id} = acres * {percent} where {constraint}_id = {constraint_id} {priority_query};
'''.format(
project_schema=options['project_schema'],
constraint=constraint_class.db_entity_key,
constraint_id=db_entity.id,
percent=constraint_percent,
priority_query=priority_query,
config_entity_id=self.config_entity.id
)
execute_sql(pSql)
pSql = '''
update {scenario_schema}.{end_state_table} a set
acres_developable = a.acres_gross - b.constraint_acres
FROM
(select primary_id,
sum(constraint_acres_{config_entity_id}) as constraint_acres
from {project_schema}.environmental_constraint_geographies_table_unioned
where constraint_acres_{config_entity_id} is not null group by primary_id) b
where a.id= b.primary_id;
'''.format(
scenario_schema=options['scenario_schema'],
project_schema=options['project_schema'],
end_state_table=options['end_state_table'],
config_entity_id=self.config_entity.id
)
execute_sql(pSql)
pSql = '''
update {scenario_schema}.{end_state_table}
set developable_proportion = (
case when acres_gross > 0 then acres_developable / acres_gross else 0 end
)
'''.format(
scenario_schema=options['scenario_schema'],
end_state_table=options['end_state_table']
)
execute_sql(pSql)
def populate_transformations_and_indices(sql_config_dict):
##TRANSFORMING PRELIMINARY AND MODEL VARIABLES STEPS 1 AND 2
pSql = '''
update {public_health_variables_schema}.{public_health_variables_table} a set
resmix_dens = (case
when resmix_dens = 0
then 0
else ln((du_1km_tr / resmix_dens) + 1) end),
bldg_sqft_ret = bldg_sqft_ret ^ 0.25,
b1 = (case when b1 = 0 then 1E-24 else b1 end),
b2 = (case when b2 = 0 then 1E-24 else b2 end),
b3 = (case when b3 = 0 then 1E-24 else b3 end),
b4 = (case when b4 = 0 then 1E-24 else b4 end),
b5 = (case when b5 = 0 then 1E-24 else b5 end),
local_street = local_street / 5280,
major_street = (major_street / 5280) ^ 0.5,
far_nonres = (case when far_nonres = 0 then 0 else ((bldg_sqft_ret1 + bldg_sqft_off) / (far_nonres * 43560)) ^ 0.25 end),
rail_any = (case when rail_any > 0 then 1 else 0 end),
freeway_arterial_any = (case when freeway_arterial_any <500 then 1 else 0 end),
transit_distance = transit_distance ^ 0.25,
transit_count = transit_count ^ 0.5,
school_distance = school_distance ^ 0.25,
retail_distance = retail_distance ^ 0.5,
intersection_density_sqmi = intersection_density_sqmi ^ 0.5,
park_open_space_distance = park_open_space_distance ^ 0.5,
acres_parcel_park_open_space = acres_parcel_park_open_space ^ 0.25,
du_variable = du_variable ^ 0.25,
emp_variable = emp_variable ^ 0.25
where pop > 0;
'''.format(**sql_config_dict)
execute_sql(pSql)
pSql = '''
update {public_health_variables_schema}.{public_health_variables_table} a set
du_1km_tr = du_1km_tr ^ 0.25,
a = b1 + b2 + b3 + b4 + b5
where pop > 0;
'''.format(**sql_config_dict)
execute_sql(pSql)
pSql = '''
update {public_health_variables_schema}.{public_health_variables_table} a set
mix5 = (case
when a > 0 and ((b1/a)*LN(b1/a) + (b2/a)*LN(b2/a) + (b3/a)*LN(b3/a) + (b4/a)*LN(b4/a) + (b5/a)*LN(b5/a)) / (-1 * ln(5)) < 0.0001
then 0
when a > 0 and ((b1/a)*LN(b1/a) + (b2/a)*LN(b2/a) + (b3/a)*LN(b3/a) + (b4/a)*LN(b4/a) + (b5/a)*LN(b5/a)) / (-1 * ln(5)) > 0.999
then 0
when a > 0 and ((b1/a)*LN(b1/a) + (b2/a)*LN(b2/a) + (b3/a)*LN(b3/a) + (b4/a)*LN(b4/a) + (b5/a)*LN(b5/a)) / (-1 * ln(5)) > 0.0001 and
((b1/a)*LN(b1/a) + (b2/a)*LN(b2/a) + (b3/a)*LN(b3/a) + (b4/a)*LN(b4/a) + (b5/a)*LN(b5/a)) / (-1 * ln(5)) < 0.999
then ((((b1/a)*LN(b1/a) + (b2/a)*LN(b2/a) + (b3/a)*LN(b3/a) + (b4/a)*LN(b4/a) + (b5/a)*LN(b5/a))) / (-1 * ln(5))) ^ 0.5
else 0
end)
where pop > 0;
'''.format(**sql_config_dict)
execute_sql(pSql)
##CALCULATING PRELIMINARY INDEX VARIABLES
pSql = '''
update {public_health_variables_schema}.{public_health_variables_table} a set
res_index = (resmix_dens - 1.82) / 0.9478 + (du_1km_tr - 7.451) / 1.8664,
com_index = 2 * ((bldg_sqft_ret - 24.38) / 10.4543) + 2 * ((far_nonres - 0.5938) / 0.2064) - (retail_distance - 13.91) / 13.5014 - (restaurant_distance - 599.4) / 670.6584,
park_access = (acres_parcel_park_open_space - 1.704) / 1.0956 - (park_open_space_distance - 18.48) / 11.3701,
regional_access = (du_variable - 24.92) / 6.0667 + (emp_variable - 26.13) / 7.2701,
network_index = (intersection_density_sqmi - 9.186) / 3.5870 + (local_street - 29.73) / 10.5599,
transit_access = 2*((transit_count - 3.769) / 3.9104) - (transit_distance - 4.602) / 1.8653,
major_road_index = (major_street - 0.6099) / 0.8575 + (freeway_arterial_any - 0.2126) / 0.4092
where pop > 0;
'''.format(**sql_config_dict)
execute_sql(pSql)
##CALCULATING WALK INDEX VARIABLE
pSql = '''
update {public_health_variables_schema}.{public_health_variables_table} a set
walk_index = 2 * ((res_index - 0.000221) / 1.9586) + 1.5 * ((network_index - 1.65E-16) / 1.846573714) + (com_index - 0.0005076) / 5.3122 + 0.5*((mix5 - 0.4379) / 0.1840)
where pop > 0;
'''.format(**sql_config_dict)
execute_sql(pSql)
##CALCULATING WALK INDEX VARIATIONS
pSql = '''
update {public_health_variables_schema}.{public_health_variables_table} a set
walk_index_chts_senior_walk_any = (12.69/6.59) * ((walk_index - 0.0005914) / 4.4684) + (transit_access - 2.727E-16) / 2.8399,
walk_index_chts_senior_auto_min = (7.09/2.63) * ((walk_index - 0.0005914) / 4.4684) + (transit_access - 2.727E-16) / 2.8399,
walk_index_chts_teens_walk_any = (8.24/3.34) * ((walk_index - 0.0005914) / 4.4684) + (4.91/3.34) * ((transit_access - 2.727E-16) / 2.8399) + (rail_any - 0.09589) / 0.2944,
walk_index_chts_child_walk_any = (8.94/5.81) * ((walk_index - 0.0005914) / 4.4684) + (transit_access - 2.727E-16) / 2.8399,
walk_index_chts_adult_bike_any = (8.22/2.19) * ((walk_index - 0.0005914) / 4.4684) + (regional_access) / 1.9944,
walk_index_chts_adult_walk_min = (9.79/5.56) * ((walk_index - 0.0005914) / 4.4684) + (transit_access - 2.727E-16) / 2.8399,
walk_index_chts_senior_walk_min = (2.43/2.34) * ((walk_index - 0.0005914) / 4.4684) + (transit_access - 2.727E-16) / 2.8399,
walk_index_chts_teens_walk_min = (5.12/2.42) * ((walk_index - 0.0005914) / 4.4684) + (transit_access - 2.727E-16) / 2.8399,
walk_index_chts_adult_auto_min = (7.99/2.11) * ((walk_index - 0.0005914) / 4.4684) + (rail_any - 0.09589) / 0.2944,
walk_index_chis_adult_modPA_any = (8.57/2.94) * ((walk_index - 0.0005914) / 4.4684) + (4.36/2.94) * ((transit_access - 2.727E-16) / 2.8399) + ((rail_any - 0.09589) / 0.2944) + (3.22/2.94) * ((regional_access) / 1.9944),
walk_index_chis_adult_overweight = (walk_index - 0.0005914) / 4.4684 + (7.92/3.15) * ((regional_access) / 1.9944),
walk_index_chis_senior_overweight = (walk_index - 0.0005914) / 4.4684 + (3.15/2.01) * ((regional_access) / 1.9944),
walk_index_chis_adult_obese = (walk_index - 0.0005914) / 4.4684 + (6.07/2.58) * ((regional_access) / 1.9944),
walk_index_chis_senior_gh = (2.74/2.14) * ((walk_index - 0.0005914) / 4.4684) + (regional_access) / 1.9944,
walk_index_chis_senior_walk_le_min = (2.82/2.08) * ((walk_index - 0.0005914) / 4.4684) + (transit_access - 2.727E-16) / 2.8399,
walk_index_chis_adult_modPA_min = (7.96/1.98) * ((walk_index - 0.0005914) / 4.4684) + (4.32/1.98) * ((transit_access - 2.727E-16) / 2.8399) + (rail_any - 0.09589) / 0.2944,
walk_index_chis_adult_bmi = (walk_index - 0.0005914) / 4.4684 + (4.47/3.50) * ((transit_access - 2.727E-16) / 2.8399) + (8.94/3.50) * ((regional_access) / 1.9944),
walk_index_chis_child_PA60 =(4.25/2.12) * ((walk_index - 0.0005914) / 4.4684) + (regional_access) / 1.9944,
walk_index_chis_child_walk_school = (8.13/3.17)*((walk_index - 0.0005914) / 4.4684) + (regional_access) / 1.9944
where pop > 0;
'''.format(**sql_config_dict)
execute_sql(pSql)
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 populate_grid_census_rate_attributes(sql_config_dict):
pSql = '''
update {public_health_variables_schema}.{public_health_variables_table} a set
pop = b.pop,
hh = b.hh,
intersection_density_sqmi = b.intersection_density_sqmi,
gender2 = (case when b.pop > 0 then b.pop_female_rate else 0 end),
age_children = (case when b.pop > 0 then b.pop_avg_age5_11 else 0 end),
age_teens = (case when b.pop > 0 then b.pop_avg_age12_17 else 0 end),
age_adult = (case when b.pop > 0 then b.pop_avg_age18_64 else 0 end),
age_seniors = (case when b.pop > 0 then b.pop_avg_age65_up else 0 end),
racehisp1 = (case when b.pop > 0 then b.pop_white_rate else 0 end),
racehisp2 = (case when b.pop > 0 then b.pop_black_rate else 0 end),
racehisp4 = (case when b.pop > 0 then b.pop_asian_rate else 0 end),
racehisp97 = (case when b.pop > 0 then b.pop_american_indian_rate + b.pop_hawaiian_islander_rate + b.pop_other_ethnicity_rate else 0 end),
emply2 = (case when b.pop > 0 then b.pop_age16_up_rate * b.pop_unemployed_rate else 0 end),
educa2 = (case when b.pop > 0 then b.pop_hs_diploma_rate else 0 end),
educa3 = (case when b.pop > 0 then b.pop_assoc_some_coll_rate else 0 end),
educa4 = (case when b.pop > 0 then b.pop_coll_degree_rate else 0 end),
educa5 = (case when b.pop > 0 then b.pop_grad_degree_rate else 0 end),
own2 = (case when b.hh > 0 then b.hh_rent_occ_rate else 0 end),
hhveh = (case when b.hh > 0 then b.hh_agg_veh_rate else 0 end),
hhsize = (case when b.hh > 0 then b.pop / b.hh else 0 end),
incom2 = (case when b.hh > 0 then b.hh_inc_10_20_rate + b.hh_inc_20_30_rate + b.hh_inc_30_40_rate / 2 else 0 end),
incom3 = (case when b.hh > 0 then hh_inc_30_40_rate / 2 + b.hh_inc_40_50_rate else 0 end),
incom4 = (case when b.hh > 0 then hh_inc_50_60_rate + b.hh_inc_60_75_rate else 0 end),
incom5 = (case when b.hh > 0 then hh_inc_75_100_rate else 0 end),
incom6 = (case when b.hh > 0 then hh_inc_100_125_rate + b.hh_inc_125_150_rate else 0 end),
incom7 = (case when b.hh > 0 then hh_inc_150_200_rate + b.hh_inc_200p_rate else 0 end),
child_any1 = (case when b.hh > 0 then b.hh_with_children_under_18yr_rate else 0 end),
disabled1_children = (case when b.pop > 0 then (b.pop_age5_17_disability_rate - b.pop_age5_17_ambulatory_disability_rate) / 2 else 0 end),
disabled2_children = (case when b.pop > 0 then b.pop_age5_17_ambulatory_disability_rate / 2 else 0 end),
disabled1_teens = (case when b.pop > 0 then (b.pop_age5_17_disability_rate - b.pop_age5_17_ambulatory_disability_rate) / 2 else 0 end),
disabled2_teens = (case when b.pop > 0 then b.pop_age5_17_ambulatory_disability_rate / 2 else 0 end),
disabled1_adult = (case when b.pop > 0 then b.pop_age18_64_disability_rate - b.pop_age18_64_ambulatory_disability_rate else 0 end),
disabled2_adult = (case when b.pop > 0 then b.pop_age18_64_ambulatory_disability_rate else 0 end),
disabled1_seniors = (case when b.pop > 0 then b.pop_age65up_disability_rate - b.pop_age65up_ambulatory_disability_rate else 0 end),
disabled2_seniors = (case when b.pop > 0 then b.pop_age65up_ambulatory_disability_rate else 0 end),
emply_hh = (case when b.hh > 0 then b.pop / b.hh * b.pop_employed_rate else 0 end),
educa_hh2 = (case when b.pop > 0 then b.pop_hs_diploma_rate else 0 end),
educa_hh3 = (case when b.pop > 0 then b.pop_assoc_some_coll_rate else 0 end),
educa_hh4 = (case when b.pop > 0 then b.pop_coll_degree_rate else 0 end),
educa_hh5 = (case when b.pop > 0 then b.pop_grad_degree_rate else 0 end),
pop_age_children = (case when b.pop > 0 then pop_age_5_11_rate else 0 end),
pop_age_teens = (case when b.pop > 0 then pop_age_12_17_rate else 0 end),
pop_age_adult = (case when b.pop > 0 then pop_age_18_64_rate else 0 end),
pop_age_seniors = (case when b.pop > 0 then pop_age65_up_rate else 0 end),
-- unlike the fields above, these describe population counts for the analysis groups
pop_children = (case when b.pop > 0 then b.pop * pop_age_5_11_rate else 0 end),
pop_teen = (case when b.pop > 0 then b.pop * pop_age_12_17_rate else 0 end),
pop_senior = (case when b.pop > 0 then b.pop * pop_age65_up_rate else 0 end),
pop_adult = (case when b.pop > 0 then b.pop * pop_age_18_64_rate else 0 end),
pop_adult_low = (case when b.pop > 0 then b.pop * pop_age_18_64_rate * (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) else 0 end),
pop_adult_med = (case when b.pop > 0 then b.pop * pop_age_18_64_rate * (hh_inc_50_60_rate + hh_inc_60_75_rate + hh_inc_75_100_rate) else 0 end),
pop_adult_high = (case when b.pop > 0 then b.pop * pop_age_18_64_rate * (hh_inc_100_125_rate + hh_inc_125_150_rate + hh_inc_150_200_rate + hh_inc_200p_rate) else 0 end)
from (
select a.grid_id, pop, hh, intersection_density_sqmi, c.*
from (select grid_id, census_id, sum(pop * primary_proportion) as pop, sum(hh * primary_proportion) as hh,
avg(intersection_density_sqmi) as intersection_density_sqmi
from {public_health_variables_schema}.{source_grid_table}_join a
left join {uf_canvas_schema}.{uf_canvas_table} b on a.primary_id = b.id group by grid_id, census_id) a
LEFT JOIN {census_rate_schema}.{census_rate_table} c on a.census_id = c.id
) b where b.grid_id = a.id;
'''.format(**sql_config_dict)
execute_sql(pSql)
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 update(self, **kwargs):
"""
This function handles the update or creation on the environmental constraints geography producing the area
for each layer with the environmental constraint behavior. This function will both add and remove
constraints and produce the final constraints layer in the primary geography of the active scenario
"""
# TODO : remove hard-coded 3310 (only works in CA), need to set an "analysis projection" in the Region
start_time = time.time()
current_db_entities = \
set(self.config_entity.db_entities_having_behavior_key(BehaviorKey.Fab.ricate('environmental_constraint')))
base_feature_class = self.config_entity.db_entity_feature_class(
DbEntityKey.BASE_CANVAS)
options = dict(
project_schema=parse_schema_and_table(base_feature_class._meta.db_table)[0],
base_table=base_feature_class.db_entity_key
)
logger.info('Inserting raw geographies into the environmental constraint geographies table for DbEntities: %s' % \
', '.join(map(lambda db_entity: db_entity.name, current_db_entities)))
drop_table('{project_schema}.environmental_constraint_geographies_table'.format(
project_schema=options['project_schema'])
)
current_environmental_constraints = []
for db_entity in current_db_entities:
constraint_class = self.config_entity.db_entity_feature_class(db_entity.key)
current_environmental_constraints.append(constraint_class.db_entity_key)
create_id_field_format = create_sql_calculations(current_environmental_constraints, '{0}_id int')
insert_id_field_format = create_sql_calculations(current_environmental_constraints, '{0}_id')
pSql = '''
create table {project_schema}.environmental_constraint_geographies_table
(primary_id integer, wkb_geometry geometry, {create_id_field_format});
SELECT UpdateGeometrySRID('{project_schema}', 'environmental_constraint_geographies_table', 'wkb_geometry', 3310)
'''.format(project_schema=options['project_schema'], create_id_field_format=create_id_field_format)
execute_sql(pSql)
for db_entity in current_db_entities:
logger.info('Inserting into environmental constraint geographies table for DbEntity: %s' % db_entity.full_name)
constraint_class = self.config_entity.db_entity_feature_class(db_entity.key)
pSql = '''
insert into {project_schema}.environmental_constraint_geographies_table (primary_id, wkb_geometry, {constraint_db_entity_key}_id) select
cast(primary_id as int), wkb_geometry, {constraint_db_entity_key}_id from (
select
id as primary_id,
{constraint_db_entity_id} as {constraint_db_entity_key}_id,
st_setSRID(st_transform(st_buffer((st_dump(wkb_geometry)).geom, 0), 3310), 3310) as wkb_geometry
from (
select b.id, st_intersection(a.wkb_geometry, b.wkb_geometry) as wkb_geometry
from {constraint_schema}.{constraint_db_entity_key} a,
{project_schema}.{base_table} b
where st_intersects(a.wkb_geometry, b.wkb_geometry)) as intersection
) as polygons;
'''.format(
project_schema=options['project_schema'],
base_table=options['base_table'],
constraint_schema=parse_schema_and_table(constraint_class._meta.db_table)[0],
constraint_db_entity_key=constraint_class.db_entity_key,
constraint_db_entity_id=db_entity.id
)
execute_sql(pSql)
logger.info('finished inserting db_entity: {db_entity} {time} elapsed'.format(
time=time.time() - start_time,
db_entity=constraint_class.db_entity_key))
#only regenerate the merged environmental constraint whenever an envrionmental constraint is added or removed
# from the layer
add_geom_idx(options['project_schema'], 'environmental_constraint_geographies_table')
logger.info('Unioning all environmental constraint geographies')
drop_table('{project_schema}.environmental_constraint_geographies_table_unioned'.format(
project_schema=options['project_schema'])
)
pSql = '''
CREATE TABLE {project_schema}.environmental_constraint_geographies_table_unioned
(id serial, wkb_geometry geometry, acres float, primary_id int, {create_id_field_format});
SELECT UpdateGeometrySRID('{project_schema}', 'environmental_constraint_geographies_table_unioned', 'wkb_geometry', 3310);
'''.format(project_schema=options['project_schema'], create_id_field_format=create_id_field_format)
execute_sql(pSql)
pSql = '''
insert into {project_schema}.environmental_constraint_geographies_table_unioned (wkb_geometry, acres, primary_id, {insert_id_field_format})
SELECT
st_buffer(wkb_geometry, 0) as wkb_geometry,
st_area(st_buffer(wkb_geometry, 0)) * 0.000247105 as acres,
primary_id, {insert_id_field_format}
FROM (
SELECT
(ST_Dump(wkb_geometry)).geom as wkb_geometry,
primary_id, {insert_id_field_format}
FROM (
SELECT ST_Polygonize(wkb_geometry) AS wkb_geometry, primary_id, {insert_id_field_format} FROM (
SELECT ST_Collect(wkb_geometry) AS wkb_geometry, primary_id, {insert_id_field_format} FROM (
SELECT ST_ExteriorRing(wkb_geometry) AS wkb_geometry, primary_id, {insert_id_field_format}
FROM {project_schema}.environmental_constraint_geographies_table) AS lines
group by primary_id, {insert_id_field_format}) AS noded_lines
group by primary_id, {insert_id_field_format}) as polygons
) as final
WHERE st_area(st_buffer(wkb_geometry, 0)) > 5;'''.format(
project_schema=options['project_schema'],
insert_id_field_format=insert_id_field_format
)
execute_sql(pSql)
logger.info('finished unioning env constraints: {time} elapsed'.format(
time=time.time() - start_time))
#reproject table back to 4326 for integration with web viewing
pSql = '''
SELECT UpdateGeometrySRID('{project_schema}', 'environmental_constraint_geographies_table_unioned', 'wkb_geometry', 4326);
update {project_schema}.environmental_constraint_geographies_table_unioned a set wkb_geometry = st_transform(st_buffer(wkb_geometry, 0), 4326);
'''.format(
project_schema=options['project_schema']
)
execute_sql(pSql)
add_geom_idx(options['project_schema'], 'environmental_constraint_geographies_table_unioned')
logger.info('Env Union Finished: %s' % str(time.time() - start_time))
def calculate_distance(distance_options):
print 'Calculating distance from target features areas'
##ST_DISTANCE returns distances in meters from geometries in WGS84 projection if set to false
thread_count = count_cores()
queue = queue_process()
#if the source table query has not results set all values to the max and break
zero_values_check = report_sql_values(
'''select
sum(*)
from {source_table} where {source_table_query};'''.format(**distance_options), 'fetchone')
if len(zero_values_check) == 0:
pSql = '''
update {target_table_schema}.{target_table} a set
{column} = {maximum_distance}
where {target_table_query} and {column} = 0
'''.format(**distance_options)
execute_sql(pSql)
return
pSql = '''drop function if exists distance_tool(
in_id int,
in_wkb_geometry geometry,
out id int,
out {column} float) cascade;'''.format(**distance_options)
execute_sql(pSql)
pSql = '''
CREATE OR REPLACE FUNCTION distance_tool(
in_id int,
in_wkb_geometry geometry,
out id int,
out {column} float)
AS
$$
select
$1 as id,
cast(st_distance(st_centroid($2), st_centroid(ref.geometry)) as float) as {column}
from
(select *, {source_geometry_column} as geometry from {source_table}) ref
where ST_DWITHIN($2, ref.geometry, {maximum_distance}) and ({source_table_query})
order by {column};
$$
COST 10000
language SQL STABLE strict;
'''.format(**distance_options)
execute_sql(pSql)
drop_table('{target_table_schema}.{target_table}_{column}'.format(**distance_options))
pSql = '''
create table {target_table_schema}.{target_table}_{column} (id int, {column} float);
'''.format(**distance_options)
execute_sql(pSql)
id_list = flatten(report_sql_values(
'''select cast({target_table_pk} as int) from {target_table_schema}.{target_table}
where {target_table_query} order by {target_table_pk}'''.format(
**distance_options), 'fetchall'))
insert_sql = '''
insert into {target_table_schema}.{target_table}_{column}
select (f).* from (
select distance_tool(a.id, a.wkb_geometry) as f
from (select {target_table_pk} as id, {target_geometry_column} as wkb_geometry
from {target_table_schema}.{target_table}
where
{target_table_pk} >= {bottom_range_id} and
{target_table_pk} <= {top_range_id} and
({target_table_query})
) a
) s;
'''.format(bottom_range_id="{start_id}", top_range_id="{end_id}", **distance_options)
for i in range(thread_count):
t = MultithreadProcess(queue, insert_sql)
t.setDaemon(True)
t.start()
#populate queue with data
rows_per_thread = len(id_list) / thread_count
offset = 0
for i in range(thread_count):
if i == thread_count - 1:
## last bucket gets any remainder, too
last_thread = len(id_list) - 1
else:
last_thread = offset + rows_per_thread - 1
rows_to_process = {
'start_id': id_list[offset],
'end_id': id_list[last_thread]
}
offset += rows_per_thread
queue.put(rows_to_process)
#wait on the queue until everything has been processed
queue.join()
add_attribute_idx(distance_options['target_table_schema'],
'{target_table}_{column}'.format(**distance_options), 'id')
pSql = '''
update {target_table_schema}.{target_table} a set
{column} = source_column
from (select id as source_id, {column} as source_column from {target_table_schema}.{target_table}_{column}) b
where cast(a.{target_table_pk} as int) = b.source_id and ({target_table_query})
'''.format(**distance_options)
execute_sql(pSql)
pSql = '''
update {target_table_schema}.{target_table} a set
{column} = {maximum_distance}
where {target_table_query} and {column} = 0
'''.format(**distance_options)
execute_sql(pSql)
drop_table('{target_table_schema}.{target_table}_{column}'.format(**distance_options))
def aggregate_within_variable_distance(distance_options):
thread_count = count_cores()
queue = queue_process()
sql_format = 'out {formatter} float'.format(formatter="{0}")
output_field_format = create_sql_calculations(distance_options['variable_field_list'], sql_format, ', ')
sql_format = 'cast({aggregation_type}({formatter}) as float) as {formatter}'.format(formatter="{0}", aggregation_type=distance_options['aggregation_type'])
sql_calculations_format = create_sql_calculations(distance_options['variable_field_list'], sql_format, ', ')
pSql = '''
drop function if exists aggregate_within_variable_distance_tool(
in_id int,
in_distance float,
in_geometry geometry,
out id int,
out wkb_geometry geometry,
{output_field_format}) cascade;'''.format(
output_field_format=output_field_format)
execute_sql(pSql)
pSql = '''
CREATE OR REPLACE FUNCTION aggregate_within_variable_distance_tool(
in_id int,
id_distance float,
in_geometry geometry,
out id int,
out wkb_geometry geometry,
{output_field_format})
AS
$$
select
$1 as id,
$3 as wkb_geometry,
{sql_calculations_format}
from {source_table} ref
WHERE st_dwithin($3, ref.wkb_geometry, $2) and (ref.{source_table_query});
$$
COST 10000
language SQL STABLE strict;
'''.format(source_table=distance_options['source_table'],
source_table_query=distance_options['source_table_query'],
output_field_format=output_field_format,
sql_calculations_format=sql_calculations_format)
execute_sql(pSql)
drop_table('{target_table_schema}.{target_table}_{suffix}'.format(
target_table_schema=distance_options['target_table_schema'],
target_table=distance_options['target_table'], suffix=distance_options['suffix']))
sql_format = '{formatter} float'.format(formatter="{0}", suffix=distance_options['suffix'])
output_table_field_format = create_sql_calculations(distance_options['variable_field_list'], sql_format, ', ')
pSql = '''create table {target_table_schema}.{target_table}_{suffix} (id int, wkb_geometry geometry, {output_table_field_format});'''.format(
target_table_schema=distance_options['target_table_schema'], target_table=distance_options['target_table'],
suffix=distance_options['suffix'], output_table_field_format=output_table_field_format)
execute_sql(pSql)
pSql = 'select cast(id as int) from {source_table} where id is not null order by id'.format(
source_table=distance_options['source_table'])
id_list = flatten(report_sql_values(pSql, 'fetchall'))
insert_sql = '''
insert into {target_table_schema}.{target_table}_{suffix}
select (f).* from (
select aggregate_within_variable_distance_tool(id, distance, wkb_geometry) as f
from {source_table}
where id >= {bottom_range_id} and id <= {top_range_id} and {source_table_query}
) s
where (f).id is not null;
'''.format(
target_table_schema=distance_options['target_table_schema'],
source_table_query=distance_options['source_table_query'],
target_table=distance_options['target_table'],
source_table=distance_options['source_table'],
suffix=distance_options['suffix'],
bottom_range_id="{start_id}",
top_range_id="{end_id}")
for i in range(thread_count):
t = MultithreadProcess(queue, insert_sql)
t.setDaemon(True)
t.start()
#populate queue with data
rows_per_thread = len(id_list) / thread_count
offset = 0
for i in range(thread_count):
if i == thread_count - 1:
## last bucket gets any remainder, too
last_thread = len(id_list) - 1
else:
last_thread = offset + rows_per_thread - 1
rows_to_process = {
'start_id': id_list[offset],
'end_id': id_list[last_thread]
}
offset += rows_per_thread
queue.put(rows_to_process)
#wait on the queue until everything has been processed
queue.join()
add_attribute_idx(distance_options['target_table_schema'],
'{target_table}_{suffix}'.format(target_table=distance_options['target_table'],
suffix=distance_options['suffix']), 'id')
update_table_field_format = create_sql_calculations(distance_options['variable_field_list'], '{0} = (case when b.{0}_var is null then 0 else b.{0}_var end)', ', ')
select_format = create_sql_calculations(distance_options['variable_field_list'], '{0} as {0}_var', ', ')
pSql = '''
update {target_table_schema}.{target_table} a set {update_table_field_format}
from (select id as {suffix}_id, wkb_geometry, {select_format} from {target_table_schema}.{target_table}_{suffix}) b
where st_intersects(st_centroid(a.analysis_geom), b.wkb_geometry) and {target_table_query};
'''.format(
target_table_schema=distance_options['target_table_schema'],
target_table=distance_options['target_table'],
target_table_query=distance_options['target_table_query'],
target_table_pk=distance_options['target_table_pk'],
update_table_field_format=update_table_field_format,
select_format=select_format,
suffix=distance_options['suffix']
)
execute_sql(pSql)
drop_table('{target_table_schema}.{target_table}_{suffix}'.format(
target_table_schema=distance_options['target_table_schema'],
target_table=distance_options['target_table'],
suffix=distance_options['suffix']))
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 update(self, **kwargs):
# Make sure all related models have been created before querying
logger.info("Executing Vmt using {0}".format(self.config_entity))
self.vmt_progress(0.1, **kwargs)
vmt_result_class = self.config_entity.db_entity_feature_class(
DbEntityKey.VMT)
vmt_variables_feature_class = self.config_entity.db_entity_feature_class(
DbEntityKey.VMT_VARIABLES)
census_rates_feature_class = self.config_entity.db_entity_feature_class(
DbEntityKey.CENSUS_RATES)
if isinstance(self.config_entity.subclassed, FutureScenario):
scenario_class = self.config_entity.db_entity_feature_class(
DbEntityKey.END_STATE)
trip_lengths_class = self.config_entity.db_entity_feature_class(
DbEntityKey.VMT_FUTURE_TRIP_LENGTHS)
transit_stop_class = self.config_entity.db_entity_feature_class(
DbEntityKey.FUTURE_TRANSIT_STOPS)
is_future = True
else:
scenario_class = self.config_entity.db_entity_feature_class(
DbEntityKey.BASE_CANVAS)
trip_lengths_class = self.config_entity.db_entity_feature_class(
DbEntityKey.VMT_BASE_TRIP_LENGTHS)
transit_stop_class = self.config_entity.db_entity_feature_class(
DbEntityKey.BASE_TRANSIT_STOPS)
is_future = False
sql_config_dict = dict(
vmt_result_table=vmt_result_class.db_entity_key,
vmt_schema=parse_schema_and_table(
vmt_result_class._meta.db_table)[0],
uf_canvas_table=scenario_class.db_entity_key,
uf_canvas_schema=parse_schema_and_table(
scenario_class._meta.db_table)[0],
census_rates_table=census_rates_feature_class.db_entity_key,
census_rates_schema=parse_schema_and_table(
census_rates_feature_class._meta.db_table)[0],
trip_lengths_table=trip_lengths_class.db_entity_key,
trip_lengths_schema=parse_schema_and_table(
trip_lengths_class._meta.db_table)[0],
vmt_variables_table=vmt_variables_feature_class.db_entity_key,
vmt_variables_schema=parse_schema_and_table(
vmt_variables_feature_class._meta.db_table)[0],
vmt_rel_table=parse_schema_and_table(
vmt_result_class._meta.db_table)[1],
vmt_rel_column=vmt_result_class._meta.parents.values()[0].column,
transit_stop_schema=parse_schema_and_table(
transit_stop_class._meta.db_table)[0],
transit_stop_table=transit_stop_class.db_entity_key,
config_entity=self.config_entity)
#
if not kwargs.get('postprocess_only'):
self.run_vmt_preprocesses(sql_config_dict, **kwargs)
drop_table('{vmt_schema}.{vmt_result_table}'.format(**sql_config_dict))
truncate_table(
'{vmt_schema}.{vmt_rel_table}'.format(**sql_config_dict))
attribute_list = filter(lambda x: x != 'id', vmt_output_field_list)
output_field_syntax = 'id int, ' + create_sql_calculations(
attribute_list, '{0} numeric(14, 4)')
pSql = '''
create table {vmt_schema}.{vmt_result_table} ({output_field_syntax});'''.format(
output_field_syntax=output_field_syntax, **sql_config_dict)
execute_sql(pSql)
trip_lengths = DbEntityKey.VMT_FUTURE_TRIP_LENGTHS if is_future else DbEntityKey.VMT_BASE_TRIP_LENGTHS
total_employment = scenario_class.objects.aggregate(Sum('emp'))
all_features = scenario_class.objects.filter(
Q(du__gt=0) | Q(emp__gt=0))
all_features_length = len(all_features)
max_id = scenario_class.objects.all().order_by("-id")[0].id
min_id = scenario_class.objects.all().order_by("id")[0].id
# This section of the model passes data from POSTGRES into Python and is saved in memory before being committed
# back to the database. In order to not use all memory with large datasets, jobs are broken up with a maximum
# job size of JOB_SIZE rows before being committed to the database. It will iterate through until all rows are
# calculated and committed.
if all_features_length > self.JOB_SIZE:
job_count = all_features_length / self.JOB_SIZE
rows_per_range = (max_id - min_id) / job_count
else:
rows_per_range = max_id - min_id
job_count = 1
print 'Job Count: {0}'.format(job_count)
start_id = min_id
for i in range(job_count):
if i == job_count - 1:
end_id = max_id
else:
end_id = start_id + rows_per_range - 1
logger.info('Job: {0}'.format(i))
logger.info('Start Id: {0}'.format(start_id))
logger.info('End Id: {0}'.format(end_id))
vmt_output_list = []
features = all_features.filter(id__range=(start_id, end_id))
annotated_features = annotated_related_feature_class_pk_via_geographies(
features, self.config_entity, [
DbEntityKey.VMT_VARIABLES, DbEntityKey.CENSUS_RATES,
DbEntityKey.VMT_FUTURE_TRIP_LENGTHS,
DbEntityKey.VMT_BASE_TRIP_LENGTHS, trip_lengths
])
assert annotated_features.exists(
), "VMT is about to process 0 results"
failed_features = []
for feature in annotated_features:
trip_length_id = feature.vmt_future_trip_lengths if is_future else feature.vmt_base_trip_lengths
try:
trip_lengths_feature = trip_lengths_class.objects.get(
id=trip_length_id)
except trip_lengths_class.DoesNotExist, e:
failed_features.append(feature)
logger.error(
'Cannot find trip lengths for geography with id = {0}'.
format(feature.id))
continue
vmt_variables_feature = vmt_variables_feature_class.objects.get(
id=feature.vmt_variables)
try:
census_rates_feature = census_rates_feature_class.objects.get(
id=feature.census_rates)
except census_rates_feature_class.DoesNotExist, e:
logger.error(
'Cannot find census rate with id = {0}'.format(
feature.census_rates))
continue
vmt_feature = dict(
id=int(feature.id),
acres_gross=float(feature.acres_gross) or 0,
acres_parcel=float(feature.acres_parcel) or 0,
acres_parcel_res=float(feature.acres_parcel_res) or 0,
acres_parcel_emp=float(feature.acres_parcel_emp) or 0,
acres_parcel_mixed=float(feature.acres_parcel_mixed_use)
or 0,
intersections_qtrmi=float(
feature.intersection_density_sqmi) or 0,
du=float(feature.du) or 0,
du_occupancy_rate=float(feature.hh /
feature.du if feature.du else 0),
du_detsf=float(feature.du_detsf) or 0,
du_attsf=float(feature.du_attsf) or 0,
du_mf=float(feature.du_mf) or 0,
du_mf2to4=float(feature.du_mf2to4) or 0,
du_mf5p=float(feature.du_mf5p) or 0,
hh=float(feature.hh) or 0,
hh_avg_size=float(feature.pop /
feature.hh if feature.hh > 0 else 0),
hh_avg_inc=float(census_rates_feature.hh_agg_inc_rate)
or 0,
hh_inc_00_10=float(
feature.hh * census_rates_feature.hh_inc_00_10_rate)
or 0,
hh_inc_10_20=float(
feature.hh * census_rates_feature.hh_inc_10_20_rate)
or 0,
hh_inc_20_30=float(
feature.hh * census_rates_feature.hh_inc_20_30_rate)
or 0,
hh_inc_30_40=float(
feature.hh * census_rates_feature.hh_inc_30_40_rate)
or 0,
hh_inc_40_50=float(
feature.hh * census_rates_feature.hh_inc_40_50_rate)
or 0,
hh_inc_50_60=float(
feature.hh * census_rates_feature.hh_inc_50_60_rate)
or 0,
hh_inc_60_75=float(
feature.hh * census_rates_feature.hh_inc_60_75_rate)
or 0,
hh_inc_75_100=float(
feature.hh * census_rates_feature.hh_inc_75_100_rate)
or 0,
hh_inc_100p=float(
feature.hh *
(census_rates_feature.hh_inc_100_125_rate +
census_rates_feature.hh_inc_125_150_rate +
census_rates_feature.hh_inc_150_200_rate +
census_rates_feature.hh_inc_200p_rate)) or 0,
pop=float(feature.pop) or 0,
pop_employed=float(
feature.pop * census_rates_feature.pop_age16_up_rate *
census_rates_feature.pop_employed_rate) or 0,
pop_age16_up=float(
feature.pop * census_rates_feature.pop_age16_up_rate)
or 0,
pop_age65_up=float(
feature.pop * census_rates_feature.pop_age65_up_rate)
or 0,
emp=float(feature.emp) or 0,
emp_retail=float(feature.emp_retail_services +
feature.emp_other_services) or 0,
emp_restaccom=float(feature.emp_accommodation +
feature.emp_restaurant) or 0,
emp_arts_entertainment=float(
feature.emp_arts_entertainment) or 0,
emp_office=float(feature.emp_off) or 0,
emp_public=float(feature.emp_public_admin +
feature.emp_education) or 0,
emp_industry=float(feature.emp_ind + feature.emp_ag) or 0,
emp_within_1mile=float(vmt_variables_feature.emp_1mile)
or 0,
hh_within_quarter_mile_trans=1
if vmt_variables_feature.transit_1km > 0 else 0,
vb_acres_parcel_res_total=float(
vmt_variables_feature.acres_parcel_res_vb) or 0,
vb_acres_parcel_emp_total=float(
vmt_variables_feature.acres_parcel_emp_vb) or 0,
vb_acres_parcel_mixed_total=float(
vmt_variables_feature.acres_parcel_mixed_use_vb) or 0,
vb_du_total=float(vmt_variables_feature.du_vb) or 0,
vb_pop_total=float(vmt_variables_feature.pop_vb) or 0,
vb_emp_total=float(vmt_variables_feature.emp_vb) or 0,
vb_emp_retail_total=float(vmt_variables_feature.emp_ret_vb)
or 0,
vb_hh_total=float(vmt_variables_feature.hh_vb) or 0,
vb_du_mf_total=float(vmt_variables_feature.du_mf_vb) or 0,
vb_hh_inc_00_10_total=float(
vmt_variables_feature.hh_inc_00_10_vb) or 0,
vb_hh_inc_10_20_total=float(
vmt_variables_feature.hh_inc_10_20_vb) or 0,
vb_hh_inc_20_30_total=float(
vmt_variables_feature.hh_inc_20_30_vb) or 0,
vb_hh_inc_30_40_total=float(
vmt_variables_feature.hh_inc_30_40_vb) or 0,
vb_hh_inc_40_50_total=float(
vmt_variables_feature.hh_inc_40_50_vb) or 0,
vb_hh_inc_50_60_total=float(
vmt_variables_feature.hh_inc_50_60_vb) or 0,
vb_hh_inc_60_75_total=float(
vmt_variables_feature.hh_inc_60_75_vb) or 0,
vb_hh_inc_75_100_total=float(
vmt_variables_feature.hh_inc_75_100_vb) or 0,
vb_hh_inc_100p_total=float(
vmt_variables_feature.hh_inc_100p_vb) or 0,
vb_pop_employed_total=float(
vmt_variables_feature.pop_employed_vb) or 0,
vb_pop_age16_up_total=float(
vmt_variables_feature.pop_age16_up_vb) or 0,
vb_pop_age65_up_total=float(
vmt_variables_feature.pop_age65_up_vb) or 0,
emp30m_transit=float(
trip_lengths_feature.emp_30min_transit) or 0,
emp45m_transit=float(
trip_lengths_feature.emp_45min_transit) or 0,
prod_hbw=float(trip_lengths_feature.productions_hbw) or 0,
prod_hbo=float(trip_lengths_feature.productions_hbo) or 0,
prod_nhb=float(trip_lengths_feature.productions_nhb) or 0,
attr_hbw=float(trip_lengths_feature.attractions_hbw) or 0,
attr_hbo=float(trip_lengths_feature.attractions_hbo) or 0,
attr_nhb=float(trip_lengths_feature.attractions_nhb) or 0,
qmb_acres_parcel_res_total=float(
vmt_variables_feature.acres_parcel_res_qtrmi) or 0,
qmb_acres_parcel_emp_total=float(
vmt_variables_feature.acres_parcel_emp_qtrmi) or 0,
qmb_acres_parcel_mixed_total=float(
vmt_variables_feature.acres_parcel_mixed_use_qtrmi)
or 0,
qmb_du_total=float(vmt_variables_feature.du_qtrmi) or 0,
qmb_pop_total=float(vmt_variables_feature.pop_qtrmi) or 0,
qmb_emp_total=float(vmt_variables_feature.emp_qtrmi) or 0,
qmb_emp_retail=float(vmt_variables_feature.emp_ret_qtrmi)
or 0,
hh_avg_veh=float(census_rates_feature.hh_agg_veh_rate)
or 0,
truck_adjustment_factor=0.031,
total_employment=float(total_employment['emp__sum']) or 0)
# run raw trip generation
vmt_feature_trips = generate_raw_trips(vmt_feature)
# run trip purpose splits
vmt_feature_trip_purposes = calculate_trip_purpose_splits(
vmt_feature_trips)
# run log odds
vmt_feature_log_odds = calculate_log_odds(
vmt_feature_trip_purposes)
# run vmt equations
vmt_output = calculate_final_vmt_results(vmt_feature_log_odds)
# filters the vmt feature dictionary for specific output fields for writing to the database
output_list = map(lambda key: vmt_output[key],
vmt_output_field_list)
vmt_output_list.append(output_list)
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 update(self, **kwargs):
# Make sure all related models have been created before querying
logger.info("Executing Vmt using {0}".format(self.config_entity))
self.vmt_progress(0.1, **kwargs)
vmt_result_class = self.config_entity.db_entity_feature_class(DbEntityKey.VMT)
vmt_variables_feature_class = self.config_entity.db_entity_feature_class(DbEntityKey.VMT_VARIABLES)
census_rates_feature_class = self.config_entity.db_entity_feature_class(DbEntityKey.CENSUS_RATES)
if isinstance(self.config_entity.subclassed, FutureScenario):
scenario_class = self.config_entity.db_entity_feature_class(DbEntityKey.END_STATE)
trip_lengths_class = self.config_entity.db_entity_feature_class(DbEntityKey.VMT_FUTURE_TRIP_LENGTHS)
transit_stop_class = self.config_entity.db_entity_feature_class(DbEntityKey.FUTURE_TRANSIT_STOPS)
is_future = True
else:
scenario_class = self.config_entity.db_entity_feature_class(DbEntityKey.BASE_CANVAS)
trip_lengths_class = self.config_entity.db_entity_feature_class(DbEntityKey.VMT_BASE_TRIP_LENGTHS)
transit_stop_class = self.config_entity.db_entity_feature_class(DbEntityKey.BASE_TRANSIT_STOPS)
is_future = False
sql_config_dict = dict(
vmt_result_table=vmt_result_class.db_entity_key,
vmt_schema=parse_schema_and_table(vmt_result_class._meta.db_table)[0],
uf_canvas_table=scenario_class.db_entity_key,
uf_canvas_schema=parse_schema_and_table(scenario_class._meta.db_table)[0],
census_rates_table=census_rates_feature_class.db_entity_key,
census_rates_schema=parse_schema_and_table(census_rates_feature_class._meta.db_table)[0],
trip_lengths_table=trip_lengths_class.db_entity_key,
trip_lengths_schema=parse_schema_and_table(trip_lengths_class._meta.db_table)[0],
vmt_variables_table=vmt_variables_feature_class.db_entity_key,
vmt_variables_schema=parse_schema_and_table(vmt_variables_feature_class._meta.db_table)[0],
vmt_rel_table=parse_schema_and_table(vmt_result_class._meta.db_table)[1],
vmt_rel_column=vmt_result_class._meta.parents.values()[0].column,
transit_stop_schema=parse_schema_and_table(transit_stop_class._meta.db_table)[0],
transit_stop_table=transit_stop_class.db_entity_key,
config_entity=self.config_entity
)
#
if not kwargs.get('postprocess_only'):
self.run_vmt_preprocesses(sql_config_dict, **kwargs)
drop_table('{vmt_schema}.{vmt_result_table}'.format(**sql_config_dict))
truncate_table('{vmt_schema}.{vmt_rel_table}'.format(**sql_config_dict))
attribute_list = filter(lambda x: x != 'id', vmt_output_field_list)
output_field_syntax = 'id int, ' + create_sql_calculations(attribute_list, '{0} numeric(14, 4)')
pSql = '''
create table {vmt_schema}.{vmt_result_table} ({output_field_syntax});'''.format(
output_field_syntax=output_field_syntax, **sql_config_dict)
execute_sql(pSql)
trip_lengths = DbEntityKey.VMT_FUTURE_TRIP_LENGTHS if is_future else DbEntityKey.VMT_BASE_TRIP_LENGTHS
total_employment = scenario_class.objects.aggregate(Sum('emp'))
all_features = scenario_class.objects.filter(Q(du__gt=0) | Q(emp__gt=0))
all_features_length = len(all_features)
max_id = scenario_class.objects.all().order_by("-id")[0].id
min_id = scenario_class.objects.all().order_by("id")[0].id
# This section of the model passes data from POSTGRES into Python and is saved in memory before being committed
# back to the database. In order to not use all memory with large datasets, jobs are broken up with a maximum
# job size of JOB_SIZE rows before being committed to the database. It will iterate through until all rows are
# calculated and committed.
if all_features_length > self.JOB_SIZE:
job_count = all_features_length / self.JOB_SIZE
rows_per_range = (max_id - min_id) / job_count
else:
rows_per_range = max_id - min_id
job_count = 1
print 'Job Count: {0}'.format(job_count)
start_id = min_id
for i in range(job_count):
if i == job_count - 1:
end_id = max_id
else:
end_id = start_id + rows_per_range - 1
logger.info('Job: {0}'.format(i))
logger.info('Start Id: {0}'.format(start_id))
logger.info('End Id: {0}'.format(end_id))
vmt_output_list = []
features = all_features.filter(id__range=(start_id, end_id))
annotated_features = annotated_related_feature_class_pk_via_geographies(features, self.config_entity, [
DbEntityKey.VMT_VARIABLES, DbEntityKey.CENSUS_RATES, DbEntityKey.VMT_FUTURE_TRIP_LENGTHS, DbEntityKey.VMT_BASE_TRIP_LENGTHS, trip_lengths])
assert annotated_features.exists(), "VMT is about to process 0 results"
failed_features = []
for feature in annotated_features:
trip_length_id = feature.vmt_future_trip_lengths if is_future else feature.vmt_base_trip_lengths
try:
trip_lengths_feature = trip_lengths_class.objects.get(id=trip_length_id)
except trip_lengths_class.DoesNotExist, e:
failed_features.append(feature)
logger.error('Cannot find trip lengths for geography with id = {0}'.format(feature.id))
continue
vmt_variables_feature = vmt_variables_feature_class.objects.get(id=feature.vmt_variables)
try:
census_rates_feature = census_rates_feature_class.objects.get(id=feature.census_rates)
except census_rates_feature_class.DoesNotExist, e:
logger.error('Cannot find census rate with id = {0}'.format(feature.census_rates))
continue
vmt_feature = dict(
id=int(feature.id),
acres_gross=float(feature.acres_gross) or 0,
acres_parcel=float(feature.acres_parcel) or 0,
acres_parcel_res=float(feature.acres_parcel_res) or 0,
acres_parcel_emp=float(feature.acres_parcel_emp) or 0,
acres_parcel_mixed=float(feature.acres_parcel_mixed_use) or 0,
intersections_qtrmi=float(feature.intersection_density_sqmi) or 0,
du=float(feature.du) or 0,
du_occupancy_rate=float(feature.hh / feature.du if feature.du else 0),
du_detsf=float(feature.du_detsf) or 0,
du_attsf=float(feature.du_attsf) or 0,
du_mf=float(feature.du_mf) or 0,
du_mf2to4=float(feature.du_mf2to4) or 0,
du_mf5p=float(feature.du_mf5p) or 0,
hh=float(feature.hh) or 0,
hh_avg_size=float(feature.pop / feature.hh if feature.hh > 0 else 0),
hh_avg_inc=float(census_rates_feature.hh_agg_inc_rate) or 0,
hh_inc_00_10=float(feature.hh * census_rates_feature.hh_inc_00_10_rate) or 0,
hh_inc_10_20=float(feature.hh * census_rates_feature.hh_inc_10_20_rate) or 0,
hh_inc_20_30=float(feature.hh * census_rates_feature.hh_inc_20_30_rate) or 0,
hh_inc_30_40=float(feature.hh * census_rates_feature.hh_inc_30_40_rate) or 0,
hh_inc_40_50=float(feature.hh * census_rates_feature.hh_inc_40_50_rate) or 0,
hh_inc_50_60=float(feature.hh * census_rates_feature.hh_inc_50_60_rate) or 0,
hh_inc_60_75=float(feature.hh * census_rates_feature.hh_inc_60_75_rate) or 0,
hh_inc_75_100=float(feature.hh * census_rates_feature.hh_inc_75_100_rate) or 0,
hh_inc_100p=float(feature.hh * (census_rates_feature.hh_inc_100_125_rate +
census_rates_feature.hh_inc_125_150_rate +
census_rates_feature.hh_inc_150_200_rate +
census_rates_feature.hh_inc_200p_rate)) or 0,
pop=float(feature.pop) or 0,
pop_employed=float(feature.pop * census_rates_feature.pop_age16_up_rate *
census_rates_feature.pop_employed_rate) or 0,
pop_age16_up=float(feature.pop * census_rates_feature.pop_age16_up_rate) or 0,
pop_age65_up=float(feature.pop * census_rates_feature.pop_age65_up_rate) or 0,
emp=float(feature.emp) or 0,
emp_retail=float(feature.emp_retail_services + feature.emp_other_services) or 0,
emp_restaccom=float(feature.emp_accommodation + feature.emp_restaurant) or 0,
emp_arts_entertainment=float(feature.emp_arts_entertainment) or 0,
emp_office=float(feature.emp_off) or 0,
emp_public=float(feature.emp_public_admin + feature.emp_education) or 0,
emp_industry=float(feature.emp_ind + feature.emp_ag) or 0,
emp_within_1mile=float(vmt_variables_feature.emp_1mile) or 0,
hh_within_quarter_mile_trans=1 if vmt_variables_feature.transit_1km > 0 else 0,
vb_acres_parcel_res_total=float(vmt_variables_feature.acres_parcel_res_vb) or 0,
vb_acres_parcel_emp_total=float(vmt_variables_feature.acres_parcel_emp_vb) or 0,
vb_acres_parcel_mixed_total=float(vmt_variables_feature.acres_parcel_mixed_use_vb) or 0,
vb_du_total=float(vmt_variables_feature.du_vb) or 0,
vb_pop_total=float(vmt_variables_feature.pop_vb) or 0,
vb_emp_total=float(vmt_variables_feature.emp_vb) or 0,
vb_emp_retail_total=float(vmt_variables_feature.emp_ret_vb) or 0,
vb_hh_total=float(vmt_variables_feature.hh_vb) or 0,
vb_du_mf_total=float(vmt_variables_feature.du_mf_vb) or 0,
vb_hh_inc_00_10_total=float(vmt_variables_feature.hh_inc_00_10_vb) or 0,
vb_hh_inc_10_20_total=float(vmt_variables_feature.hh_inc_10_20_vb) or 0,
vb_hh_inc_20_30_total=float(vmt_variables_feature.hh_inc_20_30_vb) or 0,
vb_hh_inc_30_40_total=float(vmt_variables_feature.hh_inc_30_40_vb) or 0,
vb_hh_inc_40_50_total=float(vmt_variables_feature.hh_inc_40_50_vb) or 0,
vb_hh_inc_50_60_total=float(vmt_variables_feature.hh_inc_50_60_vb) or 0,
vb_hh_inc_60_75_total=float(vmt_variables_feature.hh_inc_60_75_vb) or 0,
vb_hh_inc_75_100_total=float(vmt_variables_feature.hh_inc_75_100_vb) or 0,
vb_hh_inc_100p_total=float(vmt_variables_feature.hh_inc_100p_vb) or 0,
vb_pop_employed_total=float(vmt_variables_feature.pop_employed_vb) or 0,
vb_pop_age16_up_total=float(vmt_variables_feature.pop_age16_up_vb) or 0,
vb_pop_age65_up_total=float(vmt_variables_feature.pop_age65_up_vb) or 0,
emp30m_transit=float(trip_lengths_feature.emp_30min_transit) or 0,
emp45m_transit=float(trip_lengths_feature.emp_45min_transit) or 0,
prod_hbw=float(trip_lengths_feature.productions_hbw) or 0,
prod_hbo=float(trip_lengths_feature.productions_hbo) or 0,
prod_nhb=float(trip_lengths_feature.productions_nhb) or 0,
attr_hbw=float(trip_lengths_feature.attractions_hbw) or 0,
attr_hbo=float(trip_lengths_feature.attractions_hbo) or 0,
attr_nhb=float(trip_lengths_feature.attractions_nhb) or 0,
qmb_acres_parcel_res_total=float(vmt_variables_feature.acres_parcel_res_qtrmi) or 0,
qmb_acres_parcel_emp_total=float(vmt_variables_feature.acres_parcel_emp_qtrmi) or 0,
qmb_acres_parcel_mixed_total=float(vmt_variables_feature.acres_parcel_mixed_use_qtrmi) or 0,
qmb_du_total=float(vmt_variables_feature.du_qtrmi) or 0,
qmb_pop_total=float(vmt_variables_feature.pop_qtrmi) or 0,
qmb_emp_total=float(vmt_variables_feature.emp_qtrmi) or 0,
qmb_emp_retail=float(vmt_variables_feature.emp_ret_qtrmi) or 0,
hh_avg_veh=float(census_rates_feature.hh_agg_veh_rate) or 0,
truck_adjustment_factor=0.031,
total_employment=float(total_employment['emp__sum']) or 0)
# run raw trip generation
vmt_feature_trips = generate_raw_trips(vmt_feature)
# run trip purpose splits
vmt_feature_trip_purposes = calculate_trip_purpose_splits(vmt_feature_trips)
# run log odds
vmt_feature_log_odds = calculate_log_odds(vmt_feature_trip_purposes)
# run vmt equations
vmt_output = calculate_final_vmt_results(vmt_feature_log_odds)
# filters the vmt feature dictionary for specific output fields for writing to the database
output_list = map(lambda key: vmt_output[key], vmt_output_field_list)
vmt_output_list.append(output_list)
def calculate_distance(distance_options):
print 'Calculating distance from target features areas'
##ST_DISTANCE returns distances in meters from geometries in WGS84 projection if set to false
thread_count = count_cores()
queue = queue_process()
#if the source table query has not results set all values to the max and break
zero_values_check = report_sql_values(
'''select
sum(*)
from {source_table} where {source_table_query};'''.format(**distance_options), 'fetchone')
if len(zero_values_check) == 0:
pSql = '''
update {target_table_schema}.{target_table} a set
{column} = {maximum_distance}
where {target_table_query} and {column} = 0
'''.format(**distance_options)
execute_sql(pSql)
return
pSql = '''drop function if exists distance_tool(
in_id int,
in_wkb_geometry geometry,
out id int,
out {column} float) cascade;'''.format(**distance_options)
execute_sql(pSql)
pSql = '''
CREATE OR REPLACE FUNCTION distance_tool(
in_id int,
in_wkb_geometry geometry,
out id int,
out {column} float)
AS
$$
select
$1 as id,
cast(st_distance(st_centroid($2), st_centroid(ref.geometry)) as float) as {column}
from
(select *, {source_geometry_column} as geometry from {source_table}) ref
where ST_DWITHIN($2, ref.geometry, {maximum_distance}) and ({source_table_query})
order by {column};
$$
COST 10000
language SQL STABLE strict;
'''.format(**distance_options)
execute_sql(pSql)
drop_table('{target_table_schema}.{target_table}_{column}'.format(**distance_options))
pSql = '''
create table {target_table_schema}.{target_table}_{column} (id int, {column} float);
'''.format(**distance_options)
execute_sql(pSql)
id_list = flatten(report_sql_values(
'''select cast({target_table_pk} as int) from {target_table_schema}.{target_table}
where {target_table_query} order by {target_table_pk}'''.format(
**distance_options), 'fetchall'))
insert_sql = '''
insert into {target_table_schema}.{target_table}_{column}
select (f).* from (
select distance_tool(a.id, a.wkb_geometry) as f
from (select {target_table_pk} as id, {target_geometry_column} as wkb_geometry
from {target_table_schema}.{target_table}
where
{target_table_pk} >= {bottom_range_id} and
{target_table_pk} <= {top_range_id} and
({target_table_query})
) a
) s;
'''.format(bottom_range_id="{start_id}", top_range_id="{end_id}", **distance_options)
for i in range(thread_count):
t = MultithreadProcess(queue, insert_sql)
t.setDaemon(True)
t.start()
#populate queue with data
rows_per_thread = len(id_list) / thread_count
offset = 0
for i in range(thread_count):
if i == thread_count - 1:
## last bucket gets any remainder, too
last_thread = len(id_list) - 1
else:
last_thread = offset + rows_per_thread - 1
rows_to_process = {
'start_id': id_list[offset],
'end_id': id_list[last_thread]
}
offset += rows_per_thread
queue.put(rows_to_process)
#wait on the queue until everything has been processed
queue.join()
add_attribute_idx(distance_options['target_table_schema'],
'{target_table}_{column}'.format(**distance_options), 'id')
pSql = '''
update {target_table_schema}.{target_table} a set
{column} = source_column
from (select id as source_id, {column} as source_column from {target_table_schema}.{target_table}_{column}) b
where cast(a.{target_table_pk} as int) = b.source_id and ({target_table_query})
'''.format(**distance_options)
execute_sql(pSql)
pSql = '''
update {target_table_schema}.{target_table} a set
{column} = {maximum_distance}
where {target_table_query} and {column} = 0
'''.format(**distance_options)
execute_sql(pSql)
drop_table('{target_table_schema}.{target_table}_{column}'.format(**distance_options))
def aggregate_within_variable_distance(distance_options):
thread_count = count_cores()
queue = queue_process()
sql_format = 'out {formatter} float'.format(formatter="{0}")
output_field_format = create_sql_calculations(distance_options['variable_field_list'], sql_format, ', ')
sql_format = 'cast({aggregation_type}({formatter}) as float) as {formatter}'.format(formatter="{0}", aggregation_type=distance_options['aggregation_type'])
sql_calculations_format = create_sql_calculations(distance_options['variable_field_list'], sql_format, ', ')
pSql = '''
drop function if exists aggregate_within_variable_distance_tool(
in_id int,
in_distance float,
in_geometry geometry,
out id int,
out wkb_geometry geometry,
{output_field_format}) cascade;'''.format(
output_field_format=output_field_format)
execute_sql(pSql)
pSql = '''
CREATE OR REPLACE FUNCTION aggregate_within_variable_distance_tool(
in_id int,
id_distance float,
in_geometry geometry,
out id int,
out wkb_geometry geometry,
{output_field_format})
AS
$$
select
$1 as id,
$3 as wkb_geometry,
{sql_calculations_format}
from {source_table} ref
WHERE st_dwithin($3, ref.wkb_geometry, $2) and (ref.{source_table_query});
$$
COST 10000
language SQL STABLE strict;
'''.format(source_table=distance_options['source_table'],
source_table_query=distance_options['source_table_query'],
output_field_format=output_field_format,
sql_calculations_format=sql_calculations_format)
execute_sql(pSql)
drop_table('{target_table_schema}.{target_table}_{suffix}'.format(
target_table_schema=distance_options['target_table_schema'],
target_table=distance_options['target_table'], suffix=distance_options['suffix']))
sql_format = '{formatter} float'.format(formatter="{0}", suffix=distance_options['suffix'])
output_table_field_format = create_sql_calculations(distance_options['variable_field_list'], sql_format, ', ')
pSql = '''create table {target_table_schema}.{target_table}_{suffix} (id int, wkb_geometry geometry, {output_table_field_format});'''.format(
target_table_schema=distance_options['target_table_schema'], target_table=distance_options['target_table'],
suffix=distance_options['suffix'], output_table_field_format=output_table_field_format)
execute_sql(pSql)
pSql = 'select cast(id as int) from {source_table} where id is not null order by id'.format(
source_table=distance_options['source_table'])
id_list = flatten(report_sql_values(pSql, 'fetchall'))
insert_sql = '''
insert into {target_table_schema}.{target_table}_{suffix}
select (f).* from (
select aggregate_within_variable_distance_tool(id, distance, wkb_geometry) as f
from {source_table}
where id >= {bottom_range_id} and id <= {top_range_id} and {source_table_query}
) s
where (f).id is not null;
'''.format(
target_table_schema=distance_options['target_table_schema'],
source_table_query=distance_options['source_table_query'],
target_table=distance_options['target_table'],
source_table=distance_options['source_table'],
suffix=distance_options['suffix'],
bottom_range_id="{start_id}",
top_range_id="{end_id}")
for i in range(thread_count):
t = MultithreadProcess(queue, insert_sql)
t.setDaemon(True)
t.start()
#populate queue with data
rows_per_thread = len(id_list) / thread_count
offset = 0
for i in range(thread_count):
if i == thread_count - 1:
## last bucket gets any remainder, too
last_thread = len(id_list) - 1
else:
last_thread = offset + rows_per_thread - 1
rows_to_process = {
'start_id': id_list[offset],
'end_id': id_list[last_thread]
}
offset += rows_per_thread
queue.put(rows_to_process)
#wait on the queue until everything has been processed
queue.join()
add_attribute_idx(distance_options['target_table_schema'],
'{target_table}_{suffix}'.format(target_table=distance_options['target_table'],
suffix=distance_options['suffix']), 'id')
update_table_field_format = create_sql_calculations(distance_options['variable_field_list'], '{0} = (case when b.{0}_var is null then 0 else b.{0}_var end)', ', ')
select_format = create_sql_calculations(distance_options['variable_field_list'], '{0} as {0}_var', ', ')
pSql = '''
update {target_table_schema}.{target_table} a set {update_table_field_format}
from (select id as {suffix}_id, wkb_geometry, {select_format} from {target_table_schema}.{target_table}_{suffix}) b
where st_intersects(st_centroid(a.analysis_geom), b.wkb_geometry) and {target_table_query};
'''.format(
target_table_schema=distance_options['target_table_schema'],
target_table=distance_options['target_table'],
target_table_query=distance_options['target_table_query'],
target_table_pk=distance_options['target_table_pk'],
update_table_field_format=update_table_field_format,
select_format=select_format,
suffix=distance_options['suffix']
)
execute_sql(pSql)
drop_table('{target_table_schema}.{target_table}_{suffix}'.format(
target_table_schema=distance_options['target_table_schema'],
target_table=distance_options['target_table'],
suffix=distance_options['suffix']))
def populate_transformations_and_indices(sql_config_dict):
##TRANSFORMING PRELIMINARY AND MODEL VARIABLES STEPS 1 AND 2
pSql = '''
update {public_health_variables_schema}.{public_health_variables_table} a set
resmix_dens = (case
when resmix_dens = 0
then 0
else ln((du_1km_tr / resmix_dens) + 1) end),
bldg_sqft_ret = bldg_sqft_ret ^ 0.25,
b1 = (case when b1 = 0 then 1E-24 else b1 end),
b2 = (case when b2 = 0 then 1E-24 else b2 end),
b3 = (case when b3 = 0 then 1E-24 else b3 end),
b4 = (case when b4 = 0 then 1E-24 else b4 end),
b5 = (case when b5 = 0 then 1E-24 else b5 end),
local_street = local_street / 5280,
major_street = (major_street / 5280) ^ 0.5,
far_nonres = (case when far_nonres = 0 then 0 else ((bldg_sqft_ret1 + bldg_sqft_off) / (far_nonres * 43560)) ^ 0.25 end),
rail_any = (case when rail_any > 0 then 1 else 0 end),
freeway_arterial_any = (case when freeway_arterial_any <500 then 1 else 0 end),
transit_distance = transit_distance ^ 0.25,
transit_count = transit_count ^ 0.5,
school_distance = school_distance ^ 0.25,
retail_distance = retail_distance ^ 0.5,
intersection_density_sqmi = intersection_density_sqmi ^ 0.5,
park_open_space_distance = park_open_space_distance ^ 0.5,
acres_parcel_park_open_space = acres_parcel_park_open_space ^ 0.25,
du_variable = du_variable ^ 0.25,
emp_variable = emp_variable ^ 0.25
where pop > 0;
'''.format(**sql_config_dict)
execute_sql(pSql)
pSql = '''
update {public_health_variables_schema}.{public_health_variables_table} a set
du_1km_tr = du_1km_tr ^ 0.25,
a = b1 + b2 + b3 + b4 + b5
where pop > 0;
'''.format(**sql_config_dict)
execute_sql(pSql)
pSql = '''
update {public_health_variables_schema}.{public_health_variables_table} a set
mix5 = (case
when a > 0 and ((b1/a)*LN(b1/a) + (b2/a)*LN(b2/a) + (b3/a)*LN(b3/a) + (b4/a)*LN(b4/a) + (b5/a)*LN(b5/a)) / (-1 * ln(5)) < 0.0001
then 0
when a > 0 and ((b1/a)*LN(b1/a) + (b2/a)*LN(b2/a) + (b3/a)*LN(b3/a) + (b4/a)*LN(b4/a) + (b5/a)*LN(b5/a)) / (-1 * ln(5)) > 0.999
then 0
when a > 0 and ((b1/a)*LN(b1/a) + (b2/a)*LN(b2/a) + (b3/a)*LN(b3/a) + (b4/a)*LN(b4/a) + (b5/a)*LN(b5/a)) / (-1 * ln(5)) > 0.0001 and
((b1/a)*LN(b1/a) + (b2/a)*LN(b2/a) + (b3/a)*LN(b3/a) + (b4/a)*LN(b4/a) + (b5/a)*LN(b5/a)) / (-1 * ln(5)) < 0.999
then ((((b1/a)*LN(b1/a) + (b2/a)*LN(b2/a) + (b3/a)*LN(b3/a) + (b4/a)*LN(b4/a) + (b5/a)*LN(b5/a))) / (-1 * ln(5))) ^ 0.5
else 0
end)
where pop > 0;
'''.format(**sql_config_dict)
execute_sql(pSql)
##CALCULATING PRELIMINARY INDEX VARIABLES
pSql = '''
update {public_health_variables_schema}.{public_health_variables_table} a set
res_index = (resmix_dens - 1.82) / 0.9478 + (du_1km_tr - 7.451) / 1.8664,
com_index = 2 * ((bldg_sqft_ret - 24.38) / 10.4543) + 2 * ((far_nonres - 0.5938) / 0.2064) - (retail_distance - 13.91) / 13.5014 - (restaurant_distance - 599.4) / 670.6584,
park_access = (acres_parcel_park_open_space - 1.704) / 1.0956 - (park_open_space_distance - 18.48) / 11.3701,
regional_access = (du_variable - 24.92) / 6.0667 + (emp_variable - 26.13) / 7.2701,
network_index = (intersection_density_sqmi - 9.186) / 3.5870 + (local_street - 29.73) / 10.5599,
transit_access = 2*((transit_count - 3.769) / 3.9104) - (transit_distance - 4.602) / 1.8653,
major_road_index = (major_street - 0.6099) / 0.8575 + (freeway_arterial_any - 0.2126) / 0.4092
where pop > 0;
'''.format(**sql_config_dict)
execute_sql(pSql)
##CALCULATING WALK INDEX VARIABLE
pSql = '''
update {public_health_variables_schema}.{public_health_variables_table} a set
walk_index = 2 * ((res_index - 0.000221) / 1.9586) + 1.5 * ((network_index - 1.65E-16) / 1.846573714) + (com_index - 0.0005076) / 5.3122 + 0.5*((mix5 - 0.4379) / 0.1840)
where pop > 0;
'''.format(**sql_config_dict)
execute_sql(pSql)
##CALCULATING WALK INDEX VARIATIONS
pSql = '''
update {public_health_variables_schema}.{public_health_variables_table} a set
walk_index_chts_senior_walk_any = (12.69/6.59) * ((walk_index - 0.0005914) / 4.4684) + (transit_access - 2.727E-16) / 2.8399,
walk_index_chts_senior_auto_min = (7.09/2.63) * ((walk_index - 0.0005914) / 4.4684) + (transit_access - 2.727E-16) / 2.8399,
walk_index_chts_teens_walk_any = (8.24/3.34) * ((walk_index - 0.0005914) / 4.4684) + (4.91/3.34) * ((transit_access - 2.727E-16) / 2.8399) + (rail_any - 0.09589) / 0.2944,
walk_index_chts_child_walk_any = (8.94/5.81) * ((walk_index - 0.0005914) / 4.4684) + (transit_access - 2.727E-16) / 2.8399,
walk_index_chts_adult_bike_any = (8.22/2.19) * ((walk_index - 0.0005914) / 4.4684) + (regional_access) / 1.9944,
walk_index_chts_adult_walk_min = (9.79/5.56) * ((walk_index - 0.0005914) / 4.4684) + (transit_access - 2.727E-16) / 2.8399,
walk_index_chts_senior_walk_min = (2.43/2.34) * ((walk_index - 0.0005914) / 4.4684) + (transit_access - 2.727E-16) / 2.8399,
walk_index_chts_teens_walk_min = (5.12/2.42) * ((walk_index - 0.0005914) / 4.4684) + (transit_access - 2.727E-16) / 2.8399,
walk_index_chts_adult_auto_min = (7.99/2.11) * ((walk_index - 0.0005914) / 4.4684) + (rail_any - 0.09589) / 0.2944,
walk_index_chis_adult_modPA_any = (8.57/2.94) * ((walk_index - 0.0005914) / 4.4684) + (4.36/2.94) * ((transit_access - 2.727E-16) / 2.8399) + ((rail_any - 0.09589) / 0.2944) + (3.22/2.94) * ((regional_access) / 1.9944),
walk_index_chis_adult_overweight = (walk_index - 0.0005914) / 4.4684 + (7.92/3.15) * ((regional_access) / 1.9944),
walk_index_chis_senior_overweight = (walk_index - 0.0005914) / 4.4684 + (3.15/2.01) * ((regional_access) / 1.9944),
walk_index_chis_adult_obese = (walk_index - 0.0005914) / 4.4684 + (6.07/2.58) * ((regional_access) / 1.9944),
walk_index_chis_senior_gh = (2.74/2.14) * ((walk_index - 0.0005914) / 4.4684) + (regional_access) / 1.9944,
walk_index_chis_senior_walk_le_min = (2.82/2.08) * ((walk_index - 0.0005914) / 4.4684) + (transit_access - 2.727E-16) / 2.8399,
walk_index_chis_adult_modPA_min = (7.96/1.98) * ((walk_index - 0.0005914) / 4.4684) + (4.32/1.98) * ((transit_access - 2.727E-16) / 2.8399) + (rail_any - 0.09589) / 0.2944,
walk_index_chis_adult_bmi = (walk_index - 0.0005914) / 4.4684 + (4.47/3.50) * ((transit_access - 2.727E-16) / 2.8399) + (8.94/3.50) * ((regional_access) / 1.9944),
walk_index_chis_child_PA60 =(4.25/2.12) * ((walk_index - 0.0005914) / 4.4684) + (regional_access) / 1.9944,
walk_index_chis_child_walk_school = (8.13/3.17)*((walk_index - 0.0005914) / 4.4684) + (regional_access) / 1.9944
where pop > 0;
'''.format(**sql_config_dict)
execute_sql(pSql)
