Importing OpenStreetMap data with the osm2pgsql command

In this recipe, you will import OpenStreetMap (OSM) data to PostGIS using the osm2pgsql command.

You will first download a sample dataset from the OSM website, and then you will import it using the osm2pgsql command.

You will add the imported layers in a GIS Desktop software and generate a view to get subdatasets, using the hstore PostgreSQL additional module to extract features based on their tags.

Getting ready

We need the following in place before we can proceed with the steps required for the recipe:

1. Install osm2pgsql. If you are using Windows, follow the instructions available at http://wiki.openstreetmap.org/wiki/Osm2pgsql. If you are on Linux, you can install it from the preceding website or from packages. For example, for Debian distributions, use the following:

   $ sudo apt-get install osm2pgsql
   

2. For more information about the installation of the osm2pgsql command for the other Linux distributions, Mac OS X, and MS Windows, please refer to the osm2pgsql web page available at http://wiki.openstreetmap.org/wiki/Osm2pgsql.

3. Although, it's most likely that you will need to compile osm2pgsql yourself as the one that is installed with your package manager could already be obsolete. In my Linux Mint 12 box, this was the case (it was osm2pgsql v0.75), so I have installed Version 0.80 following the instructions on the osm2pgsql web page. You can check the installed version just by typing the following command:

   $ osm2pgsql
   osm2pgsql SVN version 0.80.0 (32bit id space)
   

4. We will create a different database only for this recipe, as we will use this OSM database in other chapters. For this purpose, create a new database named rome and assign privileges to your user:

   postgres=# CREATE DATABASE rome OWNER me;
   postgres=# \connect rome;
   rome=# create extension postgis;
   

5. You will not create a different schema in this new database, though, as the osm2pgsql command can only import OSM data in the public schema at the time of writing.

6. Be sure that your PostgreSQL installation supports hstore. If not, download and install it; for example, in Debian-based Linux distributions, you will need to install the postgresql-contrib-9.1 package. Then, add the hstore support to the rome database using the CREATE EXTENSION syntax:

   $ sudo apt-get update
   $ sudo apt-get install postgresql-contrib-9.1
   $ psql -U me -d romerome=# CREATE EXTENSION hstore;
   

How to do it...

The steps you need to follow to complete this recipe are as follows:

1. Download a .osm file from the openstreetmap.org website:

   1. Go to the openstreetmap.org website.

   2. Select the area of interest for which you want to export data. You should not select a large area, as the live export from the website is limited to 50,000 nodes.

      Tip

      If you want to export larger areas, you should consider downloading the whole database, built daily at planet.osm (250 GB uncompressed and 16 GB compressed). At planet.osm, you may also download extracts that contain OpenstreetMap Data for individual continents, countries, and metropolitan areas.

   3. If you want to get the same dataset used for this recipe, just copy and paste the following URL in your browser: http://www.openstreetmap.org/export?lat=41.88745&lon=12.4899&zoom=15&layers=M; or, get it from the book datasets (chp01/map.osm file).

   4. Click on the Export link.

   5. Select OpenStreetMap XML Data as the output format.

   6. Download the map.osm file to your working directory.

2. Run osm2pgsql to import the OSM data in the PostGIS database. Use the -hstore option, as you wish to add tags with an additional hstore (key/value) column in the PostgreSQL tables:

   $ osm2pgsql -d rome -U me --hstore map.osm
   osm2pgsql SVN version 0.80.0 (32bit id space)Using projection SRS 900913 (Spherical Mercator)Setting up table: planet_osm_point...All indexes on planet_osm_polygon created in 1sCompleted planet_osm_polygonOsm2pgsql took 3s overall
   
   $ osm2pgsql -d rome -U me --hstore map.osm
   osm2pgsql SVN version 0.80.0 (32bit id space)
   Using projection SRS 900913 (Spherical Mercator)
   Setting up table: planet_osm_point
   ...
   All indexes on planet_osm_polygon created in 1s
   Completed planet_osm_polygon
   Osm2pgsql took 3s overall
   
   

3. At this point, you should have the following geometry tables in your database:

   rome=# SELECT f_table_name, f_geometry_column, coord_dimension, srid, type FROM geometry_columns;
   

   The output of the preceding command is as shown below:

      f_table_name    | f_geometry_column | coord_dimension |  srid  |    type    
   --------------------+-------------------+-----------------+--------+------------
    planet_osm_roads   | way               |               2 | 900913 | LINESTRING
    planet_osm_point   | way               |               2 | 900913 | POINT
    planet_osm_polygon | way               |               2 | 900913 | GEOMETRY
    planet_osm_line    | way               |               2 | 900913 | LINESTRING
   (4 rows)
   

4. Note that the osm2pgsql command imports everything in the public schema. If you did not deal differently with the command's input parameter, your data is imported in the Mercator Projection (900913).

   

5. Open the PostGIS tables and inspect them with your favorite Desktop GIS. The preceding screenshot shows how it looks in QGIS. All the different thematic features are mixed at this time, so it looks a bit confusing.

   

6. Generate a PostGIS view that extracts all the polygons tagged with trees as land cover. For this purpose, create the following view:

   rome=# CREATE VIEW rome_trees ASSELECT way, tags FROM planet_osm_polygonWHERE (tags -> 'landcover') = 'trees';
   

7. Open the view with a Desktop GIS that supports PostGIS views, such as QGIS, and add your rome_trees view. The preceding screenshot shows you how it looks.

How it works...

OpenStreetMap is a popular collaborative project for creating a free map of the world. Every user participating in the project can edit data; at the same time, it is possible for everyone to download those datasets in .osm datafiles (an XML format) under the terms of the Open Data Commons Open Database License (ODbL) at the time of writing.

The osm2pgsql command is a command-line tool that can import .osm datafiles (eventually zipped) to the PostGIS database. For using the command, it is enough to give the PostgreSQL connection parameters and the .osm file to import.

It is possible to import only features having certain tags in the spatial database, as defined in the default.style configuration file. You can decide to comment in or out from this file the OSM tagged features that you would like to import or not. The command by default exports all the nodes and ways to linestring, point, and geometry PostGIS geometries.

It is highly recommended to enable the hstore support in the PostgreSQL database and use the –hstore option of osm2pgsql when importing the data. Having enabled this support, the OSM tags for each feature will be stored in an hstore PostgreSQL data type, which is optimized for storing (and retrieving) sets of key/values pairs in a single field. This way it will be possible to query the database as follows

SELECT way, tags FROM planet_osm_polygonWHERE (tags -> 'landcover') = 'trees';