How to use osm2pgsql for loading raw OSM data into a Postgresql database in Windows

In this post we will look at how to use osm2pgsql, a command-line program for loading raw OSM data into a PostGIS database. In order to  get OpenStreetMap data into a database, you could create a Postgresql PostGIS enable database in which you can store geographic data. 

You can find the step to set up Postgresql with PostGIS in Windows in this blog post.

http://effective-gis.blogspot.com/2015/02/how-to-download-and-install-postgresql.html

Follow below steps to set up osm2pgsql on Windows , we assume that you have set up PostGIS database(s) correctly and later will give you some tips on that.

Get OSM2PGSQL

Step 1

osm2pgsql can be downloaded  from here and if you want, there are many builds available in internet.
https://github.com/openstreetmap/osm2pgsql

Step 2

Download the .zip  file and unzip it in a location where you will  not change later .To import data from OSM you have to run a program called osm2pgsql.exe in the extracted  folder. Extracted folder location will add to system path later.
Make sure that custom style file is in the extracted folder . This custom style file define which tags are included in the database during import

Set System Path 

Step 1

Go to Settings-> Settings->PC info-> Advanced system settings.

Click  "OK"  several times to  save new settings

Step 2

You can check the functioning of osm2pgsql using Windows Command Prompt

If you are getting something similar to this , then osm2pgsql  works fine

Getting Raw OSM Data

Before starting we need to  have some raw OSM data to import into  a database. This can be downloaded from OSM or  https://mapzen.com/data/metro-extracts/  which  host many OSM extracts all over the world.

Import Raw OSM data 

Step 1

Before importing data open PgAdmin III and create a new database named gis with superuser ownership.Then run osm2pgsql program via the command line .

We need to provide at least 

• The location of the OSM Data File
• Name of the database and username
• Location of the style file

Step 2

Run following command replacing location of the OSM Data File  and location of the style file.

osm2pgsql -c -d gis -U postgres -H localhost -S C:\default.style C:\osm2pgsql\TryOSM.osm

Step 3

It takes time to load all data into the database. If everything works fine then you will get  something similar to  this.

Testing Data

We can view the data in our database using QGIS if the import was successful .

Follow below steps to  view the data in QGIS

• Open QGIS and click on "Add postGIS Layer" In the connection bar click "New"

• Enter username as "postgres" and your password

• Click "OK" to save the connection settings and then click "Connect " to  connect  to  your PostgreSQL server.

• Click next to  see "public" and expand it to see all the layers (tables) in your database .

• There you can see a separate table for each object types-points, lines and polygons.

• Select layers and click "Add". Choose WGS84  as the CSR.

• If everything works fine, you will see the layers you selected displayed in QGIS.