Hi I am trying to convert the coordinates stored in the planet_osm_nodes table in my GIS database to normal latitude/longitude coordinates (4326).

Here is an example of what the coordinates in the table look like:

 lat    |    lon    
 754030751 | -39701762

Here is an SQL query that I am using for the conversion:

select ST_AsText(ST_Transform(ST_GeomFromEWKT('SRID=900913;POINT(' || lon || ' ' || lat || ')'), 4326)) from planet_osm_nodes;

However the coordinates are like:

POINT(40.5500029995757 90)

When they should be more like (55.3, -3.10)

Is the SRID that the latitude and longitude in the planet_osm_nodes table 900913 or is it something different?


asked 15 Mar '13, 15:24

srose's gravatar image

accept rate: 0%

It depends on how the values were imported. My data was created by Nominatim which simply converts lat/lon to an integer by multiplying by 10000000.

permanent link

answered 05 Mar, 12:57

neilireson's gravatar image

accept rate: 0%

Hi! I'm looking for the same answer and I wrote this code, in C:

const double R_TERRA = 6378137;
struct node {
   float latitude;
   float longitude;

void mercatorSphericalToLatLon(long mercX, long mercY, struct node *point) {

   double lon = (mercX / 100.0) / (M_PI * R_TERRA) * 180.0;
   double lat = (mercY / 100.0) / (M_PI * R_TERRA) * 180.0;
   lat = 180.0 / M_PI * (2.0 * atan(exp(lat * M_PI / 180.0)) - M_PI / 2.0);

   point->longitude = (float)lon;
   point->latitude = (float)lat;
permanent link

answered 25 Aug '15, 21:24

a_manfrinati's gravatar image

accept rate: 0%

edited 25 Aug '15, 21:32

The coordinates in the planet_osm_nodes table aren't in any proper spatial reference system. They are stored as integers, not as coordinate values, and are manipulated when converted to the spatial tables. If you look closely, they are too large to be spherical mercator coordinates (hence the clipping to 90), and the fact that they are in integer columns would otherwise suggest a large amount of rounding!

Instead, you should grab the coordinates from the geometry ("way") column of the planet_osm_point table, which are indeed actual coordinates. If for some reason you must use the nodes table (which is unusual) then divide the integers by 100.

permanent link

answered 18 Mar '13, 09:25

Andy%20Allan's gravatar image

Andy Allan
accept rate: 29%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 15 Mar '13, 15:24

question was seen: 6,174 times

last updated: 05 Mar, 12:57

powered by OSQA