Projecting OpenStreetMap data in PostGIS

Question

I've loaded an extract of Openstreetmap data into a Postgresql database with PostGIS 1.5 using the following command withosm2pgsql.exe:

C:\Program Files (x86)\HOTOSM\bin>osm2pgsql.exe -c -s -H localhost -d osm_cloudmade -b -122.0,18.03,-64.85,46.05 -E EPSG:900913 -U postgres -P 5432 J:\DATA\OSM\usa.osm.bz2 -S J:\DATA\OSM\default.style

I understand that the Openstreetmap data is in Lat/Long WGS 84. If I inspect my table planet_osm_roads I see way geometry but no SRID.

Is there a way that I can project this to another system? Or do I first need to specify what the SRID is of this data?

Answer 1

Have you looked in the table "geometry_columns"? There should be an entry detailing the SRID for the "geometry" column in the planet_osm_roads table.

There is some background info here but that was an old thread and you haven't said you had any error messages, so assume that EPSG 900913 is present in your version of PostGIS and osmpgsql.

Assuming the co-ordinates in your geometry and your SRID are specified correctly, it is just a case of running ST_Transform on your geometry column (having created a new geometry column with the correct SRID first of course).

Answer 2

To be sure about the current projection of your data, simply use ST_SRID, i.e. SELECT st_srid(the_geom) from osm.roads limit 10;. The next step, just to be safe, add another geometry column and fill that column with the transformed geometry, i.e. UPDATE osm.roads SET geom_new = ST_Transform(the_geom), 4269);. Once that operation has finished smoothly, you can then delete the original geometry column if you like. Don't forget to add a spatial index and cluster your table on this spatial index! It makes a huge difference in performance!