CODEWITHSUNDEEP
pythonpostgresqlsqlalchemypostgisgeoalchemy2
Andrew Mackie
Andrew Mackie

Reputation: 374

Geoalchemy2 & ST_Within - type mismatch between point and polygon?

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:

Upvotes: 2

Views: 1989

Answers (2)

bartolo-otrit
bartolo-otrit

Reputation: 2519

If you want to

return every point which falls within a rectangle, where the points and the rectangle are based on real-world longitudes and latitudes

using geography data type, you may use ST_Intersects and ST_Covers, because they support geography data type and give more accurate results.

PostGIS documentation: "Functions on geographies (areas, distances, lengths, intersections, etc) are calculated using arcs on the sphere. By taking the spheroidal shape of the world into account, the functions provide more accurate results."

On the other hand they "take more CPU time to execute" [1]

There is a list of PostGIS Geography Support Functions

Upvotes: 0

Andrew Mackie
Andrew Mackie

Reputation: 374

Thanks to help from elsewhere (Mike Bayer and Greg Baker), I can post an answer.

The problem was that:

  1. my point was of type Geography and my polygon was type Geometry, and
  2. many other PostGIS functions, including ST_Within, do not support geographies (i.e. they only support geometries).

The answer is to cast the Geography as a Geometry in the query. The following query works:

results = session.query(Store.id).filter(func.ST_Within(cast(Store.location, Geometry), func.ST_GeomFromEWKT('SRID=4326;POLYGON((150 -33, 152 -33, 152 -31, 150 -31, 150 -33))')))

Upvotes: 1

Related Questions