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