def populate_public_health_grid_relation_table(sql_config_dict):
join_exists = table_exists(
sql_config_dict['source_grid_table'] + '_join',
sql_config_dict['public_health_variables_schema'])
if not join_exists:
pSql = '''
create table {public_health_variables_schema}.{source_grid_table}_join
as select
a.id as grid_id, b.id as primary_id, c.id as census_id,
st_area(st_intersection(a.wkb_geometry, b.wkb_geometry)) / st_area(b.wkb_geometry) as primary_proportion
from
{source_grid_schema}.{source_grid_table} a,
{uf_canvas_schema}.{uf_canvas_table} b,
{census_rate_schema}.{census_rate_table} c
where
st_intersects(a.wkb_geometry, b.wkb_geometry) and
st_intersects(st_centroid(a.wkb_geometry), c.wkb_geometry);
'''.format(**sql_config_dict)
execute_sql(pSql)
add_attribute_idx(sql_config_dict['public_health_variables_schema'],
sql_config_dict['source_grid_table'] + '_join',
'grid_id')
add_attribute_idx(sql_config_dict['public_health_variables_schema'],
sql_config_dict['source_grid_table'] + '_join',
'primary_id')
add_attribute_idx(sql_config_dict['public_health_variables_schema'],
sql_config_dict['source_grid_table'] + '_join',
'census_id')
def populate_public_health_grid_relation_table(sql_config_dict):
join_exists = table_exists(sql_config_dict['source_grid_table'] + '_join',
sql_config_dict['public_health_variables_schema'])
if not join_exists:
pSql = '''
create table {public_health_variables_schema}.{source_grid_table}_join
as select
a.id as grid_id, b.id as primary_id, c.id as census_id,
st_area(st_intersection(a.wkb_geometry, b.wkb_geometry)) / st_area(b.wkb_geometry) as primary_proportion
from
{source_grid_schema}.{source_grid_table} a,
{uf_canvas_schema}.{uf_canvas_table} b,
{census_rate_schema}.{census_rate_table} c
where
st_intersects(a.wkb_geometry, b.wkb_geometry) and
st_intersects(st_centroid(a.wkb_geometry), c.wkb_geometry);
'''.format(**sql_config_dict)
execute_sql(pSql)
add_attribute_idx(sql_config_dict['public_health_variables_schema'],
sql_config_dict['source_grid_table'] + '_join', 'grid_id')
add_attribute_idx(sql_config_dict['public_health_variables_schema'],
sql_config_dict['source_grid_table'] + '_join', 'primary_id')
add_attribute_idx(sql_config_dict['public_health_variables_schema'],
sql_config_dict['source_grid_table'] + '_join', 'census_id')
def handle(self, *args, **options):
full_source_table = options.get('from')
source = full_source_table.split('.')
assert source, "you must provide a source for the import scenario data"
if len(source) == 1:
source_table = source[0]
source_schema = 'public'
elif len(source) == 2:
source_table = source[1]
source_schema = source[0]
else:
raise Exception("invalid format for source data - please use 'schema'.'table'")
full_source_table = '"{schema}"."{table}"'.format(schema=source_schema, table=source_table)
name = options.get('name')
assert table_exists(source_table, source_schema), "the source table you provided does not exist in the database"
if options.get('to'):
scenario = Scenario.objects.get(name=options.get('to'))
else:
assert name, "you must provide a name for the scenario with the --name option"
scenario = create_scenario_clone()[0]
scenario.name = options.get('name')
previous = scenario._no_post_save_publishing
scenario._no_post_save_publishing = True
scenario.save()
scenario._no_post_save_publishing = previous
end_state = scenario.db_entity_by_key('scenario_end_state')
cursor = connection.cursor()
source_columns = {
c.name: c for c in SmartDatabaseIntrospection(connection).describe_table_columns(cursor, full_source_table)
}
destination_columns = {
c.name: c for c in SmartDatabaseIntrospection(connection).describe_table_columns(cursor, end_state.full_table_name)
}
del destination_columns['id']
join_on = options.get('join_on')
assert join_on in source_columns, "The join column {c} is not in your source table.".format(c=join_on)
assert 'built_form_key' in source_columns, "The source data has no built_form_key field"
join_to = options.get('join_to')
assert join_to in ['geography_id', 'source_id', 'id']
if join_to == 'geography_id':
raise NotImplementedError("Complex query using rel_id's not implemented - use source_id (default) for join")
user_id = User.objects.get(username="******").id
if options.get('full'):
from_column_check = source_columns.copy()
for k, v in destination_columns.items():
from_column_check.pop(k)
destination_columns.pop('built_form_base')
columns = [k for k, v in destination_columns.items()]
equations = ''
for c in columns:
equations += '\n{column} = source.{column},'.format(column=c)
equations = equations[:-1]
import_query = "update {destination_table} as destination set {equations} from {source_table} as source " \
"where destination.{join_to} = source.{join_on};".format(destination_table=end_state.full_table_name,
source_table=full_source_table,
equations=equations,
join_to=join_to,
join_on=join_on)
rel_table = '"{0}"."{1}"'.format(end_state.schema, 'scenario_end_staterel')
rel_join = "coreendstatefeature{db_entity_id}_ptr_id".format(db_entity_id=end_state.id)
rel_update_query = """
UPDATE {rel_table} AS rel SET
updater_id = {user_id},
built_form_id = b.built_form_id,
updated = CURRENT_TIMESTAMP
FROM (
SELECT endstate.id, endstate.built_form_key, bf.id AS built_form_id
FROM {destination_table} AS endstate
LEFT JOIN main_builtform bf ON (endstate.built_form_key = bf.key)) b
WHERE rel.{rel_join} = b.id""".format(destination_table=end_state.full_table_name,
user_id=user_id, rel_join=rel_join, rel_table=rel_table)
# insert = "insert into {table} as destination ({columns}) (select {columns} from {source});".format(
# table=end_state.full_table_name, source=full_source_table, columns=columns_str)
logger.info(import_query)
cursor.execute(import_query)
logger.info("Imported {count} rows to the scenario".format(count=cursor.rowcount))
logger.info(rel_update_query)
cursor.execute(rel_update_query)
logger.info("Imported {count} rows to the scenario".format(count=cursor.rowcount))
else:
optional_columns = {
'dev_pct': 1,
'density_pct': 1,
'gross_net_pct': 1,
'dirty_flag': True,
'clear_flag': False,
'redevelopment_flag': False,
'developable_proportion': 1,
}
#todo really what else do we need?
required_columns = ['built_form_key']
select_columns = filter(lambda c: (c in optional_columns or c in required_columns), source_columns)
default_columns = filter(lambda c: c not in source_columns, optional_columns)
equations = ''
for c in select_columns:
equations += '{column} = source.{column},'.format(column=c)
for c in default_columns:
logger.warning("No source data for {column}, using default of {default}".format(column=c,
default=default_columns[c]))
equations += "{column} = {default},".format(column=c, default=default_columns[c])
equations = equations[:-1]
import_query = "update {destination_table} as destination set {equations} from {source_table} as source " \
"where destination.{join_to} = source.{join_on};".format(destination_table=end_state.full_table_name,
source_table=full_source_table,
equations=equations,
join_to=join_to,
join_on=join_on)
cursor = connection.cursor()
logger.info("Importing scenario {0}".format(scenario.key))
cursor.execute(import_query)
logger.info("Updated {count} rows in the rel table".format(count=cursor.rowcount))
# todo populate increment table with diff between base and end state
increment_table = scenario.db_entity_by_key('scenario_increment')
base_table = scenario.db_entity_by_key('base_feature').full_table_name
update_increment = """
update {increment} a set
land_development_category = b.land_development_category ,
pop = b.pop,
hh = b.hh,
du = b.du,
du_detsf = b.du_detsf,
du_detsf_ll= b.du_detsf_ll,
du_detsf_sl = b.du_detsf_sl,
du_attsf = b.du_attsf,
du_mf = b.du_mf,
emp = b.emp,
emp_ret = b.emp_ret,
emp_off = b.emp_off,
emp_pub = b.emp_pub,
emp_ind = b.emp_ind,
emp_ag = b.emp_ag,
emp_military = b.emp_military,
emp_retail_services = b.emp_retail_services,
emp_restaurant = b.emp_restaurant, emp_accommodation = b.emp_accommodation,
emp_arts_entertainment = b.emp_arts_entertainment, emp_other_services = b.emp_other_services,
emp_office_services = b.emp_office_services, emp_education = b.emp_education,
emp_public_admin = b.emp_public_admin, emp_medical_services = b.emp_medical_services,
emp_wholesale = b.emp_wholesale, emp_transport_warehousing = b.emp_transport_warehousing,
emp_manufacturing = b.emp_manufacturing, emp_utilities = b.emp_utilities,
emp_construction = b.emp_construction, emp_agriculture = b.emp_agriculture, emp_extraction = b.emp_extraction
FROM (select
a.id, a.land_development_category,
a.pop - b.pop as pop, a.hh - b.hh as hh,
a.du - b.du as du, a.du_detsf - b.du_detsf as du_detsf,
a.du_detsf_ll - b.du_detsf_ll as du_detsf_ll, a.du_detsf_sl - b.du_detsf_sl as du_detsf_sl,
a.du_attsf - b.du_attsf as du_attsf, a.du_mf - b.du_mf as du_mf,
a.emp - b.emp as emp, a.emp_ret - b.emp_ret as emp_ret, a.emp_off - b.emp_off as emp_off,
a.emp_ind - b.emp_ind as emp_ind, a.emp_pub - b.emp_pub as emp_pub, a.emp_ag - b.emp_ag as emp_ag,
a.emp_military - b.emp_military as emp_military,
a.emp_retail_services - b.emp_retail_services as emp_retail_services,
a.emp_restaurant - b.emp_restaurant as emp_restaurant,
a.emp_arts_entertainment - b.emp_arts_entertainment as emp_arts_entertainment,
a.emp_accommodation - b.emp_accommodation as emp_accommodation,
a.emp_other_services - b.emp_other_services as emp_other_services,
a.emp_office_services - b.emp_office_services as emp_office_services,
a.emp_education - b.emp_education as emp_education,
a.emp_public_admin - b.emp_public_admin as emp_public_admin,
a.emp_medical_services - b.emp_medical_services as emp_medical_services,
a.emp_wholesale - b.emp_wholesale as emp_wholesale,
a.emp_transport_warehousing - b.emp_transport_warehousing as emp_transport_warehousing,
a.emp_manufacturing - b.emp_manufacturing as emp_manufacturing,
a.emp_utilities - b.emp_utilities as emp_utilities,
a.emp_construction - b.emp_construction as emp_construction ,
a.emp_agriculture - b.emp_agriculture as emp_agriculture,
a.emp_extraction - b.emp_extraction as emp_extraction
from {end_state} a
left join {base} b on a.id = b.id) b
where a.id = b.id;
""".format(increment=increment_table.full_table_name, end_state=end_state.full_table_name, base=base_table)
cursor.execute(update_increment)
increment_rel = '"{0}"."{1}"'.format(increment_table.schema, increment_table.table + "rel")
rel_join = "coreincrementfeature{db_entity_id}_ptr_id".format(db_entity_id=increment_table.id)
update_increment_built_form = """
update {increment} as increment
set built_form_key = b.built_form_key
from (
select endstate.id as endstate_id, base.id as base_id,
base.built_form_key as built_form_base, endstate.built_form_key as built_form_key
from {endstate} as endstate
LEFT JOIN {base} as base on endstate.id = base.id) as b
WHERE b.endstate_id = increment.id and b.built_form_base != b.built_form_key
""".format(increment=increment_table.full_table_name, endstate=end_state.full_table_name, base=base_table)
update_increment_rel = """
UPDATE {rel_table} AS rel SET
updater_id = {user_id},
built_form_id = b.built_form_id,
updated = CURRENT_TIMESTAMP
FROM (
SELECT increment.id, increment.built_form_key, bf.id AS built_form_id
FROM {destination_table} AS increment
LEFT JOIN main_builtform bf ON (increment.built_form_key = bf.key)) b
WHERE rel.{rel_join} = b.id""".format(destination_table=increment_table.full_table_name,
user_id=user_id, rel_join=rel_join, rel_table=increment_rel)
logger.info(update_increment_built_form)
cursor.execute(update_increment_built_form)
logger.info("Updated {count} rows in the increments tableq".format(count=cursor.rowcount))
logger.info(update_increment_rel)
cursor.execute(update_increment_rel)
logger.info("Updated {count} rows in the increments rel table".format(count=cursor.rowcount))
