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?

Thanks

asked 15 Mar '13, 15:24

srose's gravatar image

srose
146101015
accept rate: 0%


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
11.8k23121144
accept rate: 30%

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

a_manfrinati
211
accept rate: 0%

edited 25 Aug '15, 21:32

Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

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:

×548
×119
×22
×15

question asked: 15 Mar '13, 15:24

question was seen: 5,612 times

last updated: 25 Aug '15, 21:32

powered by OSQA