[GIS] Casting between PostGIS types in GeoAlchethe

geoalchemyopenstreetmaposm2pgsqlpostgispython

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?

Best Answer

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 )

Related Solutions

PostGIS – Understanding ST_DWithin with Geometry or Geography Type

As per documentation of function it can use both: http://postgis.net/docs/ST_DWithin.html

boolean ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);

boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters);

boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters, boolean use_spheroid);

Watch out for this:

For geography units are in meters and measurement is defaulted to use_spheroid=true (measure around WGS 84 spheroid), for faster check, use_spheroid=false to measure along sphere.

But for 10km buffer radius it shouldn't be a big error.

[GIS] PostGIS – Geometry or Geography find points within distance in meters

The definition of SRID 4326 is in fact IN DEGREES. ALso as it says in the ST_DWithin page:

Returns true if the geometries are within the specified distance of one another. For geometry units are in those of spatial reference and For geography units are in meters and measurement is defaulted to use_spheroid=true (measure around spheroid),

Since you're are using the 4326 SRID, you can cast from geometry to geography and vice versa like this :

select st_distance(<geom_column>::geography) from myTable;

Most of the functions in postgis are overloaded, meaning, they return values and run a different procedure based on the input data type.

eg:

boolean ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);

boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters);

they have the same name, but the first function returns T/F based on units of the srid, and the second returns T/F based on meters.

If you plug into the function geog types, for its calculation, will take into account the curve of the earth so the algorithm 'costs' a bit more.

If you want to check how much more it costs, you can run for each query an EXPLAIN-ANALYSE

Best Answer

Related Solutions

PostGIS – Understanding ST_DWithin with Geometry or Geography Type

[GIS] PostGIS – Geometry or Geography find points within distance in meters

Related Question