Projection of osm2pgsql nodes table?

Question

When importing data from OSM using osm2pgsql in slim mode, the resulting nodes table has two columns, named 'lat' and 'lon', which contain coordinates for each node. The values are large (~9 digit) integers, not lat-lon, values and the projection is not given anywhere. e.g.

lat,lon
395980942,-860674711
394871880,-860148511
394879027,-860153601
382614407,-857420524
382613507,-857418018

These coordinates do not appear to be effected by setting a projection in osm2pgsql, though that does effect geometry fields in other tables. The nodes table doesn't have a geometry column, and I am simply trying to create one.

I have tried assuming that these coordinates are in osm2pgsql's default projection, spherical mercator, EPSG:3857.

ALTER TABLE nodes ADD COLUMN geom geometry(POINT,4326);
UPDATE nodes SET geom = ST_Transform(
    ST_SetSRID( ST_MakePoint(lon,lat), 3857),
    4326
);

This doesn't throw any errors, but gives lat, lon values that are clearly wrong, and which fail to render in QGIS. I read somewhere that the coordinate values needed to be divided by 100 (I can't find the source now...):

UPDATE nodes SET geom = ST_Transform(
    ST_SetSRID( ST_MakePoint(lon/100.0,lat/100.0), 3857),
    4326
);

And this seems to get closer. QGIS renders the geometry now, and I can see the right shapes in the data, but it's still off by a few hundred miles from where it should be.

To the northwest is the points table projected correctly. The other dataset is the nodes table, as projected in the preceding code block.

What is the projection of these coordinates?

Answer 1

Well, I feel dumb. They ARE actually lat, lon values, except they are stored as integers, i.e. with out a decimal. The following works:

UPDATE nodes SET geom = ST_SetSRID( 
    ST_MakePoint(lon/10000000.0,lat/10000000.0),
    4326
)

You simply need to divide the values by ten million.