CODEWITHSUNDEEP
pythonsqlsqlalchemypostgisgeoalchemy
Andreas Neumann
Andreas Neumann

Reputation: 51

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

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.

  1. 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?

Upvotes: 5

Views: 635

Answers (1)

bananafish
bananafish

Reputation: 2917

Try this query:

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

Upvotes: 0

Related Questions