converting planet_osm_node coordinates to 4326

Question

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

Answer 1

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 '20, 12:57 neilireson 1 accept rate: 0%

Answer 2

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;
}

Answer 3

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.

converting planet_osm_node coordinates to 4326

Follow this question

Related questions