OSM To Postgis & WGS84 problems

Question

I’m trying to import OpenStreetMap (OSM) data into postGIS it doesn’t appear to be working in that features do not appear to be in the correct place..

Using the QGIS OSM add in and a coastal shape file imported into PostGIS setting the SRS to 4326 on import (all my data uses WGS84 Longitude & Latitude) the two data sets are in the same space.

Importing the OSM data set into postgis with the following code & adding it as a QGIS layer I would expect lines points etc of the OSM file & PostGIS layers to be adjacent if not on top of each other but they are not, I can’t actually see the coastal shapefile & the OSM PostGIS layer together without turning one layer off and selecting zoom to layer on the other.

BTW I have attempted to use the -E 4326 switch with out -m but it failed So what am I doing wrong?

createDB osm
createlang plpgsql osm –U username 
psql –U username –d osm  -f /path/to/postgis1.5/postgis.sql
psql –U username –d osm  -f /path/to/postgis1.5/spatial_ref_sys.sql
psql –U username –d osm  -f /path/to/osm2pgsql/900913.sql

osm2pgsql –S /path/to/osm2pgsql/default.style –U username –d osm  -l –c –m /path/to/ cambridgeshire.osm.bz2

I then tried following the instructions on http://spatialmounty.blogspot.co.uk/2010/12/transform-osm-data-in-postgis-to-wgs84.html to re-project the data to WGS84 Long Lat it failed at The first line with invalid SRID

SELECT AddGeometryColumn( 'planet_osm_line', 'geom_wgs84', 4326, 'LINESTRING', 2);

My database has the following tables

Geography_columns  view
Geometry_columns table
Planet_osm_line table
Planet_osm_point table
Planet_osm_polygon table
Planet_osm_roads table
Spatial_ref_sys table

Can somebody give me the steps / syntax i need to follow to accurately import OSM data into PostGIS so that it uses ESPG:4326?

Any one know of any ground truth data I can use to test the process?

Answer 1

Just done the process for Cambridge. [This is the fastest method]

Using PostGIS 2.0 (pgAdmin postgres SQL Tools 1.14)

Downloaded the Cambridge OpenStreetMap data from here:

http://download.geofabrik.de/osm/europe/great_britain/england/

selected Cambridge note the cambridgeshire.shp.zip 01-May-2012 06:08 11M ESRI Shapefile (EPSG:4326), zipped

Loaded into PostGIS with no changes to the data using the 'PostGIS Shapefile Import/Export Manager'

Then loaded the data into QGIS

Loading this will confirm that the WGS84 is being used.

Answer 2

Use only the -l parameter and drop -m in your command. From the osm2pgsql help:

-l|--latlong    Store data in degrees of latitude & longitude.
-m|--merc       Store data in proper spherical mercator (default)

Answer 3

Hi All I've managed to this I apologize for answering my own question but thought others might find it use full

create a database but DO NOT use the 900913.sql The code below shows the steps i made

createDB osm
createlang plpgsql osm –U username 
psql –U username –d osm  -f /path/to/postgis1.5/postgis.sql
psql –U username –d osm  -f /path/to/postgis1.5/spatial_ref_sys.sql

osm2pgsql –S /path/to/osm2pgsql/default.style –U username –d osm  -l –c /path/to/ cambridgeshire.osm.bz2

Thank you for your contributions as they led me to this answer

Answer 4

This is harder than it sounds - I made some notes on my (abortive) attempts to get the whole of OSM into a postgis data base which may help you get started.

The bottom line is I gave up and went and used a different data set.