# Projecting OpenStreetMap data in PostGIS

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?

-

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

-
Hi Stev_k - yes EPSG 900913 is present, and there were no errors. Can I just run ST_Transform() without having to identify what the initial SRID is? I can't a see any reference in the data that was loaded to 900913 so I'm just not clear on how PostGIS knows what the reference system is. –  djq Mar 23 '12 at 1:26
can you confirm you've looked in the table "geometry_columns"? What is the contents of that table in your db? –  Stev_k Mar 23 '12 at 9:30
Ah, I understand what you mean. The value in that table is 4326 for each entry, so I suppose that means it is ok. (I'm a little confused switching back and forth between PostGIS 1.5 and 2.0) –  djq Mar 23 '12 at 13:31
I would have thought it should be 900913? –  Stev_k Mar 24 '12 at 12:18
That's true. I wonder if it ignored the -E EPSG:900913 argument. –  djq Mar 24 '12 at 15:06

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!

-