In the previous chapter we saw how to set up Postgresql with PostGIS in Windows and
how to set up a database and load it with shapefile data. In order to get OpenStreetMap
data into a database, you could get the data in shapefile format and use the shapefile
loader, but this may leave you without all the data that you want. In this chapter we
will learn how to use osm2pgsql, a command-line program for loading raw OSM
data into a PostGIS database.
We will go through the steps to set up osm2pgsql on Windows, though the steps should
be roughly the same on another operating system, assuming you have set up your
PostGIS database(s) correctly.
- Download the file named osm2pgsql.zip
- Unzip the file on your system. You should move the unzipped folder to a location where you will
not move it later, because we need to add its location to the system path.
- In the osm2pgsql directory that you unzipped is a file called osm2pgsql.exe. This is a program
that we will run to import the data, but in order for Windows to find it, we need to add its
location to the system path. Click on the Start Menu and type “system path.”
- You should see an option named “Edit the system environment variables.” Click on it.
- Click on the button named “Environment Variables.”
- At the bottom find the variable named “Path” and click “Edit…”
- You must add the directory where osm2pgsql.exe is located to the Path variable.
In the black command window that opens, type:
If everything is working right, you should get a message like this:
- If you don’t see an error message like this, and it says that it cannot find the application
osm2pgsql, then you may have entered the Path variable incorrectly.
Getting Raw OSM Data
Before we can run osm2pgsql we need to have some raw OSM data to import into a database.
If you don’t already have a .osm file that you can use, try downloading a file from
http://metro.teczno.com/. This site hosts many OSM extracts for
different cities. Find a city to import and download the PBF file for it. PBF files are
compressed versions of the normal .osm files. You can use any of the extract services
listed in the chapter on getting data, if you’d like the raw data
for another area.
Get the Style File
- osm2pgsql requires the use of a custom style file to define which tags are included
in the database during import. You can download the default style file
Importing the Data
- Open PgAdmin III and create a new database named osm, just as you did in the
- To import the data, we will run the osm2pgsql program via the command line. Click on
the Start Menu and type “cmd” and Enter to open the Command Prompt.
Here we will run the application osm2pgsql with several options. We need at least
to supply it with:
- The location of the OSM Data File
- The name of the database, and the database username
- The style file which defines which OSM tags will be imported to the database
We have placed our OSM file into the C:\ directory to make this easier.
Type the following command, replacing the location of the OSM file and style file with your own.
osm2pgsql -c -d osm -U postgres -H localhost
-S C:\default.style C:\bangkok.osm.pbf
Press Enter. If all goes well, the process should begin running. It may take a few minutes for all
of the data to load into the database.
- If your raw OSM file is large, you may need to add additional memory
to the osm2pgsql import process. To do this, add the following to the command:
We can test that the import was successful and view the data in our database using QGIS.
- Open QGIS and click on the “Add PostGIS Layers” button.
- Under “Connections” at the top, click “New.”
- Give the new connection a name. Under database type osm (the name of your database).
Enter the username postgres and your password below.
- Click OK to save the connection settings. Then click “Connect” to connect to your PostgreSQL
- Click next to “public” to see all of the layers (tables) in your database. Notice that osm2pgsql
creates a separate table for different object types - points, lines, and polygons. It also creates
a roads table, which contains only major roads.
- Select one or more of the layers and click “Add.” If asked, choose WGS84 as the CRS.
- If everything is successful, you will see the layers you selected displayed in QGIS.
process. In order to add specific tags that are not included by default, you can edit the
*style* file that osm2pgsql references to define the data schema.
When you want to import OpenStreetMap data into your own database, osm2pgsql is a great tool.
It can be extremely useful when you need to be able to get the most up-to-date OSM data and
customize the attributes you want, or when working on more complex projects.
Another import tool has been developed recently, called imposm, and offers
some speed and other improvements over osm2pgsql, although as of this writing it lacks other key
functions which are promised in imposom version 3.
For more information on osm2pgsql, refer to the OSM Wiki -