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 aggregate_results_to_block_group(self, sql_config_dict): """ Aggregates the result table (at grid scale) to a Census Block Group result """ attribute_list = filter( lambda x: x not in [ 'id', 'hh', 'pop', 'pop_adult', 'pop_adult_high', 'pop_adult_med', 'pop_adult_low', 'pop_senior', 'pop_teen', 'pop_children' ], self.outcome_fields) field_calculations = ''', '''.join([ "case when SUM(grid.pop) > 0 then SUM(grid.{field} * grid.pop) / SUM(grid.pop) else 0 end as {field}" .format(field=field) for field in attribute_list ]) drop_table( '{block_group_outcome_schema}.{block_group_outcome_table}'.format( **sql_config_dict)) create_blockgroup_results = """ CREATE TABLE {block_group_outcome_schema}.{block_group_outcome_table} AS SELECT bg.id as id, bg.blockgroup as blockgroup, bg.tract as tract, bg.county_name as county_name, SUM(grid.pop) as pop, SUM(grid.hh) as hh, SUM(grid.pop_adult) as pop_adult, SUM(grid.pop_adult_high) as pop_adult_high, SUM(grid.pop_adult_med) as pop_adult_med, SUM(grid.pop_adult_low) as pop_adult_low, SUM(grid.pop_senior) as pop_senior, SUM(grid.pop_teen) as pop_teen, SUM(grid.pop_children) as pop_children, {field_calculations}, bg.wkb_geometry as wkb_geometry FROM (select grid_id, census_id from {public_health_variables_schema}.{source_grid_table}_join group by grid_id, census_id) grid_portions inner join {grid_outcome_schema}.{grid_outcome_table} grid on grid_portions.grid_id = grid.id inner join {census_rate_schema}.{census_rate_table} bg on grid_portions.census_id = bg.id group by bg.id, bg.blockgroup, tract, bg.county_name, bg.wkb_geometry; """.format(field_calculations=field_calculations, **sql_config_dict) execute_sql(create_blockgroup_results)
def 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 run_aggregate_within_variable_distance_processes(sql_config_dict): drop_table( '{public_health_variables_schema}.{uf_canvas_table}_variable'.format( public_health_variables_schema=sql_config_dict[ 'public_health_variables_schema'], uf_canvas_table=sql_config_dict['uf_canvas_table'])) pSql = ''' create table {public_health_variables_schema}.{uf_canvas_table}_variable as select a.id, st_transform(a.wkb_geometry, 3310) as wkb_geometry, cast(a.attractions_hbw * 1609.0 as float) as distance, sum(du * st_area(st_intersection(a.wkb_geometry, b.wkb_geometry)) / st_area(b.wkb_geometry)) as du_variable, sum(emp * st_area(st_intersection(a.wkb_geometry, b.wkb_geometry)) / st_area(b.wkb_geometry)) as emp_variable from (select id, wkb_geometry, attractions_hbw from {trip_lengths_schema}.{trip_lengths_table}) a, (select wkb_geometry, du, emp from {uf_canvas_schema}.{uf_canvas_table} where du + emp > 0) b where st_intersects(b.wkb_geometry, a.wkb_geometry) group by a.id, a.wkb_geometry, a.attractions_hbw; '''.format(public_health_variables_schema=sql_config_dict[ 'public_health_variables_schema'], uf_canvas_schema=sql_config_dict['uf_canvas_schema'], uf_canvas_table=sql_config_dict['uf_canvas_table'], trip_lengths_schema=sql_config_dict['trip_lengths_schema'], trip_lengths_table=sql_config_dict['trip_lengths_table']) execute_sql(pSql) add_geom_idx(sql_config_dict['public_health_variables_schema'], sql_config_dict['uf_canvas_table'] + '_variable') add_primary_key(sql_config_dict['public_health_variables_schema'], sql_config_dict['uf_canvas_table'] + '_variable', 'id') add_analysis_geom(sql_config_dict['public_health_variables_schema'], sql_config_dict['public_health_variables_table']) aggregate_within_variable_distance( dict(source_table=sql_config_dict['public_health_variables_schema'] + '.' + sql_config_dict['uf_canvas_table'] + '_variable', source_table_query='id is not null', target_table_schema=sql_config_dict[ 'public_health_variables_schema'], target_table=sql_config_dict['public_health_variables_table'], target_table_query='pop > 0', target_table_pk='id', suffix='variable', aggregation_type='sum', variable_field_list=['du_variable', 'emp_variable'])) drop_table( '{public_health_variables_schema}.{uf_canvas_table}_variable'.format( public_health_variables_schema=sql_config_dict[ 'public_health_variables_schema'], uf_canvas_table=sql_config_dict['uf_canvas_table']))
def aggregate_results_to_block_group(self, sql_config_dict): """ Aggregates the result table (at grid scale) to a Census Block Group result """ attribute_list = filter(lambda x: x not in ['id', 'hh', 'pop', 'pop_adult', 'pop_adult_high', 'pop_adult_med', 'pop_adult_low', 'pop_senior', 'pop_teen', 'pop_children'], self.outcome_fields) field_calculations = ''', '''.join([ "case when SUM(grid.pop) > 0 then SUM(grid.{field} * grid.pop) / SUM(grid.pop) else 0 end as {field}".format( field=field) for field in attribute_list ]) drop_table('{block_group_outcome_schema}.{block_group_outcome_table}'.format(**sql_config_dict)) create_blockgroup_results = """ CREATE TABLE {block_group_outcome_schema}.{block_group_outcome_table} AS SELECT bg.id as id, bg.blockgroup as blockgroup, bg.tract as tract, bg.county_name as county_name, SUM(grid.pop) as pop, SUM(grid.hh) as hh, SUM(grid.pop_adult) as pop_adult, SUM(grid.pop_adult_high) as pop_adult_high, SUM(grid.pop_adult_med) as pop_adult_med, SUM(grid.pop_adult_low) as pop_adult_low, SUM(grid.pop_senior) as pop_senior, SUM(grid.pop_teen) as pop_teen, SUM(grid.pop_children) as pop_children, {field_calculations}, bg.wkb_geometry as wkb_geometry FROM (select grid_id, census_id from {public_health_variables_schema}.{source_grid_table}_join group by grid_id, census_id) grid_portions inner join {grid_outcome_schema}.{grid_outcome_table} grid on grid_portions.grid_id = grid.id inner join {census_rate_schema}.{census_rate_table} bg on grid_portions.census_id = bg.id group by bg.id, bg.blockgroup, tract, bg.county_name, bg.wkb_geometry; """.format(field_calculations=field_calculations, **sql_config_dict) execute_sql(create_blockgroup_results)
def 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_aggregate_within_variable_distance_processes(sql_config_dict): drop_table('{public_health_variables_schema}.{uf_canvas_table}_variable'.format( public_health_variables_schema=sql_config_dict['public_health_variables_schema'], uf_canvas_table=sql_config_dict['uf_canvas_table'])) pSql = ''' create table {public_health_variables_schema}.{uf_canvas_table}_variable as select a.id, st_transform(a.wkb_geometry, 3310) as wkb_geometry, cast(a.attractions_hbw * 1609.0 as float) as distance, sum(du * st_area(st_intersection(a.wkb_geometry, b.wkb_geometry)) / st_area(b.wkb_geometry)) as du_variable, sum(emp * st_area(st_intersection(a.wkb_geometry, b.wkb_geometry)) / st_area(b.wkb_geometry)) as emp_variable from (select id, wkb_geometry, attractions_hbw from {trip_lengths_schema}.{trip_lengths_table}) a, (select wkb_geometry, du, emp from {uf_canvas_schema}.{uf_canvas_table} where du + emp > 0) b where st_intersects(b.wkb_geometry, a.wkb_geometry) group by a.id, a.wkb_geometry, a.attractions_hbw; '''.format(public_health_variables_schema=sql_config_dict['public_health_variables_schema'], uf_canvas_schema=sql_config_dict['uf_canvas_schema'], uf_canvas_table=sql_config_dict['uf_canvas_table'], trip_lengths_schema=sql_config_dict['trip_lengths_schema'], trip_lengths_table=sql_config_dict['trip_lengths_table']) execute_sql(pSql) add_geom_idx(sql_config_dict['public_health_variables_schema'], sql_config_dict['uf_canvas_table'] + '_variable') add_primary_key(sql_config_dict['public_health_variables_schema'], sql_config_dict['uf_canvas_table'] + '_variable', 'id') add_analysis_geom(sql_config_dict['public_health_variables_schema'], sql_config_dict['public_health_variables_table']) aggregate_within_variable_distance(dict( source_table=sql_config_dict['public_health_variables_schema'] + '.' + sql_config_dict['uf_canvas_table'] + '_variable', source_table_query='id is not null', target_table_schema=sql_config_dict['public_health_variables_schema'], target_table=sql_config_dict['public_health_variables_table'], target_table_query='pop > 0', target_table_pk='id', suffix='variable', aggregation_type='sum', variable_field_list=['du_variable', 'emp_variable'] )) drop_table('{public_health_variables_schema}.{uf_canvas_table}_variable'.format( public_health_variables_schema=sql_config_dict['public_health_variables_schema'], uf_canvas_table=sql_config_dict['uf_canvas_table']))
def 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 import_data(self, **kwargs): """ Imports data from an external source to create the test data :return a two item tuple containing the region that was imported and a list of the imported projects """ # Calculate a sample lat/lon box of the config_entity config_entity = self.config_entity if self.test: bounds = chop_geom(config_entity.bounds, 0.90) logger.info( u"Creating subselection with extents: {0}. This will be used to crop any table that doesn't have a sample version" .format(bounds)) conn = psycopg2.connect( **pg_connection_parameters(settings.DATABASES['default'])) conn.set_isolation_level( psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) cursor = conn.cursor() for db_entity in self.db_entities: # This is the index on wkb_geometry. spatial_index_name = '{schema}_{key}_geom_idx'.format( schema=db_entity.schema, key=db_entity.key) table = db_entity.table if db_entity.has_file_url: # Remove any table of the same name from the import schema. This is unlikley since imported # tables have timestamps drop_table('"%s"."%s"' % (settings.IMPORT_SCHEMA, db_entity.key)) sql_file_path = file_url_to_path(db_entity.url) # Create a command that pipes shp2pgsql to psql db_entity.srid = db_entity.srid or '4326' logger.info("verifying SRID {0}".format(db_entity.srid)) verify_srid(db_entity.srid) # Create the import schema if needed PGNamespace.objects.create_schema(settings.IMPORT_SCHEMA) # Import the table import_sql_command = '/usr/bin/psql {0} -f {1}'.format( self.target_database_connection, sql_file_path) stdin = "{0}\n{1}".format( self.arguments.get('password', None), self.target_database.get('PASSWORD', None)) results = self.command_execution.run(import_sql_command, stdin=stdin) if results.returncode: raise Exception(results.stderr.text) # We expect a table in the public schema with a named based on db_entity.key # Move the table from the public schema to the db_entity schema move_to_schema = "alter table {0}.{1} set schema {2};".format( settings.IMPORT_SCHEMA, db_entity.key, db_entity.schema) logger.info("Moving import file table to schema: %s" % move_to_schema) cursor.execute(move_to_schema) # Drop the constraint that enforces the srid of the wkb_geometry if one exists drop_constraint = '''alter table {0}.{1} drop constraint if exists enforce_srid_wkb_geometry'''.format( db_entity.schema, db_entity.key) logger.info("Dropping constraint on wkb_geometry: %s" % drop_constraint) cursor.execute(drop_constraint) # Note we're not creating an index on wkb_geometry # here because imported files already have an index # created. elif db_entity.has_db_url: # The import database currently stores tables as # public.[config_entity.key]_[feature_class._meta.db_table (with schema removed)][_sample (for samples)] # # We always use the table name without the word sample for the target table name if settings.USE_SAMPLE_DATA_SETS or self.test: source_table = "{0}_{1}_{2}".format( config_entity.import_key or config_entity.key, db_entity.table, 'sample') else: source_table = "{0}_{1}".format( config_entity.import_key or config_entity.key, db_entity.table) connection_dict = postgres_url_to_connection_dict( db_entity.url) self._dump_tables_to_target('-t %s' % source_table, source_schema='public', target_schema=db_entity.schema, source_table=source_table, target_table=table, connection_dict=connection_dict) # Create a spatial index spatial_index = '''create index {index_name} on {schema}.{key} using GIST (wkb_geometry);'''.format( index_name=spatial_index_name, schema=db_entity.schema, key=db_entity.key) cursor.execute(spatial_index) # Whether the table comes from our server or an upload, we want to transform the SRID to 4326 transform_to_4326 = 'ALTER TABLE {schema}.{table} ALTER COLUMN wkb_geometry ' \ 'TYPE Geometry(geometry, 4326) ' \ 'USING ST_Transform(ST_Force_2d(wkb_geometry), 4326);'.format logger.info("Transforming to 4326: %s" % transform_to_4326( schema=db_entity.schema, table=db_entity.table)) cursor.execute( transform_to_4326(schema=db_entity.schema, table=db_entity.table)) # Now cluster the data and vacuum so that future joins are faster: # * CLUSTER rewrites the data on disk so that rows that are spatially near each # other are also near each other on disk # * VACUUM cleans up disk space, removing sparse holes on disk. # * ANALYZE regenerates statistics about wkb_geometry so that the query planner can make # better decisions. logger.info('Clustering %s.%s to optimize spatial joins', db_entity.schema, table) cluster = 'CLUSTER {index_name} ON {target_schema}.{target_table};'.format( index_name=spatial_index_name, target_schema=db_entity.schema, target_table=table) cursor.execute(cluster) logger.info('Vacuuming and analyzing %s.%s.', db_entity.schema, table) analyze = 'VACUUM ANALYZE {target_schema}.{target_table};'.format( target_schema=db_entity.schema, target_table=table) cursor.execute(analyze) logger.info( "Finished importing data for DbEntity table {0}.{1}".format( db_entity.schema, db_entity.key))
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 calculate_distance(distance_options): print 'Calculating distance from target features areas' ##ST_DISTANCE returns distances in meters from geometries in WGS84 projection if set to false thread_count = count_cores() queue = queue_process() #if the source table query has not results set all values to the max and break zero_values_check = report_sql_values( '''select sum(*) from {source_table} where {source_table_query};'''.format(**distance_options), 'fetchone') if len(zero_values_check) == 0: pSql = ''' update {target_table_schema}.{target_table} a set {column} = {maximum_distance} where {target_table_query} and {column} = 0 '''.format(**distance_options) execute_sql(pSql) return pSql = '''drop function if exists distance_tool( in_id int, in_wkb_geometry geometry, out id int, out {column} float) cascade;'''.format(**distance_options) execute_sql(pSql) pSql = ''' CREATE OR REPLACE FUNCTION distance_tool( in_id int, in_wkb_geometry geometry, out id int, out {column} float) AS $$ select $1 as id, cast(st_distance(st_centroid($2), st_centroid(ref.geometry)) as float) as {column} from (select *, {source_geometry_column} as geometry from {source_table}) ref where ST_DWITHIN($2, ref.geometry, {maximum_distance}) and ({source_table_query}) order by {column}; $$ COST 10000 language SQL STABLE strict; '''.format(**distance_options) execute_sql(pSql) drop_table('{target_table_schema}.{target_table}_{column}'.format(**distance_options)) pSql = ''' create table {target_table_schema}.{target_table}_{column} (id int, {column} float); '''.format(**distance_options) execute_sql(pSql) id_list = flatten(report_sql_values( '''select cast({target_table_pk} as int) from {target_table_schema}.{target_table} where {target_table_query} order by {target_table_pk}'''.format( **distance_options), 'fetchall')) insert_sql = ''' insert into {target_table_schema}.{target_table}_{column} select (f).* from ( select distance_tool(a.id, a.wkb_geometry) as f from (select {target_table_pk} as id, {target_geometry_column} as wkb_geometry from {target_table_schema}.{target_table} where {target_table_pk} >= {bottom_range_id} and {target_table_pk} <= {top_range_id} and ({target_table_query}) ) a ) s; '''.format(bottom_range_id="{start_id}", top_range_id="{end_id}", **distance_options) for i in range(thread_count): t = MultithreadProcess(queue, insert_sql) t.setDaemon(True) t.start() #populate queue with data rows_per_thread = len(id_list) / thread_count offset = 0 for i in range(thread_count): if i == thread_count - 1: ## last bucket gets any remainder, too last_thread = len(id_list) - 1 else: last_thread = offset + rows_per_thread - 1 rows_to_process = { 'start_id': id_list[offset], 'end_id': id_list[last_thread] } offset += rows_per_thread queue.put(rows_to_process) #wait on the queue until everything has been processed queue.join() add_attribute_idx(distance_options['target_table_schema'], '{target_table}_{column}'.format(**distance_options), 'id') pSql = ''' update {target_table_schema}.{target_table} a set {column} = source_column from (select id as source_id, {column} as source_column from {target_table_schema}.{target_table}_{column}) b where cast(a.{target_table_pk} as int) = b.source_id and ({target_table_query}) '''.format(**distance_options) execute_sql(pSql) pSql = ''' update {target_table_schema}.{target_table} a set {column} = {maximum_distance} where {target_table_query} and {column} = 0 '''.format(**distance_options) execute_sql(pSql) drop_table('{target_table_schema}.{target_table}_{column}'.format(**distance_options))
def aggregate_within_variable_distance(distance_options): thread_count = count_cores() queue = queue_process() sql_format = 'out {formatter} float'.format(formatter="{0}") output_field_format = create_sql_calculations(distance_options['variable_field_list'], sql_format, ', ') sql_format = 'cast({aggregation_type}({formatter}) as float) as {formatter}'.format(formatter="{0}", aggregation_type=distance_options['aggregation_type']) sql_calculations_format = create_sql_calculations(distance_options['variable_field_list'], sql_format, ', ') pSql = ''' drop function if exists aggregate_within_variable_distance_tool( in_id int, in_distance float, in_geometry geometry, out id int, out wkb_geometry geometry, {output_field_format}) cascade;'''.format( output_field_format=output_field_format) execute_sql(pSql) pSql = ''' CREATE OR REPLACE FUNCTION aggregate_within_variable_distance_tool( in_id int, id_distance float, in_geometry geometry, out id int, out wkb_geometry geometry, {output_field_format}) AS $$ select $1 as id, $3 as wkb_geometry, {sql_calculations_format} from {source_table} ref WHERE st_dwithin($3, ref.wkb_geometry, $2) and (ref.{source_table_query}); $$ COST 10000 language SQL STABLE strict; '''.format(source_table=distance_options['source_table'], source_table_query=distance_options['source_table_query'], output_field_format=output_field_format, sql_calculations_format=sql_calculations_format) execute_sql(pSql) drop_table('{target_table_schema}.{target_table}_{suffix}'.format( target_table_schema=distance_options['target_table_schema'], target_table=distance_options['target_table'], suffix=distance_options['suffix'])) sql_format = '{formatter} float'.format(formatter="{0}", suffix=distance_options['suffix']) output_table_field_format = create_sql_calculations(distance_options['variable_field_list'], sql_format, ', ') pSql = '''create table {target_table_schema}.{target_table}_{suffix} (id int, wkb_geometry geometry, {output_table_field_format});'''.format( target_table_schema=distance_options['target_table_schema'], target_table=distance_options['target_table'], suffix=distance_options['suffix'], output_table_field_format=output_table_field_format) execute_sql(pSql) pSql = 'select cast(id as int) from {source_table} where id is not null order by id'.format( source_table=distance_options['source_table']) id_list = flatten(report_sql_values(pSql, 'fetchall')) insert_sql = ''' insert into {target_table_schema}.{target_table}_{suffix} select (f).* from ( select aggregate_within_variable_distance_tool(id, distance, wkb_geometry) as f from {source_table} where id >= {bottom_range_id} and id <= {top_range_id} and {source_table_query} ) s where (f).id is not null; '''.format( target_table_schema=distance_options['target_table_schema'], source_table_query=distance_options['source_table_query'], target_table=distance_options['target_table'], source_table=distance_options['source_table'], suffix=distance_options['suffix'], bottom_range_id="{start_id}", top_range_id="{end_id}") for i in range(thread_count): t = MultithreadProcess(queue, insert_sql) t.setDaemon(True) t.start() #populate queue with data rows_per_thread = len(id_list) / thread_count offset = 0 for i in range(thread_count): if i == thread_count - 1: ## last bucket gets any remainder, too last_thread = len(id_list) - 1 else: last_thread = offset + rows_per_thread - 1 rows_to_process = { 'start_id': id_list[offset], 'end_id': id_list[last_thread] } offset += rows_per_thread queue.put(rows_to_process) #wait on the queue until everything has been processed queue.join() add_attribute_idx(distance_options['target_table_schema'], '{target_table}_{suffix}'.format(target_table=distance_options['target_table'], suffix=distance_options['suffix']), 'id') update_table_field_format = create_sql_calculations(distance_options['variable_field_list'], '{0} = (case when b.{0}_var is null then 0 else b.{0}_var end)', ', ') select_format = create_sql_calculations(distance_options['variable_field_list'], '{0} as {0}_var', ', ') pSql = ''' update {target_table_schema}.{target_table} a set {update_table_field_format} from (select id as {suffix}_id, wkb_geometry, {select_format} from {target_table_schema}.{target_table}_{suffix}) b where st_intersects(st_centroid(a.analysis_geom), b.wkb_geometry) and {target_table_query}; '''.format( target_table_schema=distance_options['target_table_schema'], target_table=distance_options['target_table'], target_table_query=distance_options['target_table_query'], target_table_pk=distance_options['target_table_pk'], update_table_field_format=update_table_field_format, select_format=select_format, suffix=distance_options['suffix'] ) execute_sql(pSql) drop_table('{target_table_schema}.{target_table}_{suffix}'.format( target_table_schema=distance_options['target_table_schema'], target_table=distance_options['target_table'], suffix=distance_options['suffix']))
def 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_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 import_data(self, **kwargs): """ Imports data from an external source to create the test data :return a two item tuple containing the region that was imported and a list of the imported projects """ # Calculate a sample lat/lon box of the config_entity config_entity = self.config_entity if self.test: bounds = chop_geom(config_entity.bounds, 0.90) logger.info(u"Creating subselection with extents: {0}. This will be used to crop any table that doesn't have a sample version".format(bounds)) conn = psycopg2.connect(**pg_connection_parameters(settings.DATABASES['default'])) conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) cursor = conn.cursor() for db_entity in self.db_entities: # This is the index on wkb_geometry. spatial_index_name = '{schema}_{key}_geom_idx'.format(schema=db_entity.schema, key=db_entity.key) table = db_entity.table if db_entity.has_file_url: # Remove any table of the same name from the import schema. This is unlikley since imported # tables have timestamps drop_table('"%s"."%s"' % (settings.IMPORT_SCHEMA, db_entity.key)) sql_file_path = file_url_to_path(db_entity.url) # Create a command that pipes shp2pgsql to psql db_entity.srid = db_entity.srid or '4326' logger.info("verifying SRID {0}".format(db_entity.srid)) verify_srid(db_entity.srid) # Create the import schema if needed PGNamespace.objects.create_schema(settings.IMPORT_SCHEMA) # Import the table import_sql_command = '/usr/bin/psql {0} -f {1}'.format(self.target_database_connection, sql_file_path) stdin = "{0}\n{1}".format(self.arguments.get('password', None), self.target_database.get('PASSWORD', None)) results = self.command_execution.run(import_sql_command, stdin=stdin) if results.returncode: raise Exception(results.stderr.text) # We expect a table in the public schema with a named based on db_entity.key # Move the table from the public schema to the db_entity schema move_to_schema = "alter table {0}.{1} set schema {2};".format(settings.IMPORT_SCHEMA, db_entity.key, db_entity.schema) logger.info("Moving import file table to schema: %s" % move_to_schema) cursor.execute(move_to_schema) # Drop the constraint that enforces the srid of the wkb_geometry if one exists drop_constraint = '''alter table {0}.{1} drop constraint if exists enforce_srid_wkb_geometry'''.format(db_entity.schema, db_entity.key) logger.info("Dropping constraint on wkb_geometry: %s" % drop_constraint) cursor.execute(drop_constraint) # Note we're not creating an index on wkb_geometry # here because imported files already have an index # created. elif db_entity.has_db_url: # The import database currently stores tables as # public.[config_entity.key]_[feature_class._meta.db_table (with schema removed)][_sample (for samples)] # # We always use the table name without the word sample for the target table name if settings.USE_SAMPLE_DATA_SETS or self.test: source_table = "{0}_{1}_{2}".format( config_entity.import_key or config_entity.key, db_entity.table, 'sample') else: source_table = "{0}_{1}".format(config_entity.import_key or config_entity.key, db_entity.table) connection_dict = postgres_url_to_connection_dict(db_entity.url) self._dump_tables_to_target( '-t %s' % source_table, source_schema='public', target_schema=db_entity.schema, source_table=source_table, target_table=table, connection_dict=connection_dict) # Create a spatial index spatial_index = '''create index {index_name} on {schema}.{key} using GIST (wkb_geometry);'''.format( index_name=spatial_index_name, schema=db_entity.schema, key=db_entity.key) cursor.execute(spatial_index) # Whether the table comes from our server or an upload, we want to transform the SRID to 4326 transform_to_4326 = 'ALTER TABLE {schema}.{table} ALTER COLUMN wkb_geometry ' \ 'TYPE Geometry(geometry, 4326) ' \ 'USING ST_Transform(ST_Force_2d(wkb_geometry), 4326);'.format logger.info("Transforming to 4326: %s" % transform_to_4326(schema=db_entity.schema, table=db_entity.table)) cursor.execute(transform_to_4326(schema=db_entity.schema, table=db_entity.table)) # Now cluster the data and vacuum so that future joins are faster: # * CLUSTER rewrites the data on disk so that rows that are spatially near each # other are also near each other on disk # * VACUUM cleans up disk space, removing sparse holes on disk. # * ANALYZE regenerates statistics about wkb_geometry so that the query planner can make # better decisions. logger.info('Clustering %s.%s to optimize spatial joins', db_entity.schema, table) cluster = 'CLUSTER {index_name} ON {target_schema}.{target_table};'.format( index_name=spatial_index_name, target_schema=db_entity.schema, target_table=table) cursor.execute(cluster) logger.info('Vacuuming and analyzing %s.%s.', db_entity.schema, table) analyze = 'VACUUM ANALYZE {target_schema}.{target_table};'.format( target_schema=db_entity.schema, target_table=table) cursor.execute(analyze) logger.info("Finished importing data for DbEntity table {0}.{1}".format(db_entity.schema, db_entity.key))
def run_vmt_variable_trip_length_buffers(sql_config_dict): drop_table('{vmt_variables_schema}.{vmt_variables_table}_vmt_variable'.format( vmt_variables_schema=sql_config_dict['vmt_variables_schema'], vmt_variables_table=sql_config_dict['vmt_variables_table'])) pSql = ''' create table {vmt_variables_schema}.{vmt_variables_table}_vmt_variable as select a.id, st_transform(a.wkb_geometry, 3310) as wkb_geometry, cast(a.attractions_hbw * 1609.0 as float) as distance, sum(acres_parcel_res) as acres_parcel_res_vb, sum(acres_parcel_emp) as acres_parcel_emp_vb, sum(acres_parcel_mixed_use) as acres_parcel_mixed_use_vb, sum(pop) as pop_vb, sum(hh) as hh_vb, sum(du) as du_vb, sum(du_mf) as du_mf_vb, sum(emp) as emp_vb, sum(emp_ret) as emp_ret_vb, sum(hh_inc_00_10) as hh_inc_00_10_vb, sum(hh_inc_10_20) as hh_inc_10_20_vb, sum(hh_inc_20_30) as hh_inc_20_30_vb, sum(hh_inc_30_40) as hh_inc_30_40_vb, sum(hh_inc_40_50) as hh_inc_40_50_vb, sum(hh_inc_50_60) as hh_inc_50_60_vb, sum(hh_inc_60_75) as hh_inc_60_75_vb, sum(hh_inc_75_100) as hh_inc_75_100_vb, sum(hh_inc_100p) as hh_inc_100p_vb, sum(pop_employed) as pop_employed_vb, sum(pop_age16_up) as pop_age16_up_vb, sum(pop_age65_up) as pop_age65_up_vb from (select id, wkb_geometry, attractions_hbw from {trip_lengths_schema}.{trip_lengths_table}) a, (select point, acres_parcel_res, acres_parcel_emp, acres_parcel_mixed_use, pop, hh, du, du_mf, emp, emp_ret, hh * hh_inc_00_10_rate as hh_inc_00_10, hh * hh_inc_10_20_rate as hh_inc_10_20, hh * hh_inc_20_30_rate as hh_inc_20_30, hh * hh_inc_30_40_rate as hh_inc_30_40, hh * hh_inc_40_50_rate as hh_inc_40_50, hh * hh_inc_50_60_rate as hh_inc_50_60, hh * hh_inc_60_75_rate as hh_inc_60_75, hh * hh_inc_75_100_rate as hh_inc_75_100, hh * hh_inc_100p_rate as hh_inc_100p, pop * pop_age16_up_rate * pop_employed_rate as pop_employed, pop * pop_age16_up_rate as pop_age16_up, pop * pop_age65_up_rate as pop_age65_up from (select st_centroid(wkb_geometry) as point, pop, hh, du, du_mf, emp, emp_ret, acres_parcel_res, acres_parcel_emp, acres_parcel_mixed_use from {uf_canvas_schema}.{uf_canvas_table}) a, (select wkb_geometry, hh_inc_00_10_rate, hh_inc_10_20_rate, hh_inc_20_30_rate, hh_inc_30_40_rate, hh_inc_40_50_rate, hh_inc_50_60_rate, hh_inc_60_75_rate, hh_inc_75_100_rate, hh_inc_100_125_rate + hh_inc_125_150_rate + hh_inc_150_200_rate + hh_inc_200p_rate as hh_inc_100p_rate, pop_employed_rate, pop_age16_up_rate, pop_age65_up_rate from {census_rates_schema}.{census_rates_table}) c where st_intersects(point, c.wkb_geometry) ) b where st_intersects(point, a.wkb_geometry) group by a.id, a.wkb_geometry, a.attractions_hbw; '''.format(vmt_variables_schema=sql_config_dict['vmt_variables_schema'], vmt_variables_table=sql_config_dict['vmt_variables_table'], uf_canvas_schema=sql_config_dict['uf_canvas_schema'], uf_canvas_table=sql_config_dict['uf_canvas_table'], census_rates_schema=sql_config_dict['census_rates_schema'], census_rates_table=sql_config_dict['census_rates_table'], trip_lengths_schema=sql_config_dict['trip_lengths_schema'], trip_lengths_table=sql_config_dict['trip_lengths_table']) execute_sql(pSql) add_geom_idx(sql_config_dict['vmt_variables_schema'], sql_config_dict['vmt_variables_table'] + '_vmt_variable') add_primary_key(sql_config_dict['vmt_variables_schema'], sql_config_dict['vmt_variables_table'] + '_vmt_variable', 'id') aggregate_within_variable_distance(dict( source_table=sql_config_dict['vmt_variables_schema'] + '.' + sql_config_dict['vmt_variables_table'] + '_vmt_variable', source_table_query='du_vb + emp_vb > 0', target_table_schema=sql_config_dict['vmt_variables_schema'], target_table=sql_config_dict['vmt_variables_table'], target_table_query='id is not null', target_table_pk='id', suffix='vmt_vb', aggregation_type='sum', variable_field_list=['acres_parcel_res_vb', 'acres_parcel_emp_vb', 'acres_parcel_mixed_use_vb', 'du_vb', 'pop_vb', 'emp_vb', 'emp_ret_vb', 'hh_vb', 'du_mf_vb', 'hh_inc_00_10_vb', 'hh_inc_10_20_vb', 'hh_inc_20_30_vb', 'hh_inc_30_40_vb', 'hh_inc_40_50_vb', 'hh_inc_50_60_vb', 'hh_inc_60_75_vb', 'hh_inc_75_100_vb', 'hh_inc_100p_vb', 'pop_employed_vb', 'pop_age16_up_vb', 'pop_age65_up_vb'] )) pSql = '''DROP INDEX {schema}.{schema}_{table}_analysis_geom; Alter Table {schema}.{table} drop column analysis_geom;'''.format(schema=sql_config_dict['vmt_variables_schema'], table=sql_config_dict['vmt_variables_table']) execute_sql(pSql)
def update(self, **kwargs): """ 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))