How to get `geom` part from ST_Dump using SQLAlchemy?

Question

I want to translate following SQL-query into the Python code using SQLAlchemy.

SELECT (ST_Dump(ST_Linemerge(ST_Union(geog::geometry)))).geom
FROM my_table
GROUP BY some_foreign_key

I had a partial success with

from geoalchemy2 import Geometry
from sqlalchemy import func, cast

data = session.query(
    func.ST_Dump(
        func.ST_Linemerge(
            func.ST_Union(
                cast(MyTable.geog, Geometry)
            )
        )
    ).label('dumped_geom')
).group_by(
    MyTable.some_foreign_key
)

The issue is that ST_Dump returns set instead of single value and this query in Python returns string with a path and geometry and I need only geometry part. Documentation for ST_Dump in geoalchemy2 doesn't help at all.

you can turn it around: `select geom from st_dump(...);` – JGH Apr 09 '19 at 15:35 — JGH, Apr 09 '19 at 15:35
@JGH any idea how to rewrite query in Python? – SS_Rebelious Apr 10 '19 at 10:33 — SS_Rebelious, Apr 10 '19 at 10:33

score 0 · Answer 1 · answered Sep 05 '19 at 08:32

geoalchemy2 provides a ST_Dump function (wich return an object with a geom property)

Ex:

from geoalchemy2 import Geometry
from geoalchemy2.functions import ST_Dump
from sqlalchemy import cast, func

data = session.query(
    ST_Dump(
        func.ST_Linemerge(
            func.ST_Union(
                cast(MyTable.geog, Geometry)
            )
        )
    ).geom
).group_by(
    MyTable.some_foreign_key
)

How to get `geom` part from ST_Dump using SQLAlchemy?

1 Answers1