3

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

  • How did you import to PostGIS did you set the coordinate system? – Mapperz Dec 11 '13 at 15:29
  • 1
    With your current data as-is try the SELECT with a transform SELECT name, ST_AsText(ST_Transform(way,4326)) – Mapperz Dec 11 '13 at 15:35
  • 1
    As to why you're not seeing lat / long values, OSM's native projection is WGS84 / Web Mercator, the values you saw were in meters. wiki.openstreetmap.org/wiki/EPSG:3857 – HeyOverThere Dec 11 '13 at 16:46
5

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

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.