Collect eGoDP data using oedialect¶

Repository: https://github.com/openego/oedialect

Please report bugs and improvements here: https://github.com/OpenEnergyPlatform/oedialect/issues
Using SAIO functions to setup SqlAlchemy classes: https://github.com/coroa/saio

In [1]:

__copyright__ = "Reiner Lemoine Institut"
__license__   = "GNU Affero General Public License Version 3 (AGPL-3.0)"
__url__       = "https://github.com/openego/data_processing/blob/master/LICENSE"
__author__    = "Ludee"

__copyright__ = "Reiner Lemoine Institut" __license__ = "GNU Affero General Public License Version 3 (AGPL-3.0)" __url__ = "https://github.com/openego/data_processing/blob/master/LICENSE" __author__ = "Ludee"

In [2]:

import oedialect
import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker

import pandas as pd
import numpy as np
import getpass

import matplotlib.pyplot as plt
from matplotlib import collections
import geopandas as gpd

%matplotlib inline

import oedialect import sqlalchemy as sa from sqlalchemy.orm import sessionmaker import pandas as pd import numpy as np import getpass import matplotlib.pyplot as plt from matplotlib import collections import geopandas as gpd %matplotlib inline

Connection to OEP¶

If we want to upload data to the OEP we first need to connect to it, using our OEP user name and token. Note: You ca view your token on your OEP profile page after logging in.

In [3]:

# White spaces in the username are fine!
user = input('Enter OEP-username:')
token = getpass.getpass('Token:')

# White spaces in the username are fine! user = input('Enter OEP-username:') token = getpass.getpass('Token:')

Enter OEP-username:Ludee
Token:········

Now we'll create an sql-alchemy-engine. The engine is what 'speaks' oedialect to the data base api. We need to tell it where the data base is and pass our credentials.

In [4]:

# Create Engine:
oep_url = 'openenergy-platform.org' #'193.175.187.164' #'openenergy-platform.org'
oed = f'postgresql+oedialect://{user}:{token}@{oep_url}'

engine = sa.create_engine(oed)
metadata = sa.MetaData(bind=engine)
print(metadata)

# Create Engine: oep_url = 'openenergy-platform.org' #'193.175.187.164' #'openenergy-platform.org' oed = f'postgresql+oedialect://{user}:{token}@{oep_url}' engine = sa.create_engine(oed) metadata = sa.MetaData(bind=engine) print(metadata)

MetaData(bind=Engine(postgresql+oedialect://Ludee:***@openenergy-platform.org))

Setup ORM¶

In [23]:

import saio
saio.register_schema("boundaries", engine)
saio.register_schema("grid", engine)
saio.register_schema("supply", engine)

import saio saio.register_schema("boundaries", engine) saio.register_schema("grid", engine) saio.register_schema("supply", engine)

In [31]:

# Included in SAIO
# from sqlalchemy.ext.declarative import declarative_base
# Base = declarative_base()
# metadata = Base.metadata
# metadata.bind=engine

# Included in SAIO # from sqlalchemy.ext.declarative import declarative_base # Base = declarative_base() # metadata = Base.metadata # metadata.bind=engine

In [ ]:

Get data¶

Bundesländer¶

In [7]:

from saio.boundaries import bkg_vg250_2_lan as BkgVg250Lan

from saio.boundaries import bkg_vg250_2_lan as BkgVg250Lan

In [8]:

Session = sessionmaker(bind=engine)
session = Session()
try:
    df_lan = saio.as_pandas(session.query(BkgVg250Lan))
    session.commit()
    print('Querry successful!')
except Exception as e:
    session.rollback()
    raise
    print('Querry incomplete!')
finally:
    session.close()

Session = sessionmaker(bind=engine) session = Session() try: df_lan = saio.as_pandas(session.query(BkgVg250Lan)) session.commit() print('Querry successful!') except Exception as e: session.rollback() raise print('Querry incomplete!') finally: session.close()

Querry successful!

In [9]:

ax = df_lan.plot(figsize=(5, 5), color='white', edgecolor='black')

ax = df_lan.plot(figsize=(5, 5), color='white', edgecolor='black')

In [ ]:

Gemeinden¶

In [10]:

from saio.boundaries import bkg_vg250_6_gem as BkgVg250Gem
from saio.boundaries import bkg_vg250_4_krs as BkgVg250Krs

from saio.boundaries import bkg_vg250_6_gem as BkgVg250Gem from saio.boundaries import bkg_vg250_4_krs as BkgVg250Krs

In [11]:

Session = sessionmaker(bind=engine)
session = Session()
try:
    df_krs = saio.as_pandas(session.query(BkgVg250Krs))
    session.commit()
    print('Querry successful!')
except Exception as e:
    session.rollback()
    raise
    print('Querry incomplete!')
finally:
    session.close()

Session = sessionmaker(bind=engine) session = Session() try: df_krs = saio.as_pandas(session.query(BkgVg250Krs)) session.commit() print('Querry successful!') except Exception as e: session.rollback() raise print('Querry incomplete!') finally: session.close()

Querry successful!

In [19]:

ax = df_lan.plot(figsize=(5, 5), color='white', edgecolor='black')
df_krs.plot(ax=ax, alpha=0.5, color='white', edgecolor='grey')

ax = df_lan.plot(figsize=(5, 5), color='white', edgecolor='black') df_krs.plot(ax=ax, alpha=0.5, color='white', edgecolor='grey')

Out[19]:

<matplotlib.axes._subplots.AxesSubplot at 0x1fa000a69e8>

In [ ]:

eGoDP - EHV¶

In [13]:

from saio.grid import ego_dp_ehv_substation as eGoEHVSub
from saio.grid import ego_dp_ehv_griddistrict as eGoEHVGD

from saio.grid import ego_dp_ehv_substation as eGoEHVSub from saio.grid import ego_dp_ehv_griddistrict as eGoEHVGD

In [14]:

Session = sessionmaker(bind=engine)
session = Session()
try:
    df_ehvsub = saio.as_pandas(session.query(eGoEHVSub), geometry='point')
    df_ehvgd = saio.as_pandas(session.query(eGoEHVGD))
    session.commit()
    print('Querry successful!')
except Exception as e:
    session.rollback()
    raise
    print('Querry incomplete!')
finally:
    session.close()

Session = sessionmaker(bind=engine) session = Session() try: df_ehvsub = saio.as_pandas(session.query(eGoEHVSub), geometry='point') df_ehvgd = saio.as_pandas(session.query(eGoEHVGD)) session.commit() print('Querry successful!') except Exception as e: session.rollback() raise print('Querry incomplete!') finally: session.close()

Querry successful!

In [15]:

fig, ax = plt.subplots(1, figsize=(20, 20))
ax.set_aspect('equal')
ax.set_axis_off()
ax.set_title('open_eGo - EHV')

df_ehvgd.to_crs({'init': 'epsg:3035'}).plot(ax=ax, color='white', edgecolor='grey', linewidth=1)

df_lan.to_crs({'init': 'epsg:3035'}).plot(ax=ax, color='white', alpha=.5, edgecolor='black', linewidth=2)

df_ehvsub.to_crs({'init': 'epsg:3035'}).plot(ax=ax, marker='o', color='orange', markersize=5)

plt.axis('equal')

fig, ax = plt.subplots(1, figsize=(20, 20)) ax.set_aspect('equal') ax.set_axis_off() ax.set_title('open_eGo - EHV') df_ehvgd.to_crs({'init': 'epsg:3035'}).plot(ax=ax, color='white', edgecolor='grey', linewidth=1) df_lan.to_crs({'init': 'epsg:3035'}).plot(ax=ax, color='white', alpha=.5, edgecolor='black', linewidth=2) df_ehvsub.to_crs({'init': 'epsg:3035'}).plot(ax=ax, marker='o', color='orange', markersize=5) plt.axis('equal')

Out[15]:

(3888119.089487874, 4827604.8328158, 2454107.801562896, 3773079.9792611585)