[BUG] Error in heat_pump_import due to invalid database query or multipolygon handling

[joda9]

Describe the bug
calling the function import_heat_pumps_oedb returns an error. This happens in here

from edisgo.edisgo import import_heat_pumps_oedb
from edisgo.io.db import engine
edisgo = import_heat_pumps_oedb(edisgo_object=edisgo_day, engine=engine(), scenario='eGon2035')

Expected behavior
When importing the three tables individually, everything works fine. However, when attempting to join them, either another error occurs or the resulting dataframe is empty. This seems to be because of a problem with the multipolygon in gdf_district_heating_areas.

Here is the code responsible for querying and joining the tables:

# Query for egon_era5_weather_cells
query_weather_cells = (
    session.query(
        egon_era5_weather_cells.w_id.label("weather_cell_id"),
        egon_era5_weather_cells.geom
    )
)
gdf_weather_cells = gpd.read_postgis(
    query_weather_cells.statement,
    engine,
    index_col=None,
    crs=f"EPSG:{edisgo_object.topology.grid_district['srid']}",
).to_crs(mv_grid_geom_srid)

# Query for egon_district_heating_areas
query_district_heating_areas = (
    session.query(
        egon_district_heating_areas.id.label("district_heating_id"),
        egon_district_heating_areas.area_id,
        egon_district_heating_areas.geom_polygon,
        egon_district_heating_areas.scenario
    ).filter(
        egon_district_heating_areas.scenario == scenario
    )
)
gdf_district_heating_areas = gpd.read_postgis(
    query_district_heating_areas.statement,
    engine,
    geom_col="geom_polygon",
    index_col=None,
    crs=f"EPSG:{edisgo_object.topology.grid_district['srid']}",
).to_crs(mv_grid_geom_srid)
# Ensure all GeoDataFrames have the same CRS (Coordinate Reference System)
srid_etrago_bus = db.get_srid_of_db_table(session, egon_etrago_bus.geom)
gdf_etrago_bus = gdf_etrago_bus.set_crs(epsg=srid_etrago_bus)
gdf_weather_cells = gdf_weather_cells.set_crs(epsg=srid_etrago_bus)
gdf_district_heating_areas = gdf_district_heating_areas.set_crs(epsg=srid_etrago_bus)

# Perform spatial join with district heating 
df_geom = gpd.sjoin(gdf_combined, gdf_district_heating_areas, how='left', predicate='intersects', rsuffix='_district_heating')

[joda9]

from edisgo.tools.config import Config
from edisgo.io.db import engine, session_scope_egon_data
import pandas as pd
from sqlalchemy import func
from edisgo.io import db
import geopandas as gpd
from edisgo.io.db import get_srid_of_db_table

# Define scenario and carrier
scenario = 'eGon2035'
carrier = "central_heat_pump"

# Initialize configuration and import tables
config = Config()
egon_etrago_bus, egon_etrago_link = config.import_tables_from_oep(
    engine(), ["egon_etrago_bus", "egon_etrago_link"], "supply"
)
egon_era5_weather_cells, = config.import_tables_from_oep(
    engine(), ["egon_era5_weather_cells"], "supply"
)
egon_district_heating_areas, = config.import_tables_from_oep(
    engine(), ["egon_district_heating_areas"], "supply"
)

# Define edisgo object and spatial reference IDs
edisgo_object = edisgo_day
mv_grid_geom_srid = edisgo_object.topology.grid_district["srid"]

# Start session to query the database
with session_scope_egon_data(engine()) as session:
    # Get SRID of egon_etrago_bus table
    srid_etrago_bus = db.get_srid_of_db_table(session, egon_etrago_bus.geom)

    # Query to get heat pumps / resistive heaters in the grid
    query = session.query(
        egon_etrago_link.bus0,
        egon_etrago_link.bus1,
        egon_etrago_link.p_nom.label("p_set"),
    ).filter(
        egon_etrago_link.scn_name == scenario,
        egon_etrago_link.bus0 == edisgo_object.topology.id,
        egon_etrago_link.carrier == carrier,
        egon_etrago_link.p_nom <= edisgo_object.config["grid_connection"]["upper_limit_voltage_level_4"],
    )
    df = pd.read_sql(query.statement, engine(), index_col=None)

    # Query to get bus information and join with weather cells and district heating areas
    query = session.query(
        egon_etrago_bus.bus_id.label("bus1"),
        egon_etrago_bus.geom,
        # egon_era5_weather_cells.w_id.label("weather_cell_id"),
        # egon_district_heating_areas.id.label("district_heating_id"),
        # egon_district_heating_areas.area_id,
    # ).filter(
    #     egon_etrago_bus.scn_name == scenario,
    #     egon_district_heating_areas.scenario == scenario,
    #     egon_etrago_bus.bus_id.in_(df.bus1),
    # ).outerjoin(
    #     egon_era5_weather_cells,
    #     db.sql_within(egon_etrago_bus.geom, egon_era5_weather_cells.geom, mv_grid_geom_srid),
    # ).outerjoin(
    #     egon_district_heating_areas,
    #     func.ST_Transform(func.ST_Centroid(egon_district_heating_areas.geom_polygon), srid_etrago_bus) == egon_etrago_bus.geom,
    )
    df = pd.read_sql(query.statement, engine(), index_col=None)
    gdf = gpd.read_postgis(
        query.statement,
        engine(),
        geom_col="geom",
        index_col=None,
        crs=f"EPSG:{srid_etrago_bus}",
    ).to_crs(mv_grid_geom_srid)

    # Query for egon_era5_weather_cells
    query_weather_cells = session.query(
        egon_era5_weather_cells.w_id.label("weather_cell_id"),
        egon_era5_weather_cells.geom
    )
    gdf_weather_cells = gpd.read_postgis(
        query_weather_cells.statement,
        engine(),
        index_col=None,
        crs=f"EPSG:{edisgo_object.topology.grid_district['srid']}",
    ).to_crs(mv_grid_geom_srid)
    
    # Query for egon_district_heating_areas
    query_district_heating_areas = session.query(
        egon_district_heating_areas.id.label("district_heating_id"),
        egon_district_heating_areas.area_id,
        egon_district_heating_areas.geom_polygon,
        egon_district_heating_areas.scenario
    ).filter(
        egon_district_heating_areas.scenario == scenario
    )
    gdf_district_heating_areas = gpd.read_postgis(
        query_district_heating_areas.statement,
        engine(),
        geom_col="geom_polygon",
        index_col=None,
        crs=f"EPSG:{get_srid_of_db_table(session, egon_district_heating_areas.geom_polygon)}",
    ).to_crs(mv_grid_geom_srid)

this works fine so far, will test it on monday. Was a problem with the srid