data.datasets.osmtgmod.substation.create_tables() - Code Metrics - Inspection of "Fixes/#856 avoid conflicting substation ids" - openego/eGon-data - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — dev (#860)

unknown

created 2022-09-30 10:37 UTC

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

↳ Parent: data.datasets.osmtgmod.substation

Complexity

Conditions

Size

Total Lines	27
Code Lines	10

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	10
dl	0
loc	27
rs	9.9
c	0
b	0
f	0
cc	1
nop	0

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

"""
from geoalchemy2.types import Geometry
from sqlalchemy import Column, Float, Integer, Text
from sqlalchemy.ext.declarative import declarative_base

from egon.data import db

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

openego / eGon-data

Pull Request — dev (#860)

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

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like