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

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%

edited 22 Dec '16, 12:57

aseerel4c26 ♦
32.2k●16●239●552

3 Answers:

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 Ramm ♦
73.3k●86●664●1137
accept rate: 24%

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
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

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, 13:00

neilireson
1
accept rate: 0%

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "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

learn more about Markdown

Question tags:

coordinates ×122
database ×97
convert ×33
planet_osm_node ×1

question asked: 22 Dec '16, 03:04

question was seen: 2,989 times

last updated: 05 Mar, 13:00

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

Related questions