def upload_to_socrata(**kwargs):
from sodapy import Socrata
# make a sodapy Socrata client
s = Socrata("data.detroitmi.gov", Variable.get("SOCRATA_TOKEN"),
Variable.get("SOCRATA_USER"), Variable.get("SOCRATA_PASS"))
# get the SQLAlchemy engine
hook = PostgresHook('etl_postgres')
eng = hook.get_sqlalchemy_engine()
# get the payload
result = eng.execute(f"select * from {kwargs['table']}")
payload = [dict(row) for row in result]
if kwargs['method'] == 'replace':
job = s.replace(kwargs['id'], payload)
else:
chunk_size = 10000
for i in range(0, len(payload), chunk_size):
try:
r = s.upsert(kwargs['id'], payload[i:i + chunk_size])
except:
print(f"Error on record {i}")
r = s.upsert(kwargs['id'], payload[i:i + chunk_size])
def copy_from_local_file(schema,
table,
local_path,
delimiter=',',
encoding='utf-8',
truncate=True,
connection='covid_db_postgres'):
conn = PostgresHook(connection)
conn_engine = conn.get_sqlalchemy_engine()
if encoding != 'utf-8':
print('Starting encoding conversion...')
targetFileName = 'teste.csv'
BLOCKSIZE = 1048576 # or some other, desired size in bytes
with codecs.open(local_path, 'r', encoding) as sourceFile:
with codecs.open(targetFileName, 'w', 'utf-8') as targetFile:
while True:
contents = sourceFile.read(BLOCKSIZE)
if not contents:
break
targetFile.write(contents)
local_path = targetFileName
if truncate:
print('Truncating table...')
conn_engine.execute(f'truncate table {schema}.{table};')
print('Loading table...')
sql = f"COPY {schema}.{table} FROM STDIN DELIMITERS '{delimiter}' csv header encoding 'utf-8'"
conn.copy_expert(sql, filename=local_path)
return f'Table: {table} loaded!'
def create_table_ego_grid_ehv_substation(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
DROP TABLE IF EXISTS model_draft.ego_grid_ehv_substation CASCADE;
CREATE TABLE model_draft.ego_grid_ehv_substation (
subst_id serial NOT NULL,
lon float NOT NULL,
lat float NOT NULL,
point geometry(Point,4326) NOT NULL,
polygon geometry NOT NULL,
voltage text,
power_type text,
substation text,
osm_id text PRIMARY KEY NOT NULL,
osm_www text NOT NULL,
frequency text,
subst_name text,
ref text,
operator text,
dbahn text,
status smallint NOT NULL);
ALTER TABLE model_draft.ego_grid_ehv_substation OWNER TO oeuser;
COMMENT ON TABLE model_draft.ego_grid_ehv_substation IS '{
"comment": "eGoDP - Versioning table",
"version": "v0.4.5" }' ; ''')
def create_view_substation_hoes(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
DROP VIEW IF EXISTS model_draft.substation_hoes CASCADE;
CREATE VIEW model_draft.substation_hoes
AS
SELECT *,
'http://www.osm.org/relation/'|| model_draft.relation_substations_with_hoes.id as osm_www,
'r'|| model_draft.relation_substations_with_hoes.id as osm_id,
'1'::smallint as status
FROM model_draft.relation_substations_with_hoes
UNION
SELECT *,
'http://www.osm.org/way/'|| model_draft.way_substations_with_hoes.id as osm_www,
'w'|| model_draft.way_substations_with_hoes.id as osm_id,
'2'::smallint as status
FROM model_draft.way_substations_with_hoes
UNION
SELECT *,
'http://www.osm.org/node/'|| model_draft.node_substations_with_hoes.id as osm_www,
'n'|| model_draft.node_substations_with_hoes.id as osm_id,
'4'::smallint as status
FROM model_draft.node_substations_with_hoes;
ALTER VIEW model_draft.substation_hoes OWNER TO oeuser; ''')
def create_common_countries_table():
'''
Creates a common country_or_area table from commodities_staging and temperature_staging
'''
table = "country_or_area"
postgres_hook = PostgresHook(postgres_conn_id='postgres', schema='world')
engine = postgres_hook.get_sqlalchemy_engine()
min_year_commodities = postgres_hook.get_first(
"select min(year) from commodities_staging;")[0]
max_year_commodities = postgres_hook.get_first(
"select max(year) from commodities_staging;")[0]
get_countries_from_commodities_staging = "select distinct(country_or_area) from commodities_staging;"
get_countries_from_temperature_staging = f"select distinct(country_or_area) from temperature_staging where year >= {min_year_commodities} and year <= {max_year_commodities};"
commodities_countries_records = postgres_hook.get_records(
get_countries_from_commodities_staging)
temperature_countries_records = postgres_hook.get_records(
get_countries_from_temperature_staging)
commodities_countries_set = set(
reduce(operator.concat, commodities_countries_records))
temperature_countries_set = set(
reduce(operator.concat, temperature_countries_records))
common_country_set = commodities_countries_set.union(
temperature_countries_set)
print(f"common_country_set: {common_country_set}")
country_or_area_df = pd.DataFrame(list(common_country_set),
columns=['country_or_area'])
country_or_area_df.to_sql(table, engine, index=False, if_exists="append")
def drop_create_view_substation_110kv(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
DROP VIEW IF EXISTS model_draft.substation_110kV CASCADE;
CREATE VIEW model_draft.substation_110kV
AS
SELECT *,
'http://www.osm.org/way/'|| model_draft.way_substations_with_110kV.id as osm_www,
'w'|| model_draft.way_substations_with_110kV.id as osm_id,
'1'::smallint as status
FROM model_draft.way_substations_with_110kV
UNION
SELECT *,
'http://www.osm.org/way/'|| model_draft.way_substations_without_110kV_intersected_by_110kV_line.id as osm_www,
'w'|| model_draft.way_substations_without_110kV_intersected_by_110kV_line.id as osm_id,
'2'::smallint as status
FROM model_draft.way_substations_without_110kV_intersected_by_110kV_line
UNION
SELECT *,
'http://www.osm.org/node/'|| model_draft.node_substations_with_110kV.id as osm_www,
'n'|| model_draft.node_substations_with_110kV.id as osm_id,
'3'::smallint as status
FROM model_draft.node_substations_with_110kV;
ALTER VIEW model_draft.substation_110kV OWNER TO oeuser; ''')
def create_view_substations_to_drop_hoes(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
DROP MATERIALIZED VIEW IF EXISTS model_draft.substations_to_drop_hoes CASCADE;
CREATE MATERIALIZED VIEW model_draft.substations_to_drop_hoes
AS
SELECT DISTINCT
(CASE WHEN model_draft.buffer_75_hoes.area < model_draft.buffer_75_a_hoes.area_a
THEN model_draft.buffer_75_hoes.osm_id
ELSE model_draft.buffer_75_a_hoes.osm_id END) as osm_id,
(CASE WHEN model_draft.buffer_75_hoes.area < model_draft.buffer_75_a_hoes.area_a
THEN model_draft.buffer_75_hoes.area
ELSE model_draft.buffer_75_a_hoes.area_a END) as area,
(CASE WHEN model_draft.buffer_75_hoes.area < model_draft.buffer_75_a_hoes.area_a
THEN model_draft.buffer_75_hoes.buffer_75
ELSE model_draft.buffer_75_a_hoes.buffer_75_a END) as buffer
FROM model_draft.buffer_75_hoes, model_draft.buffer_75_a_hoes
WHERE ST_Intersects(model_draft.buffer_75_hoes.buffer_75, model_draft.buffer_75_a_hoes.buffer_75_a)
AND NOT model_draft.buffer_75_hoes.osm_id = model_draft.buffer_75_a_hoes.osm_id;
ALTER MATERIALIZED VIEW model_draft.substations_to_drop_hoes OWNER TO oeuser; '''
)
class LoadSqlOperator(BaseOperator):
@apply_defaults
def __init__(self,
include_timestamp: bool = True,
postgres_conn_id: str = "postgres_ods_dev",
database: str = "ODS",
data_key: str = "entity_snapshots",
chunksize: int = 1000,
*args,
**kwargs):
BaseOperator.__init__(self, *args, **kwargs)
self.include_timestamp = include_timestamp
self.postgres_conn_id = postgres_conn_id
self.database = database
self.data_key = data_key
self.chunksize = chunksize
def execute(self, context):
ti = context["ti"]
URL = ti.xcom_pull(key='location', task_ids='settings')
print('Inicia desde -> ' + URL)
data_row = requests.get(url=URL, timeout=None)
print(" Estatus: " + str(data_row.status_code))
print("data Row!")
data_text = data_row.text
print("data Text!")
data_clean = data_text.replace("'", '"')
print("data clean!")
my_data = {'entity': 'LOCATION', 'data': data_clean}
self.hook = PostgresHook(postgres_conn_id=self.postgres_conn_id,
schema=self.database)
engine = self.hook.get_sqlalchemy_engine()
dataframe = pd.DataFrame(my_data, index=[0])
table_name, schema = 'entity_snapshots', 'fbplatform'
if self.include_timestamp:
ts = context["ts"]
print('TS> ' + ts)
ts = dateutil.parser.parse(ts) + datetime.timedelta(days=1)
timestamp = tzinfo.convert(ts).isoformat("T", "seconds")
self.log.info(f"data timestamp: {timestamp}")
dataframe = dataframe.assign(data_timestamp=timestamp)
print('realizando insert a BD: ')
dataframe.to_sql(table_name,
schema=schema,
con=engine,
if_exists='append',
index=False,
chunksize=self.chunksize)
self.log.info(
f"{dataframe.shape[0]} rows inserted in {schema}.{table_name} table"
)
def create_index_gist(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
CREATE INDEX summary_hoes_gix ON model_draft.summary_hoes USING GIST (polygon); '''
)
def insert_into_ego_grid_ehv_substation(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
INSERT INTO model_draft.ego_grid_ehv_substation (lon, lat, point, polygon, voltage, power_type, substation, osm_id, osm_www, frequency, subst_name, ref, operator, dbahn, status)
SELECT lon, lat, point, polygon, voltage, power_type, substation, osm_id, osm_www, frequency, subst_name, ref, operator, dbahn, status
FROM model_draft.final_result_hoes; ''')
def get_file_list(prefix, **kwargs):
try:
pg = PostgresHook(postgres_conn_id='file_list_db')
engine = pg.get_sqlalchemy_engine()
logging.info('Using db-based object list')
fl = DBBasedObjectList(engine=engine, prefix=prefix, **kwargs)
except:
logging.info(f'Using file-based object list for { prefix }')
fl = FileBasedObjectList(prefix=prefix, **kwargs)
return fl
def add_dummy_points(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
INSERT INTO model_draft.ego_grid_ehv_substation (subst_name, point, subst_id, otg_id, lon, lat, polygon, osm_id, osm_www, status)
SELECT 'DUMMY', ST_TRANSFORM(geom,4326), subst_id, subst_id, ST_X (ST_Transform (geom, 4326)), ST_Y (ST_Transform (geom, 4326)),
'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))', 'dummy'||row_number() OVER(), 'dummy', 0
FROM model_draft.ego_grid_hvmv_substation_dummy; ''')
def update_ego_grid_ehv_substation(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
conn = engine.connect()
#alembic
ctx = MigrationContext.configure(conn)
op = Operations(ctx)
#add column
op.add_column('ego_grid_ehv_substation',
Column('otg_id', BigInteger),
schema='model_draft')
# load the tables
meta = MetaData()
conn.execute("SET search_path TO model_draft, grid, public")
ego_grid_hvmv_substation = Table('ego_grid_hvmv_substation',
meta,
autoload=True,
autoload_with=conn,
postgresql_ignore_search_path=True)
ego_grid_ehv_substation = Table('ego_grid_ehv_substation',
meta,
autoload=True,
autoload_with=conn,
postgresql_ignore_search_path=True)
otg_ehvhv_bus_data = Table('otg_ehvhv_bus_data',
meta,
autoload=True,
autoload_with=conn,
postgresql_ignore_search_path=True)
#operations with "ego_grid_ehv_substation" table
#update
ehv_substation_update = ego_grid_ehv_substation.update().values(
otg_id=otg_ehvhv_bus_data.c.bus_i).where(
and_(
otg_ehvhv_bus_data.c.base_kv > 110,
otg_ehvhv_bus_data.c.osm_substation_id == cast(
func.trim(ego_grid_ehv_substation.c.osm_id, 'nwr'),
BigInteger)))
#delete
ehv_substation_delete = ego_grid_ehv_substation.delete().where(
ego_grid_ehv_substation.c.otg_id.is_(None))
#execution
conn.execute(ehv_substation_update)
conn.execute(ehv_substation_delete)
def run(self, table, schema, date=None, conn_id='postgres_bills3'):
df = self.inputs['dataframe'].read()
if date is not None:
df[date] = pd.to_datetime(df[date], format="%Y-%m-%d")
pg_hook = PostgresHook(postgres_conn_id=conn_id)
engine = pg_hook.get_sqlalchemy_engine()
df.to_sql(table,
con=engine,
schema=schema,
if_exists='replace',
index=False)
def load_to_pg(p_table_name: str, p_file: str):
"""Loads the generated output file to Postgres database via Pandas to_sql
It creates the table according to dataframe if it doesnt exists
and appends data if it exists
Args:
p_table_name ([str]): Table name
p_table_name ([str]): File needs to be loaded
"""
pg_hook = PostgresHook(postgres_conn_id='pg_local')
conn = pg_hook.get_sqlalchemy_engine()
load_df = pd.read_csv(p_file)
load_df.to_sql(p_table_name, conn, index=False, if_exists='append')
def gemeindeschluessel(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
UPDATE model_draft.ego_grid_hvmv_substation AS t1
SET ags_0 = t2.ags_0
FROM (SELECT sub.subst_id AS subst_id, vg.ags_0 AS ags_0
FROM model_draft.ego_grid_hvmv_substation AS sub, model_draft.ego_boundaries_bkg_vg250_6_gem_clean AS vg
WHERE vg.geom && sub.geom AND ST_CONTAINS(vg.geom,sub.geom)
) AS t2
WHERE t1.subst_id = t2.subst_id; ''')
def create_view_summary_total_hoes(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
DROP VIEW IF EXISTS model_draft.summary_total_hoes CASCADE;
CREATE VIEW model_draft.summary_total_hoes
AS
SELECT ST_X(ST_Centroid(ST_Transform(substation.way,4326))) as lon,
ST_Y(ST_Centroid(ST_Transform(substation.way,4326))) as lat,
ST_Centroid(ST_Transform(substation.way,4326)) as point,
ST_Transform(substation.way,4326) as polygon,
(CASE WHEN hstore(substation.tags)->'voltage' <> ''
THEN hstore(substation.tags)->'voltage'
ELSE 'hoes' END) as voltage,
hstore(substation.tags)->'power' as power_type,
(CASE WHEN hstore(substation.tags)->'substation' <> ''
THEN hstore(substation.tags)->'substation'
ELSE 'NA' END) as substation,
substation.osm_id as osm_id,
osm_www,
(CASE WHEN hstore(substation.tags)->'frequency' <> ''
THEN hstore(substation.tags)->'frequency'
ELSE 'NA' END) as frequency,
(CASE WHEN hstore(substation.tags)->'name' <> ''
THEN hstore(substation.tags)->'name'
ELSE 'NA' END) as subst_name,
(CASE WHEN hstore(substation.tags)->'ref' <> ''
THEN hstore(substation.tags)->'ref'
ELSE 'NA' END) as ref,
(CASE WHEN hstore(substation.tags)->'operator' <> ''
THEN hstore(substation.tags)->'operator'
ELSE 'NA' END) as operator,
(CASE WHEN hstore(substation.tags)->'operator' in ('DB_Energie','DB Netz AG','DB Energie GmbH','DB Netz')
THEN 'see operator'
ELSE (CASE WHEN '16.7' = ANY( string_to_array(hstore(substation.tags)->'frequency',';'))
OR '16.67' = ANY( string_to_array(hstore(substation.tags)->'frequency',';'))
THEN 'see frequency'
ELSE 'no'
END)
END) as dbahn,
status
FROM model_draft.substation_hoes substation ORDER BY osm_www;
ALTER VIEW model_draft.summary_total_hoes OWNER TO oeuser; '''
)
def create_view_buffer_75_a_hoes(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
DROP MATERIALIZED VIEW IF EXISTS model_draft.buffer_75_a_hoes CASCADE;
CREATE MATERIALIZED VIEW model_draft.buffer_75_a_hoes
AS
SELECT osm_id, ST_Area(ST_Transform(model_draft.summary_de_hoes.polygon,4326)) as area_a,
ST_Buffer_Meters(ST_Transform(model_draft.summary_de_hoes.polygon,4326), 75) as buffer_75_a
FROM model_draft.summary_de_hoes;
ALTER MATERIALIZED VIEW model_draft.buffer_75_a_hoes OWNER TO oeuser; '''
)
def create_view_summary_de_hoes(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
DROP VIEW IF EXISTS model_draft.summary_de_hoes CASCADE;
CREATE VIEW model_draft.summary_de_hoes
AS
SELECT *
FROM model_draft.summary_hoes, boundaries.bkg_vg250_1_sta_union_mview as vg
WHERE ST_Transform(vg.geom,4326) && model_draft.summary_hoes.polygon
AND ST_CONTAINS(ST_Transform(vg.geom,4326),model_draft.summary_hoes.polygon);
ALTER VIEW model_draft.summary_de_hoes OWNER TO oeuser; ''')
def create_view_filter_substations_final_result(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
DROP VIEW IF EXISTS model_draft.final_result CASCADE;
CREATE VIEW model_draft.final_result
AS
SELECT *
FROM model_draft.summary_de
WHERE model_draft.summary_de.osm_id
NOT IN ( SELECT model_draft.substations_to_drop.osm_id FROM model_draft.substations_to_drop);
ALTER VIEW model_draft.final_result OWNER TO oeuser; ''')
def create_view_summary_hoes(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
DROP MATERIALIZED VIEW IF EXISTS model_draft.summary_hoes CASCADE;
CREATE MATERIALIZED VIEW model_draft.summary_hoes
AS
SELECT *
FROM model_draft.summary_total_hoes
WHERE dbahn = 'no' AND substation NOT IN ('traction','transition');
ALTER MATERIALIZED VIEW model_draft.summary_hoes OWNER TO oeuser; '''
)
def create_view_way_substations_with_hoes(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
DROP VIEW IF EXISTS model_draft.way_substations_with_hoes CASCADE;
CREATE VIEW model_draft.way_substations_with_hoes
AS
SELECT *
FROM model_draft.way_substations
WHERE '220000' = ANY( string_to_array(hstore(model_draft.way_substations.tags)->'voltage',';'))
OR '380000' = ANY( string_to_array(hstore(model_draft.way_substations.tags)->'voltage',';'));
ALTER VIEW model_draft.way_substations_with_hoes OWNER TO oeuser; '''
)
def create_view_filters_irrelevant_tags(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
DROP MATERIALIZED VIEW IF EXISTS model_draft.summary CASCADE;
CREATE MATERIALIZED VIEW model_draft.summary
AS
SELECT *
FROM model_draft.summary_total
WHERE dbahn = 'no' AND substation NOT IN ('traction','transition');
CREATE INDEX summary_gix ON model_draft.summary USING GIST (polygon);
ALTER MATERIALIZED VIEW model_draft.summary OWNER TO oeuser; '''
)
def copy_from_file(schema,
table,
local_path,
delimiter=',',
encoding='utf-8',
truncate=True,
connection='covid_db_postgres'):
conn = PostgresHook(connection)
conn_engine = conn.get_sqlalchemy_engine()
if truncate:
print('Truncating table...')
conn_engine.execute(f'truncate table {schema}.{table};')
print('Loading table...')
conn_engine.execute(f"""copy {schema}.{table}
from '{local_path}'
delimiters '{delimiter}' csv header encoding '{encoding}';
commit;""")
return f'Table: {table} loaded!'
def create_view_way_substations_without_110kv_intersected_by_110kv_line(
**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
DROP VIEW IF EXISTS model_draft.way_substations_without_110kV_intersected_by_110kV_line CASCADE;
CREATE VIEW model_draft.way_substations_without_110kV_intersected_by_110kV_line
AS
SELECT DISTINCT model_draft.way_substations_without_110kV.*
FROM model_draft.way_substations_without_110kV, model_draft.way_lines_110kV
WHERE ST_Contains(model_draft.way_substations_without_110kV.geom,ST_StartPoint(model_draft.way_lines_110kV.geom))
OR ST_Contains(model_draft.way_substations_without_110kV.geom,ST_EndPoint(model_draft.way_lines_110kV.geom));
ALTER VIEW model_draft.way_substations_without_110kV_intersected_by_110kV_line OWNER TO oeuser; '''
)
def create_view_node_substation_110kv(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
DROP VIEW IF EXISTS model_draft.node_substations_with_110kV CASCADE;
CREATE VIEW model_draft.node_substations_with_110kV
AS
SELECT openstreetmap.osm_deu_nodes.id, openstreetmap.osm_deu_nodes.tags, openstreetmap.osm_deu_point.geom
FROM openstreetmap.osm_deu_nodes JOIN openstreetmap.osm_deu_point ON openstreetmap.osm_deu_nodes.id = openstreetmap.osm_deu_point.osm_id
WHERE '110000' = ANY( string_to_array(hstore(openstreetmap.osm_deu_nodes.tags)->'voltage',';'))
AND hstore(openstreetmap.osm_deu_nodes.tags)->'power' in ('substation','sub_station','station')
OR '60000' = ANY( string_to_array(hstore(openstreetmap.osm_deu_nodes.tags)->'voltage',';'))
AND hstore(openstreetmap.osm_deu_nodes.tags)->'power' in ('substation','sub_station','station');
ALTER VIEW model_draft.node_substations_with_110kV OWNER TO oeuser; '''
)
def create_view_way_substation(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
DROP VIEW IF EXISTS model_draft.way_substations CASCADE;
CREATE VIEW model_draft.way_substations
AS
SELECT openstreetmap.osm_deu_ways.id, openstreetmap.osm_deu_ways.tags, openstreetmap.osm_deu_polygon.geom
FROM openstreetmap.osm_deu_ways JOIN openstreetmap.osm_deu_polygon ON openstreetmap.osm_deu_ways.id = openstreetmap.osm_deu_polygon.osm_id
WHERE hstore(openstreetmap.osm_deu_ways.tags)->'power' in ('substation','sub_station','station')
UNION
SELECT openstreetmap.osm_deu_ways.id, openstreetmap.osm_deu_ways.tags, openstreetmap.osm_deu_line.geom
FROM openstreetmap.osm_deu_ways JOIN openstreetmap.osm_deu_line ON openstreetmap.osm_deu_ways.id = openstreetmap.osm_deu_line.osm_id
WHERE hstore(openstreetmap.osm_deu_ways.tags)->'power' in ('substation','sub_station','station');
ALTER VIEW model_draft.way_substations OWNER TO oeuser; ''')
def create_view_relation_substations_with_hoes(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
DROP VIEW IF EXISTS model_draft.relation_substations_with_hoes CASCADE;
CREATE VIEW model_draft.relation_substations_with_hoes
AS
SELECT openstreetmap.osm_deu_rels.id, openstreetmap.osm_deu_rels.tags, relation_geometry(openstreetmap.osm_deu_rels.members) as way
FROM openstreetmap.osm_deu_rels
WHERE '220000' = ANY( string_to_array(hstore(openstreetmap.osm_deu_rels.tags)->'voltage',';'))
AND hstore(openstreetmap.osm_deu_rels.tags)->'power' in ('substation','sub_station','station')
OR '380000' = ANY( string_to_array(hstore(openstreetmap.osm_deu_rels.tags)->'voltage',';'))
AND hstore(openstreetmap.osm_deu_rels.tags)->'power' in ('substation','sub_station','station');
ALTER VIEW model_draft.relation_substations_with_hoes OWNER TO oeuser; '''
)
def update_ego_grid_ehv_substation(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
ALTER TABLE model_draft.ego_grid_ehv_substation
ADD COLUMN otg_id bigint;
UPDATE model_draft.ego_grid_ehv_substation
SET otg_id = grid.otg_ehvhv_bus_data.bus_i
FROM grid.otg_ehvhv_bus_data
WHERE grid.otg_ehvhv_bus_data.base_kv > 110 AND
(SELECT TRIM(leading 'n'
FROM TRIM(leading 'w' FROM TRIM(leading 'r'
FROM model_draft.ego_grid_ehv_substation.osm_id)))::BIGINT)=grid.otg_ehvhv_bus_data.osm_substation_id;
DELETE FROM model_draft.ego_grid_ehv_substation WHERE otg_id IS NULL; '''
)
def drop(**kwargs):
postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb')
engine = postgres_hook.get_sqlalchemy_engine()
with engine.connect() as connection:
result = connection.execute('''
DROP VIEW IF EXISTS model_draft.final_result_hoes CASCADE;
DROP MATERIALIZED VIEW IF EXISTS model_draft.substations_to_drop_hoes CASCADE;
DROP MATERIALIZED VIEW IF EXISTS model_draft.buffer_75_hoes CASCADE;
DROP MATERIALIZED VIEW IF EXISTS model_draft.buffer_75_a_hoes CASCADE;
DROP VIEW IF EXISTS model_draft.summary_de_hoes CASCADE;
DROP MATERIALIZED VIEW IF EXISTS model_draft.summary_hoes CASCADE;
DROP VIEW IF EXISTS model_draft.summary_total_hoes CASCADE;
DROP VIEW IF EXISTS model_draft.substation_hoes CASCADE;
DROP VIEW IF EXISTS model_draft.relation_substations_with_hoes CASCADE;
DROP VIEW IF EXISTS model_draft.node_substations_with_hoes CASCADE;
DROP VIEW IF EXISTS model_draft.way_substations_with_hoes CASCADE;
DROP VIEW IF EXISTS model_draft.way_substations CASCADE; '''
)
