How to set volume of spatial SQL query in python (geo-alchemy)

Question

ever since I was bustling trying to figure out how to correctly query data from a PostGIS database using geoalchemy2, an extension for sqlalchemy python that supports spatial database operations.

I am working with python3.4 and Openstreetmaps data from Brandenburg (administrative area in Germany), which I uploaded to my local Postgres-DB. Data in lat / long. I follow 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 well.

• 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'))
  

• Define database 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()
  

• Follow my request

  buildings = session.query(QuerySchema)
  

, - , , , , , .

4. , (WKT-)

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

.filter() , . , filter() bool, . , ?

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

func.ST_Contains(bbox, QuerySchema.way) <geoalchemy2.functions.ST_Contains at 0x10a12a400; ST_Contains>, , filter() .

: , .. ?

Answer 1

Try this query:

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