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.
On 02/08/2017 05:41 PM, Andrew M wrote:
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 <http://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
<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
<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
<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+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
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.
