NOTICE: is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

I have downloaded the osm data and imported to my local database. There is a table called planet_osm_node and two columns lon and lat. As the description of the osm doc, latitude and longitude are stored as scaled integers with a scale factor of 1e7, so an integer latitude of -412870685 equates to -41.2870685.But I do this to the data, there is still difference with the real coordinate,for example the data is from (485231938,1295641506) to (48.5231938,129.5641506) while the real one is (116.389456757943,39.9061897544305). Is there a function or something to transform between these two forms?

Please help and thank you very much.

asked 22 Dec '16, 03:04

Connie%20Wang's gravatar image

Connie Wang
accept rate: 0%

edited 22 Dec '16, 12:57

aseerel4c26's gravatar image

aseerel4c26 ♦

Ignore the information about scaled integers, it is not relevant to your use case. If you have imported with osm2pgsql then observe the following:

  • planet_osm_nodes is almost certainly not what you are interested in as it has raw coordinates for nodes but no other information (is this node a tree? a shop? a city? etc)
  • Instead, use the tables planet_osm_point, planet_osm_polygon, and planet_osm_line, where the coordinates are stored in the "way" column. Read up on PostGIS functions. You can use st_astext to display the "way" column in a human readable format.
  • Your database is in "spherical Mercator" coordinates, not lat/lon degrees. Hence the confusion. Use the PostGIS function st_transform(way,4326) to convert these coordinates to lat/lon degrees, or alternatively use the -l command line option when importing with osm2pgsql.
permanent link

answered 22 Dec '16, 07:01

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
accept rate: 23%

edited 22 Dec '16, 07:03

Thank you for your answering. I actually use the roads and nodes to partitioning the city into small areas. Then I need to reflect my order records into different area. So I need to know the right coordinate to confirm the scope of an area. I have tried the functions you mentioned above but I haven't gotten the format that I want.

(22 Dec '16, 07:36) Connie Wang

Hi Connie,

I have the same problem. Did you manage to get a solution?

Best Regards, Varun.

permanent link

answered 02 Mar '17, 03:03

VarunDhr's gravatar image

accept rate: 0%

Yes,you can use the function of postgis,which is a opensource program of postgreSQL. Something like this:select id,ST_X(ST_AsText(st_transform(st_geomfromtext('POINT ('||lon/100||' '||lat/100||')',900913),4326))) as longi, ST_Y(ST_AsText(st_transform(st_geomfromtext('POINT ('||lon/100||' '||lat/100||')',900913),4326))) as lati from new_nodes

(02 Mar '17, 04:16) Connie Wang

For anyone else who stumbles here looking for the answer. 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. This looks like the same case as the OP.

permanent link

answered 05 Mar '20, 13:00

neilireson's gravatar image

accept rate: 0%

for those who ask for : lon, lat from planet_osm_nodes, here is your response : "select lon::numeric/10000000 as lon, lat::numeric/10000000 as lat from planet_osm_nodes".

permanent link

answered 15 Jul '20, 05:05

Abdelkader's gravatar image

accept rate: 0%

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 22 Dec '16, 03:04

question was seen: 5,699 times

last updated: 15 Jul '20, 05:05

NOTICE: is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum