This is less of a how to but more of a reminder to myself where I downloaded files and certain setup information. The following has worked on 64 bit Windows 7 and 10.

PostGIS

The first and most important part is to download Postgresql, which can be found here. The Stack Builder can be used to install the spatial packages listed under Spatial Extensions, looking for PostGIS for 32 or 64 bit version depending on your setup.

Opening up pgAdmin III, a new database can be created with a specific name and owner. The next step is to set up the database to accept the spatial data. To do this, highlight your created database and near the top of the screen there should be a SQL button, click this and execute the command below:

CREATE EXTENSION postgis

An output of: Query returned successfully with no result in 1480 ms. should be returned.

osm2pgsql

Step 1

Unfortunately, the Open Street Map wiki has not been updated for awhile. The Windows binary had been broken since the switch to 64 bit objects. A working version of osm2pgsql can be found here provided by alex85k from the osm2pgsql GitHub page:

osm2pgsql

Step 2

The next part is to download the PROJ libraries needed to run the above code provided again by alex85. If this is not extracted to C:\, then you will get an error Projection code failed to initialise when trying to import data to PostGIS using osm2pgsql.

PROJ libraries

Step 3

The last piece needed is the default.style file which needs to be explicitly given to osm2pgsql under Windows. This file can be found here. I’ve saved it to C drive as it is easy to remember.

Step 4

With these two files, the osm2pgsql guide can almost be followed on the Open Street Map wiki. The example prompt does not show the use of the style file.

After osm2pgsql environment variable path is set up, a command like below can be used to read in data:

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

I’ve had issues (Couldn’t open style file ‘D:/maps/default.style’: Permission denied) if the style file (-S) location was not the first variable.

Importing larger regions, the cache may need to be enlarged with the -C flag. Playing around with that value until you are able to read in the data may be necessary.

EDIT: If you encounter an error such as: “Error: Connection to database failed: fe_sendauth: no password supplied” the pg_hba.conf file needs to be modified from md5 to trust. The file was located in the data directory of the PostgreSQL installation location. I recently ran into this when I reinstalled Windows 10.

Acquiring Data

There are two sources I’ve used to get data:

• Geofabrik
• Mapzen Metro Extract

Each has its pros and cons. Geofabrik can give you a large region like state or province where as Mapzen will give you city information. Depending what is required one may be better than the other.