data.datasets.osmtgmod.substation.extract() - Code Metrics - Inspection of "Features/#684 distribute pv rooftop buildings 2" - openego/eGon-data - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — dev (#809)

unknown

created 2022-08-24 07:58 UTC

data.datasets.osmtgmod.substation.extract() A

↳ Parent: data.datasets.osmtgmod.substation

Complexity

Conditions

Size

Total Lines	43
Code Lines	6

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	6
dl	0
loc	43
rs	10
c	0
b	0
f	0
cc	1
nop	0

"""The central module containing code to create substation tables

"""
from airflow.operators.postgres_operator import PostgresOperator
from geoalchemy2.types import Geometry
from sqlalchemy import Column, Float, Integer, Sequence, Text
from sqlalchemy.ext.declarative import declarative_base
import importlib_resources as resources

from egon.data import db
from egon.data.datasets import Dataset
import egon.data.config

Base = declarative_base()


class EgonEhvSubstation(Base):

    __tablename__ = "egon_ehv_substation"
    __table_args__ = {"schema": "grid"}
    bus_id = Column(
        Integer,
        primary_key=True,
    )
    lon = Column(Float(53))
    lat = Column(Float(53))
    point = Column(Geometry("POINT", 4326), index=True)
    polygon = Column(Geometry)
    voltage = Column(Text)
    power_type = Column(Text)
    substation = Column(Text)
    osm_id = Column(Text)
    osm_www = Column(Text)
    frequency = Column(Text)
    subst_name = Column(Text)
    ref = Column(Text)
    operator = Column(Text)
    dbahn = Column(Text)
    status = Column(Integer)


class EgonHvmvSubstation(Base):

    __tablename__ = "egon_hvmv_substation"
    __table_args__ = {"schema": "grid"}
    bus_id = Column(
        Integer,
        primary_key=True,
    )
    lon = Column(Float(53))
    lat = Column(Float(53))
    point = Column(Geometry("POINT", 4326), index=True)
    polygon = Column(Geometry)
    voltage = Column(Text)
    power_type = Column(Text)
    substation = Column(Text)
    osm_id = Column(Text)
    osm_www = Column(Text)
    frequency = Column(Text)
    subst_name = Column(Text)
    ref = Column(Text)
    operator = Column(Text)
    dbahn = Column(Text)
    status = Column(Integer)


def create_tables():
    """Create tables for substation data
    Returns
    -------
    None.
    """

    db.execute_sql(
        f"CREATE SCHEMA IF NOT EXISTS {EgonHvmvSubstation.__table__.schema};"
    )

    # Drop tables
    db.execute_sql(
        f"""DROP TABLE IF EXISTS
            {EgonEhvSubstation.__table__.schema}.
            {EgonEhvSubstation.__table__.name} CASCADE;"""
    )

    db.execute_sql(
        f"""DROP TABLE IF EXISTS
            {EgonHvmvSubstation.__table__.schema}.
            {EgonHvmvSubstation.__table__.name} CASCADE;"""
    )

    engine = db.engine()
    EgonEhvSubstation.__table__.create(bind=engine, checkfirst=True)
    EgonHvmvSubstation.__table__.create(bind=engine, checkfirst=True)


def extract():
    """
    Extract ehv and hvmv substation from transfer buses and results from osmtgmod

    Returns
    -------
    None.

    """
    # Create tables for substations
    create_tables()

    # Extract eHV substations
    db.execute_sql(
        f"""
        INSERT INTO {EgonEhvSubstation.__table__.schema}.{EgonEhvSubstation.__table__.name}
        
        SELECT * FROM grid.egon_ehv_transfer_buses;
        
        
        -- update ehv_substation table with new column of respective osmtgmod bus_i
        ALTER TABLE {EgonEhvSubstation.__table__.schema}.{EgonEhvSubstation.__table__.name}
        	ADD COLUMN otg_id bigint;

        -- fill table with bus_i from osmtgmod
        UPDATE {EgonEhvSubstation.__table__.schema}.{EgonEhvSubstation.__table__.name}
        	SET otg_id = osmtgmod_results.bus_data.bus_i
        	FROM osmtgmod_results.bus_data
        	WHERE osmtgmod_results.bus_data.base_kv > 110 AND(SELECT TRIM(leading 'n' FROM TRIM(leading 'w' FROM TRIM(leading 'r' FROM grid.egon_ehv_substation.osm_id)))::BIGINT)=osmtgmod_results.bus_data.osm_substation_id; 

        DELETE FROM {EgonEhvSubstation.__table__.schema}.{EgonEhvSubstation.__table__.name} WHERE otg_id IS NULL;

        UPDATE {EgonEhvSubstation.__table__.schema}.{EgonEhvSubstation.__table__.name}
        	SET 	bus_id = otg_id;

        ALTER TABLE {EgonEhvSubstation.__table__.schema}.{EgonEhvSubstation.__table__.name}
        	DROP COLUMN otg_id;
        """
    )

    # Extract HVMV substations
    db.execute_sql(
        f"""
        INSERT INTO {EgonHvmvSubstation.__table__.schema}.{EgonHvmvSubstation.__table__.name}
        
        SELECT * FROM grid.egon_hvmv_transfer_buses;
        
        
        ALTER TABLE {EgonHvmvSubstation.__table__.schema}.{EgonHvmvSubstation.__table__.name}
        	ADD COLUMN otg_id bigint;
        
        -- fill table with bus_i from osmtgmod
        UPDATE {EgonHvmvSubstation.__table__.schema}.{EgonHvmvSubstation.__table__.name}
        	SET 	otg_id = osmtgmod_results.bus_data.bus_i
        	FROM 	osmtgmod_results.bus_data
        	WHERE 	osmtgmod_results.bus_data.base_kv <= 110 AND (SELECT TRIM(leading 'n' FROM TRIM(leading 'w' FROM grid.egon_hvmv_substation.osm_id))::BIGINT)=osmtgmod_results.bus_data.osm_substation_id; 
        
        DELETE FROM {EgonHvmvSubstation.__table__.schema}.{EgonHvmvSubstation.__table__.name} WHERE otg_id IS NULL;
        
        UPDATE {EgonHvmvSubstation.__table__.schema}.{EgonHvmvSubstation.__table__.name}
        	SET 	bus_id = otg_id;
        
        ALTER TABLE {EgonHvmvSubstation.__table__.schema}.{EgonHvmvSubstation.__table__.name}
        	DROP COLUMN otg_id;
        """
    )


1		"""The central module containing code to create substation tables
2
3		"""
4		from airflow.operators.postgres_operator import PostgresOperator
5		from geoalchemy2.types import Geometry
6		from sqlalchemy import Column, Float, Integer, Sequence, Text
7		from sqlalchemy.ext.declarative import declarative_base
8		import importlib_resources as resources
9
10		from egon.data import db
11		from egon.data.datasets import Dataset
12		import egon.data.config
13
14		Base = declarative_base()
15
16
17	View Code Duplication	class EgonEhvSubstation(Base):
		0 ignored issues – show Duplication introduced 2022-08-02 11:37 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
18		__tablename__ = "egon_ehv_substation"
19		__table_args__ = {"schema": "grid"}
20		bus_id = Column(
21		Integer,
22		primary_key=True,
23		)
24		lon = Column(Float(53))
25		lat = Column(Float(53))
26		point = Column(Geometry("POINT", 4326), index=True)
27		polygon = Column(Geometry)
28		voltage = Column(Text)
29		power_type = Column(Text)
30		substation = Column(Text)
31		osm_id = Column(Text)
32		osm_www = Column(Text)
33		frequency = Column(Text)
34		subst_name = Column(Text)
35		ref = Column(Text)
36		operator = Column(Text)
37		dbahn = Column(Text)
38		status = Column(Integer)
39
40
41	View Code Duplication	class EgonHvmvSubstation(Base):
		0 ignored issues – show Duplication introduced 2022-08-02 11:37 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
42		__tablename__ = "egon_hvmv_substation"
43		__table_args__ = {"schema": "grid"}
44		bus_id = Column(
45		Integer,
46		primary_key=True,
47		)
48		lon = Column(Float(53))
49		lat = Column(Float(53))
50		point = Column(Geometry("POINT", 4326), index=True)
51		polygon = Column(Geometry)
52		voltage = Column(Text)
53		power_type = Column(Text)
54		substation = Column(Text)
55		osm_id = Column(Text)
56		osm_www = Column(Text)
57		frequency = Column(Text)
58		subst_name = Column(Text)
59		ref = Column(Text)
60		operator = Column(Text)
61		dbahn = Column(Text)
62		status = Column(Integer)
63
64
65		def create_tables():
66		"""Create tables for substation data
67		Returns
68		-------
69		None.
70		"""
71
72		db.execute_sql(
73		f"CREATE SCHEMA IF NOT EXISTS {EgonHvmvSubstation.__table__.schema};"
74		)
75
76		# Drop tables
77		db.execute_sql(
78		f"""DROP TABLE IF EXISTS
79		{EgonEhvSubstation.__table__.schema}.
80		{EgonEhvSubstation.__table__.name} CASCADE;"""
81		)
82
83		db.execute_sql(
84		f"""DROP TABLE IF EXISTS
85		{EgonHvmvSubstation.__table__.schema}.
86		{EgonHvmvSubstation.__table__.name} CASCADE;"""
87		)
88
89		engine = db.engine()
90		EgonEhvSubstation.__table__.create(bind=engine, checkfirst=True)
91		EgonHvmvSubstation.__table__.create(bind=engine, checkfirst=True)
92
93
94		def extract():
95		"""
96		Extract ehv and hvmv substation from transfer buses and results from osmtgmod
97
98		Returns
99		-------
100		None.
101
102		"""
103		# Create tables for substations
104		create_tables()
105
106		# Extract eHV substations
107		db.execute_sql(
108		f"""
109		INSERT INTO {EgonEhvSubstation.__table__.schema}.{EgonEhvSubstation.__table__.name}
110
111		SELECT * FROM grid.egon_ehv_transfer_buses;
112
113
114		-- update ehv_substation table with new column of respective osmtgmod bus_i
115		ALTER TABLE {EgonEhvSubstation.__table__.schema}.{EgonEhvSubstation.__table__.name}
116		ADD COLUMN otg_id bigint;
117
118		-- fill table with bus_i from osmtgmod
119		UPDATE {EgonEhvSubstation.__table__.schema}.{EgonEhvSubstation.__table__.name}
120		SET otg_id = osmtgmod_results.bus_data.bus_i
121		FROM osmtgmod_results.bus_data
122		WHERE osmtgmod_results.bus_data.base_kv > 110 AND(SELECT TRIM(leading 'n' FROM TRIM(leading 'w' FROM TRIM(leading 'r' FROM grid.egon_ehv_substation.osm_id)))::BIGINT)=osmtgmod_results.bus_data.osm_substation_id;
123
124		DELETE FROM {EgonEhvSubstation.__table__.schema}.{EgonEhvSubstation.__table__.name} WHERE otg_id IS NULL;
125
126		UPDATE {EgonEhvSubstation.__table__.schema}.{EgonEhvSubstation.__table__.name}
127		SET bus_id = otg_id;
128
129		ALTER TABLE {EgonEhvSubstation.__table__.schema}.{EgonEhvSubstation.__table__.name}
130		DROP COLUMN otg_id;
131		"""
132		)
133
134		# Extract HVMV substations
135		db.execute_sql(
136		f"""
137		INSERT INTO {EgonHvmvSubstation.__table__.schema}.{EgonHvmvSubstation.__table__.name}
138
139		SELECT * FROM grid.egon_hvmv_transfer_buses;
140
141
142		ALTER TABLE {EgonHvmvSubstation.__table__.schema}.{EgonHvmvSubstation.__table__.name}
143		ADD COLUMN otg_id bigint;
144
145		-- fill table with bus_i from osmtgmod
146		UPDATE {EgonHvmvSubstation.__table__.schema}.{EgonHvmvSubstation.__table__.name}
147		SET otg_id = osmtgmod_results.bus_data.bus_i
148		FROM osmtgmod_results.bus_data
149		WHERE osmtgmod_results.bus_data.base_kv <= 110 AND (SELECT TRIM(leading 'n' FROM TRIM(leading 'w' FROM grid.egon_hvmv_substation.osm_id))::BIGINT)=osmtgmod_results.bus_data.osm_substation_id;
150
151		DELETE FROM {EgonHvmvSubstation.__table__.schema}.{EgonHvmvSubstation.__table__.name} WHERE otg_id IS NULL;
152
153		UPDATE {EgonHvmvSubstation.__table__.schema}.{EgonHvmvSubstation.__table__.name}
154		SET bus_id = otg_id;
155
156		ALTER TABLE {EgonHvmvSubstation.__table__.schema}.{EgonHvmvSubstation.__table__.name}
157		DROP COLUMN otg_id;
158		"""
159		)
160

openego / eGon-data

Pull Request — dev (#809)

data.datasets.osmtgmod.substation.extract() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like