Convert planet_osm_nodes lat lon to "normal" lat long

Question

I have downloaded the openstreetmap data for Germany. I am trying to find a closest point using planet_osm_nodes, but the lat lon in the table do not make any sense to me. Let me try to show by example why not:

I take a random point

SELECT * FROM planet_osm_nodes LIMIT 1;

    id     |    lat    |    lon    |         tags         
-----------+-----------+-----------+----------------------
 363692391 | 596568676 | 109247330 | {ref,1A,power,tower}

When inserting a point into the (lat, lon), so (59.6568676, 10.9247330) or (10.9247330, 59.6568676), and entering it in https://www.openstreetmap.org/search?query=59.6568676%2C%2010.9247330#map=5/59.657/10.925, I end up in Norway or the sea.

When using a EPSG:3857 to EPSG:4326 converter (https://epsg.io/transform#s_srs=3857&t_srs=4326&x=109247330.0000000&y=596568676.0000000), I try to use the converted GPS coordinates to pinpoint on a map, and even swap x/y and lat/lon, but I still do not end up in Germany (but North/South Pole, US, etc).

When I look up the "way" that belongs to the "node":

SELECT * FROM planet_osm_ways WHERE 363692391 = ANY (nodes);
 48616848 | {363692392,363692391,...,302275015,346153952,251417206} | {cables,6,name,"Bürs - Obermooweiler blau;Bürs - Ober
mooweiler weiß",power,line,ref,401;402,voltage,380000,wires,quad}

Find the "way" (thanks google) in OSM online: https://www.openstreetmap.org/relation/1750798/history#map=9/47.3988/9.7439

Pick a GPS coordinate closeby: https://www.openstreetmap.org/node/346153670

I get a GPS that does make me land in Germany: Location: 47.6525789, 9.8031666 Which makes me believe that I do have the right database, and the data should be there somehow.

Question: How do I convert the lat lon in the table mentioned above to get coordinates that I can enter in Google Maps/Openstreetmaps that show me the correct location (in Germany)?

Answer 1

   select st_setsrid(
           st_makepoint(
              n.lon/power(10,floor(log(abs(n.lon)))-1),
              n.lat/power(10,floor(log(abs(n.lat)))-1)),4326)
   from planet_osm_nodes n

because geometry is saved as integer we have to convert it into double. projection is 4326 (not 3857 that is used for ways geometry). So we have to

1. determine 10-based logarithm of the value.

2. reduce it by 1 as assumption is that first two digits from integer value should stay before point.

3. divide original value by 10 power whatever was calculated on step 2.

Answer 2

Proper transformation of this strange data should be:

select ST_Transform(ST_GeomFromText('POINT('||lon::numeric/100||' '||lat::numeric/100||')',3857 ),4326) from planet_osm_nodes

Without adding ::numeric for "lat" and "lon" you lost precision and this can be even kilometers in some cases. EPSG 3857, 3785, 900913 (and many others) are all same but most "proper"(in terms wildly used) is 3857

Answer 3

As pointed out by JGH, a similar question was asked at: https://gis.stackexchange.com/questions/57003/what-format-is-lat-long-stored-in-osm-postgis

The main gist I got from it:

• "The planet_osm_nodes table you refer is listed (see osm2pgsql wiki entry) as a temporary table used when running the importer with limited memory ("slim" mode)." by user diciu
• Because of this, most tags are available and reachable by performing geometry on the "ways" key in other tables
• The maxspeed tag however is not converted into the new tables as far as I can see
• one can transform the lat lon from planet_osm_nodes, get the id, and search for the id in the nodes column of planet_osm_ways. This will give access to the tags

The final answer in code to get the lat lon converted (from the linked question on gis.stackexchange.com with the variables plugged in):
ST_Transform( ST_GeomFromText('POINT('||lon/100||' '||lat/100||')',3785 ),4326)