1

I'm working with OpenStreetMap data, and loading it into a PostGIS database using osm2pgsql.

The issue that I am currently dealing with is that osm2pgsql stores the shape as a geometry type.

I'm using GeoAlchemy2 to query the Postgres database, but I am having issues with the following query.

query = session.query(Route).filter(func.ST_Distance(Route.way,'SRID=4326;POINT(10.71223 47.69716)') < distance )

Because osm2pgsql stores the data as a Geometry type, it doesn't interpret my points as a geography type, and therefore doesn't filter based on distance in meters, but rather in degrees.

Would there be a way to cast the Geometry type objects (Route.way and the string representation of the point ) to a Geography form?

Improve this question

2 Answers 2

Reset to default
2

I realized that it is possible to cast from the Geometry object to the Geography object using the SQLAlchemy function, despite the two not being native SQLAlchemy objects. 

query = session.query(Route).filter(func.ST_Distance(cast(Route.way,Geography),'SRID=4326;POINT(10.71223 47.69716)') < distance )
Improve this answer
0

If your route.way is already in WGS84, EPSG:4326, then casting should be possible.

I have not used GeoAlchemy2 but based on your syntax it should be:

query = session.query(Route).filter(func.ST_Distance(Route.way::geography,ST_GeomFromText('POINT(10.71223 47.69716)',4326)::geography) < distance
Improve this answer
3
  • Sadly that is not valid syntax in python. (Oops, let me add that tag now)
    – Andrew Wong
    Commented Apr 18, 2016 at 17:24
  • 2
    You could store your data as geography in the database? Simply create a geography column and populate it using "route.way::geography" then you should be able to create your query points in geoalchamy using'ST_GeogFromText'. geoalchemy-2.readthedocs.org/en/0.2.6/_modules/geoalchemy2/…
    – HeikkiVesanto ♦
    Commented Apr 18, 2016 at 23:13
  • 1
    That is a valid option that I failed to consider. It's a bit clunky to add the column in after importing the OSM data, but I'll work with it until I give up on searching for a solution on the side. Thanks!
    – Andrew Wong
    Commented Apr 19, 2016 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.