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