Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Question:
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.
share|improve this question
    
Why was this question voted down? I've tried to google and search this stackexchange but haven't been able to find the answer. –  dasickis 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. –  pacofvf 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) –  dasickis Apr 8 '13 at 19:35
show 1 more comment

3 Answers

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

share|improve this answer
    
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 –  dasickis 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. –  dasickis 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
add comment

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

share|improve this answer
add comment

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

**Schema:** 
> 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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