Type mismatch on a GeoAlchemy2 query?

[Andrew M]

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

[mike bayer]

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
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Andrew M]

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

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

[mike bayer]

no worries, those errors from PG are very common and while I used to be
kind of scared off by them for a long time, I realized they are actually
very useful as PG has strict typing behavior.

> > [SQL: 'SELECT store.id <http://store.id> AS store_id \nFROM store

> > an email to sqlalchemy+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+...@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

> <https://groups.google.com/group/sqlalchemy>.

> > For more options, visit https://groups.google.com/d/optout

> <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+...@googlegroups.com

> <mailto:sqlalchemy+...@googlegroups.com>.