4

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.

text

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?

  • Can you add to your question a sample of what numbers you see at the moment? Also an image of your last test would be nice. – tilt Jul 24 '17 at 16:01
  • More detail added! – Nate Wessel Jul 24 '17 at 16:36
  • You know what I'm just realizing? Those numbers look an awful lot like lat lon values without the decimal... – Nate Wessel Jul 24 '17 at 16:38
6

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.

  • you should mark your answer as chosen one. – LR1234567 Jul 24 '17 at 18:00
  • I tried. It says I may do so in two days. – Nate Wessel Jul 24 '17 at 18:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.