How to set extent of spatial SQL query in python (geoalchemy)

Question

since days i've been hustling around trying to figure out, how to properly query data from a PostGIS database using geoalchemy2, an extension to python's sqlalchemy supporting spatial database operations.

I'm working with python3.4 and Openstreetmaps data from Brandenburg (admin area within Germany) that i fed into my local Postgres-DB. Data is in lat/long. I've been following the tutorials on how to set things up using the ORM part of the geoalchemy package (https://geoalchemy-2.readthedocs.org/en/latest/orm_tutorial.html). In the beginning, everything went fine

1. Define a mapping

   Base = declarative_base()
   
   class QuerySchema(Base):
      __tablename__ = "brandenburg_polygon"
      osm_id = Column(Integer, primary_key=True)
      name = Column(String)
      amenity = Column(String)
      way = Column(Geometry('POLYGON'))
   

2. Define DB setup

   engine = create_engine(
   'postgresql+psycopg2://postgres_andi:{pwd}@localhost/osm'.format(
       pwd=keyring.get_password('osm', 'andi_postgres')))
   Session = sessionmaker(bind=engine)
   session = Session()
   

3. Do my query

   buildings = session.query(QuerySchema)
   

Now, everything is working fine until i try to reduce the extent - for i don't want to have all buildings stored inside my DB but maybe only those within a given boundary or a boundary polygon.

4. Reduce the extent by defining a boundary box (WKT-format)

   bbox = 'POLYGON ((13.01881424267171 52.50091209200498, 13.01881424267171 52.57800809377812, 12.87181701302189 52.57800809377812, 12.87181701302189 52.50091209200498, 13.01881424267171 52.50091209200498))'
   

I tried using .filter() with various options but it didn't work out. So far i understood, that filter() needs some kind of bool input, so one has to define a statement according to that. So what is wrong with a statement like this?

    session.query(QuerySchema).filter(func.ST_Contains(bbox, QuerySchema.way))

Checking the result of func.ST_Contains(bbox, QuerySchema.way) brings <geoalchemy2.functions.ST_Contains at 0x10a12a400; ST_Contains>so obviously filter() won't work properly.

Question: How do I have to perform the operation to work properly, i.e. giving me only those DB entries within a given boundary?

Answer 1

Try this query:

session.query(QuerySchema).filter(QuerySchema.way.ST_Within(bbox))