def rvo_warmtenetten():
print('Creating table for RVO Warmtenetten...')
create_rvo_warmtenetten_table()
print('Downloading the RVO Warmtenetten...')
download_rvo_warmtenetten_data()
print('Loading the data into Postgres...')
load_rvo_warmtenetten()
print('Creating indexes...')
add_index('rvo_warmtenetten', 'buurt_code')
def CBS_kerncijfers():
print('Creating table for CBS PC6...')
create_CBS_PC6_2017_kerncijfers_table()
print('Downloading data...')
download_CBS_PC6_2017_kerncijfers()
print('Loading the data into Postgres...')
load_CBS_PC6_2017_kerncijfers()
print('Creating indexes...')
add_index('cbs_pc6_2017_kerncijfers', 'pc6')
def CBS_PC6():
print('Creating table for CBS PC6...')
create_CBS_PC6_2019_energy_use_table()
print('Downloading data...')
download_CBS_PC6_2019_energy_use()
print('Loading the data into Postgres...')
load_CBS_PC6_2019_energy_use()
print('Creating indexes...')
add_index('cbs_pc6_2019_energy_use', 'pc6')
def energy_labels():
print('Creating table for energy labels...')
create_energy_labels_table()
print('Downloading the EP-Online database...')
try:
download_energy_labels_data()
except requests.exceptions.ConnectionError as e:
print(
f'\tERROR! Could not download EP-Online due to a ConnectionError. Are you connected to the internet? Error:\n\t{e}'
)
# do not process any further
return
except ConnectionError as e:
print(
f'\tERROR! Could not download EP-Online due to a ConnectionError. Error:\n\t{e}'
)
# do not process any further
return
print('Loading the data into Postgres...')
load_energy_labels_data()
print('Deleting columns...')
# is always NULL
delete_column('energy_labels', 'bagstandplaatsid')
# is only relevant for 'U' buildings
delete_column('energy_labels', 'sbicode')
print('Creating indexes...')
add_index('energy_labels', 'vbo_id')
add_index('energy_labels', 'pand_id')
add_index('energy_labels', 'pc6')
def bag():
print('Creating table for BAG...')
create_BAG_table()
print('Loading the data into Postgres...')
load_BAG()
print('Creating indexes...')
add_index('bag', 'pc6')
add_index('bag', 'pand_id')
add_index('bag', 'buurt_id')
def cbs():
# Include a tuple with (table_id, typed_data_set),
# with the second value indicating whether you want the
# table to not 'expand' IDs (such as Buurten en Wijken, this
# can be helpful when you need the raw buurt_id)
cbs_tables = [
# Energieverbruik particuliere woningen; woningtype, wijken en buurten, 2018
# https://opendata.cbs.nl/statline/portal.html?_la=nl&_catalog=CBS&tableId=84585NED&_theme=279
("84585NED", False),
# Woningen; hoofdverwarmings; buurt 2019
# https://opendata.cbs.nl/statline/portal.html?_la=nl&_catalog=CBS&tableId=84983NED&_theme=126
("84983NED", True),
# Kerncijfers wijken en buurten 2020
# https://opendata.cbs.nl/portal.html?_la=nl&_catalog=CBS&tableId=84799NED&_theme=235
("84799NED", False),
# Kerncijfers wijken en buurten 2019
# https://opendata.cbs.nl/portal.html?_la=nl&_catalog=CBS&tableId=84583NED&_theme=235
("84583NED", False),
# Woonplaatsen in Nederland 2020
# https://www.cbs.nl/nl-nl/cijfers/detail/84734NED
("84734NED", False),
# Elektriciteitslevering vanuit het openbare net; woningkenmerken, bewoners
# https://opendata.cbs.nl/#/CBS/nl/dataset/83882NED/table?ts=1622540319896
("83882NED", False),
# Aardgaslevering vanuit het openbare net; woningkenmerken
# https://opendata.cbs.nl/#/CBS/nl/dataset/83878NED/table
("83878NED", False)
]
for table in cbs_tables:
load_cbs_table(table[0], typed_data_set=table[1])
print('Renaming columns...')
column_renames = [
# Format: (table_name, col_name, new_col_name)
('cbs_84799ned_kerncijfers_wijken_en_buurten_2020', 'codering',
'area_code'),
('cbs_84983ned_woningen_hoofdverwarmings_buurt_2019_typed',
'wijken_en_buurten', 'area_code')
]
for rename in column_renames:
rename_column(*rename)
print('Creating indexes...')
indexes = [('cbs_84799ned_kerncijfers_wijken_en_buurten_2020',
'area_code'),
('cbs_84983ned_woningen_hoofdverwarmings_buurt_2019_typed',
'area_code')]
for index in indexes:
add_index(*index)
print('Transforming data...')
col_type = get_column_type('cbs_83878ned_aardgaslevering_woningkenmerken',
'energielabelklasse')
if col_type != 'energy_label_class':
delete_statement = '''
DELETE FROM cbs_83878ned_aardgaslevering_woningkenmerken
WHERE energielabelklasse = 'Totaal'
'''
change_to_null_statement = '''
UPDATE cbs_83878ned_aardgaslevering_woningkenmerken
SET energielabelklasse = NULL
WHERE energielabelklasse = 'Geen label'
'''
alter_type_statement = '''
ALTER TABLE cbs_83878ned_aardgaslevering_woningkenmerken
ALTER COLUMN energielabelklasse
TYPE energy_label_class
USING LEFT(energielabelklasse, 1)::energy_label_class
'''
execute(delete_statement)
execute(change_to_null_statement)
execute(alter_type_statement)