[GIS] Retrieve geographic coordinates from OSM imported data

geometryimportopenstreetmappostgresql

We have just finished importing OSM data into our PostGIS (Postgresql) database. And we are having difficulty understanding the geometry of various shapes imported.

We run a query to retrieve the lines, for example:

SELECT osm_id, "access", "addr:housename", "addr:housenumber", "addr:interpolation", layer, leisure, "lock", man_made, maxspeed, ST_asText(way) FROM planet_osm_line

This returns us all the lines. However, when we inspect the geometry (way column), we find something like this:

"LINESTRING(-8426184.85 4679341.47,-8426249.88 4679495.66,-8426327.24 4679668.92,-8426374.7 4679770.75,-8426379.19 4679784.19,-8426381.28 4679795.83,-8426381.56 4679810.66,-8426384.24 4679827.68,-8426389.03 4679840.52,-8426396.19 4679859.94,-8426421.87 4679923.03)"

We are unable to understand the coordinates shown above. We are expecting to get geographic coordinates, like

-47.34572, 82.98789

. Instead we are getting coordinates like

-8426184.85 4679341.47

Any help with this? Thanks in advance

Best Answer

The format you need (standard longitude, latitude) can be obtained by converting the current Reference System to World Geodetic System whose identifier (SRID) is 4326.

In order to do so, use the function st_transform(way, 4326) (returns a Geometry).

So, for example, in a query similar to yours it would be:

SELECT st_asText(st_transform(way, 4326)) FROM planet_osm_line
Related Question