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.
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()