Importing OpenStreetMap data into a PostgreSQL/PostGIS database

I'm going to show show how one can install and configure PostreSQL with PostGis extension and populate it with OSM XML data. The description is written for Debian Wheezy. We start with installing some packages:

      sudo apt-get install postgresql postgresql-9.1-postgis postgresql-contrib
The next step is to configure PostgreSQL so that we can access our database database from the same machine without a password. In order to do this, we must edit the file /etc/postgresql/9.1/main/pg_hba.conf. What we do is that we change the content of the METHOD column from peer to trust for all rows with the value local in column TYPE. For instance, we change the row
local   all             postgres                                peer 
local   all             postgres                                trust 
As soon as this has been done, we must restart the postgresql service to our changes to take effect:
sudo service postgresql restart

There is one more step left if before we start importing OSM data: we must set up the PostGIS database, which we will name osm:

psql -U postgres -c "create database osm;"
psql -U postgres -d osm -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql
psql -U postgres -d osm -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql
psql -U postgres -d osm -c "create extension hstore;"

Load OSM data into our database

If we want to load some OSM data extract (either of .osm or .pbf format) into our database then we need to install the tool osm2pgsql first (another option would be imposm, but it's not covered here):

sudo apt-get install osm2pgsql
It's up to you where do you get your osm extract from, but I offer a sample here to download for demonstrational purposes. You can import this into the database by issuing the command
sudo -u postgres osm2pgsql -c -C 1600 -G -k -s -d osm sopron.osm
A little overview of the options I used here:
  • The -c switch removes existing data from the database.
  • Under -C we specify how many MBs are used for caching nodes.
  • -G tells that we want to generate multi-geometry features in postgresql tables (I have no idea what it means but sounds like a cool thing to have).
  • -k adds tags without column to an additional hstore column. Again, I'm not familiar with this thing yet but I was told that it's wise to have it.
  • -s Store temporary data in the database, which reduces RAM usage.
  • We specify the name of the target database using -d.
Issue osm2pgsql -h for a detailed help page. At this point you should already have your data imported into the database. In my upcoming post I'm going to describe how TileMill can be used to render this data into a set of map tiles.

Comments !