Hi,
I'm stuck on a query which might be a PostGIS problem, sorry, but in case
it relates to the SQLAlchemy side (or someone can help regardless) I'm
posting it here.
I want to run a query which returns every point which falls within a
rectangle, where the points and the rectangle are based on real-world
longitudes and latitudes.
This is the query which fails:
results = session.query(Store.id).filter(func.ST_Within(Store.location,
func.ST_GeomFromEWKT('SRID=4326;POLYGON((150 -33, 152 -33, 152 -31, 150
-31, 150 -33))')))
It runs without complaint, but when calling results.first(), I see the
following errors and warnings:
>sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) function
st_within(geography, geometry) does not exist
LINE 3: WHERE ST_Within(store.location, ST_GeomFromEWKT('SRID=4326;P...
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
[SQL: 'SELECT store.id AS store_id \nFROM store \nWHERE
ST_Within(store.location, ST_GeomFromEWKT(%(ST_GeomFromEWKT_1)s
)) \n LIMIT %(param_1)s'] [parameters: {'ST_GeomFromEWKT_1':
'SRID=4326;POLYGON((150 -33, 152 -33, 152 -31, 150 -31, 150
-33))', 'param_1': 1}]
I can make the query work, however, by creating a dummy point in the query
(which causes every store to be matched):
results =
session.query(Store.id).filter(func.ST_Within(func.ST_GeomFromEWKT('SRID=4326;POINT(151
-32)'), func.ST_GeomFromEWKT('SRID=4326;POLYGON((150 -33, 152 -33, 152 -31,
150 -31, 150 -33))')))
This would indicate that the problem is my Store.location field, but
nothing I've tried [including type_coerce(Store.location, Geoography)] has
worked.
This is my SQLAlchemy definition for the location column:
location = Column(Geography(geometry_type='POINT', srid=4326))
This is the code I ran to turn longitude & latitude into a location (and
I've also tried using func.ST_GeomFromEWKT() to coerce the type):
stores = session.query(Store)
for store in stores:
store.location = 'SRID=4326;POINT({} {})'.format(store.longitude,
store.latitude)
session.commit()
Python tells me that the type of Store.location is
'geoalchemy2.elements.WKBElement', which is what I'd expect from the
documentation.
Does anyone have any suggestions on how to fix the query, please?
FYI I'm running:
- Python 3.6
- PostgreSQL 9.6.1
- PostGIS 2.3.2
- psycopg2 2.6.2
- SQLAlchemy 1.1.4, and
- Geoalchemy2 0.4.0
Thanks,
Andrew
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
