def zensus_misc_to_postgres():
"""Import data on buildings, households and apartments to postgres db"""
# Get information from data configuration file
data_config = egon.data.config.datasets()
zensus_misc_processed = data_config["zensus_misc"]["processed"]
zensus_population_processed = data_config["zensus_population"]["processed"]
population_table = (
f"{zensus_population_processed['schema']}"
f".{zensus_population_processed['table']}"
)
# Read database configuration from docker-compose.yml
docker_db_config = db.credentials()
for input_file, table in zensus_misc_processed["path_table_map"].items():
with zipfile.ZipFile(os.path.join(
os.path.dirname(__file__), input_file)) as zf:
csvfiles = [n for n in zf.namelist() if n.lower()[-3:] == "csv"]
for filename in csvfiles:
zf.extract(filename)
host = ["-h", f"{docker_db_config['HOST']}"]
port = ["-p", f"{docker_db_config['PORT']}"]
pgdb = ["-d", f"{docker_db_config['POSTGRES_DB']}"]
user = ["-U", f"{docker_db_config['POSTGRES_USER']}"]
command = [
"-c",
rf"\copy {zensus_population_processed['schema']}.{table}"
f"""(grid_id,
grid_id_new,
attribute,
characteristics_code,
characteristics_text,
quantity,
quantity_q)
FROM '{filename}' DELIMITER ','
CSV HEADER
ENCODING 'iso-8859-1';""",
]
subprocess.run(
["psql"] + host + port + pgdb + user + command,
env={"PGPASSWORD": docker_db_config["POSTGRES_PASSWORD"]},
)
os.remove(filename)
db.execute_sql(
f"""UPDATE {zensus_population_processed['schema']}.{table} as b
SET gid_ha = zs.gid
FROM {population_table} zs
WHERE b.grid_id = zs.grid_id;"""
)
db.execute_sql(
f"""ALTER TABLE {zensus_population_processed['schema']}.{table}
ADD CONSTRAINT {table}_fkey
FOREIGN KEY (gid_ha)
REFERENCES {population_table}(gid);"""
)
def insert_society_data():
""" Insert population and number of households per nuts3-region in Germany
according to demandregio using its disaggregator-tool
Returns
-------
None.
"""
cfg = egon.data.config.datasets()['demandregio']['society_data']
engine = db.engine()
for table in cfg['table_names']:
db.execute_sql(
f"DELETE FROM {cfg['schema']}.{cfg['table_names'][table]};")
for year in cfg['target_years']:
df_pop = pd.DataFrame(data.population(year=year))
df_pop['year'] = year
df_pop = df_pop.rename({'value': 'population'}, axis='columns')
df_pop.to_sql(cfg['table_names']['population'],
engine,
schema=cfg['schema'],
if_exists='append')
df_hh = pd.DataFrame(data.households_per_size(year=year))
for hh_size in df_hh.columns:
df = pd.DataFrame(df_hh[hh_size])
df['year'] = year
df['hh_size'] = hh_size
df = df.rename({hh_size: 'households'}, axis='columns')
df.to_sql(cfg['table_names']['household'],
engine,
schema=cfg['schema'],
if_exists='append')
def create_zensus_tables():
"""Create tables for zensus data in postgres database"""
# Get information from data configuration file
data_config = egon.data.config.datasets()
zensus_population_processed = data_config["zensus_population"]["processed"]
zensus_misc_processed = data_config["zensus_misc"]["processed"]
# Create target schema
db.execute_sql(
f"CREATE SCHEMA IF NOT EXISTS {zensus_population_processed['schema']};"
)
# Create table for population data
population_table = (
f"{zensus_population_processed['schema']}"
f".{zensus_population_processed['table']}"
)
db.execute_sql(f"DROP TABLE IF EXISTS {population_table} CASCADE;")
db.execute_sql(
f"CREATE TABLE {population_table}"
f""" (gid SERIAL NOT NULL,
grid_id character varying(254) NOT NULL,
x_mp int,
y_mp int,
population smallint,
geom_point geometry(Point,3035),
geom geometry (Polygon, 3035),
CONSTRAINT {zensus_population_processed['table']}_pkey
PRIMARY KEY (gid)
);
"""
)
# Create tables for household, apartment and building
for table in zensus_misc_processed["path_table_map"].values():
misc_table = f"{zensus_misc_processed['schema']}.{table}"
db.execute_sql(f"DROP TABLE IF EXISTS {misc_table} CASCADE;")
db.execute_sql(
f"CREATE TABLE {misc_table}"
f""" (id SERIAL,
grid_id VARCHAR(50),
grid_id_new VARCHAR (50),
attribute VARCHAR(50),
characteristics_code smallint,
characteristics_text text,
quantity smallint,
quantity_q smallint,
gid_ha int,
CONSTRAINT {table}_pkey PRIMARY KEY (id)
);
"""
)
def create_tables():
"""Create tables for demandregio data
Returns
-------
None.
"""
cfg = egon.data.config.datasets()['demandregio']
db.execute_sql(
f"CREATE SCHEMA IF NOT EXISTS {cfg['demand_data']['schema']};")
db.execute_sql(
f"CREATE SCHEMA IF NOT EXISTS {cfg['society_data']['schema']};")
engine = db.engine()
EgonDemandRegioHH.__table__.create(bind=engine, checkfirst=True)
EgonDemandRegioCtsInd.__table__.create(bind=engine, checkfirst=True)
EgonDemandRegioPopulation.__table__.create(bind=engine, checkfirst=True)
EgonDemandRegioHouseholds.__table__.create(bind=engine, checkfirst=True)
EgonDemandRegioWz.__table__.create(bind=engine, checkfirst=True)
def insert_demands():
""" Insert electricity demands per nuts3-region in Germany according to
demandregio using its disaggregator-tool in MWh
Returns
-------
None.
"""
cfg = egon.data.config.datasets()['demandregio']['demand_data']
engine = db.engine()
for table in cfg['table_names']:
db.execute_sql(
f"DELETE FROM {cfg['schema']}.{cfg['table_names'][table]};")
for scenario in cfg['scenarios'].keys():
year = cfg['scenarios'][scenario]
# Insert demands of private households
insert_hh_demand(scenario, year, engine, cfg)
# Insert demands of CTS and industry
# data only available for years before 2036
if cfg['scenarios'][scenario] > 2035:
year = 2035
# target values per scenario in MWh
target_values = {
# according to NEP 2021
# new consumers will be added seperatly
'eGon2035': {
'CTS': 135300,
'industry': 225400
},
# CTS: reduce overall demand from demandregio (without traffic)
# by share of heat according to JRC IDEES, data from 2011
# industry: no specific heat demand, use data from demandregio
'eGon100RE': {
'CTS': (1 - (5.96 + 6.13) / 154.64) * 125183.403
}
}
insert_cts_ind_demand(scenario, year, engine, target_values, cfg)
def modify_tables():
"""Adjust primary keys, indices and schema of OSM tables.
* The Column "gid" is added and used as the new primary key.
* Indices (GIST, GIN) are reset
* The tables are moved to the schema configured as the "output_schema".
"""
# Get dataset config
data_config = egon.data.config.datasets()["openstreetmap"]
# Replace indices and primary keys
for table in [
f"{data_config['original_data']['target']['table_prefix']}_" +
suffix for suffix in ["line", "point", "polygon", "roads"]
]:
# Drop indices
sql_statements = [f"DROP INDEX IF EXISTS {table}_index;"]
# Drop primary keys
sql_statements.append(f"DROP INDEX IF EXISTS {table}_pkey;")
# Add primary key on newly created column "gid"
sql_statements.append(f"ALTER TABLE public.{table} ADD gid SERIAL;")
sql_statements.append(
f"ALTER TABLE public.{table} ADD PRIMARY KEY (gid);")
sql_statements.append(
f"ALTER TABLE public.{table} RENAME COLUMN way TO geom;")
# Add indices (GIST and GIN)
sql_statements.append(
f"CREATE INDEX {table}_geom_idx ON public.{table} "
f"USING gist (geom);")
sql_statements.append(
f"CREATE INDEX {table}_tags_idx ON public.{table} "
f"USING GIN (tags);")
# Execute collected SQL statements
for statement in sql_statements:
db.execute_sql(statement)
# Move table to schema "openstreetmap"
db.execute_sql(
f"CREATE SCHEMA IF NOT EXISTS {data_config['processed']['schema']};")
for out_table in data_config["processed"]["tables"]:
db.execute_sql(f"DROP TABLE IF EXISTS "
f"{data_config['processed']['schema']}.{out_table};")
sql_statement = (f"ALTER TABLE public.{out_table} "
f"SET SCHEMA {data_config['processed']['schema']};")
db.execute_sql(sql_statement)
def to_postgres():
# Get information from data configuraiton file
data_config = egon.data.config.datasets()
vg250_orig = data_config["vg250"]["original_data"]
vg250_processed = data_config["vg250"]["processed"]
# Create target schema
db.execute_sql(f"CREATE SCHEMA IF NOT EXISTS {vg250_processed['schema']};")
zip_file = os.path.join(os.path.dirname(__file__),
vg250_orig["target"]["path"])
engine_local_db = db.engine()
# Extract shapefiles from zip archive and send it to postgres db
for filename, table in vg250_processed["file_table_map"].items():
# Open files and read .shp (within .zip) with geopandas
data = gpd.read_file(
f"zip://{zip_file}!vg250_01-01.geo84.shape.ebenen/"
f"vg250_ebenen_0101/{filename}")
# Set index column and format column headings
data.index.set_names("gid", inplace=True)
data.columns = [x.lower() for x in data.columns]
# Drop table before inserting data
db.execute_sql(f"DROP TABLE IF EXISTS "
f"{vg250_processed['schema']}.{table} CASCADE;")
# create database table from geopandas dataframe
data.to_postgis(
table,
engine_local_db,
schema=vg250_processed["schema"],
index=True,
if_exists="replace",
dtype={"geometry": Geometry()},
)
db.execute_sql(f"ALTER TABLE {vg250_processed['schema']}.{table} "
f"ADD PRIMARY KEY (gid);")
# Add index on geometry column
db.execute_sql(
f"CREATE INDEX {table}_geometry_idx ON "
f"{vg250_processed['schema']}.{table} USING gist (geometry);")
def population_to_postgres():
"""Import Zensus population data to postgres database"""
# Get information from data configuration file
data_config = egon.data.config.datasets()
zensus_population_orig = data_config["zensus_population"]["original_data"]
zensus_population_processed = data_config["zensus_population"]["processed"]
input_file = os.path.join(
os.path.dirname(__file__), zensus_population_orig["target"]["path"]
)
# Read database configuration from docker-compose.yml
docker_db_config = db.credentials()
population_table = (
f"{zensus_population_processed['schema']}"
f".{zensus_population_processed['table']}"
)
with zipfile.ZipFile(input_file) as zf:
for filename in zf.namelist():
zf.extract(filename)
host = ["-h", f"{docker_db_config['HOST']}"]
port = ["-p", f"{docker_db_config['PORT']}"]
pgdb = ["-d", f"{docker_db_config['POSTGRES_DB']}"]
user = ["-U", f"{docker_db_config['POSTGRES_USER']}"]
command = [
"-c",
rf"\copy {population_table} (grid_id, x_mp, y_mp, population)"
rf" FROM '{filename}' DELIMITER ';' CSV HEADER;",
]
subprocess.run(
["psql"] + host + port + pgdb + user + command,
env={"PGPASSWORD": docker_db_config["POSTGRES_PASSWORD"]},
)
os.remove(filename)
db.execute_sql(
f"UPDATE {population_table} zs"
" SET geom_point=ST_SetSRID(ST_MakePoint(zs.x_mp, zs.y_mp), 3035);"
)
db.execute_sql(
f"UPDATE {population_table} zs"
""" SET geom=ST_SetSRID(
(ST_MakeEnvelope(zs.x_mp-50,zs.y_mp-50,zs.x_mp+50,zs.y_mp+50)),
3035
);
"""
)
db.execute_sql(
f"CREATE INDEX {zensus_population_processed['table']}_geom_idx ON"
f" {population_table} USING gist (geom);"
)
db.execute_sql(
f"CREATE INDEX"
f" {zensus_population_processed['table']}_geom_point_idx"
f" ON {population_table} USING gist (geom_point);"
)
