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