Thanks Mike, it was kind of you to reply and you are, of course, correct.
I think this was a case of type blindness on my part - I hadn't noticed the
difference between 'geography' and 'geometry', and I'm still too much of a
noob (*sigh*) to properly interpret this kind of error message.
Thanks again,
Andrew
On Thursday, February 9, 2017 at 7:18:16 AM UTC+11, Mike Bayer wrote:
>
>
>
> On 02/08/2017 03:06 PM, Andrew M wrote:
> > 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.
>
>
> did you try cast() ? this is a SQL side issue so the appropriate CAST
> would be needed to ensure Postgresql sees the expected types inside of
> st_within().
>
> however, cast(Store.location, Geography) seems like it would still not
> work, because PG is telling you those are not the types expected by
> st_within:
>
> function st_within(geography, geometry) does not exist
>
> per first hit on google:
>
> http://postgis.net/docs/ST_Within.html
>
> the correct types are:
>
> boolean ST_Within(geometry A, geometry B);
>
>
> so you'd need to CAST to geometry, not geography.
>
>
> >
> > 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+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
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.
