from pathlib import Path
import codecs
import configparser
import csv
import datetime
import logging
import os
import shutil
import subprocess
import sys
import psycopg2
from egon.data import db, logger
from egon.data.config import settings
from egon.data.datasets import Dataset, DatasetSources, DatasetTargets
from egon.data.datasets.osmtgmod.substation import extract
from egon.data.datasets.scenario_parameters import get_sector_parameters
import egon.data.config
import egon.data.subprocess as subproc
[docs]
def run():
sys.setrecursionlimit(5000)
# execute osmTGmod
if settings()["egon-data"]["--dataset-boundary"] == "Everything":
target_path = Osmtgmod.sources.files["file"]
else:
target_path = Osmtgmod.sources.files["file_testmode"]
filtered_osm_pbf_path_to_file = os.path.join(
egon.data.__path__[0], "datasets", "osm", target_path
)
docker_db_config = db.credentials()
osmtgmod(
config_database=docker_db_config["POSTGRES_DB"],
config_basepath="osmTGmod/egon-data",
config_continue_run=False,
filtered_osm_pbf_path_to_file=filtered_osm_pbf_path_to_file,
docker_db_config=docker_db_config,
)
[docs]
def import_osm_data():
osmtgmod_repos = Path(".") / "osmTGmod"
# Delete repository if it already exists
if osmtgmod_repos.exists() and osmtgmod_repos.is_dir():
try:
status = subprocess.check_output(
["git", "status"], cwd=(osmtgmod_repos).absolute()
)
if status.startswith(
b"Auf Branch features/egon"
) or status.startswith(b"On branch features/egon"):
logger.info("OsmTGmod cloned and right branch checked out.")
else:
subproc.run(
[
"git",
"checkout",
"features/egon",
]
)
except subprocess.CalledProcessError:
shutil.rmtree(osmtgmod_repos)
subproc.run(
[
"git",
"clone",
"--single-branch",
"--branch",
"features/egon",
"https://github.com/openego/osmTGmod.git",
]
)
else:
subproc.run(
[
"git",
"clone",
"--single-branch",
"--branch",
"features/egon",
"https://github.com/openego/osmTGmod.git",
]
)
if settings()["egon-data"]["--dataset-boundary"] == "Everything":
target_path = Osmtgmod.sources.files["file"]
else:
target_path = Osmtgmod.sources.files["file_testmode"]
filtered_osm_pbf_path_to_file = Path(".") / "openstreetmap" / target_path
docker_db_config = db.credentials()
config_database = docker_db_config["POSTGRES_DB"]
config_basepath = "osmTGmod/egon-data"
config = configparser.ConfigParser()
config.read(config_basepath + ".cfg")
config["postgres_server"]["host"] = docker_db_config["HOST"]
config["postgres_server"]["port"] = docker_db_config["PORT"]
config["postgres_server"]["user"] = docker_db_config["POSTGRES_USER"]
config["postgres_server"]["password"] = docker_db_config[
"POSTGRES_PASSWORD"
]
logging.info("Creating status table ...")
db.execute_sql("""
DROP TABLE IF EXISTS _db_status;
CREATE TABLE _db_status (module TEXT, status BOOLEAN);
INSERT INTO _db_status (module, status) VALUES ('grid_model', FALSE);
""")
logging.info("Status table created.")
# egon-specific, in order to not fill up the results schema,
# it is dropped before creation
logging.info("Dropping osmtgmod_results schema if exists")
db.execute_sql("DROP SCHEMA IF EXISTS osmtgmod_results CASCADE;")
logging.info("Loading functions and result schema ...")
scripts = [
"sql-scripts/extensions.sql",
"sql-scripts/functions.sql",
"sql-scripts/admin_boundaries.sql",
"sql-scripts/electrical_properties.sql",
"sql-scripts/build_up_db.sql",
]
for script in scripts:
logging.info("Running script {0} ...".format(script))
with codecs.open(
os.path.join("osmTGmod", script), "r", "utf-8-sig"
) as fd:
sqlfile = fd.read()
db.execute_sql(sqlfile)
logging.info("Done.")
db.execute_sql("""UPDATE _db_status SET status = TRUE
WHERE module = 'grid_model'; """)
logging.info("osmTGmod-database successfully built up!")
logging.info("Importing OSM-data to database.")
logging.info("Using pdf file: {}".format(filtered_osm_pbf_path_to_file))
logging.info(f"""Assuming osmosis is avaliable at
{config['osm_data']['osmosis_path_to_binary']}""")
# create directory to store osmosis' temp files
osmosis_temp_dir = Path("osmTGmod") / "osmosis_temp/"
if not os.path.exists(osmosis_temp_dir):
os.mkdir(osmosis_temp_dir)
subproc.run(
"JAVACMD_OPTIONS='%s' %s --read-pbf %s --write-pgsql \
database=%s host=%s user=%s password=%s"
% (
f"-Djava.io.tmpdir={osmosis_temp_dir}",
os.path.join(
"osmTGmod", config["osm_data"]["osmosis_path_to_binary"]
),
filtered_osm_pbf_path_to_file,
config_database,
config["postgres_server"]["host"]
+ ":"
+ config["postgres_server"]["port"],
config["postgres_server"]["user"],
config["postgres_server"]["password"],
),
shell=True,
)
logging.info("Importing OSM-Data...")
# After updating OSM-Data, power_tables (for editing)
# have to be updated as well
logging.info("Creating power-tables...")
db.execute_sql("SELECT otg_create_power_tables ();")
# Update OSM Metadata
logging.info("Updating OSM metadata")
v_date = datetime.datetime.now().strftime("%Y-%m-%d")
db.execute_sql(f"UPDATE osm_metadata SET imported = '{v_date}'")
logging.info("OSM data imported to database successfully.")
[docs]
def osmtgmod(
config_database="egon-data",
config_basepath="osmTGmod/egon-data",
config_continue_run=False,
filtered_osm_pbf_path_to_file=None,
docker_db_config=None,
):
if ("germany-21" in filtered_osm_pbf_path_to_file) | (
"germany-22" in filtered_osm_pbf_path_to_file
):
"""
Manually add under construction substation expansion in Garenfeld
to existing substation. (see:)
"""
print("Manually updating geometry of substation in Garenfeld")
db.execute_sql("""DROP TRIGGER IF EXISTS
power_ways_update ON power_ways CASCADE """)
db.execute_sql("""
UPDATE power_ways
SET way = (SELECT ST_SetSRID(ST_AsText(
'0102000000160000001612D5004A081E4020A8644A35B349407B0ACA'
'7E27071E405F23EE563BB34940287CB60E0E061E4055A4C2D842B34940352FE29'
'6EA051E4017940E7B46B34940C0D02346CF051E4042EBE1CB44B34940D67E219A'
'2F051E40FECF06054AB349407F964A442F031E40C2F441F471B34940A8A544676'
'1021E40AB9412CA8FB349409C4848881E021E40B7BA08C691B34940B22D4E1430'
'001E40CE913856BDB34940E2810B122C001E40898CAEAFDBB349402CDAF043480'
'11E40ED678C32F0B349402FE640E25C041E405A86F21AF1B3494061D525C46F04'
'1E40ABEF60C892B34940DC2F9FAC18061E400D33D9E495B349401FD7868A71061'
'E40D2D8A89894B3494083932353F4061E40077360DE88B34940624ED02687071E'
'404F08782D7CB349405000C5C892091E403EFBDBAF4CB349403DDBFEF04E091E4'
'0658D7A8846B349405AD5928E72081E405BE8EF4A37B349401612D5004A081E40'
'20A8644A35B34940'), 4326))
WHERE name = 'Garenfeld'
AND id = 24667346
""")
# ==============================================================
# Setup logging
# ==============================================================
# log = logging.getLogger()
# log.setLevel(logging.INFO)
# logformat = logging.Formatter(
# "%(asctime)s %(message)s", "%m/%d/%Y %H:%M:%S"
# )
# sh = logging.StreamHandler()
# sh.setFormatter(logformat)
# log.addHandler(sh)
# logging.info("\n\n======================\nego_otg\n======================")
# logging.info("Logging to standard output...")
# # catch up some log messages from evaluation of command line arguments
# logging.info("Database: {}".format(config_database))
# logging.info(
# "Path for configuration file and results: {}".format(config_basepath)
# )
# ==============================================================
# read configuration from file and create folder structure
# ==============================================================
logging.info(
(
"Taking db connection credentials from eGon-data "
"with respect to the given docker_db_config variable"
)
)
config = configparser.ConfigParser()
config.read(config_basepath + ".cfg")
config["postgres_server"]["host"] = docker_db_config["HOST"]
config["postgres_server"]["port"] = docker_db_config["PORT"]
config["postgres_server"]["user"] = docker_db_config["POSTGRES_USER"]
config["postgres_server"]["password"] = docker_db_config[
"POSTGRES_PASSWORD"
]
# Setting osmTGmod folder structure:
logging.info("Checking/Creating file directories")
input_data_dir = os.path.join(config_basepath, "input_data")
result_dir = os.path.join(config_basepath, "results")
# Basic folders are created if not existent
if not os.path.exists(input_data_dir):
os.makedirs(input_data_dir)
if not os.path.exists(result_dir):
os.makedirs(result_dir)
# start logging to file
# logfile = os.path.join(config_basepath, config_database + ".log")
# fh = logging.FileHandler(logfile)
# fh.setFormatter(logformat)
# log.addHandler(fh)
# logging.info("Logging to file '{0}' is set up".format(logfile))
# logging.info(
# "Now logging both to standard output and to file '{0}'...".format(
# logfile
# )
# )
logging.info("\n\n======================\nego_otg\n======================")
# copy config file
logging.info(
"Copying configuration file to '{0}'.".format(
os.path.join(config_basepath, config_database + ".cfg")
)
)
os.system(
"cp {0} {1}".format(
config_basepath + ".cfg",
os.path.join(config_basepath, config_database + ".cfg"),
)
)
# Connects to new Database
logging.info("Connecting to database {} ...".format(config_database))
conn = psycopg2.connect(
host=config["postgres_server"]["host"],
port=config["postgres_server"]["port"],
database=config_database,
user=config["postgres_server"]["user"],
password=config["postgres_server"]["password"],
)
cur = conn.cursor()
min_voltage = 110000
if not config_continue_run:
logging.info("Setting min_voltage...")
cur.execute(
"""
UPDATE abstr_values
SET val_int = %s
WHERE val_description = 'min_voltage'""",
(min_voltage,),
)
conn.commit()
logging.info("Setting main_station...")
cur.execute(
"""
UPDATE abstr_values
SET val_int = %s
WHERE val_description = 'main_station'""",
(config.getint("abstraction", "main_station"),),
)
conn.commit()
logging.info("Setting graph_dfs...")
cur.execute(
"""
UPDATE abstr_values
SET val_bool = %s
WHERE val_description = 'graph_dfs'""",
(config.getboolean("abstraction", "graph_dfs"),),
)
conn.commit()
logging.info("Setting conn_subgraphs...")
cur.execute(
"""
UPDATE abstr_values
SET val_bool = %s
WHERE val_description = 'conn_subgraphs'""",
(config.getboolean("abstraction", "conn_subgraphs"),),
)
conn.commit()
logging.info("Setting transfer_busses...")
cur.execute(
"""
UPDATE abstr_values
SET val_bool = %s
WHERE val_description = 'transfer_busses'""",
(config.getboolean("abstraction", "transfer_busses"),),
)
conn.commit()
# setting transfer busses
path_for_transfer_busses = input_data_dir + "/transfer_busses.csv"
logging.info(
"Reading transfer busses from file {} ...".format(
path_for_transfer_busses
)
)
logging.info("Deleting all entries from transfer_busses table ...")
cur.execute("""
DELETE FROM transfer_busses;
""")
conn.commit()
with open(path_for_transfer_busses, "w") as this_file:
cur.copy_expert(
"""COPY transfer_busses_complete to
STDOUT WITH CSV HEADER""",
this_file,
)
conn.commit()
reader = csv.reader(open(path_for_transfer_busses, "r"))
next(reader, None) # Skips header
logging.info("Copying transfer-busses from CSV to database...")
for row in reader:
osm_id = str(row[8])
if osm_id[:1] == "w":
object_type = "way"
elif osm_id[:1] == "n":
object_type = "node"
else:
object_type = None
osm_id_int = int(osm_id[1:])
center_geom = str(row[3])
cur.execute(
"""
INSERT INTO transfer_busses (osm_id, object_type,
center_geom)
VALUES (%s, %s, %s);
""",
(osm_id_int, object_type, center_geom),
)
conn.commit()
logging.info("All transfer busses imported successfully")
# Execute power_script
logging.info(
(
"Preparing execution of abstraction script "
"'sql-scripts/power_script.sql' ..."
)
)
with codecs.open(
"osmTGmod/sql-scripts/power_script.sql", "r", "utf-8-sig"
) as fd:
sqlfile = fd.read()
# remove lines starting with "--" (comments), tabulators and empty line
# beware: comments in C-like style (such as /* comment */) arn't parsed!
sqlfile_without_comments = "".join(
[
(
line.lstrip().split("--")[0] + "\n"
if not line.lstrip().split("--")[0] == ""
else ""
)
for line in sqlfile.split("\n")
]
)
logging.info("Stating execution of power script...")
config_continue_run_at = -1
if not config_continue_run: # debugging - to be removed
cur.execute("""drop table if exists debug;create table debug
(step_before int,max_bus_id int, num_bus int,max_branch_id int,
num_branch int, num_110_bus int, num_220_bus int,
num_380_bus int)""")
conn.commit()
# split sqlfile in commands seperated by ";", while not considering
# symbols for splitting if "escaped" by single quoted strings.
# Drop everything after last semicolon.
for i, command in enumerate(
"'".join(
[
segment.replace(";", "§") if i % 2 == 0 else segment
for i, segment in enumerate(
sqlfile_without_comments.split("'")
)
]
).split("§")[:-1]
):
if i >= config_continue_run_at:
logging.info(
"Executing SQL statement {0}:{1}\n".format(i, command)
)
try:
cur.execute(command)
conn.commit()
except:
logging.exception(
(
"Exception raised with command {0}. "
"Check data and code "
"and restart with 'python ego_otg.py {1} {0}'."
).format(i, config_database)
)
sys.exit()
if i > 16: # debugging - to be removed
cur.execute("""insert into debug values ({0},
(select max(id) from bus_data),(select count(*)
from bus_data),(select max(branch_id)
from branch_data),(select count(*)
from branch_data),(select count(*)
from bus_data where voltage = 110000),
(select count (*) from bus_data where voltage = 220000),
(select count (*)
from bus_data where voltage = 380000))""".format(i))
conn.commit()
logging.info("Power-script executed successfully.")
logging.info("Saving Results...")
cur.execute("SELECT otg_save_results ();")
conn.commit()
logging.info("Abstraction process complete!")
# ==============================================================
# Write results
# ==============================================================
logging.info("Writing results")
tables = ["bus_data", "branch_data", "dcline_data", "results_metadata"]
for table in tables:
logging.info("writing %s..." % table)
filename = os.path.join(result_dir, table + ".csv")
logging.info(
"Writing contents of table {0} to {1}...".format(table, filename)
)
query = "SELECT * FROM osmtgmod_results.%s " % (table,)
outputquery = "COPY ({0}) TO STDOUT WITH DELIMITER \
',' CSV HEADER".format(query)
with open(filename, encoding="utf-8", mode="w") as fh:
cur.copy_expert(outputquery, fh)
logging.info("All tables written!")
logging.info("EXECUTION FINISHED SUCCESSFULLY!")
[docs]
def to_pypsa():
db.execute_sql(f"""
-- CLEAN UP OF TABLES
DELETE FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE carrier = 'AC';
DELETE FROM {Osmtgmod.targets.tables['etrago_line']};
DELETE FROM {Osmtgmod.targets.tables['etrago_transformer']};
""")
# for scenario_name in ["'eGon2035'", "'eGon100RE'", "'status2019'"]:
scenario_list = egon.data.config.settings()["egon-data"]["--scenarios"]
scenario_list = [
f"'{scn}'" if not scn[1] == "'" else scn for scn in scenario_list
]
for scenario_name in scenario_list:
# TODO maybe not needed anymore?
capital_cost = get_sector_parameters(
"electricity", scenario_name.replace("'", "")
)["capital_cost"]
lifetime = get_sector_parameters(
"electricity", scenario_name.replace("'", "")
)["lifetime"]
db.execute_sql(f"""
-- BUS DATA
INSERT INTO {Osmtgmod.targets.tables['etrago_bus']}
(scn_name, bus_id, v_nom, geom, x, y, carrier, country)
SELECT
{scenario_name},
bus_i AS bus_id,
base_kv AS v_nom,
geom,
ST_X(geom) as x,
ST_Y(geom) as y,
'AC' as carrier,
cntr_id
FROM {Osmtgmod.sources.tables['osmtgmod_bus']}
WHERE result_id = 1;
-- BRANCH DATA
INSERT INTO {Osmtgmod.targets.tables['etrago_line']}
(scn_name, line_id, bus0, bus1, x, r, b, s_nom, s_nom_min, s_nom_extendable,
cables, v_nom, geom, topo, carrier)
SELECT
{scenario_name},
branch_id AS line_id,
f_bus AS bus0,
t_bus AS bus1,
br_x AS x, --- change base from 100MVA (osmtgmod) to the its individual s_nom (pypsa)
br_r AS r,
br_b as b,
rate_a as s_nom,
rate_a as s_nom_min,
TRUE,
cables,
branch_voltage/1000 as v_nom,
geom,
topo,
'AC' as carrier
FROM {Osmtgmod.sources.tables['osmtgmod_branch']}
WHERE result_id = 1 AND (link_type = 'line' OR link_type = 'cable');
-- TRANSFORMER DATA
INSERT INTO {Osmtgmod.targets.tables['etrago_transformer']}
(scn_name, trafo_id, bus0, bus1, x, s_nom, s_nom_min, s_nom_extendable, tap_ratio, phase_shift, geom, topo)
SELECT
{scenario_name},
branch_id AS trafo_id,
f_bus AS bus0,
t_bus AS bus1,
br_x/(100 * rate_a) AS x,
rate_a as s_nom,
rate_a as s_nom_min,
TRUE,
tap AS tap_ratio,
shift AS phase_shift,
geom,
topo
FROM {Osmtgmod.sources.tables['osmtgmod_branch']}
WHERE result_id = 1 AND link_type = 'transformer';
-- per unit to absolute values
UPDATE {Osmtgmod.targets.tables['etrago_line']} a
SET
r = r * (((SELECT v_nom
FROM {Osmtgmod.targets.tables['etrago_bus']} b
WHERE bus_id = bus1
AND a.scn_name = b.scn_name)*1000)^2 / (100 * 10^6)),
x = x * (((SELECT v_nom FROM {Osmtgmod.targets.tables['etrago_bus']} b
WHERE bus_id = bus1
AND a.scn_name = b.scn_name)*1000)^2 / (100 * 10^6)),
b = b * (((SELECT v_nom FROM {Osmtgmod.targets.tables['etrago_bus']} b
WHERE bus_id = bus1
AND a.scn_name = b.scn_name)*1000)^2 / (100 * 10^6))
WHERE scn_name = {scenario_name};
-- calculate line length in (km) from geoms
UPDATE {Osmtgmod.targets.tables['etrago_line']} a
SET
length = result.length
FROM (
SELECT b.line_id, ST_Length(b.geom,false)/1000 as length
FROM {Osmtgmod.targets.tables['etrago_line']} b)
as result
WHERE a.line_id = result.line_id
AND scn_name = {scenario_name};
-- set capital costs for eHV-lines
UPDATE {Osmtgmod.targets.tables['etrago_line']}
SET capital_cost = {capital_cost['ac_ehv_overhead_line']} * length
WHERE v_nom > 110
AND scn_name = {scenario_name};
-- set capital costs for HV-lines
UPDATE {Osmtgmod.targets.tables['etrago_line']}
SET capital_cost = {capital_cost['ac_hv_overhead_line']} * length
WHERE v_nom = 110
AND scn_name = {scenario_name};
-- set capital costs for transformers
UPDATE {Osmtgmod.targets.tables['etrago_transformer']} a
SET capital_cost = {capital_cost['transformer_380_220']}
WHERE (a.bus0 IN
(SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 380)
AND a.bus1 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 220))
OR (a.bus0 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 220)
AND a.bus1 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 380))
AND scn_name = {scenario_name};
UPDATE {Osmtgmod.targets.tables['etrago_transformer']} a
SET capital_cost = {capital_cost['transformer_380_110']}
WHERE (a.bus0 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 380)
AND a.bus1 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 110))
OR (a.bus0 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 110)
AND a.bus1 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 380))
AND scn_name = {scenario_name};
UPDATE {Osmtgmod.targets.tables['etrago_transformer']} a
SET capital_cost = {capital_cost['transformer_220_110']}
WHERE (a.bus0 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 220)
AND a.bus1 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 110))
OR (a.bus0 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 110)
AND a.bus1 IN (SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 220))
AND scn_name = {scenario_name};
-- set lifetime for eHV-lines
UPDATE {Osmtgmod.targets.tables['etrago_line']}
SET lifetime = {lifetime['ac_ehv_overhead_line']}
WHERE v_nom > 110 AND scn_name = {scenario_name};
-- set capital costs for HV-lines
UPDATE {Osmtgmod.targets.tables['etrago_line']}
SET lifetime = {lifetime['ac_hv_overhead_line']}
WHERE v_nom = 110 AND scn_name = {scenario_name};
-- set capital costs for transformers
UPDATE {Osmtgmod.targets.tables['etrago_transformer']} a
SET lifetime = {lifetime['transformer_380_220']}
WHERE (a.bus0 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 380)
AND a.bus1 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 220))
OR (a.bus0 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 220)
AND a.bus1 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 380))
AND scn_name = {scenario_name};
UPDATE {Osmtgmod.targets.tables['etrago_transformer']} a
SET lifetime = {lifetime['transformer_380_110']}
WHERE (a.bus0 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 380)
AND a.bus1 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 110))
OR (a.bus0 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 110)
AND a.bus1 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 380))
AND scn_name = {scenario_name};
UPDATE {Osmtgmod.targets.tables['etrago_transformer']} a
SET lifetime = {lifetime['transformer_220_110']}
WHERE (a.bus0 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 220)
AND a.bus1 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 110))
OR (a.bus0 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 110)
AND a.bus1 IN (
SELECT bus_id FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE v_nom = 220))
AND scn_name = {scenario_name};
-- delete buses without connection to AC grid and generation or
-- load assigned
DELETE FROM {Osmtgmod.targets.tables['etrago_bus']}
WHERE scn_name = {scenario_name}
AND carrier = 'AC'
AND bus_id NOT IN
(SELECT bus0 FROM {Osmtgmod.targets.tables['etrago_line']}
WHERE scn_name = {scenario_name})
AND bus_id NOT IN
(SELECT bus1 FROM {Osmtgmod.targets.tables['etrago_line']}
WHERE scn_name = {scenario_name})
AND bus_id NOT IN
(SELECT bus0 FROM {Osmtgmod.targets.tables['etrago_transformer']}
WHERE scn_name = {scenario_name})
AND bus_id NOT IN
(SELECT bus1 FROM {Osmtgmod.targets.tables['etrago_transformer']}
WHERE scn_name = {scenario_name});
""")
[docs]
def reset_etrago_sequences_to_max_id():
"""
Resets all grid.etrago_* sequences so that their next value is one more than
the maximum ID currently stored in their corresponding table.
Example:
- If grid.egon_etrago_line.line_id has max value 123,
then grid.etrago_line_id_seq will be set to start at 124.
Notes
-----
- Assumes each table has an ID column named {component}_id or 'trafo_id' for 'transformer'.
- If a table is empty, sequence is reset to 1.
"""
components = [
"bus",
"line",
"transformer",
"load",
"storage",
"generator",
"link",
"store",
]
for component in components:
# Table and column naming
table_name = f"grid.egon_etrago_{component}"
id_column = (
"trafo_id" if component == "transformer" else f"{component}_id"
)
sequence_name = f"grid.etrago_{component}_id_seq"
# Get max ID from table
query = f"SELECT MAX({id_column}) AS max_id FROM {table_name}"
result = db.select_dataframe(query)
max_id = result["max_id"].iloc[0]
# Determine next value
next_val = int(max_id) + 1 if max_id is not None else 1
# Create and run ALTER SEQUENCE query
alter_query = (
f"ALTER SEQUENCE {sequence_name} RESTART WITH {next_val};"
)
print(f"Resetting {sequence_name} to {next_val}")
db.execute_sql(alter_query)
[docs]
class Osmtgmod(Dataset):
"""
Run the tool osmtgmod to generate transmission grid topology
Executes the tool osmtgmod which create a electricity grid topology based
on OSM data for the voltage levels 110 - 380 kV. For further information
on osmtgmod please refer our `osmtgmod fork. <https://github.com/openego/osmTGmod>`_
Standard electrical line parameters are added to the grid topology and
resulting data on buses, lines and transformers are exported to the data
base.
*Dependencies*
* :py:class:`ScenarioParameters <egon.data.datasets.scenario_parameters.ScenarioParameters>`
* :py:class:`EtragoSetup <egon.data.datasets.etrago_setup.EtragoSetup>`
* :py:class:`SubstationExtraction <egon.data.datasets.substation.SubstationExtraction>`
* :py:class:`OpenStreetMap <egon.data.datasets.osm.OpenStreetMap>`
*Resulting tables*
* :py:class:`grid.egon_etrago_bus <egon.data.datasets.etrago_setup.EgonPfHvBus>` is extended
* :py:class:`grid.egon_etrago_line <egon.data.datasets.etrago_setup.EgonPfHvLine>` is extended
* :py:class:`grid.egon_etrago_transformer <egon.data.datasets.etrago_setup.EgonPfHvTransformer>` is extended
"""
#:
name: str = "Osmtgmod"
#:
version: str = "0.0.12"
sources = DatasetSources(
files={
"file": "germany-250101.osm.pbf",
"file_testmode": "schleswig-holstein-250101.osm.pbf",
},
tables={
"osmtgmod_bus": "osmtgmod_results.bus_data",
"osmtgmod_branch": "osmtgmod_results.branch_data",
"osmtgmod_dcline_data": "osmtgmod_results.dcline_data",
"osmtgmod_results_meta": "osmtgmod_results.results_metadata",
"ehv_transfer_buses": "grid.egon_ehv_transfer_buses",
"hvmv_transfer_buses": "grid.egon_hvmv_transfer_buses",
},
)
targets = DatasetTargets(
tables={
"etrago_bus": "grid.egon_etrago_bus",
"etrago_line": "grid.egon_etrago_line",
"etrago_transformer": "grid.egon_etrago_transformer",
"ehv_substation": "grid.egon_ehv_substation",
"hvmv_substation": "grid.egon_hvmv_substation",
}
)
def __init__(self, dependencies):
super().__init__(
name=self.name,
version=self.version,
dependencies=dependencies,
tasks=(
import_osm_data,
run,
{
extract,
to_pypsa,
},
reset_etrago_sequences_to_max_id,
fix_transformer_snom,
),
)