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.

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?

share|improve this question

4 Answers 4

up vote 1 down vote accepted

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)
share|improve this answer
    
I created a new DB and Running the command without the –m switch results in <code> Osm2pgsql SVN version 0.80.0 (32bit id space) Using projection SRS4326 (LatLong) Setting up table: planet_osm_point Notice: table: “planet_osm_point” does not exist, skipping Notice: table: “planet_osm_point_tmp” does not exist, skipping SELECT AddGeometryColumn( 'planet_osm_point, 'way’, 4326, 'POINT’, 2); Failed: ERROR: AddGeometryColumns() – invalid SRID</code> –  Holly May 2 '12 at 16:14

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' enter image description here

Then loaded the data into QGIS

enter image description here

Loading this will confirm that the WGS84 is being used.

enter image description here

share|improve this answer
    
Thanks a couple of issues with your approach 1st i'm using postGresql 8.4 and PostGIS 1.5 so only the bash shp2psql is available 2nd Cambridgeshire is my test data set can you get a world shape file? 3rd i'm only using qgis to view the data I really need to store the data with an srs ESPG 4326 so any data analysis can be carried out within the database. Thanks for your suggestions anyway it has given me food for thought. –  Holly May 1 '12 at 21:40
    
This approach can be done for the entire world - the data is stored natively from the osm data source as ESPG 4326. Would recommend to anyone to upgrade to PostGIS 2 there are some much more useful spatial functions in there now. –  Mapperz May 2 '12 at 13:16
    
Use the OSM2SQL to append the data into the database download.geofabrik.de/osm - Example osm2pgsql -c -d gis -U postgres -W -H localhost -P 5433 N:\Geo_data\OpenStreetMap\OSM_xml\yourOSM_filename.bz2 –  Mapperz May 2 '12 at 13:26

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.

share|improve this answer
    
were you not able to use osmosis for this? BTW you data set link should be naturalearthdata.com –  Holly May 2 '12 at 8:32
    
I lost interest and moved on :-) –  iant May 2 '12 at 8:40

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

share|improve this answer

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.