def run_analysis(self, **kwargs):
start_time = time.time()
self.report_progress(0.1, **kwargs)
self.climate_zone_class = self.config_entity.db_entity_feature_class(DbEntityKey.CLIMATE_ZONES)
self.energy_class = self.config_entity.db_entity_feature_class(DbEntityKey.ENERGY)
self.base_class = self.config_entity.db_entity_feature_class(DbEntityKey.BASE_CANVAS)
self.rel_table = parse_schema_and_table(self.energy_class._meta.db_table)[1]
self.rel_column = self.energy_class._meta.parents.values()[0].column
options = dict(
energy_result_table=self.energy_class.db_entity_key,
energy_schema=parse_schema_and_table(self.energy_class._meta.db_table)[0],
base_table=self.base_class.db_entity_key,
base_schema=parse_schema_and_table(self.base_class._meta.db_table)[0],
)
logger.info("Running Energy Updater Tool with options %s" % options)
if isinstance(self.config_entity.subclassed, FutureScenario):
self.end_state_class = self.config_entity.db_entity_feature_class(DbEntityKey.END_STATE)
logger.info("Running Future Calculations")
energy_output_list, options = self.run_future_calculations(options, **kwargs)
else:
logger.info("Running Base Calculations")
energy_output_list, options = self.run_base_calculations(options, **kwargs)
logger.info("Writing to feature table {energy_schema}.{energy_result_table}".format(**options))
self.write_results_to_database(options, energy_output_list)
logger.info("Writing to relative table {energy_schema}.{rel_table}".format(
energy_schema=options['energy_schema'],
rel_table=self.rel_table))
updated = datetime.datetime.now()
truncate_table(options['energy_schema'] + '.' + self.rel_table)
pSql = '''
insert into {energy_schema}.{rel_table} ({rel_column}, updated)
select id, '{updated}' from {energy_schema}.{energy_result_table};'''.format(
energy_schema=options['energy_schema'],
energy_result_table=options['energy_result_table'],
rel_table=self.rel_table,
rel_column=self.rel_column,
updated=updated)
execute_sql(pSql)
from footprint.main.publishing.data_import_publishing import create_and_populate_relations
create_and_populate_relations(self.config_entity, self.config_entity.computed_db_entities(key=DbEntityKey.ENERGY)[0])
self.report_progress(0.2, **kwargs)
logger.info('Finished: ' + str(time.time() - start_time))
def run_analysis(self, **kwargs):
start_time = time.time()
self.report_progress(0.1, **kwargs)
self.climate_zone_class = self.config_entity.db_entity_feature_class(DbEntityKey.CLIMATE_ZONES)
self.energy_class = self.config_entity.db_entity_feature_class(DbEntityKey.ENERGY)
self.base_class = self.config_entity.db_entity_feature_class(DbEntityKey.BASE_CANVAS)
self.rel_table = parse_schema_and_table(self.energy_class._meta.db_table)[1]
self.rel_column = self.energy_class._meta.parents.values()[0].column
options = dict(
energy_result_table=self.energy_class.db_entity_key,
energy_schema=parse_schema_and_table(self.energy_class._meta.db_table)[0],
base_table=self.base_class.db_entity_key,
base_schema=parse_schema_and_table(self.base_class._meta.db_table)[0],
)
logger.info("Running Energy Updater Tool with options %s" % options)
if isinstance(self.config_entity.subclassed, FutureScenario):
self.end_state_class = self.config_entity.db_entity_feature_class(DbEntityKey.END_STATE)
logger.info("Running Future Calculations")
energy_output_list, options = self.run_future_calculations(options, **kwargs)
else:
logger.info("Running Base Calculations")
energy_output_list, options = self.run_base_calculations(options, **kwargs)
logger.info("Writing to feature table {energy_schema}.{energy_result_table}".format(**options))
self.write_results_to_database(options, energy_output_list)
logger.info("Writing to relative table {energy_schema}.{rel_table}".format(
energy_schema=options['energy_schema'],
rel_table=self.rel_table))
updated = datetime.datetime.now()
truncate_table(options['energy_schema'] + '.' + self.rel_table)
pSql = '''
insert into {energy_schema}.{rel_table} ({rel_column}, updated)
select id, '{updated}' from {energy_schema}.{energy_result_table};'''.format(
energy_schema=options['energy_schema'],
energy_result_table=options['energy_result_table'],
rel_table=self.rel_table,
rel_column=self.rel_column,
updated=updated)
execute_sql(pSql)
from footprint.main.publishing.data_import_publishing import create_and_populate_relations
create_and_populate_relations(self.config_entity, self.config_entity.computed_db_entities(key=DbEntityKey.ENERGY)[0])
self.report_progress(0.2, **kwargs)
logger.info('Finished: ' + str(time.time() - start_time))
def drop_layer_selection_table(layer_selection_class):
"""
Drop the dynamic LayerSelection class table. This should be called whenever the owning layer is deleted
:param layer_selection_class:
:return:
"""
if InformationSchema.objects.table_exists(*parse_schema_and_table(layer_selection_class._meta.db_table)):
drop_tables_for_dynamic_classes(layer_selection_class)
def create_tables_for_dynamic_classes(*model_classes):
"""
Creates the table for the dynamic model class if needed
:param model_classes: 0 or more model classes for which to create a table
:return:
"""
for model_class in model_classes:
if dynamic_model_table_exists(model_class):
continue
#info = "Model class table {model_class} doesn't exist -- creating it \n"
#logger.debug(info.format(model_class=model_class._meta.db_table))
fields = [(f.name, f) for f in model_class._meta.local_fields]
table_name = model_class._meta.db_table
db.create_table(table_name, fields)
# some fields (eg GeoDjango) require additional SQL to be executed
# Because of the poor Django/GeoDjango support for schemas, we have to manipulate the GeoDjango sql here so that the table is resolved to the correct schema, sigh
if len(table_name.split('.')) == 2:
schema, table = parse_schema_and_table(table_name)
for i, sql in enumerate(db.deferred_sql):
# Replace the POSTGIS single argument with two arguments
# TODO this stupidly assumes that all deferred sql is POSTGIS
# Substitution for '"schema"."table"' to 'schema','table'. This is for AddGeometryColumn
db.deferred_sql[i] = re.sub(
"'{0}'".format(table_name),
"'{0}','{1}'".format(schema, table), sql)
# Substitution for "schema"."table" to schema.table. This is for CREATE INDEX
db.deferred_sql[i] = re.sub("{0}".format(table_name),
"{0}.{1}".format(schema, table),
db.deferred_sql[i])
# Substitution for "schema"."tableHEX". Some indexes add random hex to the table name inside the double quotes. They may also truncate the table name, so just capture everything between "s
# Also truncate to 64 characters the schema name minus the length of the table name, favoring the end of the schema which is most unique
db.deferred_sql[i] = re.sub(r'"(".*)"\."(.*") ON',
r'\1.\2 ON'.format(schema, table),
db.deferred_sql[i])
# Last ditch effort to remove extra " when we can't match generated index
db.deferred_sql[i] = re.sub(r'""', r'"', db.deferred_sql[i])
if string.find(db.deferred_sql[i], 'CREATE INDEX') == 0:
subs = db.deferred_sql[i]
# Truncate the index name. This could be done more elegantly
# db.deferred_sql[i] = subs[0:14] + subs[14:string.index(subs, '" ON')][-63:] + subs[string.index(subs, '" ON'):]
db.deferred_sql[i] = subs[0:14] + table + '_' + re.findall(
r'"([^"]*)"',
subs)[1] + subs[string.index(subs, '" ON'):]
try:
db.execute_deferred_sql()
except Exception, e:
raise Exception(
"The table {table_name} was not created. Original exception: {message}. Deferred sql calls: {sql}"
.format(table_name=model_class._meta.db_table,
message=e.message,
sql='\n'.join(db.deferred_sql)))
# TODO I don't know if or when this is needed.
if transaction.is_managed():
transaction.commit()
def drop_tables_for_dynamic_classes(*model_classes):
for model_class in model_classes:
full_table_name = model_class._meta.db_table
schema, table = parse_schema_and_table(full_table_name)
if InformationSchema.objects.table_exists(schema, table):
try:
logger.info("Dropping table %s" % full_table_name)
db.delete_table(full_table_name)
except DatabaseError, e:
raise Exception('full_table_name: {full_table_name}. Original exception: {e}'.format(full_table_name=full_table_name, e=e))
def describe_table_columns(self, cursor, full_table_name):
schema, table = parse_schema_and_table(full_table_name)
# conn = psycopg2.connect(**pg_connection_parameters(settings.DATABASES['default']))
# conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
# cursor = conn.cursor()
cursor.execute("""
SELECT column_name, is_nullable
FROM information_schema.columns
WHERE table_name = %s and table_schema = %s""", [table, schema])
null_map = dict(cursor.fetchall())
cursor.execute('SELECT * FROM "{schema}"."{table}" LIMIT 1'.format(schema=schema, table=table))
return cursor.description
def drop_tables_for_dynamic_classes(*model_classes):
for model_class in model_classes:
full_table_name = model_class._meta.db_table
schema, table = parse_schema_and_table(full_table_name)
if InformationSchema.objects.table_exists(schema, table):
try:
logger.info("Dropping table %s" % full_table_name)
db.delete_table(full_table_name)
except DatabaseError, e:
raise Exception(
'full_table_name: {full_table_name}. Original exception: {e}'
.format(full_table_name=full_table_name, e=e))
def run_water_calculations(self, **kwargs):
self.format_policy_inputs()
self.water_class = self.config_entity.db_entity_feature_class(
DbEntityKey.WATER)
self.base_class = self.config_entity.db_entity_feature_class(
DbEntityKey.BASE_CANVAS)
self.climate_zone_class = self.config_entity.db_entity_feature_class(
DbEntityKey.CLIMATE_ZONES)
self.rel_table = parse_schema_and_table(
self.water_class._meta.db_table)[1]
self.rel_column = self.water_class._meta.parents.values()[0].column
if isinstance(self.config_entity.subclassed, FutureScenario):
self.report_progress(0.2, **kwargs)
self.end_state_class = self.config_entity.db_entity_feature_class(
DbEntityKey.END_STATE)
water_output_list, options = self.run_future_water_calculations(
**kwargs)
else:
self.report_progress(0.2, **kwargs)
water_output_list, options = self.run_base_water_calculations()
self.report_progress(0.7, **kwargs)
self.write_water_results_to_database(options, water_output_list)
updated = datetime.datetime.now()
truncate_table(options['water_schema'] + '.' + self.rel_table)
pSql = '''
insert into {water_schema}.{rel_table} ({rel_column}, updated) select id, '{updated}' from {water_schema}.{water_result_table};'''.format(
water_schema=options['water_schema'],
water_result_table=options['water_result_table'],
rel_table=self.rel_table,
rel_column=self.rel_column,
updated=updated)
execute_sql(pSql)
from footprint.main.publishing.data_import_publishing import create_and_populate_relations
create_and_populate_relations(
self.config_entity,
self.config_entity.computed_db_entities(key=DbEntityKey.WATER)[0])
self.report_progress(0.10000001, **kwargs)
def create_tables_for_dynamic_classes(*model_classes):
"""
Creates the table for the dynamic model class if needed
:param model_classes: 0 or more model classes for which to create a table
:return:
"""
for model_class in model_classes:
if dynamic_model_table_exists(model_class):
continue
#info = "Model class table {model_class} doesn't exist -- creating it \n"
#logger.debug(info.format(model_class=model_class._meta.db_table))
fields = [(f.name, f) for f in model_class._meta.local_fields]
table_name = model_class._meta.db_table
db.create_table(table_name, fields)
# some fields (eg GeoDjango) require additional SQL to be executed
# Because of the poor Django/GeoDjango support for schemas, we have to manipulate the GeoDjango sql here so that the table is resolved to the correct schema, sigh
if len(table_name.split('.'))==2:
schema, table = parse_schema_and_table(table_name)
for i, sql in enumerate(db.deferred_sql):
# Replace the POSTGIS single argument with two arguments
# TODO this stupidly assumes that all deferred sql is POSTGIS
# Substitution for '"schema"."table"' to 'schema','table'. This is for AddGeometryColumn
db.deferred_sql[i] = re.sub("'{0}'".format(table_name), "'{0}','{1}'".format(schema, table), sql)
# Substitution for "schema"."table" to schema.table. This is for CREATE INDEX
db.deferred_sql[i] = re.sub("{0}".format(table_name), "{0}.{1}".format(schema, table), db.deferred_sql[i])
# Substitution for "schema"."tableHEX". Some indexes add random hex to the table name inside the double quotes. They may also truncate the table name, so just capture everything between "s
# Also truncate to 64 characters the schema name minus the length of the table name, favoring the end of the schema which is most unique
db.deferred_sql[i] = re.sub(r'"(".*)"\."(.*") ON', r'\1.\2 ON'.format(schema, table), db.deferred_sql[i])
# Last ditch effort to remove extra " when we can't match generated index
db.deferred_sql[i] = re.sub(r'""', r'"', db.deferred_sql[i])
if string.find(db.deferred_sql[i], 'CREATE INDEX') == 0:
subs = db.deferred_sql[i]
# Truncate the index name. This could be done more elegantly
db.deferred_sql[i] = subs[0:14] + subs[14:string.index(subs, '" ON')][-63:] + subs[string.index(subs, '" ON'):]
try:
db.execute_deferred_sql()
except Exception, e:
raise Exception("The table {table_name} was not created. Original exception: {message}. Deferred sql calls: {sql}".format(table_name=model_class._meta.db_table, message=e.message, sql='\n'.join(db.deferred_sql)))
# TODO I don't know if or when this is needed.
if transaction.is_managed():
transaction.commit()
def get_table_description(self, cursor, full_table_name):
"""
Override the parent method to take schemas into account, sigh
:param cursor:
:param full_table_name:
:return:
"""
schema, table = parse_schema_and_table(full_table_name)
# conn = psycopg2.connect(**pg_connection_parameters(settings.DATABASES['default']))
# conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
# cursor = conn.cursor()
cursor.execute("""
SELECT column_name, is_nullable
FROM information_schema.columns
WHERE table_name = %s and table_schema = %s""", [table, schema])
null_map = dict(cursor.fetchall())
cursor.execute('SELECT * FROM "{schema}"."{table}" LIMIT 1'.format(schema=schema, table=table))
return [tuple([item for item in line[:6]] + [null_map[line[0]]==u'YES'])
for line in cursor.description]
def run_water_calculations(self, **kwargs):
self.format_policy_inputs()
self.water_class = self.config_entity.db_entity_feature_class(DbEntityKey.WATER)
self.base_class = self.config_entity.db_entity_feature_class(DbEntityKey.BASE_CANVAS)
self.climate_zone_class = self.config_entity.db_entity_feature_class(DbEntityKey.CLIMATE_ZONES)
self.rel_table = parse_schema_and_table(self.water_class._meta.db_table)[1]
self.rel_column = self.water_class._meta.parents.values()[0].column
if isinstance(self.config_entity.subclassed, FutureScenario):
self.report_progress(0.2, **kwargs)
self.end_state_class = self.config_entity.db_entity_feature_class(DbEntityKey.END_STATE)
water_output_list, options = self.run_future_water_calculations(**kwargs)
else:
self.report_progress(0.2, **kwargs)
water_output_list, options = self.run_base_water_calculations()
self.report_progress(0.7, **kwargs)
self.write_water_results_to_database(options, water_output_list)
updated = datetime.datetime.now()
truncate_table(options['water_schema'] + '.' + self.rel_table)
pSql = '''
insert into {water_schema}.{rel_table} ({rel_column}, updated) select id, '{updated}' from {water_schema}.{water_result_table};'''.format(
water_schema=options['water_schema'],
water_result_table=options['water_result_table'],
rel_table=self.rel_table,
rel_column=self.rel_column,
updated=updated)
execute_sql(pSql)
from footprint.main.publishing.data_import_publishing import create_and_populate_relations
create_and_populate_relations(self.config_entity, self.config_entity.computed_db_entities(key=DbEntityKey.WATER)[0])
self.report_progress(0.10000001, **kwargs)
def get_indexes(self, cursor, table_name):
"""
OVERRIDDEN to work with schemas, sigh
Returns a dictionary of fieldname -> infodict for the given table,
where each infodict is in the format:
{'primary_key': boolean representing whether it's the primary key,
'unique': boolean representing whether it's a unique index}
"""
schema, table = parse_schema_and_table(table_name)
# This query retrieves each index on the given table, including the
# first associated field name
cursor.execute(
"""
SELECT attr.attname, idx.indkey, idx.indisunique, idx.indisprimary
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
pg_catalog.pg_index idx, pg_catalog.pg_attribute attr,
information_schema.columns isc
WHERE c.oid = idx.indrelid
AND idx.indexrelid = c2.oid
AND attr.attrelid = c.oid
AND attr.attnum = idx.indkey[0]
AND c.relname = %s
AND c.relname = isc.table_name
AND isc.table_schema = %s
AND isc.column_name = attr.attname
""", [table, schema])
indexes = {}
for row in cursor.fetchall():
# row[1] (idx.indkey) is stored in the DB as an array. It comes out as
# a string of space-separated integers. This designates the field
# indexes (1-based) of the fields that have indexes on the table.
# Here, we skip any indexes across multiple fields.
if ' ' in row[1]:
continue
indexes[row[0]] = {'primary_key': row[3], 'unique': row[2]}
return indexes
def get_indexes(self, cursor, table_name):
"""
OVERRIDDEN to work with schemas, sigh
Returns a dictionary of fieldname -> infodict for the given table,
where each infodict is in the format:
{'primary_key': boolean representing whether it's the primary key,
'unique': boolean representing whether it's a unique index}
"""
schema, table = parse_schema_and_table(table_name)
# This query retrieves each index on the given table, including the
# first associated field name
cursor.execute("""
SELECT attr.attname, idx.indkey, idx.indisunique, idx.indisprimary
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
pg_catalog.pg_index idx, pg_catalog.pg_attribute attr,
information_schema.columns isc
WHERE c.oid = idx.indrelid
AND idx.indexrelid = c2.oid
AND attr.attrelid = c.oid
AND attr.attnum = idx.indkey[0]
AND c.relname = %s
AND c.relname = isc.table_name
AND isc.table_schema = %s
AND isc.column_name = attr.attname
""", [table, schema])
indexes = {}
for row in cursor.fetchall():
# row[1] (idx.indkey) is stored in the DB as an array. It comes out as
# a string of space-separated integers. This designates the field
# indexes (1-based) of the fields that have indexes on the table.
# Here, we skip any indexes across multiple fields.
if ' ' in row[1]:
continue
indexes[row[0]] = {'primary_key': row[3], 'unique': row[2]}
return indexes
def get_fields(cls, table_name):
"""
Modifies the original handle_inspection to instead get all the fields of the table described by the db_entity
:param db_entity:
:return: A dict keyed by field named and valued by models.Field or variant instance
"""
connection = connections['default']
table2model = lambda table_name: table_name.title().replace('_', '').replace(' ', '').replace('-', '')
cursor = connection.cursor()
try:
relations = connection.introspection.get_relations(cursor, table_name)
except NotImplementedError:
relations = {}
try:
indexes = connection.introspection.get_indexes(cursor, table_name)
except NotImplementedError:
indexes = {}
schema, table = parse_schema_and_table(table_name)
# Fill this dict with field definitions
fields = {}
for i, row in enumerate(cls.get_table_description(cursor, table, schema, connection)):
column_name = row[0]
att_name = column_name.lower()
comment_notes = [] # Holds Field notes, to be displayed in a Python comment.
extra_params = {} # Holds Field parameters such as 'db_column'.
# If the column name can't be used verbatim as a Python
# attribute, set the "db_column" for this Field.
if ' ' in att_name or '-' in att_name or keyword.iskeyword(att_name) or column_name != att_name:
extra_params['db_column'] = column_name
# Add primary_key and unique, if necessary.
if column_name in indexes:
if indexes[column_name]['primary_key']:
extra_params['primary_key'] = True
elif indexes[column_name]['unique']:
extra_params['unique'] = True
# Modify the field name to make it Python-compatible.
if ' ' in att_name:
att_name = att_name.replace(' ', '_')
comment_notes.append('Field renamed to remove spaces.')
if '-' in att_name:
att_name = att_name.replace('-', '_')
comment_notes.append('Field renamed to remove dashes.')
if column_name != att_name:
comment_notes.append('Field name made lowercase.')
if i in relations:
rel_to = relations[i][1] == table_name and "'cls'" or table2model(relations[i][1])
field_type = 'ForeignKey(%s' % rel_to
if att_name.endswith('_id'):
att_name = att_name[:-3]
else:
extra_params['db_column'] = column_name
else:
# Calling `get_field_type` to get the field type string and any
# additional paramters and notes.
field_type, field_params, field_notes = cls.get_field_type(connection, table_name, row)
extra_params.update(field_params)
comment_notes.extend(field_notes)
field_type += '('
if keyword.iskeyword(att_name):
att_name += '_field'
comment_notes.append('Field renamed because it was a Python reserved word.')
if att_name[0].isdigit():
att_name = 'number_%s' % att_name
extra_params['db_column'] = unicode(column_name)
comment_notes.append("Field renamed because it wasn't a "
"valid Python identifier.")
# Don't output 'id = meta.AutoField(primary_key=True)', because
# that's assumed if it doesn't exist.
if att_name == 'id' and field_type == 'AutoField(' and extra_params == {'primary_key': True}:
continue
# Add 'null' and 'blank', if the 'null_ok' flag was present in the
# table description.
if row[6]: # If it's NULL...
extra_params['blank'] = True
# Don't know why these are here, commenting out
#if not field_type in ('TextField(', 'CharField('):
extra_params['null'] = True
field_desc = 'models.%s' % field_type
if extra_params:
if not field_desc.endswith('('):
field_desc += ', '
field_desc += ', '.join(['%s=%r' % (k, v) for k, v in extra_params.items()])
field_desc += ')'
if comment_notes:
field_desc += ' # ' + ' '.join(comment_notes)
# Set the dict key/value to the field name and the evaluated field description
fields[att_name] = eval(field_desc)
fields[att_name].name = att_name
return fields
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 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 run_future_water_calculations(self, **kwargs):
self.base_year = self.config_entity.scenario.project.base_year
self.future_year = self.config_entity.scenario.year
self.increment = self.future_year - self.base_year
self.annualize_efficiencies()
features = self.end_state_class.objects.filter(
Q(du__gt=0) | Q(emp__gt=0))
annotated_features = annotated_related_feature_class_pk_via_geographies(
features, self.config_entity,
[DbEntityKey.BASE_CANVAS, DbEntityKey.CLIMATE_ZONES])
water_output_list = []
options = dict(
water_result_table=self.water_class.db_entity_key,
water_schema=parse_schema_and_table(
self.water_class._meta.db_table)[0],
base_table=self.base_class.db_entity_key,
base_schema=parse_schema_and_table(
self.base_class._meta.db_table)[0],
)
approx_fifth = int(annotated_features.count() / 14 -
1) if annotated_features.count() > 30 else 1
i = 1
for feature in annotated_features.iterator():
self.feature = feature
self.result_dict = defaultdict(lambda: float(0))
if i % approx_fifth == 0:
self.report_progress(0.05, **kwargs)
base_feature = self.base_class.objects.get(id=feature.base_canvas)
if feature.climate_zones:
climate_zone_feature = self.climate_zone_class.objects.get(
id=feature.climate_zones)
else:
logger.warn(
"No Climate Zone intersection for feature id {0} or check geography relation table"
.format(feature.id))
continue
self.feature_dict = dict(
id=feature.id,
pop=float(feature.pop),
hh=float(feature.hh),
emp=float(feature.emp),
evapotranspiration_zone=climate_zone_feature.
evapotranspiration_zone.zone,
annual_evapotranspiration=float(
climate_zone_feature.evapotranspiration_zone.
annual_evapotranspiration),
)
for use in 'residential', 'commercial':
key = "{use}_irrigated_sqft".format(use=use)
self.feature_dict.update({
key + "_redev":
self.redev_units(key, feature, base_feature),
key + "_new":
self.new_units(key, feature, base_feature),
key + "_base":
float(getattr(base_feature, key))
})
future_residential_factor = feature.hh / feature.du * feature.pop / feature.hh if feature.hh > 0 else 0
base_residential_factor = base_feature.hh / base_feature.du * base_feature.pop / base_feature.hh if base_feature.hh else 0
for key in self.RESIDENTIAL_TYPES:
self.feature_dict.update({
key + "_redev":
self.redev_units(key, feature, base_feature) *
float(base_residential_factor),
key + "_new":
self.new_units(key, feature, base_feature) *
float(future_residential_factor),
key + "_base":
float(getattr(base_feature, key)) *
float(base_residential_factor)
})
for key in self.COMMERCIAL_TYPES:
self.feature_dict.update({
key + "_redev":
self.redev_units("emp_" + key, feature, base_feature) *
float(base_residential_factor),
key + "_new":
self.new_units("emp_" + key, feature, base_feature) *
float(future_residential_factor),
key + "_base":
float(getattr(base_feature, "emp_" + key)) *
float(base_residential_factor)
})
self.calculate_future_water()
self.calculate_visualized_field()
output_row = map(lambda key: self.result_dict.get(key),
self.output_fields)
water_output_list.append(output_row)
i += 1
return water_output_list, options
def update(self, **kwargs):
"""
Executes the VMT module functions
"""
public_health_variables_class = self.config_entity.db_entity_feature_class(
DbEntityKey.PH_VARIABLES)
uf_150mgrid_class = self.config_entity.db_entity_feature_class(
DbEntityKey.GRID_150M)
census_rate_class = self.config_entity.db_entity_feature_class(
DbEntityKey.CENSUS_RATES)
grid_outcome_class = self.config_entity.db_entity_feature_class(
DbEntityKey.PH_GRID_OUTCOMES)
block_group_outcome_class = self.config_entity.db_entity_feature_class(
DbEntityKey.PH_BLOCK_GROUP_OUTCOMES)
outcome_summary_class = self.config_entity.db_entity_feature_class(
DbEntityKey.PH_OUTCOMES_SUMMARY)
if isinstance(self.config_entity.subclassed, FutureScenario):
scenario_class = self.config_entity.db_entity_feature_class(
DbEntityKey.END_STATE)
trip_length_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)
else:
scenario_class = self.config_entity.db_entity_feature_class(
DbEntityKey.BASE_CANVAS)
trip_length_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)
sql_config_dict = dict(
uf_canvas_table=scenario_class.db_entity_key,
uf_canvas_schema=parse_schema_and_table(
scenario_class._meta.db_table)[0],
public_health_variables_table=public_health_variables_class.
db_entity_key,
public_health_variables_schema=parse_schema_and_table(
public_health_variables_class._meta.db_table)[0],
source_grid_table=uf_150mgrid_class.db_entity_key,
source_grid_schema=parse_schema_and_table(
uf_150mgrid_class._meta.db_table)[0],
transit_stop_table=transit_stop_class.db_entity_key,
transit_stop_schema=parse_schema_and_table(
transit_stop_class._meta.db_table)[0],
census_rate_table=census_rate_class.db_entity_key,
census_rate_schema=parse_schema_and_table(
census_rate_class._meta.db_table)[0],
trip_lengths_table=trip_length_class.db_entity_key,
trip_lengths_schema=parse_schema_and_table(
trip_length_class._meta.db_table)[0],
grid_outcome_schema=parse_schema_and_table(
grid_outcome_class._meta.db_table)[0],
grid_outcome_table=grid_outcome_class.db_entity_key,
block_group_outcome_schema=parse_schema_and_table(
block_group_outcome_class._meta.db_table)[0],
block_group_outcome_table=block_group_outcome_class.db_entity_key,
outcome_summary_schema=parse_schema_and_table(
outcome_summary_class._meta.db_table)[0],
outcome_summary_table=outcome_summary_class.db_entity_key)
self.run_public_health_preprocesses(sql_config_dict, **kwargs)
read_UD4H_model_doc()
self.read_public_health_models()
all_features = public_health_variables_class.objects.filter(
Q(pop__gt=0))
output = []
for feature in all_features:
feature_dict = dict(
id=feature.id,
pop=feature.pop,
pop_adult=feature.pop_adult,
pop_adult_high=feature.pop_adult_high,
pop_adult_med=feature.pop_adult_med,
pop_adult_low=feature.pop_adult_low,
pop_senior=feature.pop_senior,
pop_teen=feature.pop_teen,
pop_children=feature.pop_children,
hh=feature.hh,
gender2=feature.gender2, # = percent_female
age_children=feature.age_children, # = average_age
age_adult=feature.age_adult, # = average_age
age_teens=feature.age_teens, # = average_age
age_seniors=feature.age_seniors, # = average_age
racehisp1=feature.racehisp1, # = pop_white
racehisp2=feature.racehisp2, # = pop_black
racehisp4=feature.racehisp4, # = pop_asian
racehisp97=feature.
racehisp97, # = pop_american_indian + pop_hawaiian_islander + pop_other_ethnicity
emply2=feature.emply2, # = pop_age16_up - pop_employed
educa2=feature.educa2, # = % pop_hs_diploma
educa3=feature.educa3, # = % pop_some_college
educa4=feature.educa4, # = % pop_college_degree
educa5=feature.educa5, # = % pop_graduate_degree
own2=feature.own2, # = percent renters
hhsize=feature.hhsize, # = hh_avg_size
hhveh=feature.hhveh, # = hh_avg_vehicles
incom2=feature.
incom2, # = income_10k_35k = hh_inc_10_20 + hh_inc_20_30 + hh_inc_30_40/2
incom3=feature.
incom3, # = income_35k_50k = hh_inc_30_40/2 + hh_inc_40_50
incom4=feature.
incom4, # = income_50k_75k = hh_inc_50_60 + hh_inc_60_75
incom5=feature.incom5, # = income_75k_100k = hh_inc_75_100
incom6=feature.
incom6, # = income_100k_150k = hh_inc_100_125 + hh_inc_125_150
incom7=feature.incom7, # = income_150k_plus = hh_inc_150
child_any1=feature.child_any1, # = % hh_with_children
disabled1_children=feature.
disabled1_children, # = % pop with disability (excluding trouble walking)
disabled1_teens=feature.
disabled1_teens, # = % pop with disability (excluding trouble walking)
disabled1_adult=feature.
disabled1_adult, # = % pop with disability (excluding trouble walking)
disabled1_seniors=feature.
disabled1_seniors, # = % pop with disability (excluding trouble walking)
disabled2_children=feature.
disabled2_children, # = % pop with trouble walking
disabled2_teens=feature.
disabled2_teens, # = % pop with trouble walking
disabled2_adult=feature.
disabled2_adult, # = % pop with trouble walking
disabled2_seniors=feature.
disabled2_seniors, # = % pop with trouble walking
emply_hh=feature.emply_hh, # = employed_pop / hh
educa_hh2=feature.educa_hh2, # = % hh with high school diploma
educa_hh3=feature.educa_hh3, # = % hh with some college
educa_hh4=feature.educa_hh4, # = % hh with college degree
educa_hh5=feature.educa_hh5, # = % hh with graduate degree)
# BE Vars
bldg_sqft_res=feature.bldg_sqft_res,
bldg_sqft_ret1=feature.bldg_sqft_ret1,
bldg_sqft_off=feature.bldg_sqft_off,
b1=feature.b1,
b2=feature.b2,
b3=feature.b3,
b4=feature.b4,
b5=feature.b5,
a=feature.a,
du_1km_tr=feature.du_1km_tr,
resmix_dens=feature.resmix_dens,
bldg_sqft_ret=feature.bldg_sqft_ret,
far_nonres=feature.far_nonres,
mix5=feature.mix5,
rail_any=feature.rail_any,
transit_distance=feature.transit_distance,
transit_count=feature.transit_count,
school_distance=feature.school_distance,
retail_distance=feature.retail_distance,
restaurant_distance=feature.restaurant_distance,
intersection_density_sqmi=feature.intersection_density_sqmi,
local_street=feature.local_street,
major_street=feature.major_street,
freeway_arterial_any=feature.freeway_arterial_any,
park_open_space_distance=feature.park_open_space_distance,
acres_parcel_park_open_space=feature.
acres_parcel_park_open_space,
du_variable=feature.du_variable,
emp_variable=feature.emp_variable,
res_index=feature.res_index,
com_index=feature.com_index,
park_access=feature.park_access,
regional_access=feature.regional_access,
network_index=feature.network_index,
transit_access=feature.transit_access,
majrd_index=feature.major_road_index,
walk_index=feature.walk_index,
walk_index_chts_senior_walk_any=feature.
walk_index_chts_senior_walk_any,
walk_index_chts_senior_auto_min=feature.
walk_index_chts_senior_auto_min,
walk_index_chts_teens_walk_any=feature.
walk_index_chts_teens_walk_any,
walk_index_chts_child_walk_any=feature.
walk_index_chts_child_walk_any,
walk_index_chts_adult_bike_any=feature.
walk_index_chts_adult_bike_any,
walk_index_chts_adult_walk_min=feature.
walk_index_chts_adult_walk_min,
walk_index_chts_senior_walk_min=feature.
walk_index_chts_senior_walk_min,
walk_index_chts_teens_walk_min=feature.
walk_index_chts_teens_walk_min,
walk_index_chts_adult_auto_min=feature.
walk_index_chts_adult_auto_min,
walk_index_chis_adult_modPA_any=feature.
walk_index_chis_adult_modpa_any,
walk_index_chis_adult_overweight=feature.
walk_index_chis_adult_overweight,
walk_index_chis_senior_overweight=feature.
walk_index_chis_senior_overweight,
walk_index_chis_adult_obese=feature.
walk_index_chis_adult_obese,
walk_index_chis_senior_gh=feature.walk_index_chis_senior_gh,
walk_index_chis_senior_walk_le_min=feature.
walk_index_chis_senior_walk_le_min,
walk_index_chis_adult_modPA_min=feature.
walk_index_chis_adult_modpa_min,
walk_index_chis_adult_bmi=feature.walk_index_chis_adult_bmi,
walk_index_chis_child_PA60=feature.walk_index_chis_child_pa60,
walk_index_chis_child_walk_school=feature.
walk_index_chis_child_walk_school)
model_result = self.apply_public_health_models_to_feature(
feature_dict)
result_minutes = self.populate_output_minutes(model_result)
output_dict = map(lambda key: result_minutes[key.lower()],
self.outcome_fields)
output.append(output_dict)
self.public_health_progress(0.1, **kwargs)
self.write_results_to_database(sql_config_dict, output)
self.public_health_progress(0.1, **kwargs)
self.aggregate_results_to_block_group(sql_config_dict)
self.public_health_progress(0.1, **kwargs)
self.aggregate_results_to_outcomes_summary_table(sql_config_dict)
self.public_health_progress(0.15, **kwargs)
def dynamic_model_table_exists(model_class):
#logger.debug('Checking model class table {model_class} exists'.format(model_class=model_class._meta.db_table))
return InformationSchema.objects.table_exists(
*parse_schema_and_table(model_class._meta.db_table))
def run_base_water_calculations(self):
features = self.base_class.objects.filter(Q(du__gt=0) | Q(emp__gt=0))
annotated_features = annotated_related_feature_class_pk_via_geographies(features, self.config_entity, [
DbEntityKey.CLIMATE_ZONES])
water_output_list = []
options = dict(
water_result_table=self.water_class.db_entity_key,
water_schema=parse_schema_and_table(self.water_class._meta.db_table)[0],
base_table=self.base_class.db_entity_key,
base_schema=parse_schema_and_table(self.base_class._meta.db_table)[0],
)
for feature in annotated_features.iterator():
self.result_dict = defaultdict(lambda: float(0))
self.feature = feature
if feature.climate_zones:
climate_zone_feature = self.climate_zone_class.objects.get(id=feature.climate_zones)
else:
logger.warn("No Climate Zone intersection for feature id {0} or check geography relation table".format(feature.id))
continue
hh_factor = (feature.pop / feature.hh) * (feature.hh / feature.du) if (feature.du > 0 and feature.hh > 0) else 0
self.feature_dict = dict(
id=feature.id,
pop=float(feature.pop),
hh=float(feature.hh),
emp=float(feature.emp),
evapotranspiration_zone=climate_zone_feature.evapotranspiration_zone.zone,
annual_evapotranspiration=float(climate_zone_feature.evapotranspiration_zone.annual_evapotranspiration),
residential_irrigated_sqft=float(feature.residential_irrigated_sqft),
commercial_irrigated_sqft=float(feature.commercial_irrigated_sqft),
du_detsf_ll=float(feature.du_detsf_ll * hh_factor),
du_detsf_sl=float(feature.du_detsf_sl * hh_factor),
du_attsf=float(feature.du_attsf * hh_factor),
du_mf=float(feature.du_mf * hh_factor),
retail_services=float(feature.emp_retail_services),
restaurant=float(feature.emp_restaurant),
accommodation=float(feature.emp_accommodation),
arts_entertainment=float(feature.emp_arts_entertainment),
other_services=float(feature.emp_other_services),
office_services=float(feature.emp_office_services),
public_admin=float(feature.emp_public_admin),
education=float(feature.emp_education),
medical_services=float(feature.emp_medical_services),
wholesale=float(feature.emp_wholesale),
transport_warehousing=float(feature.emp_transport_warehousing),
manufacturing=float(feature.emp_manufacturing),
construction=float(feature.emp_construction),
utilities=float(feature.emp_utilities),
agriculture=float(feature.emp_agriculture),
extraction=float(feature.emp_extraction),
military=float(feature.emp_military)
)
self.calculate_base_water()
self.calculate_visualized_field()
output_row = map(lambda key: self.result_dict[key], self.output_fields)
water_output_list.append(output_row)
return water_output_list, options
def run_future_water_calculations(self, **kwargs):
self.base_year = self.config_entity.scenario.project.base_year
self.future_year = self.config_entity.scenario.year
self.increment = self.future_year - self.base_year
self.annualize_efficiencies()
features = self.end_state_class.objects.filter(Q(du__gt=0) | Q(emp__gt=0))
annotated_features = annotated_related_feature_class_pk_via_geographies(features, self.config_entity, [
DbEntityKey.BASE_CANVAS, DbEntityKey.CLIMATE_ZONES])
water_output_list = []
options = dict(
water_result_table=self.water_class.db_entity_key,
water_schema=parse_schema_and_table(self.water_class._meta.db_table)[0],
base_table=self.base_class.db_entity_key,
base_schema=parse_schema_and_table(self.base_class._meta.db_table)[0],
)
approx_fifth = int(annotated_features.count() / 14 - 1) if annotated_features.count() > 30 else 1
i = 1
for feature in annotated_features.iterator():
self.feature = feature
self.result_dict = defaultdict(lambda: float(0))
if i % approx_fifth == 0:
self.report_progress(0.05, **kwargs)
base_feature = self.base_class.objects.get(id=feature.base_canvas)
if feature.climate_zones:
climate_zone_feature = self.climate_zone_class.objects.get(id=feature.climate_zones)
else:
logger.warn("No Climate Zone intersection for feature id {0} or check geography relation table".format(feature.id))
continue
self.feature_dict = dict(
id=feature.id,
pop=float(feature.pop),
hh=float(feature.hh),
emp=float(feature.emp),
evapotranspiration_zone=climate_zone_feature.evapotranspiration_zone.zone,
annual_evapotranspiration=float(climate_zone_feature.evapotranspiration_zone.annual_evapotranspiration),
)
for use in 'residential', 'commercial':
key = "{use}_irrigated_sqft".format(use=use)
self.feature_dict.update({
key + "_redev": self.redev_units(key, feature, base_feature),
key + "_new": self.new_units(key, feature, base_feature),
key + "_base": float(getattr(base_feature, key))
})
future_residential_factor = feature.hh / feature.du * feature.pop / feature.hh if feature.hh > 0 else 0
base_residential_factor = base_feature.hh / base_feature.du * base_feature.pop / base_feature.hh if base_feature.hh else 0
for key in self.RESIDENTIAL_TYPES:
self.feature_dict.update({
key + "_redev": self.redev_units(key, feature, base_feature) * float(base_residential_factor),
key + "_new": self.new_units(key, feature, base_feature) * float(future_residential_factor),
key + "_base": float(getattr(base_feature, key)) * float(base_residential_factor)
})
for key in self.COMMERCIAL_TYPES:
self.feature_dict.update({
key + "_redev": self.redev_units("emp_" + key, feature, base_feature) * float(base_residential_factor),
key + "_new": self.new_units("emp_" + key, feature, base_feature) * float(future_residential_factor),
key + "_base": float(getattr(base_feature, "emp_" +key)) * float(base_residential_factor)
})
self.calculate_future_water()
self.calculate_visualized_field()
output_row = map(lambda key: self.result_dict.get(key), self.output_fields)
water_output_list.append(output_row)
i += 1
return water_output_list, options
def dynamic_model_table_exists(model_class):
#logger.debug('Checking model class table {model_class} exists'.format(model_class=model_class._meta.db_table))
return InformationSchema.objects.table_exists(*parse_schema_and_table(model_class._meta.db_table))
def update(self, **kwargs):
"""
This function handles the update or creation on the environmental constraints geography producing the area
for each layer with the environmental constraint behavior. This function will both add and remove
constraints and produce the final constraints layer in the primary geography of the active scenario
"""
# TODO : remove hard-coded 3310 (only works in CA), need to set an "analysis projection" in the Region
start_time = time.time()
current_db_entities = \
set(self.config_entity.db_entities_having_behavior_key(BehaviorKey.Fab.ricate('environmental_constraint')))
base_feature_class = self.config_entity.db_entity_feature_class(
DbEntityKey.BASE_CANVAS)
options = dict(
project_schema=parse_schema_and_table(base_feature_class._meta.db_table)[0],
base_table=base_feature_class.db_entity_key
)
logger.info('Inserting raw geographies into the environmental constraint geographies table for DbEntities: %s' % \
', '.join(map(lambda db_entity: db_entity.name, current_db_entities)))
drop_table('{project_schema}.environmental_constraint_geographies_table'.format(
project_schema=options['project_schema'])
)
current_environmental_constraints = []
for db_entity in current_db_entities:
constraint_class = self.config_entity.db_entity_feature_class(db_entity.key)
current_environmental_constraints.append(constraint_class.db_entity_key)
create_id_field_format = create_sql_calculations(current_environmental_constraints, '{0}_id int')
insert_id_field_format = create_sql_calculations(current_environmental_constraints, '{0}_id')
pSql = '''
create table {project_schema}.environmental_constraint_geographies_table
(primary_id integer, wkb_geometry geometry, {create_id_field_format});
SELECT UpdateGeometrySRID('{project_schema}', 'environmental_constraint_geographies_table', 'wkb_geometry', 3310)
'''.format(project_schema=options['project_schema'], create_id_field_format=create_id_field_format)
execute_sql(pSql)
for db_entity in current_db_entities:
logger.info('Inserting into environmental constraint geographies table for DbEntity: %s' % db_entity.full_name)
constraint_class = self.config_entity.db_entity_feature_class(db_entity.key)
pSql = '''
insert into {project_schema}.environmental_constraint_geographies_table (primary_id, wkb_geometry, {constraint_db_entity_key}_id) select
cast(primary_id as int), wkb_geometry, {constraint_db_entity_key}_id from (
select
id as primary_id,
{constraint_db_entity_id} as {constraint_db_entity_key}_id,
st_setSRID(st_transform(st_buffer((st_dump(wkb_geometry)).geom, 0), 3310), 3310) as wkb_geometry
from (
select b.id, st_intersection(a.wkb_geometry, b.wkb_geometry) as wkb_geometry
from {constraint_schema}.{constraint_db_entity_key} a,
{project_schema}.{base_table} b
where st_intersects(a.wkb_geometry, b.wkb_geometry)) as intersection
) as polygons;
'''.format(
project_schema=options['project_schema'],
base_table=options['base_table'],
constraint_schema=parse_schema_and_table(constraint_class._meta.db_table)[0],
constraint_db_entity_key=constraint_class.db_entity_key,
constraint_db_entity_id=db_entity.id
)
execute_sql(pSql)
logger.info('finished inserting db_entity: {db_entity} {time} elapsed'.format(
time=time.time() - start_time,
db_entity=constraint_class.db_entity_key))
#only regenerate the merged environmental constraint whenever an envrionmental constraint is added or removed
# from the layer
add_geom_idx(options['project_schema'], 'environmental_constraint_geographies_table')
logger.info('Unioning all environmental constraint geographies')
drop_table('{project_schema}.environmental_constraint_geographies_table_unioned'.format(
project_schema=options['project_schema'])
)
pSql = '''
CREATE TABLE {project_schema}.environmental_constraint_geographies_table_unioned
(id serial, wkb_geometry geometry, acres float, primary_id int, {create_id_field_format});
SELECT UpdateGeometrySRID('{project_schema}', 'environmental_constraint_geographies_table_unioned', 'wkb_geometry', 3310);
'''.format(project_schema=options['project_schema'], create_id_field_format=create_id_field_format)
execute_sql(pSql)
pSql = '''
insert into {project_schema}.environmental_constraint_geographies_table_unioned (wkb_geometry, acres, primary_id, {insert_id_field_format})
SELECT
st_buffer(wkb_geometry, 0) as wkb_geometry,
st_area(st_buffer(wkb_geometry, 0)) * 0.000247105 as acres,
primary_id, {insert_id_field_format}
FROM (
SELECT
(ST_Dump(wkb_geometry)).geom as wkb_geometry,
primary_id, {insert_id_field_format}
FROM (
SELECT ST_Polygonize(wkb_geometry) AS wkb_geometry, primary_id, {insert_id_field_format} FROM (
SELECT ST_Collect(wkb_geometry) AS wkb_geometry, primary_id, {insert_id_field_format} FROM (
SELECT ST_ExteriorRing(wkb_geometry) AS wkb_geometry, primary_id, {insert_id_field_format}
FROM {project_schema}.environmental_constraint_geographies_table) AS lines
group by primary_id, {insert_id_field_format}) AS noded_lines
group by primary_id, {insert_id_field_format}) as polygons
) as final
WHERE st_area(st_buffer(wkb_geometry, 0)) > 5;'''.format(
project_schema=options['project_schema'],
insert_id_field_format=insert_id_field_format
)
execute_sql(pSql)
logger.info('finished unioning env constraints: {time} elapsed'.format(
time=time.time() - start_time))
#reproject table back to 4326 for integration with web viewing
pSql = '''
SELECT UpdateGeometrySRID('{project_schema}', 'environmental_constraint_geographies_table_unioned', 'wkb_geometry', 4326);
update {project_schema}.environmental_constraint_geographies_table_unioned a set wkb_geometry = st_transform(st_buffer(wkb_geometry, 0), 4326);
'''.format(
project_schema=options['project_schema']
)
execute_sql(pSql)
add_geom_idx(options['project_schema'], 'environmental_constraint_geographies_table_unioned')
logger.info('Env Union Finished: %s' % str(time.time() - start_time))
def update(self, **kwargs):
"""
Executes the VMT module functions
"""
public_health_variables_class = self.config_entity.db_entity_feature_class(DbEntityKey.PH_VARIABLES)
uf_150mgrid_class = self.config_entity.db_entity_feature_class(DbEntityKey.GRID_150M)
census_rate_class = self.config_entity.db_entity_feature_class(DbEntityKey.CENSUS_RATES)
grid_outcome_class = self.config_entity.db_entity_feature_class(DbEntityKey.PH_GRID_OUTCOMES)
block_group_outcome_class = self.config_entity.db_entity_feature_class(DbEntityKey.PH_BLOCK_GROUP_OUTCOMES)
outcome_summary_class = self.config_entity.db_entity_feature_class(DbEntityKey.PH_OUTCOMES_SUMMARY)
if isinstance(self.config_entity.subclassed, FutureScenario):
scenario_class = self.config_entity.db_entity_feature_class(DbEntityKey.END_STATE)
trip_length_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)
else:
scenario_class = self.config_entity.db_entity_feature_class(DbEntityKey.BASE_CANVAS)
trip_length_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)
sql_config_dict = dict(
uf_canvas_table=scenario_class.db_entity_key,
uf_canvas_schema=parse_schema_and_table(scenario_class._meta.db_table)[0],
public_health_variables_table=public_health_variables_class.db_entity_key,
public_health_variables_schema=parse_schema_and_table(public_health_variables_class._meta.db_table)[0],
source_grid_table=uf_150mgrid_class.db_entity_key,
source_grid_schema=parse_schema_and_table(uf_150mgrid_class._meta.db_table)[0],
transit_stop_table=transit_stop_class.db_entity_key,
transit_stop_schema= parse_schema_and_table(transit_stop_class._meta.db_table)[0],
census_rate_table=census_rate_class.db_entity_key,
census_rate_schema=parse_schema_and_table(census_rate_class._meta.db_table)[0],
trip_lengths_table=trip_length_class.db_entity_key,
trip_lengths_schema=parse_schema_and_table(trip_length_class._meta.db_table)[0],
grid_outcome_schema=parse_schema_and_table(grid_outcome_class._meta.db_table)[0],
grid_outcome_table=grid_outcome_class.db_entity_key,
block_group_outcome_schema=parse_schema_and_table(block_group_outcome_class._meta.db_table)[0],
block_group_outcome_table=block_group_outcome_class.db_entity_key,
outcome_summary_schema=parse_schema_and_table(outcome_summary_class._meta.db_table)[0],
outcome_summary_table=outcome_summary_class.db_entity_key
)
self.run_public_health_preprocesses(sql_config_dict, **kwargs)
read_UD4H_model_doc()
self.read_public_health_models()
all_features = public_health_variables_class.objects.filter(Q(pop__gt=0))
output = []
for feature in all_features:
feature_dict = dict(
id=feature.id,
pop=feature.pop,
pop_adult=feature.pop_adult,
pop_adult_high=feature.pop_adult_high,
pop_adult_med=feature.pop_adult_med,
pop_adult_low=feature.pop_adult_low,
pop_senior=feature.pop_senior,
pop_teen=feature.pop_teen,
pop_children=feature.pop_children,
hh=feature.hh,
gender2=feature.gender2, # = percent_female
age_children=feature.age_children, # = average_age
age_adult=feature.age_adult, # = average_age
age_teens=feature.age_teens, # = average_age
age_seniors=feature.age_seniors, # = average_age
racehisp1=feature.racehisp1, # = pop_white
racehisp2=feature.racehisp2, # = pop_black
racehisp4=feature.racehisp4, # = pop_asian
racehisp97=feature.racehisp97, # = pop_american_indian + pop_hawaiian_islander + pop_other_ethnicity
emply2=feature.emply2, # = pop_age16_up - pop_employed
educa2=feature.educa2, # = % pop_hs_diploma
educa3=feature.educa3, # = % pop_some_college
educa4=feature.educa4, # = % pop_college_degree
educa5=feature.educa5, # = % pop_graduate_degree
own2=feature.own2, # = percent renters
hhsize=feature.hhsize, # = hh_avg_size
hhveh=feature.hhveh, # = hh_avg_vehicles
incom2=feature.incom2, # = income_10k_35k = hh_inc_10_20 + hh_inc_20_30 + hh_inc_30_40/2
incom3=feature.incom3, # = income_35k_50k = hh_inc_30_40/2 + hh_inc_40_50
incom4=feature.incom4, # = income_50k_75k = hh_inc_50_60 + hh_inc_60_75
incom5=feature.incom5, # = income_75k_100k = hh_inc_75_100
incom6=feature.incom6, # = income_100k_150k = hh_inc_100_125 + hh_inc_125_150
incom7=feature.incom7, # = income_150k_plus = hh_inc_150
child_any1=feature.child_any1, # = % hh_with_children
disabled1_children=feature.disabled1_children, # = % pop with disability (excluding trouble walking)
disabled1_teens=feature.disabled1_teens, # = % pop with disability (excluding trouble walking)
disabled1_adult=feature.disabled1_adult, # = % pop with disability (excluding trouble walking)
disabled1_seniors=feature.disabled1_seniors, # = % pop with disability (excluding trouble walking)
disabled2_children=feature.disabled2_children, # = % pop with trouble walking
disabled2_teens=feature.disabled2_teens, # = % pop with trouble walking
disabled2_adult=feature.disabled2_adult, # = % pop with trouble walking
disabled2_seniors=feature.disabled2_seniors, # = % pop with trouble walking
emply_hh=feature.emply_hh, # = employed_pop / hh
educa_hh2=feature.educa_hh2, # = % hh with high school diploma
educa_hh3=feature.educa_hh3, # = % hh with some college
educa_hh4=feature.educa_hh4, # = % hh with college degree
educa_hh5=feature.educa_hh5, # = % hh with graduate degree)
# BE Vars
bldg_sqft_res=feature.bldg_sqft_res,
bldg_sqft_ret1=feature.bldg_sqft_ret1,
bldg_sqft_off=feature.bldg_sqft_off,
b1=feature.b1,
b2=feature.b2,
b3=feature.b3,
b4=feature.b4,
b5=feature.b5,
a=feature.a,
du_1km_tr=feature.du_1km_tr,
resmix_dens=feature.resmix_dens,
bldg_sqft_ret=feature.bldg_sqft_ret,
far_nonres=feature.far_nonres,
mix5=feature.mix5,
rail_any=feature.rail_any,
transit_distance=feature.transit_distance,
transit_count=feature.transit_count,
school_distance=feature.school_distance,
retail_distance=feature.retail_distance,
restaurant_distance=feature.restaurant_distance,
intersection_density_sqmi=feature.intersection_density_sqmi,
local_street=feature.local_street,
major_street=feature.major_street,
freeway_arterial_any=feature.freeway_arterial_any,
park_open_space_distance=feature.park_open_space_distance,
acres_parcel_park_open_space=feature.acres_parcel_park_open_space,
du_variable=feature.du_variable,
emp_variable=feature.emp_variable,
res_index=feature.res_index,
com_index=feature.com_index,
park_access=feature.park_access,
regional_access=feature.regional_access,
network_index=feature.network_index,
transit_access=feature.transit_access,
majrd_index=feature.major_road_index,
walk_index=feature.walk_index,
walk_index_chts_senior_walk_any=feature.walk_index_chts_senior_walk_any,
walk_index_chts_senior_auto_min=feature.walk_index_chts_senior_auto_min,
walk_index_chts_teens_walk_any=feature.walk_index_chts_teens_walk_any,
walk_index_chts_child_walk_any=feature.walk_index_chts_child_walk_any,
walk_index_chts_adult_bike_any=feature.walk_index_chts_adult_bike_any,
walk_index_chts_adult_walk_min=feature.walk_index_chts_adult_walk_min,
walk_index_chts_senior_walk_min=feature.walk_index_chts_senior_walk_min,
walk_index_chts_teens_walk_min=feature.walk_index_chts_teens_walk_min,
walk_index_chts_adult_auto_min=feature.walk_index_chts_adult_auto_min,
walk_index_chis_adult_modPA_any=feature.walk_index_chis_adult_modpa_any,
walk_index_chis_adult_overweight=feature.walk_index_chis_adult_overweight,
walk_index_chis_senior_overweight=feature.walk_index_chis_senior_overweight,
walk_index_chis_adult_obese=feature.walk_index_chis_adult_obese,
walk_index_chis_senior_gh=feature.walk_index_chis_senior_gh,
walk_index_chis_senior_walk_le_min=feature.walk_index_chis_senior_walk_le_min,
walk_index_chis_adult_modPA_min=feature.walk_index_chis_adult_modpa_min,
walk_index_chis_adult_bmi=feature.walk_index_chis_adult_bmi,
walk_index_chis_child_PA60=feature.walk_index_chis_child_pa60,
walk_index_chis_child_walk_school=feature.walk_index_chis_child_walk_school
)
model_result = self.apply_public_health_models_to_feature(feature_dict)
result_minutes = self.populate_output_minutes(model_result)
output_dict = map(lambda key: result_minutes[key.lower()], self.outcome_fields)
output.append(output_dict)
self.public_health_progress(0.1, **kwargs)
self.write_results_to_database(sql_config_dict, output)
self.public_health_progress(0.1, **kwargs)
self.aggregate_results_to_block_group(sql_config_dict)
self.public_health_progress(0.1, **kwargs)
self.aggregate_results_to_outcomes_summary_table(sql_config_dict)
self.public_health_progress(0.15, **kwargs)
def get_fields(cls, table_name):
"""
Modifies the original handle_inspection to instead get all the fields of the table described by the db_entity
:param db_entity:
:return: A dict keyed by field named and valued by models.Field or variant instance
"""
connection = connections['default']
table2model = lambda table_name: table_name.title().replace(
'_', '').replace(' ', '').replace('-', '')
cursor = connection.cursor()
try:
relations = connection.introspection.get_relations(
cursor, table_name)
except NotImplementedError:
relations = {}
try:
indexes = connection.introspection.get_indexes(cursor, table_name)
except NotImplementedError:
indexes = {}
schema, table = parse_schema_and_table(table_name)
# Fill this dict with field definitions
fields = {}
for i, row in enumerate(
cls.get_table_description(cursor, table, schema, connection)):
column_name = row[0]
att_name = column_name.lower()
comment_notes = [
] # Holds Field notes, to be displayed in a Python comment.
extra_params = {} # Holds Field parameters such as 'db_column'.
# If the column name can't be used verbatim as a Python
# attribute, set the "db_column" for this Field.
if ' ' in att_name or '-' in att_name or keyword.iskeyword(
att_name) or column_name != att_name:
extra_params['db_column'] = column_name
# Add primary_key and unique, if necessary.
if column_name in indexes:
if indexes[column_name]['primary_key']:
extra_params['primary_key'] = True
elif indexes[column_name]['unique']:
extra_params['unique'] = True
# Modify the field name to make it Python-compatible.
if ' ' in att_name:
att_name = att_name.replace(' ', '_')
comment_notes.append('Field renamed to remove spaces.')
if '-' in att_name:
att_name = att_name.replace('-', '_')
comment_notes.append('Field renamed to remove dashes.')
if column_name != att_name:
comment_notes.append('Field name made lowercase.')
if i in relations:
rel_to = relations[i][
1] == table_name and "'cls'" or table2model(
relations[i][1])
field_type = 'ForeignKey(%s' % rel_to
if att_name.endswith('_id'):
att_name = att_name[:-3]
else:
extra_params['db_column'] = column_name
else:
# Calling `get_field_type` to get the field type string and any
# additional paramters and notes.
field_type, field_params, field_notes = cls.get_field_type(
connection, table_name, row)
extra_params.update(field_params)
comment_notes.extend(field_notes)
field_type += '('
if keyword.iskeyword(att_name):
att_name += '_field'
comment_notes.append(
'Field renamed because it was a Python reserved word.')
if att_name[0].isdigit():
att_name = 'number_%s' % att_name
extra_params['db_column'] = unicode(column_name)
comment_notes.append("Field renamed because it wasn't a "
"valid Python identifier.")
# Don't output 'id = meta.AutoField(primary_key=True)', because
# that's assumed if it doesn't exist.
if att_name == 'id' and field_type == 'AutoField(' and extra_params == {
'primary_key': True
}:
continue
# Add 'null' and 'blank', if the 'null_ok' flag was present in the
# table description.
if row[6]: # If it's NULL...
extra_params['blank'] = True
# Don't know why these are here, commenting out
#if not field_type in ('TextField(', 'CharField('):
extra_params['null'] = True
field_desc = 'models.%s' % field_type
if extra_params:
if not field_desc.endswith('('):
field_desc += ', '
field_desc += ', '.join(
['%s=%r' % (k, v) for k, v in extra_params.items()])
field_desc += ')'
if comment_notes:
field_desc += ' # ' + ' '.join(comment_notes)
# Set the dict key/value to the field name and the evaluated field description
fields[att_name] = eval(field_desc)
fields[att_name].name = att_name
return fields
def update(self, **kwargs):
logger.debug("Executing Environmental Constraints using {0}".format(
self.config_entity))
config_entity = self.config_entity
end_state_feature_class = config_entity.db_entity_feature_class(
DbEntityKey.END_STATE)
base_table = config_entity.db_entity_feature_class(
DbEntityKey.BASE_CANVAS)
options = dict(project_schema=parse_schema_and_table(
base_table._meta.db_table)[0],
scenario_schema=parse_schema_and_table(
end_state_feature_class._meta.db_table)[0],
end_state_table=end_state_feature_class.db_entity_key)
current_db_entities, db_entities_to_add, db_entities_to_delete = \
self.update_or_create_environmental_constraint_percents(config_entity)
current_environmental_constraints = []
logger.debug("Current db_entities {0}".format(current_db_entities))
for db_entity in current_db_entities:
logger.debug("Active db_entity {0}".format(db_entity.key))
constraint_class = config_entity.db_entity_feature_class(
db_entity.key)
environmental_constraint_percent = EnvironmentalConstraintPercent.objects.filter(
db_entity_id=db_entity.id, analysis_tool_id=self.id)[0]
current_environmental_constraints.append(
dict(key=constraint_class.db_entity_key,
priority=environmental_constraint_percent.priority,
percent=environmental_constraint_percent.percent))
pSql = '''
DO $$
BEGIN
BEGIN
ALTER TABLE {project_schema}.environmental_constraint_geographies_table_unioned ADD COLUMN constraint_acres_{config_entity_id} float;
EXCEPTION
WHEN duplicate_column
THEN -- do nothing;
END;
END;
$$'''.format(project_schema=options['project_schema'],
config_entity_id=self.config_entity.id)
execute_sql(pSql)
logger.info(
'Calculate constraint acreage for the active scenario end state feature'
)
for db_entity in current_db_entities:
constraint_class = self.config_entity.db_entity_feature_class(
db_entity.key)
environmental_constraint_percent = EnvironmentalConstraintPercent.objects.filter(
db_entity_id=db_entity.id, analysis_tool_id=self.id)[0]
constraint_percent = environmental_constraint_percent.percent
active_constraint = filter(
lambda dct: constraint_class.db_entity_key in dct['key'],
current_environmental_constraints)[0]
priority_constraints = filter(
lambda dct: dct['priority'] < active_constraint['priority'] or
(dct['priority'] == active_constraint['priority'] and dct[
'percent'] > active_constraint['percent']),
current_environmental_constraints)
priority_key_list = []
for constraint in priority_constraints:
priority_key_list.append(constraint['key'])
priority_query = create_sql_calculations(
priority_key_list, ' and {0}_id is null',
' and a.primary_id is not null')
pSql = '''
update {project_schema}.environmental_constraint_geographies_table_unioned a set
constraint_acres_{config_entity_id} = acres * {percent} where {constraint}_id = {constraint_id} {priority_query};
'''.format(project_schema=options['project_schema'],
constraint=constraint_class.db_entity_key,
constraint_id=db_entity.id,
percent=constraint_percent,
priority_query=priority_query,
config_entity_id=self.config_entity.id)
execute_sql(pSql)
pSql = '''
update {scenario_schema}.{end_state_table} a set
acres_developable = a.acres_gross - b.constraint_acres
FROM
(select primary_id,
sum(constraint_acres_{config_entity_id}) as constraint_acres
from {project_schema}.environmental_constraint_geographies_table_unioned
where constraint_acres_{config_entity_id} is not null group by primary_id) b
where a.id= b.primary_id;
'''.format(scenario_schema=options['scenario_schema'],
project_schema=options['project_schema'],
end_state_table=options['end_state_table'],
config_entity_id=self.config_entity.id)
execute_sql(pSql)
pSql = '''
update {scenario_schema}.{end_state_table}
set developable_proportion = (
case when acres_gross > 0 then acres_developable / acres_gross else 0 end
)
'''.format(scenario_schema=options['scenario_schema'],
end_state_table=options['end_state_table'])
execute_sql(pSql)
def update(self, **kwargs):
logger.debug("Executing Environmental Constraints using {0}".format(self.config_entity))
config_entity = self.config_entity
end_state_feature_class = config_entity.db_entity_feature_class(DbEntityKey.END_STATE)
base_table = config_entity.db_entity_feature_class(DbEntityKey.BASE_CANVAS)
options = dict(
project_schema=parse_schema_and_table(base_table._meta.db_table)[0],
scenario_schema=parse_schema_and_table(end_state_feature_class._meta.db_table)[0],
end_state_table=end_state_feature_class.db_entity_key
)
current_db_entities, db_entities_to_add, db_entities_to_delete = \
self.update_or_create_environmental_constraint_percents(config_entity)
current_environmental_constraints = []
logger.debug("Current db_entities {0}".format(current_db_entities))
for db_entity in current_db_entities:
logger.debug("Active db_entity {0}".format(db_entity.key))
constraint_class = config_entity.db_entity_feature_class(db_entity.key)
environmental_constraint_percent = EnvironmentalConstraintPercent.objects.filter(
db_entity_id=db_entity.id,
analysis_tool_id=self.id)[0]
current_environmental_constraints.append(
dict(
key=constraint_class.db_entity_key,
priority=environmental_constraint_percent.priority,
percent=environmental_constraint_percent.percent
)
)
pSql = '''
DO $$
BEGIN
BEGIN
ALTER TABLE {project_schema}.environmental_constraint_geographies_table_unioned ADD COLUMN constraint_acres_{config_entity_id} float;
EXCEPTION
WHEN duplicate_column
THEN -- do nothing;
END;
END;
$$'''.format(
project_schema=options['project_schema'],
config_entity_id=self.config_entity.id
)
execute_sql(pSql)
logger.info('Calculate constraint acreage for the active scenario end state feature')
for db_entity in current_db_entities:
constraint_class = self.config_entity.db_entity_feature_class(db_entity.key)
environmental_constraint_percent = EnvironmentalConstraintPercent.objects.filter(
db_entity_id=db_entity.id,
analysis_tool_id=self.id)[0]
constraint_percent = environmental_constraint_percent.percent
active_constraint = filter(lambda dct: constraint_class.db_entity_key in dct['key'], current_environmental_constraints)[0]
priority_constraints = filter(lambda dct: dct['priority'] < active_constraint['priority'] or (dct['priority'] == active_constraint['priority'] and dct['percent'] > active_constraint['percent']), current_environmental_constraints)
priority_key_list = []
for constraint in priority_constraints:
priority_key_list.append(constraint['key'])
priority_query = create_sql_calculations(priority_key_list, ' and {0}_id is null', ' and a.primary_id is not null')
pSql = '''
update {project_schema}.environmental_constraint_geographies_table_unioned a set
constraint_acres_{config_entity_id} = acres * {percent} where {constraint}_id = {constraint_id} {priority_query};
'''.format(
project_schema=options['project_schema'],
constraint=constraint_class.db_entity_key,
constraint_id=db_entity.id,
percent=constraint_percent,
priority_query=priority_query,
config_entity_id=self.config_entity.id
)
execute_sql(pSql)
pSql = '''
update {scenario_schema}.{end_state_table} a set
acres_developable = a.acres_gross - b.constraint_acres
FROM
(select primary_id,
sum(constraint_acres_{config_entity_id}) as constraint_acres
from {project_schema}.environmental_constraint_geographies_table_unioned
where constraint_acres_{config_entity_id} is not null group by primary_id) b
where a.id= b.primary_id;
'''.format(
scenario_schema=options['scenario_schema'],
project_schema=options['project_schema'],
end_state_table=options['end_state_table'],
config_entity_id=self.config_entity.id
)
execute_sql(pSql)
pSql = '''
update {scenario_schema}.{end_state_table}
set developable_proportion = (
case when acres_gross > 0 then acres_developable / acres_gross else 0 end
)
'''.format(
scenario_schema=options['scenario_schema'],
end_state_table=options['end_state_table']
)
execute_sql(pSql)
def run_base_water_calculations(self):
features = self.base_class.objects.filter(Q(du__gt=0) | Q(emp__gt=0))
annotated_features = annotated_related_feature_class_pk_via_geographies(
features, self.config_entity, [DbEntityKey.CLIMATE_ZONES])
water_output_list = []
options = dict(
water_result_table=self.water_class.db_entity_key,
water_schema=parse_schema_and_table(
self.water_class._meta.db_table)[0],
base_table=self.base_class.db_entity_key,
base_schema=parse_schema_and_table(
self.base_class._meta.db_table)[0],
)
for feature in annotated_features.iterator():
self.result_dict = defaultdict(lambda: float(0))
self.feature = feature
if feature.climate_zones:
climate_zone_feature = self.climate_zone_class.objects.get(
id=feature.climate_zones)
else:
logger.warn(
"No Climate Zone intersection for feature id {0} or check geography relation table"
.format(feature.id))
continue
hh_factor = (feature.pop /
feature.hh) * (feature.hh / feature.du) if (
feature.du > 0 and feature.hh > 0) else 0
self.feature_dict = dict(
id=feature.id,
pop=float(feature.pop),
hh=float(feature.hh),
emp=float(feature.emp),
evapotranspiration_zone=climate_zone_feature.
evapotranspiration_zone.zone,
annual_evapotranspiration=float(
climate_zone_feature.evapotranspiration_zone.
annual_evapotranspiration),
residential_irrigated_sqft=float(
feature.residential_irrigated_sqft),
commercial_irrigated_sqft=float(
feature.commercial_irrigated_sqft),
du_detsf_ll=float(feature.du_detsf_ll * hh_factor),
du_detsf_sl=float(feature.du_detsf_sl * hh_factor),
du_attsf=float(feature.du_attsf * hh_factor),
du_mf=float(feature.du_mf * hh_factor),
retail_services=float(feature.emp_retail_services),
restaurant=float(feature.emp_restaurant),
accommodation=float(feature.emp_accommodation),
arts_entertainment=float(feature.emp_arts_entertainment),
other_services=float(feature.emp_other_services),
office_services=float(feature.emp_office_services),
public_admin=float(feature.emp_public_admin),
education=float(feature.emp_education),
medical_services=float(feature.emp_medical_services),
wholesale=float(feature.emp_wholesale),
transport_warehousing=float(feature.emp_transport_warehousing),
manufacturing=float(feature.emp_manufacturing),
construction=float(feature.emp_construction),
utilities=float(feature.emp_utilities),
agriculture=float(feature.emp_agriculture),
extraction=float(feature.emp_extraction),
military=float(feature.emp_military))
self.calculate_base_water()
self.calculate_visualized_field()
output_row = map(lambda key: self.result_dict[key],
self.output_fields)
water_output_list.append(output_row)
return water_output_list, options
