How to import OpenStreetMap data into PostgreSQL

13 Replies

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

Error: connection to database failed: fe_sendauth: no password supplied.

f the problem is specific to this client, you may need to change one or more of:

• pg_hba.conf

host    sa    all         192.168.0.nnn/32     trust

f you want that to be local to that batch file, use setlocal:

setlocal
set PATH=...

http://dave.webdev.pgadmin.org/docs/1.6/pg/app-dropdb.html