def write_results_to_database(self, options, public_health_output_list):
drop_table(
'{grid_outcome_schema}.{grid_outcome_table}'.format(**options))
attribute_list = filter(lambda x: x != 'id', self.outcome_fields)
options['output_field_syntax'] = 'id int, ' + \
create_sql_calculations(attribute_list, '{0} numeric(20,8)')
execute_sql(
"create table {grid_outcome_schema}.{grid_outcome_table} ({output_field_syntax});"
.format(**options))
output_textfile = StringIO("")
for row in public_health_output_list:
stringrow = []
for item in row:
if isinstance(item, int):
stringrow.append(str(item))
else:
stringrow.append(str(round(item, 8)))
output_textfile.write("\t".join(stringrow) + "\n")
output_textfile.seek(os.SEEK_SET)
#copy text file output back into Postgres
copy_from_text_to_db(
output_textfile,
'{grid_outcome_schema}.{grid_outcome_table}'.format(**options))
output_textfile.close()
##---------------------------
pSql = '''alter table {grid_outcome_schema}.{grid_outcome_table}
add column wkb_geometry geometry (GEOMETRY, 4326);'''.format(
**options)
execute_sql(pSql)
pSql = '''update {grid_outcome_schema}.{grid_outcome_table} b set
wkb_geometry = st_setSRID(a.wkb_geometry, 4326)
from (select id, wkb_geometry from {source_grid_schema}.{source_grid_table}) a
where cast(a.id as int) = cast(b.id as int);
'''.format(**options)
execute_sql(pSql)
add_geom_idx(options['grid_outcome_schema'],
options['grid_outcome_table'], 'wkb_geometry')
add_primary_key(options['grid_outcome_schema'],
options['grid_outcome_table'], 'id')
# Since not every grid cell results in a grid_outcome, we need to wipe out the rel
# table and recreate it to match the base grid_coutcome table. Otherwise there will
# be to many rel table rows and cloning the DbEntity or ConfigEntity will fail
logger.info(
"Writing to relative table {grid_outcome_schema}.{grid_outcome_table}rel"
.format(**options))
truncate_table(
"{grid_outcome_schema}.{grid_outcome_table}rel".format(**options))
from footprint.main.publishing.data_import_publishing import create_and_populate_relations
create_and_populate_relations(
self.config_entity,
self.config_entity.computed_db_entities(
key=DbEntityKey.PH_GRID_OUTCOMES)[0])
def write_results_to_database(self, options, energy_output_list):
drop_table('{energy_schema}.{energy_result_table}'.format(**options))
attribute_list = filter(
lambda x: x not in ['id', 'title24_zone', 'fcz_zone'],
self.output_fields)
output_field_syntax = 'id int, title24_zone int, fcz_zone int, ' + create_sql_calculations(
attribute_list, '{0} numeric(14, 4)')
pSql = '''
create table {energy_schema}.{energy_result_table} ({output_field_syntax});'''.format(
output_field_syntax=output_field_syntax, **options)
execute_sql(pSql)
output_textfile = StringIO("")
for row in energy_output_list:
stringrow = []
for item in row:
if isinstance(item, int):
stringrow.append(str(item))
else:
stringrow.append(str(round(item, 4)))
output_textfile.write("\t".join(stringrow) + "\n")
output_textfile.seek(os.SEEK_SET)
#copy text file output back into Postgres
copy_from_text_to_db(
output_textfile,
'{energy_schema}.{energy_result_table}'.format(**options))
output_textfile.close()
pSql = '''alter table {energy_schema}.{energy_result_table} add column wkb_geometry geometry (GEOMETRY, 4326);'''.format(
**options)
execute_sql(pSql)
pSql = '''update {energy_schema}.{energy_result_table} b set
wkb_geometry = st_setSRID(a.wkb_geometry, 4326)
from (select id, wkb_geometry from {base_schema}.{base_table}) a
where cast(a.id as int) = cast(b.id as int);
'''.format(**options)
execute_sql(pSql)
add_geom_idx(options['energy_schema'], options['energy_result_table'],
'wkb_geometry')
add_primary_key(options['energy_schema'],
options['energy_result_table'], 'id')
add_attribute_idx(options['energy_schema'],
options['energy_result_table'],
'annual_million_btus_per_unit')
def write_results_to_database(self, options, public_health_output_list):
drop_table('{grid_outcome_schema}.{grid_outcome_table}'.format(**options))
attribute_list = filter(lambda x: x != 'id', self.outcome_fields)
options['output_field_syntax'] = 'id int, ' + \
create_sql_calculations(attribute_list, '{0} numeric(20,8)')
execute_sql("create table {grid_outcome_schema}.{grid_outcome_table} ({output_field_syntax});".format(
**options))
output_textfile = StringIO("")
for row in public_health_output_list:
stringrow = []
for item in row:
if isinstance(item, int):
stringrow.append(str(item))
else:
stringrow.append(str(round(item, 8)))
output_textfile.write("\t".join(stringrow) + "\n")
output_textfile.seek(os.SEEK_SET)
#copy text file output back into Postgres
copy_from_text_to_db(output_textfile, '{grid_outcome_schema}.{grid_outcome_table}'.format(**options))
output_textfile.close()
##---------------------------
pSql = '''alter table {grid_outcome_schema}.{grid_outcome_table}
add column wkb_geometry geometry (GEOMETRY, 4326);'''.format(**options)
execute_sql(pSql)
pSql = '''update {grid_outcome_schema}.{grid_outcome_table} b set
wkb_geometry = st_setSRID(a.wkb_geometry, 4326)
from (select id, wkb_geometry from {source_grid_schema}.{source_grid_table}) a
where cast(a.id as int) = cast(b.id as int);
'''.format(**options)
execute_sql(pSql)
add_geom_idx(options['grid_outcome_schema'], options['grid_outcome_table'], 'wkb_geometry')
add_primary_key(options['grid_outcome_schema'], options['grid_outcome_table'], 'id')
# Since not every grid cell results in a grid_outcome, we need to wipe out the rel
# table and recreate it to match the base grid_coutcome table. Otherwise there will
# be to many rel table rows and cloning the DbEntity or ConfigEntity will fail
logger.info("Writing to relative table {grid_outcome_schema}.{grid_outcome_table}rel".format(**options))
truncate_table("{grid_outcome_schema}.{grid_outcome_table}rel".format(**options))
from footprint.main.publishing.data_import_publishing import create_and_populate_relations
create_and_populate_relations(
self.config_entity,
self.config_entity.computed_db_entities(key=DbEntityKey.PH_GRID_OUTCOMES)[0])
def write_vmt_results_to_database(self, options, vmt_output_list):
vmt_output_text_file = StringIO("")
for row in vmt_output_list:
stringrow = []
for item in row:
if isinstance(item, int):
stringrow.append(str(item))
else:
stringrow.append(str(round(item, 4)))
vmt_output_text_file.write("\t".join(stringrow,) + "\n")
vmt_output_text_file.seek(os.SEEK_SET)
#copy text file output back into Postgres
copy_from_text_to_db(vmt_output_text_file, '{vmt_schema}.{vmt_result_table}'.format(**options))
vmt_output_text_file.close()
def write_vmt_results_to_database(self, options, vmt_output_list):
vmt_output_text_file = StringIO("")
for row in vmt_output_list:
stringrow = []
for item in row:
if isinstance(item, int):
stringrow.append(str(item))
else:
stringrow.append(str(round(item, 4)))
vmt_output_text_file.write("\t".join(stringrow, ) + "\n")
vmt_output_text_file.seek(os.SEEK_SET)
#copy text file output back into Postgres
copy_from_text_to_db(
vmt_output_text_file,
'{vmt_schema}.{vmt_result_table}'.format(**options))
vmt_output_text_file.close()
def write_results_to_database(self, options, energy_output_list):
drop_table('{energy_schema}.{energy_result_table}'.format(**options))
attribute_list = filter(lambda x: x not in ['id', 'title24_zone', 'fcz_zone'], self.output_fields)
output_field_syntax = 'id int, title24_zone int, fcz_zone int, ' + create_sql_calculations(attribute_list, '{0} numeric(14, 4)')
pSql = '''
create table {energy_schema}.{energy_result_table} ({output_field_syntax});'''.format(output_field_syntax=output_field_syntax, **options)
execute_sql(pSql)
output_textfile = StringIO("")
for row in energy_output_list:
stringrow = []
for item in row:
if isinstance(item, int):
stringrow.append(str(item))
else:
stringrow.append(str(round(item, 4)))
output_textfile.write("\t".join(stringrow) + "\n")
output_textfile.seek(os.SEEK_SET)
#copy text file output back into Postgres
copy_from_text_to_db(output_textfile, '{energy_schema}.{energy_result_table}'.format(**options))
output_textfile.close()
pSql = '''alter table {energy_schema}.{energy_result_table} add column wkb_geometry geometry (GEOMETRY, 4326);'''.format(**options)
execute_sql(pSql)
pSql = '''update {energy_schema}.{energy_result_table} b set
wkb_geometry = st_setSRID(a.wkb_geometry, 4326)
from (select id, wkb_geometry from {base_schema}.{base_table}) a
where cast(a.id as int) = cast(b.id as int);
'''.format(**options)
execute_sql(pSql)
add_geom_idx(options['energy_schema'], options['energy_result_table'], 'wkb_geometry')
add_primary_key(options['energy_schema'], options['energy_result_table'], 'id')
add_attribute_idx(options['energy_schema'], options['energy_result_table'], 'annual_million_btus_per_unit')
