How do I to get the lat, long for all the toll booths stored in OSM for Virginia?

Current Query:

> gis=# select * from planet_osm_nodes where ('toll_booth' = ANY(tags)); 

     id     |    lat    |    lon     |                            tags                            
  496668427 | 448545276 | -881565606 | {name,"Airport Parking",barrier,toll_booth}
  525372041 | 463020204 | -874380697 | {name,"Swift Run Gap Entrance Station",barrier,toll_booth}
  526241181 | 470778112 | -870435097 | {name,"Front Royal Entrance Station",barrier,toll_booth}
  534508045 | 458513289 | -877777774 | {name,"Rockfish Gap Entrance Station",barrier,toll_booth}
  563894077 | 470183057 | -857852817 | {barrier,toll_booth}

Rest of the table

Additional Info

  1. OSM Import Log
  2. Failed Attempts
  3. Tutorial -- manually building a tile server
  4. Data source -- Cloudmade/virginia.osm.bz2.
  • Why was this question voted down? I've tried to google and search this stackexchange but haven't been able to find the answer. – prafulfillment Apr 8 '13 at 2:30
  • I really don't know why it was downvoted. It's an interesting question. – Devdatta Tengshe Apr 8 '13 at 3:59
  • 3
    Although I haven't downvoted this question, I find it incomprehensible. First, clearly the "Dividing by 100" values were not obtained by dividing any of the preceding by 100. Second, it appears at the outset that somebody has simply stored some kind of numbers (as integer types, evidently) and called them lat and lon--but what is the poor reader supposed to make of this, in the absence of any further information? – whuber Apr 8 '13 at 12:50
  • The lat/lon aren't multiplied by 10, they are in a different projection it is supposed to be EPSG:3857. – Francisco Valdez Apr 8 '13 at 18:03
  • How do I convert back to lat, long? I tried this script: maptiler.org/google-maps-coordinates-tile-bounds-projection and using MetersToLatLon(448545276 , -881565606) => (-90.0, 4029.3507705040915) – prafulfillment Apr 8 '13 at 19:35

Most likely you're querying the wrong table; look for a table called planet_osm_point.

In planet_osm_point, geometries are stored as geometry type; this allows the database to use spatial indexes and spatial operators, features that would be impossible with lat/longs stored as integers.

The default name of the geometry column used by osm2pgsql is "way" and the default projection is 900913 (also known as 3857):

osm=# select st_asewkt(way), name, amenity from planet_osm_point where place = 'city' limit 1;
                      st_asewkt                       |   name    | amenity 
 SRID=900913;POINT(2362914.15411575 5741085.65649082) | Timișoara | 

The planet_osm_nodes table you refer is listed (see osm2pgsql wiki entry) as a temporary table used when running the importer with limited memory ("slim" mode).

  • Nice so using your point and using the MetersToLatLon from the script in my question, I get: goo.gl/maps/xwxLd. However, how do I then query this table for tags such as toll_booth. The schema has a toll column, however: gis=# select count(toll) from planet_osm_point; count = 0 Though there's 62 toll_booths in planet_osm_nodes – prafulfillment Apr 8 '13 at 19:50
  • 2
    I don't know what MetersToLatLon does. If your planet_osm_points has less points than planet_osm_nodes it's likely your osm2pgsql import went wrong. Look for errors. – diciu Apr 8 '13 at 20:06
  • 1
    Also check that your import style file actually contains an entry for attribute name "toll" (see wiki.openstreetmap.org/wiki/Osm2pgsql#Import_style). If it's not there, osm2pgsql will not add it to the table definition. – diciu Apr 8 '13 at 20:11
  • Thanks! here's the output for my run: gist.github.com/dasickis/5340077. It doesn't seem that there's any errors except for out of memory. – prafulfillment Apr 8 '13 at 20:14
  • 1
    Did you check your default.style file for an entry for the "toll_booth" attribute? Presuming the id field is common between planet_osm_nodes and planet_osm_point, what does "select name from planet_osm_point where osm_id = 496668427" return? – diciu Apr 9 '13 at 5:07

OSM2PGSQL nodes table is usable, you "simply" need to divide your lat/lon by 100.

With the first record of your sample :

SELECT ST_AsText( ST_Transform( ST_GeomFromText('POINT('||'-881565606'/100||' '||'448545276'/100||')',3785 ),4326))

Result : "POINT(-79.1923852433996 37.3317650468903)"

  • This is integer division, which truncates '448545276'/100 to 4485452. Use ST_SetSRID(ST_MakePoint(lon / 100.0, lat / 100.0), 3785) to properly construct the geometry. – Mike T Aug 11 '15 at 21:02

Although you deleted it from your question, there is something wrong with your table::

> gis=# \d planet_osm_nodes Table "public.planet_osm_nodes" 
 Column | Type | Modifiers an 
     id | bigint  | not null
    lat | integer | not null
    lon | integer | not null
   tags | text[]  | 

Why should lat and lon be integer values? I guess on importing the comma just disappeared, and centimeter values are stored as metres.

As mentioned above, the useful geometry is stored in planet_osm_points.


In case anyone else is looking to do something like this with OSM data, here is a overpass query that selects all tollbooths in Virginia and gives you a CSV file with lat/lon


[out:csv(::"id", name, ::lat, ::lon)];

out body qt;

out skel qt;

Also don't forget to read up on OpenStreetMap's license before using the data http://www.openstreetmap.org/copyright

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.