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?

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 )
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
  • Sadly that is not valid syntax in python. (Oops, let me add that tag now) – Andrew Wong Apr 18 '16 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 Apr 18 '16 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 Apr 19 '16 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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