# How to transfer lon&lat of the table planet_osm_nodes into the right form?

 0 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 Wang 11●1●1●2 accept rate: 0% aseerel4c26 ♦ 32.6k●18●248●554

 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". answered 15 Jul '20, 05:05 Abdelkader 10●1 accept rate: 0%
 0 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. answered 05 Mar '20, 13:00 neilireson 1 accept rate: 0%
 0 Hi Connie, I have the same problem. Did you manage to get a solution? Best Regards, Varun. answered 02 Mar '17, 03:03 VarunDhr 1 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
 2 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. answered 22 Dec '16, 07:01 Frederik Ramm ♦ 82.2k●92●718●1268 accept rate: 23% 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
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• 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:

×144
×116
×35
×1

question asked: 22 Dec '16, 03:04

question was seen: 5,297 times

last updated: 15 Jul '20, 05:05