Passed
Pull Request — dev (#826)
by
unknown
01:40
created

data.datasets.electricity_demand_timeseries.cts_buildings   F

Complexity

Total Complexity 60

Size/Duplication

Total Lines 1498
Duplicated Lines 4.21 %

Importance

Changes 0
Metric Value
wmc 60
eloc 700
dl 63
loc 1498
rs 3.5
c 0
b 0
f 0

17 Functions

Rating   Name   Duplication   Size   Complexity  
A cts_heat() 32 32 2
A place_buildings_with_amenities() 0 41 3
B create_synthetic_buildings() 0 67 6
A delete_synthetic_cts_buildings() 0 14 2
A remove_double_bus_id() 0 47 3
B get_cts_electricity_peak_load() 0 56 5
C calc_building_profiles() 0 106 10
A amenities_without_buildings() 0 60 2
A select_cts_buildings() 0 25 1
B cells_with_cts_demand_only() 0 72 2
B get_cts_heat_peak_load() 0 68 6
A calc_building_demand_profile_share() 0 105 2
C buildings_with_amenities() 0 183 5
A cts_electricity() 31 31 2
A calc_census_cell_share() 0 56 4
B cts_buildings() 0 185 2
B buildings_without_amenities() 0 99 2

1 Method

Rating   Name   Duplication   Size   Complexity  
A CtsElectricityBuildings.__init__() 0 9 1

How to fix   Duplicated Code    Complexity   

Duplicated Code

Duplicate code is one of the most pungent code smells. A rule that is often used is to re-structure code once it is duplicated in three or more places.

Common duplication problems, and corresponding solutions are:

Complexity

 Tip:   Before tackling complexity, make sure that you eliminate any duplication first. This often can reduce the size of classes significantly.

Complex classes like data.datasets.electricity_demand_timeseries.cts_buildings often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.

Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.

1
"""
2
CTS electricity and heat demand time series for scenarios in 2035 and 2050
3
assigned to OSM-buildings.
4
5
Disaggregation of cts heat & electricity demand time series from MV Substation
6
to census cells via annual demand and then to OSM buildings via
7
amenity tags or randomly if no sufficient OSM-data is available in the
8
respective census cell. If no OSM-buildings or synthetic residential buildings
9
are available new synthetic 5x5m buildings are generated.
10
11
The resulting data is stored in separate tables
12
13
* `openstreetmap.osm_buildings_synthetic`:
14
    Lists generated synthetic building with id, zensus_population_id and
15
    building type. This table is already created within
16
    :func:`hh_buildings.map_houseprofiles_to_buildings()`
17
* `openstreetmap.egon_cts_buildings`:
18
    Table of all selected cts buildings with id, census cell id, geometry and
19
    amenity count in building. This table is created within
20
    :func:`cts_buildings()`
21
* `demand.egon_cts_electricity_demand_building_share`:
22
    Table including the mv substation electricity profile share of all selected
23
    cts buildings for scenario eGon2035 and eGon100RE. This table is created
24
    within :func:`cts_electricity()`
25
* `demand.egon_cts_heat_demand_building_share`:
26
    Table including the mv substation heat profile share of all selected
27
    cts buildings for scenario eGon2035 and eGon100RE. This table is created
28
    within :func:`cts_heat()`
29
* `demand.egon_building_peak_loads`:
30
    Mapping of demand time series and buildings including cell_id, building
31
    area and peak load. This table is already created within
32
    :func:`hh_buildings.get_building_peak_loads()`
33
34
**The following datasets from the database are mainly used for creation:**
35
36
* `openstreetmap.osm_buildings_filtered`:
37
    Table of OSM-buildings filtered by tags to selecting residential and cts
38
    buildings only.
39
* `openstreetmap.osm_amenities_shops_filtered`:
40
    Table of OSM-amenities filtered by tags to select cts only.
41
* `openstreetmap.osm_amenities_not_in_buildings_filtered`:
42
    Table of amenities which do not intersect with any building from
43
    `openstreetmap.osm_buildings_filtered`
44
* `openstreetmap.osm_buildings_synthetic`:
45
    Table of synthetic residential buildings
46
* `boundaries.egon_map_zensus_buildings_filtered_all`:
47
    Mapping table of census cells and buildings filtered even if population
48
    in census cell = 0.
49
* `demand.egon_demandregio_zensus_electricity`:
50
    Table of annual electricity load demand for residential and cts at census
51
    cell level. Residential load demand is derived from aggregated residential
52
    building profiles. DemandRegio CTS load demand at NUTS3 is distributed to
53
    census cells linearly to heat demand from peta5.
54
* `demand.egon_peta_heat`:
55
    Table of annual heat load demand for residential and cts at census cell
56
    level from peta5.
57
* `demand.egon_etrago_electricity_cts`:
58
    Scaled cts electricity time series for every MV substation. Derived from
59
    DemandRegio SLP for selected economic sectors at nuts3. Scaled with annual
60
    demand from `demand.egon_demandregio_zensus_electricity`
61
* `demand.egon_etrago_heat_cts`:
62
    Scaled cts heat time series for every MV substation. Derived from
63
    DemandRegio SLP Gas for selected economic sectors at nuts3. Scaled with
64
    annual demand from `demand.egon_peta_heat`.
65
66
**What is the goal?**
67
68
To disaggregate cts heat and electricity time series from MV substation level
69
to geo-referenced buildings. DemandRegio and Peta5 is used to identify census
70
cells with load demand. Openstreetmap data is used and filtered via tags to
71
identify buildings and count amenities within. The number of amenities serve
72
to assign the appropriate load demand share to the building.
73
74
**What is the challenge?**
75
76
The OSM, DemandRegio and Peta5 dataset differ from each other. The OSM dataset
77
is a community based dataset which is extended throughout and does not claim to
78
be complete. Therefore not all census cells which have a demand assigned by
79
DemandRegio or Peta5 methodology also have buildings with respective tags or no
80
buildings at all. Merging these datasets inconsistencies need
81
to be addressed. For example: not yet tagged buildings or amenities in OSM
82
83
**How are these datasets combined?**
84
85
------>>>>>> continue
86
87
Firstly, all cts buildings are selected. Buildings which have cts amenities
88
inside.
89
90
91
**What are central assumptions during the data processing?**
92
93
* Mapping census to OSM data is not trivial. Discrepancies are substituted.
94
* Missing OSM buildings are generated by census building count.
95
* If no census building count data is available, the number of buildings is
96
derived by an average rate of households/buildings applied to the number of
97
households.
98
99
**Drawbacks and limitations of the data**
100
101
* Missing OSM buildings in cells without census building count are derived by
102
an average rate of households/buildings applied to the number of households.
103
As only whole houses can exist, the substitute is ceiled to the next higher
104
integer. Ceiling is applied to avoid rounding to amount of 0 buildings.
105
106
* As this datasets is a cascade after profile assignement at census cells
107
also check drawbacks and limitations in hh_profiles.py.
108
109
110
111
Example Query
112
-----
113
114
115
Notes
116
-----
117
118
This module docstring is rather a dataset documentation. Once, a decision
119
is made in ... the content of this module docstring needs to be moved to
120
docs attribute of the respective dataset class.
121
"""
122
123
from geoalchemy2 import Geometry
124
from geoalchemy2.shape import to_shape
125
from sqlalchemy import REAL, Column, Integer, String, func
126
from sqlalchemy.ext.declarative import declarative_base
127
import geopandas as gpd
128
import numpy as np
129
import pandas as pd
130
import saio
131
132
from egon.data import db
133
from egon.data import logger as log
134
from egon.data.datasets import Dataset
135
from egon.data.datasets.electricity_demand import (
136
    EgonDemandRegioZensusElectricity,
137
)
138
from egon.data.datasets.electricity_demand.temporal import (
139
    EgonEtragoElectricityCts,
140
    calc_load_curves_cts,
141
)
142
from egon.data.datasets.electricity_demand_timeseries.hh_buildings import (
143
    BuildingPeakLoads,
144
    OsmBuildingsSynthetic,
145
)
146
from egon.data.datasets.electricity_demand_timeseries.tools import (
147
    random_ints_until_sum,
148
    random_point_in_square,
149
    specific_int_until_sum,
150
    write_table_to_postgis,
151
    write_table_to_postgres,
152
)
153
from egon.data.datasets.heat_demand import EgonPetaHeat
154
from egon.data.datasets.zensus_mv_grid_districts import MapZensusGridDistricts
155
from egon.data.datasets.zensus_vg250 import DestatisZensusPopulationPerHa
156
157
engine = db.engine()
158
Base = declarative_base()
159
160
# import db tables
161
saio.register_schema("openstreetmap", engine=engine)
162
saio.register_schema("boundaries", engine=engine)
163
164
165
class EgonCtsElectricityDemandBuildingShare(Base):
166
    __tablename__ = "egon_cts_electricity_demand_building_share"
167
    __table_args__ = {"schema": "demand"}
168
169
    id = Column(Integer, primary_key=True)
170
    scenario = Column(String, primary_key=True)
171
    bus_id = Column(Integer, index=True)
172
    profile_share = Column(REAL)
173
174
175
class EgonCtsHeatDemandBuildingShare(Base):
176
    __tablename__ = "egon_cts_heat_demand_building_share"
177
    __table_args__ = {"schema": "demand"}
178
179
    id = Column(Integer, primary_key=True)
180
    scenario = Column(String, primary_key=True)
181
    bus_id = Column(Integer, index=True)
182
    profile_share = Column(REAL)
183
184
185
class CtsBuildings(Base):
186
    __tablename__ = "egon_cts_buildings"
187
    __table_args__ = {"schema": "openstreetmap"}
188
189
    serial = Column(Integer, primary_key=True)
190
    id = Column(Integer, index=True)
191
    zensus_population_id = Column(Integer, index=True)
192
    geom_building = Column(Geometry("Polygon", 3035))
193
    n_amenities_inside = Column(Integer)
194
    source = Column(String)
195
196
197
class BuildingHeatPeakLoads(Base):
198
    __tablename__ = "egon_building_heat_peak_loads"
199
    __table_args__ = {"schema": "demand"}
200
201
    building_id = Column(Integer, primary_key=True)
202
    scenario = Column(String, primary_key=True)
203
    sector = Column(String, primary_key=True)
204
    peak_load_in_w = Column(REAL)
205
206
207
def amenities_without_buildings():
208
    """
209
    Amenities which have no buildings assigned and are in
210
    a cell with cts demand are determined.
211
212
    Returns
213
    -------
214
    pd.DataFrame
215
        Table of amenities without buildings
216
    """
217
    from saio.openstreetmap import osm_amenities_not_in_buildings_filtered
218
219
    with db.session_scope() as session:
220
        cells_query = (
221
            session.query(
222
                DestatisZensusPopulationPerHa.id.label("zensus_population_id"),
223
                # TODO can be used for square around amenity
224
                #  (1 geom_amenity: 1 geom_building)
225
                #  not unique amenity_ids yet
226
                osm_amenities_not_in_buildings_filtered.geom_amenity,
227
                osm_amenities_not_in_buildings_filtered.egon_amenity_id,
228
                # EgonDemandRegioZensusElectricity.demand,
229
                # # TODO can be used to generate n random buildings
230
                # # (n amenities : 1 randombuilding)
231
                # func.count(
232
                #     osm_amenities_not_in_buildings_filtered.egon_amenity_id
233
                # ).label("n_amenities_inside"),
234
                # DestatisZensusPopulationPerHa.geom,
235
            )
236
            .filter(
237
                func.st_within(
238
                    osm_amenities_not_in_buildings_filtered.geom_amenity,
239
                    DestatisZensusPopulationPerHa.geom,
240
                )
241
            )
242
            .filter(
243
                DestatisZensusPopulationPerHa.id
244
                == EgonDemandRegioZensusElectricity.zensus_population_id
245
            )
246
            .filter(
247
                EgonDemandRegioZensusElectricity.sector == "service",
248
                EgonDemandRegioZensusElectricity.scenario == "eGon2035"
249
                #         ).group_by(
250
                #             EgonDemandRegioZensusElectricity.zensus_population_id,
251
                #             DestatisZensusPopulationPerHa.geom,
252
            )
253
        )
254
    # # TODO can be used to generate n random buildings
255
    # df_cells_with_amenities_not_in_buildings = gpd.read_postgis(
256
    #     cells_query.statement, cells_query.session.bind, geom_col="geom"
257
    # )
258
    #
259
260
    # # TODO can be used for square around amenity
261
    df_amenities_without_buildings = gpd.read_postgis(
262
        cells_query.statement,
263
        cells_query.session.bind,
264
        geom_col="geom_amenity",
265
    )
266
    return df_amenities_without_buildings
267
268
269
def place_buildings_with_amenities(df, amenities=None, max_amenities=None):
270
    """
271
    Building centroids are placed randomly within census cells.
272
    The Number of buildings is derived from n_amenity_inside, the selected
273
    method and number of amenities per building.
274
275
    Returns
276
    -------
277
    df: gpd.GeoDataFrame
278
        Table of buildings centroids
279
    """
280
    if isinstance(max_amenities, int):
281
        # amount of amenities is randomly generated within bounds
282
        # (max_amenities, amenities per cell)
283
        df["n_amenities_inside"] = df["n_amenities_inside"].apply(
284
            random_ints_until_sum, args=[max_amenities]
285
        )
286
    if isinstance(amenities, int):
287
        # Specific amount of amenities per building
288
        df["n_amenities_inside"] = df["n_amenities_inside"].apply(
289
            specific_int_until_sum, args=[amenities]
290
        )
291
292
    # Unnest each building
293
    df = df.explode(column="n_amenities_inside")
294
295
    # building count per cell
296
    df["building_count"] = df.groupby(["zensus_population_id"]).cumcount() + 1
297
298
    # generate random synthetic buildings
299
    edge_length = 5
300
    # create random points within census cells
301
    points = random_point_in_square(geom=df["geom"], tol=edge_length / 2)
302
303
    df.reset_index(drop=True, inplace=True)
304
    # Store center of polygon
305
    df["geom_point"] = points
306
    # Drop geometry of census cell
307
    df = df.drop(columns=["geom"])
308
309
    return df
310
311
312
def create_synthetic_buildings(df, points=None, crs="EPSG:3035"):
313
    """
314
    Synthetic buildings are generated around points.
315
316
    Parameters
317
    ----------
318
    df: pd.DataFrame
319
        Table of census cells
320
    points: gpd.GeoSeries or str
321
        List of points to place buildings around or column name of df
322
    crs: str
323
        CRS of result table
324
325
    Returns
326
    -------
327
    df: gpd.GeoDataFrame
328
        Synthetic buildings
329
    """
330
331
    if isinstance(points, str) and points in df.columns:
332
        points = df[points]
333
    elif isinstance(points, gpd.GeoSeries):
334
        pass
335
    else:
336
        raise ValueError("Points are of the wrong type")
337
338
    # Create building using a square around point
339
    edge_length = 5
340
    df["geom_building"] = points.buffer(distance=edge_length / 2, cap_style=3)
341
342
    if "geom_point" not in df.columns:
343
        df["geom_point"] = df["geom_building"].centroid
344
345
    # TODO Check CRS
346
    df = gpd.GeoDataFrame(
347
        df,
348
        crs=crs,
349
        geometry="geom_building",
350
    )
351
352
    # TODO remove after implementation of egon_building_id
353
    df.rename(columns={"id": "egon_building_id"}, inplace=True)
354
355
    # get max number of building ids from synthetic residential table
356
    with db.session_scope() as session:
357
        max_synth_residential_id = session.execute(
358
            func.max(OsmBuildingsSynthetic.id)
359
        ).scalar()
360
    max_synth_residential_id = int(max_synth_residential_id)
361
362
    # create sequential ids
363
    df["egon_building_id"] = range(
364
        max_synth_residential_id + 1,
365
        max_synth_residential_id + df.shape[0] + 1,
366
    )
367
368
    df["area"] = df["geom_building"].area
369
    # set building type of synthetic building
370
    df["building"] = "cts"
371
    # TODO remove in #772
372
    df = df.rename(
373
        columns={
374
            # "zensus_population_id": "cell_id",
375
            "egon_building_id": "id",
376
        }
377
    )
378
    return df
379
380
381
def buildings_with_amenities():
382
    """
383
    Amenities which are assigned to buildings are determined
384
    and grouped per building and zensus cell. Buildings
385
    covering multiple cells therefore exists multiple times
386
    but in different zensus cells. This is necessary to cover
387
    all cells with a cts demand. If buildings exist in multiple
388
    substations, their amenities are summed and assigned and kept in
389
    one substation only. If as a result, a census cell is uncovered,
390
    a synthetic amenity is placed. The buildings are aggregated
391
    afterwards during the calculation of the profile_share.
392
393
    Returns
394
    -------
395
    df_buildings_with_amenities: gpd.GeoDataFrame
396
        Contains all buildings with amenities per zensus cell.
397
    df_lost_cells: gpd.GeoDataFrame
398
        Contains synthetic amenities in lost cells. Might be empty
399
    """
400
401
    from saio.openstreetmap import osm_amenities_in_buildings_filtered
402
403
    with db.session_scope() as session:
404
        cells_query = (
405
            session.query(
406
                osm_amenities_in_buildings_filtered,
407
                MapZensusGridDistricts.bus_id,
408
            )
409
            .filter(
410
                MapZensusGridDistricts.zensus_population_id
411
                == osm_amenities_in_buildings_filtered.zensus_population_id
412
            )
413
            .filter(
414
                EgonDemandRegioZensusElectricity.zensus_population_id
415
                == osm_amenities_in_buildings_filtered.zensus_population_id
416
            )
417
            .filter(
418
                EgonDemandRegioZensusElectricity.sector == "service",
419
                EgonDemandRegioZensusElectricity.scenario == "eGon2035",
420
            )
421
        )
422
    df_amenities_in_buildings = pd.read_sql(
423
        cells_query.statement, cells_query.session.bind, index_col=None
424
    )
425
426
    df_amenities_in_buildings["geom_building"] = df_amenities_in_buildings[
427
        "geom_building"
428
    ].apply(to_shape)
429
    df_amenities_in_buildings["geom_amenity"] = df_amenities_in_buildings[
430
        "geom_amenity"
431
    ].apply(to_shape)
432
433
    df_amenities_in_buildings["n_amenities_inside"] = 1
434
435
    # add identifier column for buildings in multiple substations
436
    df_amenities_in_buildings[
437
        "duplicate_identifier"
438
    ] = df_amenities_in_buildings.groupby(["id", "bus_id"])[
439
        "n_amenities_inside"
440
    ].transform(
441
        "cumsum"
442
    )
443
    df_amenities_in_buildings = df_amenities_in_buildings.sort_values(
444
        ["id", "duplicate_identifier"]
445
    )
446
    # sum amenities of buildings with multiple substations
447
    df_amenities_in_buildings[
448
        "n_amenities_inside"
449
    ] = df_amenities_in_buildings.groupby(["id", "duplicate_identifier"])[
450
        "n_amenities_inside"
451
    ].transform(
452
        "sum"
453
    )
454
455
    # create column to always go for bus_id with max amenities
456
    df_amenities_in_buildings[
457
        "max_amenities"
458
    ] = df_amenities_in_buildings.groupby(["id", "bus_id"])[
459
        "n_amenities_inside"
460
    ].transform(
461
        "sum"
462
    )
463
    # sort to go for
464
    df_amenities_in_buildings.sort_values(
465
        ["id", "max_amenities"], ascending=False, inplace=True
466
    )
467
468
    # identify lost zensus cells
469
    df_lost_cells = df_amenities_in_buildings.loc[
470
        df_amenities_in_buildings.duplicated(
471
            subset=["id", "duplicate_identifier"], keep="first"
472
        )
473
    ]
474
    df_lost_cells.drop_duplicates(
475
        subset=["zensus_population_id"], inplace=True
476
    )
477
478
    # drop buildings with multiple substation and lower max amenity
479
    df_amenities_in_buildings.drop_duplicates(
480
        subset=["id", "duplicate_identifier"], keep="first", inplace=True
481
    )
482
483
    # check if lost zensus cells are already covered
484
    if not df_lost_cells.empty:
485
        if not (
486
            df_amenities_in_buildings["zensus_population_id"]
487
            .isin(df_lost_cells["zensus_population_id"])
488
            .empty
489
        ):
490
            # query geom data for cell if not
491
            with db.session_scope() as session:
492
                cells_query = session.query(
493
                    DestatisZensusPopulationPerHa.id,
494
                    DestatisZensusPopulationPerHa.geom,
495
                ).filter(
496
                    DestatisZensusPopulationPerHa.id.in_(
497
                        df_lost_cells["zensus_population_id"]
498
                    )
499
                )
500
501
            df_lost_cells = gpd.read_postgis(
502
                cells_query.statement,
503
                cells_query.session.bind,
504
                geom_col="geom",
505
            )
506
            # TODO maybe adapt method
507
            # place random amenity in cell
508
            df_lost_cells["n_amenities_inside"] = 1
509
            df_lost_cells.rename(
510
                columns={
511
                    "id": "zensus_population_id",
512
                },
513
                inplace=True,
514
            )
515
            df_lost_cells = place_buildings_with_amenities(
516
                df_lost_cells, amenities=1
517
            )
518
            df_lost_cells.rename(
519
                columns={
520
                    # "id": "zensus_population_id",
521
                    "geom_point": "geom_amenity",
522
                },
523
                inplace=True,
524
            )
525
            df_lost_cells.drop(
526
                columns=["building_count", "n_amenities_inside"], inplace=True
527
            )
528
        else:
529
            df_lost_cells = None
530
    else:
531
        df_lost_cells = None
532
533
    # drop helper columns
534
    df_amenities_in_buildings.drop(
535
        columns=["duplicate_identifier"], inplace=True
536
    )
537
538
    # sum amenities per building and cell
539
    df_amenities_in_buildings[
540
        "n_amenities_inside"
541
    ] = df_amenities_in_buildings.groupby(["zensus_population_id", "id"])[
542
        "n_amenities_inside"
543
    ].transform(
544
        "sum"
545
    )
546
    # drop duplicated buildings
547
    df_buildings_with_amenities = df_amenities_in_buildings.drop_duplicates(
548
        ["id", "zensus_population_id"]
549
    )
550
    df_buildings_with_amenities.reset_index(inplace=True, drop=True)
551
552
    df_buildings_with_amenities = df_buildings_with_amenities[
553
        ["id", "zensus_population_id", "geom_building", "n_amenities_inside"]
554
    ]
555
    df_buildings_with_amenities.rename(
556
        columns={
557
            # "zensus_population_id": "cell_id",
558
            "egon_building_id": "id"
559
        },
560
        inplace=True,
561
    )
562
563
    return df_buildings_with_amenities, df_lost_cells
564
565
566
def buildings_without_amenities():
567
    """
568
    Buildings (filtered and synthetic) in cells with
569
    cts demand but no amenities are determined.
570
571
    Returns
572
    -------
573
    df_buildings_without_amenities: gpd.GeoDataFrame
574
        Table of buildings without amenities in zensus cells
575
        with cts demand.
576
    """
577
    from saio.boundaries import egon_map_zensus_buildings_filtered_all
578
    from saio.openstreetmap import (
579
        osm_amenities_shops_filtered,
580
        osm_buildings_filtered,
581
        osm_buildings_synthetic,
582
    )
583
584
    # buildings_filtered in cts-demand-cells without amenities
585
    with db.session_scope() as session:
586
587
        # Synthetic Buildings
588
        q_synth_buildings = session.query(
589
            osm_buildings_synthetic.cell_id.cast(Integer).label(
590
                "zensus_population_id"
591
            ),
592
            osm_buildings_synthetic.id.cast(Integer).label("id"),
593
            osm_buildings_synthetic.area.label("area"),
594
            osm_buildings_synthetic.geom_building.label("geom_building"),
595
            osm_buildings_synthetic.geom_point.label("geom_point"),
596
        )
597
598
        # Buildings filtered
599
        q_buildings_filtered = session.query(
600
            egon_map_zensus_buildings_filtered_all.zensus_population_id,
601
            osm_buildings_filtered.id,
602
            osm_buildings_filtered.area,
603
            osm_buildings_filtered.geom_building,
604
            osm_buildings_filtered.geom_point,
605
        ).filter(
606
            osm_buildings_filtered.id
607
            == egon_map_zensus_buildings_filtered_all.id
608
        )
609
610
        # Amenities + zensus_population_id
611
        q_amenities = (
612
            session.query(
613
                DestatisZensusPopulationPerHa.id.label("zensus_population_id"),
614
            )
615
            .filter(
616
                func.st_within(
617
                    osm_amenities_shops_filtered.geom_amenity,
618
                    DestatisZensusPopulationPerHa.geom,
619
                )
620
            )
621
            .distinct(DestatisZensusPopulationPerHa.id)
622
        )
623
624
        # Cells with CTS demand but without amenities
625
        q_cts_without_amenities = (
626
            session.query(
627
                EgonDemandRegioZensusElectricity.zensus_population_id,
628
            )
629
            .filter(
630
                EgonDemandRegioZensusElectricity.sector == "service",
631
                EgonDemandRegioZensusElectricity.scenario == "eGon2035",
632
            )
633
            .filter(
634
                EgonDemandRegioZensusElectricity.zensus_population_id.notin_(
635
                    q_amenities
636
                )
637
            )
638
            .distinct()
639
        )
640
641
        # Buildings filtered + synthetic buildings residential in
642
        # cells with CTS demand but without amenities
643
        cells_query = q_synth_buildings.union(q_buildings_filtered).filter(
644
            egon_map_zensus_buildings_filtered_all.zensus_population_id.in_(
645
                q_cts_without_amenities
646
            )
647
        )
648
649
    # df_buildings_without_amenities = pd.read_sql(
650
    #     cells_query.statement, cells_query.session.bind, index_col=None)
651
    df_buildings_without_amenities = gpd.read_postgis(
652
        cells_query.statement,
653
        cells_query.session.bind,
654
        geom_col="geom_building",
655
    )
656
657
    df_buildings_without_amenities = df_buildings_without_amenities.rename(
658
        columns={
659
            # "zensus_population_id": "cell_id",
660
            "egon_building_id": "id",
661
        }
662
    )
663
664
    return df_buildings_without_amenities
665
666
667
def select_cts_buildings(df_buildings_wo_amenities, max_n):
668
    """
669
    N Buildings (filtered and synthetic) in each cell with
670
    cts demand are selected. Only the first n buildings
671
    are taken for each cell. The buildings are sorted by surface
672
    area.
673
674
    Returns
675
    -------
676
    df_buildings_with_cts_demand: gpd.GeoDataFrame
677
        Table of buildings
678
    """
679
680
    df_buildings_wo_amenities.sort_values(
681
        "area", ascending=False, inplace=True
682
    )
683
    # select first n ids each census cell if available
684
    df_buildings_with_cts_demand = (
685
        df_buildings_wo_amenities.groupby("zensus_population_id")
686
        .nth(list(range(max_n)))
687
        .reset_index()
688
    )
689
    df_buildings_with_cts_demand.reset_index(drop=True, inplace=True)
690
691
    return df_buildings_with_cts_demand
692
693
694
def cells_with_cts_demand_only(df_buildings_without_amenities):
695
    """
696
    Cells with cts demand but no amenities or buildilngs
697
    are determined.
698
699
    Returns
700
    -------
701
    df_cells_only_cts_demand: gpd.GeoDataFrame
702
        Table of cells with cts demand but no amenities or buildings
703
    """
704
    from saio.openstreetmap import osm_amenities_shops_filtered
705
706
    # cells mit amenities
707
    with db.session_scope() as session:
708
        sub_query = (
709
            session.query(
710
                DestatisZensusPopulationPerHa.id.label("zensus_population_id"),
711
            )
712
            .filter(
713
                func.st_within(
714
                    osm_amenities_shops_filtered.geom_amenity,
715
                    DestatisZensusPopulationPerHa.geom,
716
                )
717
            )
718
            .distinct(DestatisZensusPopulationPerHa.id)
719
        )
720
721
        cells_query = (
722
            session.query(
723
                EgonDemandRegioZensusElectricity.zensus_population_id,
724
                EgonDemandRegioZensusElectricity.scenario,
725
                EgonDemandRegioZensusElectricity.sector,
726
                EgonDemandRegioZensusElectricity.demand,
727
                DestatisZensusPopulationPerHa.geom,
728
            )
729
            .filter(
730
                EgonDemandRegioZensusElectricity.sector == "service",
731
                EgonDemandRegioZensusElectricity.scenario == "eGon2035",
732
            )
733
            .filter(
734
                EgonDemandRegioZensusElectricity.zensus_population_id.notin_(
735
                    sub_query
736
                )
737
            )
738
            .filter(
739
                EgonDemandRegioZensusElectricity.zensus_population_id
740
                == DestatisZensusPopulationPerHa.id
741
            )
742
        )
743
744
    df_cts_cell_without_amenities = gpd.read_postgis(
745
        cells_query.statement,
746
        cells_query.session.bind,
747
        geom_col="geom",
748
        index_col=None,
749
    )
750
751
    # TODO maybe remove
752
    df_buildings_without_amenities = df_buildings_without_amenities.rename(
753
        columns={"cell_id": "zensus_population_id"}
754
    )
755
756
    # Census cells with only cts demand
757
    df_cells_only_cts_demand = df_cts_cell_without_amenities.loc[
758
        ~df_cts_cell_without_amenities["zensus_population_id"].isin(
759
            df_buildings_without_amenities["zensus_population_id"].unique()
760
        )
761
    ]
762
763
    df_cells_only_cts_demand.reset_index(drop=True, inplace=True)
764
765
    return df_cells_only_cts_demand
766
767
768
def calc_census_cell_share(scenario="eGon2035", sector="electricity"):
769
    """
770
    The profile share for each census cell is calculated by it's
771
    share of annual demand per substation bus. The annual demand
772
    per cell is defined by DemandRegio/Peta5. The share is for both
773
    scenarios identical as the annual demand is linearly scaled.
774
775
    Parameters
776
    ----------
777
    scenario: str
778
        Scenario for which the share is calculated.
779
    sector: str
780
        Scenario for which the share is calculated.
781
782
    Returns
783
    -------
784
    df_census_share: pd.DataFrame
785
    """
786
    if sector == "electricity":
787
        demand_table = EgonDemandRegioZensusElectricity
788
    elif sector == "heat":
789
        demand_table = EgonPetaHeat
790
791
    with db.session_scope() as session:
792
        cells_query = (
793
            session.query(demand_table, MapZensusGridDistricts.bus_id)
0 ignored issues
show
introduced by
The variable demand_table does not seem to be defined for all execution paths.
Loading history...
794
            .filter(demand_table.sector == "service")
795
            .filter(demand_table.scenario == scenario)
796
            .filter(
797
                demand_table.zensus_population_id
798
                == MapZensusGridDistricts.zensus_population_id
799
            )
800
        )
801
802
    df_demand = pd.read_sql(
803
        cells_query.statement,
804
        cells_query.session.bind,
805
        index_col="zensus_population_id",
806
    )
807
808
    # get demand share of cell per bus
809
    df_census_share = df_demand["demand"] / df_demand.groupby("bus_id")[
810
        "demand"
811
    ].transform("sum")
812
    df_census_share = df_census_share.rename("cell_share")
813
814
    df_census_share = pd.concat(
815
        [
816
            df_census_share,
817
            df_demand[["bus_id", "scenario"]],
818
        ],
819
        axis=1,
820
    )
821
822
    df_census_share.reset_index(inplace=True)
823
    return df_census_share
824
825
826
def calc_building_demand_profile_share(
827
    df_cts_buildings, scenario="eGon2035", sector="electricity"
828
):
829
    """
830
    Share of cts electricity demand profile per bus for every selected building
831
    is calculated. Building-amenity share is multiplied with census cell share
832
    to get the substation bus profile share for each building. The share is
833
    grouped and aggregated per building as some buildings exceed the shape of
834
    census cells and have amenities assigned from multiple cells. Building
835
    therefore get the amenity share of all census cells.
836
837
    Parameters
838
    ----------
839
    df_cts_buildings: gpd.GeoDataFrame
840
        Table of all buildings with cts demand assigned
841
    scenario: str
842
        Scenario for which the share is calculated.
843
    sector: str
844
        Sector for which the share is calculated.
845
846
    Returns
847
    -------
848
    df_building_share: pd.DataFrame
849
        Table of bus profile share per building
850
851
    """
852
853
    # from saio.boundaries import egon_map_zensus_buildings_filtered_all
854
855
    def calc_building_amenity_share(df_cts_buildings):
856
        """
857
        Calculate the building share by the number amenities per building
858
        within a census cell. Building ids can exist multiple time but with
859
        different zensus_population_ids.
860
        """
861
        df_building_amenity_share = df_cts_buildings[
862
            "n_amenities_inside"
863
        ] / df_cts_buildings.groupby("zensus_population_id")[
864
            "n_amenities_inside"
865
        ].transform(
866
            "sum"
867
        )
868
        df_building_amenity_share = pd.concat(
869
            [
870
                df_building_amenity_share.rename("building_amenity_share"),
871
                df_cts_buildings[["zensus_population_id", "id"]],
872
            ],
873
            axis=1,
874
        )
875
        return df_building_amenity_share
876
877
    df_building_amenity_share = calc_building_amenity_share(df_cts_buildings)
878
879
    df_census_cell_share = calc_census_cell_share(
880
        scenario=scenario, sector=sector
881
    )
882
883
    df_demand_share = pd.merge(
884
        left=df_building_amenity_share,
885
        right=df_census_cell_share,
886
        left_on="zensus_population_id",
887
        right_on="zensus_population_id",
888
    )
889
    df_demand_share["profile_share"] = df_demand_share[
890
        "building_amenity_share"
891
    ].multiply(df_demand_share["cell_share"])
892
893
    # TODO bus_id fix
894
    df_demand_share = df_demand_share[
895
        ["id", "bus_id", "scenario", "profile_share"]
896
    ]
897
    # df_demand_share = df_demand_share[["id", "scenario", "profile_share"]]
898
    #
899
    # # assign bus_id via census cell of building centroid
900
    # with db.session_scope() as session:
901
    #     cells_query = session.query(
902
    #         egon_map_zensus_buildings_filtered_all.id,
903
    #         egon_map_zensus_buildings_filtered_all.zensus_population_id,
904
    #         MapZensusGridDistricts.bus_id,
905
    #     ).filter(
906
    #         MapZensusGridDistricts.zensus_population_id
907
    #         == egon_map_zensus_buildings_filtered_all.zensus_population_id
908
    #     )
909
    #
910
    # df_egon_map_zensus_buildings_buses = pd.read_sql(
911
    #     cells_query.statement,
912
    #     cells_query.session.bind,
913
    #     index_col=None,
914
    # )
915
    # df_demand_share = pd.merge(
916
    #     left=df_demand_share, right=df_egon_map_zensus_buildings_buses, on="id"
917
    # )
918
919
    # TODO adapt groupby?
920
    # Group and aggregate per building for multi cell buildings
921
    df_demand_share = (
922
        df_demand_share.groupby(["scenario", "id", "bus_id"])
923
        .sum()
924
        .reset_index()
925
    )
926
    if df_demand_share.duplicated("id", keep=False).any():
927
        print(
928
            df_demand_share.loc[df_demand_share.duplicated("id", keep=False)]
929
        )
930
    return df_demand_share
931
932
933
def calc_building_profiles(
934
    egon_building_id=None,
935
    bus_id=None,
936
    scenario="eGon2035",
937
    sector="electricity",
938
):
939
    """
940
    Calculate the demand profile for each building. The profile is
941
    calculated by the demand share of the building per substation bus.
942
943
    Parameters
944
    ----------
945
    egon_building_id: int
946
        Id of the building for which the profile is calculated.
947
        If not given, the profiles are calculated for all buildings.
948
    bus_id: int
949
        Id of the substation for which the all profiles are calculated.
950
        If not given, the profiles are calculated for all buildings.
951
    scenario: str
952
        Scenario for which the share is calculated.
953
    sector: str
954
        Sector for which the share is calculated.
955
956
    Returns
957
    -------
958
    df_building_profiles: pd.DataFrame
959
        Table of demand profile per building
960
    """
961
    if sector == "electricity":
962
        with db.session_scope() as session:
963
            cells_query = session.query(
964
                EgonCtsElectricityDemandBuildingShare,
965
            ).filter(
966
                EgonCtsElectricityDemandBuildingShare.scenario == scenario
967
            )
968
969
        df_demand_share = pd.read_sql(
970
            cells_query.statement, cells_query.session.bind, index_col=None
971
        )
972
973
        # TODO maybe use demand.egon_etrago_electricity_cts
974
        # with db.session_scope() as session:
975
        #     cells_query = (
976
        #         session.query(
977
        #             EgonEtragoElectricityCts
978
        #         ).filter(
979
        #             EgonEtragoElectricityCts.scn_name == scenario)
980
        #     )
981
        #
982
        # df_cts_profiles = pd.read_sql(
983
        #     cells_query.statement,
984
        #     cells_query.session.bind,
985
        # )
986
        # df_cts_profiles = pd.DataFrame.from_dict(
987
        #   df_cts_profiles.set_index('bus_id')['p_set'].to_dict(),
988
        #   orient="index")
989
        df_cts_profiles = calc_load_curves_cts(scenario)
990
991
    elif sector == "heat":
992
        with db.session_scope() as session:
993
            cells_query = session.query(
994
                EgonCtsHeatDemandBuildingShare,
995
            ).filter(EgonCtsHeatDemandBuildingShare.scenario == scenario)
996
997
        df_demand_share = pd.read_sql(
998
            cells_query.statement, cells_query.session.bind, index_col=None
999
        )
1000
1001
        # TODO cts heat substation profiles missing
1002
1003
    # get demand share of selected building id
1004
    if isinstance(egon_building_id, int):
1005
        if egon_building_id in df_demand_share["id"]:
0 ignored issues
show
introduced by
The variable df_demand_share does not seem to be defined for all execution paths.
Loading history...
1006
            df_demand_share = df_demand_share.loc[
1007
                df_demand_share["id"] == egon_building_id
1008
            ]
1009
        else:
1010
            raise KeyError(f"Building with id {egon_building_id} not found")
1011
    # TODO maybe add list
1012
    # elif isinstance(egon_building_id, list):
1013
1014
    # get demand share of all buildings for selected bus id
1015
    if isinstance(bus_id, int):
1016
        if bus_id in df_demand_share["bus_id"]:
1017
            df_demand_share = df_demand_share.loc[
1018
                df_demand_share["bus_id"] == bus_id
1019
            ]
1020
        else:
1021
            raise KeyError(f"Bus with id {bus_id} not found")
1022
1023
    # get demand profile for all buildings for selected demand share
1024
    # TODO takes a few seconds per iteration
1025
    df_building_profiles = pd.DataFrame()
1026
    for bus_id, df in df_demand_share.groupby("bus_id"):
1027
        shares = df.set_index("id", drop=True)["profile_share"]
1028
        profile = df_cts_profiles.loc[:, bus_id]
0 ignored issues
show
introduced by
The variable df_cts_profiles does not seem to be defined for all execution paths.
Loading history...
1029
        # building_profiles = profile.apply(lambda x: x * shares)
1030
        building_profiles = np.outer(profile, shares)
1031
        building_profiles = pd.DataFrame(
1032
            building_profiles, index=profile.index, columns=shares.index
1033
        )
1034
        df_building_profiles = pd.concat(
1035
            [df_building_profiles, building_profiles], axis=1
1036
        )
1037
1038
    return df_building_profiles
1039
1040
1041
def delete_synthetic_cts_buildings():
1042
    """
1043
    All synthetic cts buildings are deleted from the DB. This is necessary if
1044
    the task is run multiple times as the existing synthetic buildings
1045
    influence the results.
1046
    """
1047
    # import db tables
1048
    from saio.openstreetmap import osm_buildings_synthetic
1049
1050
    # cells mit amenities
1051
    with db.session_scope() as session:
1052
        session.query(osm_buildings_synthetic).filter(
1053
            osm_buildings_synthetic.building == "cts"
1054
        ).delete()
1055
1056
1057
def remove_double_bus_id(df_cts_buildings):
1058
    """"""
1059
    # from saio.boundaries import egon_map_zensus_buildings_filtered_all
1060
1061
    # assign bus_id via census cell of building centroid
1062
    with db.session_scope() as session:
1063
        cells_query = session.query(
1064
            # egon_map_zensus_buildings_filtered_all.id,
1065
            # egon_map_zensus_buildings_filtered_all.zensus_population_id,
1066
            MapZensusGridDistricts.zensus_population_id,
1067
            MapZensusGridDistricts.bus_id
1068
            # ).filter(
1069
            #     MapZensusGridDistricts.zensus_population_id
1070
            #     == egon_map_zensus_buildings_filtered_all.zensus_population_id
1071
        )
1072
1073
    df_egon_map_zensus_buildings_buses = pd.read_sql(
1074
        cells_query.statement,
1075
        cells_query.session.bind,
1076
        index_col=None,
1077
    )
1078
    df_cts_buildings = pd.merge(
1079
        left=df_cts_buildings,
1080
        right=df_egon_map_zensus_buildings_buses,
1081
        on="zensus_population_id",
1082
    )
1083
1084
    substation_per_building = df_cts_buildings.groupby("id")[
1085
        "bus_id"
1086
    ].nunique()
1087
    building_id = substation_per_building.loc[
1088
        substation_per_building > 1
1089
    ].index
1090
    df_duplicates = df_cts_buildings.loc[
1091
        df_cts_buildings["id"].isin(building_id)
1092
    ]
1093
    for unique_id in df_duplicates["id"].unique():
1094
        drop_index = df_duplicates[df_duplicates["id"] == unique_id].index[0]
1095
        print(
1096
            f"Buildings {df_cts_buildings.loc[drop_index, 'id']}"
1097
            f" dropped because of double substation"
1098
        )
1099
        df_cts_buildings.drop(index=drop_index, inplace=True)
1100
1101
    df_cts_buildings.drop(columns="bus_id", inplace=True)
1102
1103
    return df_cts_buildings
1104
1105
1106
def cts_buildings():
1107
    """
1108
    Assigns CTS demand to buildings and calculates the respective demand
1109
    profiles. The demand profile per substation are disaggregated per
1110
    annual demand share of each census cell and by the number of amenities
1111
    per building within the cell. If no building data is available,
1112
    synthetic buildings are generated around the amenities. If no amenities
1113
    but cts demand is available, buildings are randomly selected. If no
1114
    building nor amenity is available, random synthetic buildings are
1115
    generated. The demand share is stored in the database.
1116
1117
    Note:
1118
    -----
1119
    Cells with CTS demand, amenities and buildings do not change within
1120
    the scenarios, only the demand itself. Therefore scenario eGon2035
1121
    can be used universally to determine the cts buildings but not for
1122
    he demand share.
1123
    """
1124
1125
    log.info("Start logging!")
1126
    # Buildings with amenities
1127
    df_buildings_with_amenities, df_lost_cells = buildings_with_amenities()
1128
    log.info("Buildings with amenities selected!")
1129
1130
    # Median number of amenities per cell
1131
    median_n_amenities = int(
1132
        df_buildings_with_amenities.groupby("zensus_population_id")[
1133
            "n_amenities_inside"
1134
        ]
1135
        .sum()
1136
        .median()
1137
    )
1138
    log.info(f"Median amenity value: {median_n_amenities}")
1139
1140
    # Remove synthetic CTS buildings if existing
1141
    delete_synthetic_cts_buildings()
1142
    log.info("Old synthetic cts buildings deleted!")
1143
1144
    # Amenities not assigned to buildings
1145
    df_amenities_without_buildings = amenities_without_buildings()
1146
    log.info("Amenities without buildlings selected!")
1147
1148
    # Append lost cells due to duplicated ids, to cover all demand cells
1149
    if not df_lost_cells.empty:
1150
1151
        df_lost_cells["amenities"] = median_n_amenities
1152
        # create row for every amenity
1153
        df_lost_cells["amenities"] = (
1154
            df_lost_cells["amenities"].astype(int).apply(range)
1155
        )
1156
        df_lost_cells = df_lost_cells.explode("amenities")
1157
        df_lost_cells.drop(columns="amenities", inplace=True)
1158
        df_amenities_without_buildings = df_amenities_without_buildings.append(
1159
            df_lost_cells, ignore_index=True
1160
        )
1161
        log.info("Lost cells due to substation intersection appended!")
1162
1163
    # One building per amenity
1164
    df_amenities_without_buildings["n_amenities_inside"] = 1
1165
    # Create synthetic buildings for amenites without buildings
1166
    df_synthetic_buildings_with_amenities = create_synthetic_buildings(
1167
        df_amenities_without_buildings, points="geom_amenity"
1168
    )
1169
    log.info("Synthetic buildings created!")
1170
1171
    # TODO write to DB and remove renaming
1172
    write_table_to_postgis(
1173
        df_synthetic_buildings_with_amenities.rename(
1174
            columns={
1175
                "zensus_population_id": "cell_id",
1176
                "egon_building_id": "id",
1177
            }
1178
        ),
1179
        OsmBuildingsSynthetic,
1180
        drop=False,
1181
    )
1182
    log.info("Synthetic buildings exported to DB!")
1183
1184
    # Cells without amenities but CTS demand and buildings
1185
    df_buildings_without_amenities = buildings_without_amenities()
1186
    log.info("Buildings without amenities in demand cells identified!")
1187
1188
    # TODO Fix Adhoc Bugfix duplicated buildings
1189
    # drop building ids which have already been used
1190
    mask = df_buildings_without_amenities.loc[
1191
        df_buildings_without_amenities["id"].isin(
1192
            df_buildings_with_amenities["id"]
1193
        )
1194
    ].index
1195
    df_buildings_without_amenities = df_buildings_without_amenities.drop(
1196
        index=mask
1197
    ).reset_index(drop=True)
1198
    log.info(f"{len(mask)} duplicated ids removed!")
1199
1200
    # select median n buildings per cell
1201
    df_buildings_without_amenities = select_cts_buildings(
1202
        df_buildings_without_amenities, max_n=median_n_amenities
1203
    )
1204
    df_buildings_without_amenities["n_amenities_inside"] = 1
1205
    log.info(f"{median_n_amenities} buildings per cell selected!")
1206
1207
    # Create synthetic amenities and buildings in cells with only CTS demand
1208
    df_cells_with_cts_demand_only = cells_with_cts_demand_only(
1209
        df_buildings_without_amenities
1210
    )
1211
    log.info("Cells with only demand identified!")
1212
1213
    # Median n Amenities per cell
1214
    df_cells_with_cts_demand_only["amenities"] = median_n_amenities
1215
    # create row for every amenity
1216
    df_cells_with_cts_demand_only["amenities"] = (
1217
        df_cells_with_cts_demand_only["amenities"].astype(int).apply(range)
1218
    )
1219
    df_cells_with_cts_demand_only = df_cells_with_cts_demand_only.explode(
1220
        "amenities"
1221
    )
1222
    df_cells_with_cts_demand_only.drop(columns="amenities", inplace=True)
1223
1224
    # Only 1 Amenity per Building
1225
    df_cells_with_cts_demand_only["n_amenities_inside"] = 1
1226
    df_cells_with_cts_demand_only = place_buildings_with_amenities(
1227
        df_cells_with_cts_demand_only, amenities=1
1228
    )
1229
    df_synthetic_buildings_without_amenities = create_synthetic_buildings(
1230
        df_cells_with_cts_demand_only, points="geom_point"
1231
    )
1232
    log.info(f"{median_n_amenities} synthetic buildings per cell created")
1233
1234
    # TODO write to DB and remove (backup) renaming
1235
    write_table_to_postgis(
1236
        df_synthetic_buildings_without_amenities.rename(
1237
            columns={
1238
                "zensus_population_id": "cell_id",
1239
                "egon_building_id": "id",
1240
            }
1241
        ),
1242
        OsmBuildingsSynthetic,
1243
        drop=False,
1244
    )
1245
    log.info("Synthetic buildings exported to DB")
1246
1247
    # Concat all buildings
1248
    columns = [
1249
        "zensus_population_id",
1250
        "id",
1251
        "geom_building",
1252
        "n_amenities_inside",
1253
        "source",
1254
    ]
1255
1256
    df_buildings_with_amenities["source"] = "bwa"
1257
    df_synthetic_buildings_with_amenities["source"] = "sbwa"
1258
    df_buildings_without_amenities["source"] = "bwoa"
1259
    df_synthetic_buildings_without_amenities["source"] = "sbwoa"
1260
1261
    df_cts_buildings = pd.concat(
1262
        [
1263
            df_buildings_with_amenities[columns],
1264
            df_synthetic_buildings_with_amenities[columns],
1265
            df_buildings_without_amenities[columns],
1266
            df_synthetic_buildings_without_amenities[columns],
1267
        ],
1268
        axis=0,
1269
        ignore_index=True,
1270
    )
1271
    df_cts_buildings = remove_double_bus_id(df_cts_buildings)
1272
    log.info("Double bus_id checked")
1273
1274
    # TODO maybe remove after #772
1275
    df_cts_buildings["id"] = df_cts_buildings["id"].astype(int)
1276
1277
    # Write table to db for debugging
1278
    # TODO remove later
1279
    df_cts_buildings = gpd.GeoDataFrame(
1280
        df_cts_buildings, geometry="geom_building", crs=3035
1281
    )
1282
    df_cts_buildings = df_cts_buildings.reset_index().rename(
1283
        columns={"index": "serial"}
1284
    )
1285
    write_table_to_postgis(
1286
        df_cts_buildings,
1287
        CtsBuildings,
1288
        drop=True,
1289
    )
1290
    log.info("CTS buildings exported to DB!")
1291
1292
1293 View Code Duplication
def cts_electricity():
0 ignored issues
show
Duplication introduced by
This code seems to be duplicated in your project.
Loading history...
1294
    """
1295
    Calculate cts electricity demand share of hvmv substation profile
1296
     for buildings.
1297
    """
1298
    log.info("Start logging!")
1299
    with db.session_scope() as session:
1300
        cells_query = session.query(CtsBuildings)
1301
1302
    df_cts_buildings = pd.read_sql(
1303
        cells_query.statement, cells_query.session.bind, index_col=None
1304
    )
1305
    log.info("CTS buildings from DB imported!")
1306
    df_demand_share_2035 = calc_building_demand_profile_share(
1307
        df_cts_buildings, scenario="eGon2035", sector="electricity"
1308
    )
1309
    log.info("Profile share for egon2035 calculated!")
1310
    df_demand_share_100RE = calc_building_demand_profile_share(
1311
        df_cts_buildings, scenario="eGon100RE", sector="electricity"
1312
    )
1313
    log.info("Profile share for egon100RE calculated!")
1314
    df_demand_share = pd.concat(
1315
        [df_demand_share_2035, df_demand_share_100RE],
1316
        axis=0,
1317
        ignore_index=True,
1318
    )
1319
1320
    write_table_to_postgres(
1321
        df_demand_share, EgonCtsElectricityDemandBuildingShare, drop=True
1322
    )
1323
    log.info("Profile share exported to DB!")
1324
1325
1326 View Code Duplication
def cts_heat():
0 ignored issues
show
Duplication introduced by
This code seems to be duplicated in your project.
Loading history...
1327
    """
1328
    Calculate cts electricity demand share of hvmv substation profile
1329
     for buildings.
1330
    """
1331
    log.info("Start logging!")
1332
    with db.session_scope() as session:
1333
        cells_query = session.query(CtsBuildings)
1334
1335
    df_cts_buildings = pd.read_sql(
1336
        cells_query.statement, cells_query.session.bind, index_col=None
1337
    )
1338
    log.info("CTS buildings from DB imported!")
1339
1340
    df_demand_share_2035 = calc_building_demand_profile_share(
1341
        df_cts_buildings, scenario="eGon2035", sector="heat"
1342
    )
1343
    log.info("Profile share for egon2035 calculated!")
1344
    df_demand_share_100RE = calc_building_demand_profile_share(
1345
        df_cts_buildings, scenario="eGon100RE", sector="heat"
1346
    )
1347
    log.info("Profile share for egon100RE calculated!")
1348
    df_demand_share = pd.concat(
1349
        [df_demand_share_2035, df_demand_share_100RE],
1350
        axis=0,
1351
        ignore_index=True,
1352
    )
1353
1354
    write_table_to_postgres(
1355
        df_demand_share, EgonCtsHeatDemandBuildingShare, drop=True
1356
    )
1357
    log.info("Profile share exported to DB!")
1358
1359
1360
def get_cts_electricity_peak_load():
1361
    """
1362
    Get electricity peak load of all CTS buildings for both scenarios and
1363
    store in DB.
1364
    """
1365
    log.info("Start logging!")
1366
    # Delete rows with cts demand
1367
    with db.session_scope() as session:
1368
        session.query(BuildingPeakLoads).filter(
1369
            BuildingPeakLoads.sector == "cts"
1370
        ).delete()
1371
    log.info("CTS Peak load removed from DB!")
1372
1373
    for scenario in ["eGon2035", "eGon100RE"]:
1374
1375
        with db.session_scope() as session:
1376
            cells_query = session.query(
1377
                EgonCtsElectricityDemandBuildingShare
1378
            ).filter(
1379
                EgonCtsElectricityDemandBuildingShare.scenario == scenario
1380
            )
1381
1382
        df_demand_share = pd.read_sql(
1383
            cells_query.statement, cells_query.session.bind, index_col=None
1384
        )
1385
1386
        df_cts_profiles = calc_load_curves_cts(scenario=scenario)
1387
1388
        df_peak_load = pd.merge(
1389
            left=df_cts_profiles.max(axis=0).astype(float).rename("max"),
1390
            right=df_demand_share,
1391
            left_on="bus_id",
1392
            right_on="bus_id",
1393
        )
1394
1395
        # Convert unit from MWh to W
1396
        df_peak_load["max"] = df_peak_load["max"] * 1e6
1397
        df_peak_load["peak_load_in_w"] = (
1398
            df_peak_load["max"] * df_peak_load["profile_share"]
1399
        )
1400
        log.info(f"Peak load for {scenario} determined!")
1401
1402
        df_peak_load.rename(columns={"id": "building_id"}, inplace=True)
1403
        df_peak_load["sector"] = "cts"
1404
1405
        df_peak_load = df_peak_load[
1406
            ["building_id", "sector", "scenario", "peak_load_in_w"]
1407
        ]
1408
1409
        # Write peak loads into db
1410
        with db.session_scope() as session:
1411
            session.bulk_insert_mappings(
1412
                BuildingPeakLoads,
1413
                df_peak_load.to_dict(orient="records"),
1414
            )
1415
        log.info(f"Peak load for {scenario} exported to DB!")
1416
1417
1418
def get_cts_heat_peak_load():
1419
    """
1420
    Get heat peak load of all CTS buildings for both scenarios and store in DB.
1421
    """
1422
    log.info("Start logging!")
1423
1424
    BuildingHeatPeakLoads.__table__.create(bind=engine, checkfirst=True)
1425
1426
    # Delete rows with cts demand
1427
    with db.session_scope() as session:
1428
        session.query(BuildingHeatPeakLoads).filter(
1429
            BuildingHeatPeakLoads.sector == "cts"
1430
        ).delete()
1431
    log.info("CTS Peak load removed from DB!")
1432
1433
    for scenario in ["eGon2035", "eGon100RE"]:
1434
1435
        with db.session_scope() as session:
1436
            cells_query = session.query(EgonCtsHeatDemandBuildingShare).filter(
1437
                EgonCtsHeatDemandBuildingShare.scenario == scenario
1438
            )
1439
1440
        df_demand_share = pd.read_sql(
1441
            cells_query.statement, cells_query.session.bind, index_col=None
1442
        )
1443
1444
        with db.session_scope() as session:
1445
            cells_query = session.query(EgonEtragoElectricityCts).filter(
1446
                EgonEtragoElectricityCts.scn_name == scenario
1447
            )
1448
1449
        df_cts_profiles = pd.read_sql(
1450
            cells_query.statement,
1451
            cells_query.session.bind,
1452
        )
1453
        df_cts_profiles = pd.DataFrame.from_dict(
1454
            df_cts_profiles.set_index("bus_id")["p_set"].to_dict(),
1455
            orient="index",
1456
        )
1457
1458
        df_peak_load = pd.merge(
1459
            left=df_cts_profiles.max(axis=0).astype(float).rename("max"),
1460
            right=df_demand_share,
1461
            left_on="bus_id",
1462
            right_on="bus_id",
1463
        )
1464
1465
        # Convert unit from MWh to W
1466
        df_peak_load["max"] = df_peak_load["max"] * 1e6
1467
        df_peak_load["peak_load_in_w"] = (
1468
            df_peak_load["max"] * df_peak_load["profile_share"]
1469
        )
1470
        log.info(f"Peak load for {scenario} determined!")
1471
1472
        df_peak_load.rename(columns={"id": "building_id"}, inplace=True)
1473
        df_peak_load["sector"] = "cts"
1474
1475
        df_peak_load = df_peak_load[
1476
            ["building_id", "sector", "scenario", "peak_load_in_w"]
1477
        ]
1478
1479
        # Write peak loads into db
1480
        with db.session_scope() as session:
1481
            session.bulk_insert_mappings(
1482
                BuildingHeatPeakLoads,
1483
                df_peak_load.to_dict(orient="records"),
1484
            )
1485
        log.info(f"Peak load for {scenario} exported to DB!")
1486
1487
1488
class CtsElectricityBuildings(Dataset):
1489
    def __init__(self, dependencies):
1490
        super().__init__(
1491
            name="CtsElectricityBuildings",
1492
            version="0.0.0",
1493
            dependencies=dependencies,
1494
            tasks=(
1495
                cts_buildings,
1496
                {cts_electricity, cts_heat},
1497
                get_cts_electricity_peak_load,
1498
            ),
1499
        )
1500