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?