pythonsqlalchemypostgisgeoalchemy2

How to calculate ST_Union with GeoAlchemy2?


I have a many-to-many relationship, with instances of OsmAdminUnit (polygon geometries) grouped into OsmAdminAgg instances.

The model definitions are essentially:

class OsmAdminUnit(db.Model):
    __tablename__ = 'osm_admin'

    id          = db.Column(db.Integer, primary_key=True)
    geometry    = db.Column(Geometry(
                    geometry_type='GEOMETRY', 
                    srid=3857), nullable=False)
    agg_units   = db.relationship('OsmAdminAgg',
                    secondary=aggregations,
                    backref=db.backref('osm_admin', lazy='dynamic'))

class OsmAdminAgg(db.Model):
    __tablename__ = 'admin_agg'

    id   = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=False)

Now what I am struggling to do is selecting OsmAdminUnits that belong to a certain OsmAdminAgg AND getting the polgyons merged by applying ST_Union from GeoAlchemy.

Selecting all admin units that belong to admin agg with id=1 works:

units = OsmAdminUnit.query.filter(OsmAdminUnit.agg_units.any(id=1)).all()

But I don't get how I can apply ST_Union on that result. My approach so far was:

union = db.session.query(
        OsmAdminUnit.geometry.ST_Union().ST_AsGeoJSON().label('agg_union')
        ).filter(OsmAdminUnit.agg_units.any(id=1)).subquery()

So how do I get the union of these geometries, and get it as GeoJSON?

Btw, I am building this on top of Flask, using SQLAlchemy, Flask-SQLAlchemy, Geoalchemy2.


Solution

  • Try this:

    from sqlalchemy.sql.functions import func
    
    union = db.session.query(func.ST_AsGeoJSON(func.ST_Union(
        OsmAdminUnit.geometry)).label('agg_union')
        ).filter(OsmAdminUnit.agg_units.any(id=1)).subquery()