How to import OpenStreetMap data into PostgreSQL

1. Download data and software

The instructions are fairly generic, so should work for both Windows, Linux and Mac OS X. I wrote them for Windows, but I’ve since then switched to Mac OS X.

PostgreSQL+PostGIS

I assume that you do not already have Postgres/PostGIS installed on your system.

Download PostgreSQL+PostGIS for all platforms here: http://www.postgresql.org/download/

Follow the instructions provided to install the database software.

OSM data

Download OpenStreetMap data (.osm file):
downloads.cloudmade.com

I chose europe -> denmark.

osm2pgsql

Download the version for your platform (Windows, Linux, Mac OS X etc):
wiki.openstreetmap.org/wiki/Osm2pgsql

2. Create and prepare database

If you add the PostgreSQL bin folder to your path, you’ll have access to commands like createuser and createdb in your shell.

Create a user (after running command, answer ‘y’ to superuser):

createuser -U postgres <enter your username, I entered kostas>

Create the database (should be called ‘gis’):

createdb -U postgres -E UTF8 -O kostas gis

Install language plpgsql (on my system this was already installed):

createlang -U postgres plpgsql gis

Add PostGIS functionality to database (you should get pages of output as functions etc are created):

psql -U postgres -d gis -f PATH_TO_POSTGRES/share/contrib/postgis-1.5/postgis.sql

Download the file 900913.sql. The following will add spherical mercator projection to the database:

psql -U postgres -d gis -f PATH_TO_FILE/900913.sql

3. Add OSM data to database

Change directory to where you unzipped osm2pgsql:

cd PATH_TO_OSM2PGSQL

Import OSM data:

osm2pgsql -U postgres -s -S ./default.style PATH_TO_OSM_FILE/denmark.osm

Options for osm2pgsql:

  • -U postgres
    Run program with the PostgreSQL admin user
  • -s
    Run in “slim” mode, which means running the program not in RAM, which is necessary on my system
  • -S ./default.style
    On windows (maybe also Linux and other OS) you must specify path to style file. Use default which comes with osm2pgsql.

That’s it! You now have a database full of street data.

4. Testing the database

This is where I live:

select name, astext(way) from planet_osm_line where name='Anders Henriksens Gade'

Which gives the name of the road plus a WKT representation of the geometry:

Anders Henriksens Gade

LINESTRING(1402528.63 7491973.55,1402602.4 7491829.85)

Conclusion

It works, but tables are created in the ‘public’ schema of the ‘gis’ database. This is not so nice. I’d prefer that tables were created e.g. in ‘osm’ schema. When I’ve looked into how this is done, I’ll update this post.

I’d like to write a howto that uses Osmosis to continuously update the local OSM data with diffs from OSM.

15 thoughts on “How to import OpenStreetMap data into PostgreSQL”

  1. Thank your for sharing the experience. But with following your instruction, I still cannot complete the function. 🙁 Errors always occur after the last step which is setting up tables. I’m going to transfer it on Linux…

  2. I am pretty new to all this but this is a very nice explanation I have tried to follow. As in the case of CHanning Zong I am getting errors when adding the tables. Let me summarized what I have done.
    * Install PostgreSQL 9.1
    * Install PostGiS 1.5
    Follow your instructions exactly with the exception of
    >psql -U postgres -d testdb -f /900913.sql
    which gave me an error saying that testdb was not found. Istead I run the following:
    >psql -U postgres -d my_gis -f /900913.sql
    being my_gis the database I created before (instead of gis)
    when running osm2pgsql
    osm2pgsql -U postgres -s -S .\default.style my_osm_file.osm -W
    the error it gaves is that AddGeometryColumn() has an ivalid SRID:

    Can you help me?? If you need more info please let me know. Thanks a bunch.

    1. Providers are pretty easy to write. It’s a very small infetrace to implement. I actually spent more time learning the different dialects of sql than coding the providers. If you post a link to a free version of db2 and a .net connector to it, I can probably whip one up this week.

  3. Thank you! This was a huge help.

    I’m churning through this with (apparently semi-recent, semi-ancient) windows binaries, so, to repay the karma, here are a couple of things that helped me:

    “C:\Program Files (x86)\PostgreSQL\9.1\bin\psql” -U -d gis -f “C:\Program Files (x86)\PostgreSQL\9.1\share\contrib\postgis-2.0\legacy.sql”

    springmeyer’s note from here: http://trac.osgeo.org/postgis/ticket/1287
    (it’s the sql that begins with … “CREATE OPERATOR CLASS gist_geometry_ops”)

    “C:\Program Files (x86)\PostgreSQL\9.1\bin\psql” -U -d gis -f “C:\Program Files (x86)\PostgreSQL\9.1\share\contrib\postgis-2.0\postgis.sql”

    I think it was easier for me to learn solve the Rubik’s cube. Good luck to us all….

Leave a Reply