What format is lat/long stored in OSM PostGIS?

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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

http://overpass-turbo.eu/s/aTR

[out:csv(::"id", name, ::lat, ::lon)];
area
  ["admin_level"="4"]
  ["border_type"="state"]
  ["name:en"="Virginia"]
  ({{bbox}});

node
  [barrier=toll_booth]
  (area);
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