Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
branch: master
Fetching contributors…

Octocat-spinner-32-eaf2f5

Cannot retrieve contributors at this time

file 577 lines (399 sloc) 22.557 kb

An introduction to QGIS, PostGIS, and TileMill for Windows

This guide is intended to demonstrate basic usage of Natural Earth data and OpenStreetMap data within QuantumGIS (QGIS), PostGIS, and TileMill.

QGIS is a free desktop GIS program with features similar to ArcMap.

TileMill is a cartography design studio that runs on both the desktop and web.

PostGIS adds GIS functions to the PostgreSQL database.

We will get OSM data into PostGIS using the osm2pgsql importer.

We chose these tools because they have easy installers for windows, but also for their functionality. For more on the "why" behind these tools see: http://sproke.blogspot.com/2012/02/game-changer-open-source-mapping-in.html

Table of Contents

  1. Installing the Tools
  2. Importing OSM data
  3. Appendices
  4. References

Advanced FAQ

Advanced or curious users may wonder:

Q: Why not use the 'OpenStreetMap' plugin for QGIS? instead of the approach detailed below that recommends osm2pgsql to import OSM data into PostGIS using the 'OSM Tools' plugin.

A: The reasons are:

  • The authors have previously found the 'OpenStreetMap' plugin quite unstable/buggy (hopefully this is fixed now).
  • osm2pgsql supports importing custom tags (through the use of a style file), which is critical for humanitarian and other uses of OSM data that leverage custom tags.
  • osm2pgsql is the main tool to import OSM data by the most users worldwide, so using its schema can be beneficial, for example to work with style templates like: https://github.com/mapbox/osm-bright.
  • imposm is another great, widely used tool, but it does not (at this time) work on windows.

Q: Why not show how to use command line tools like ogr2ogr and gdal_translate?

A: These are great tools, but we aim for avoiding any command line usage in this tutorial. If/when this tutorial demands their functionality, we'll document how to use them through the QGIS interface.

Installing the Tools

Note: all installers and data referenced below as "local" are mirrored online at http://osgis-windows.s3.amazonaws.com

These are designed to be downloaded and packaged locally by instructors of this workshop by doing:

mkdir installers
mkdir data
cd installers
wget http://osgis-windows.s3.amazonaws.com/installers/7z920.exe
wget http://osgis-windows.s3.amazonaws.com/installers/HOT_Installer.exe
wget http://osgis-windows.s3.amazonaws.com/installers/QGIS-OSGeo4W-1.7.4-d211b16-Setup.exe
wget http://osgis-windows.s3.amazonaws.com/installers/TileMill-v0.9.1-Setup.exe
wget http://osgis-windows.s3.amazonaws.com/installers/postgis_2_0_pg91.exe
wget http://osgis-windows.s3.amazonaws.com/installers/postgresql-9.1.3-2-windows.exe
wget http://osgis-windows.s3.amazonaws.com/installers/osm_tools.zip
cd ../data
wget http://osgis-windows.s3.amazonaws.com/data/10m-admin-0-countries.zip
wget http://osgis-windows.s3.amazonaws.com/data/washington.osm.bz2
wget http://osgis-windows.s3.amazonaws.com/data/900913.sql
wget http://osgis-windows.s3.amazonaws.com/data/legacy.sql
wget http://osgis-windows.s3.amazonaws.com/data/gistfix.sql

Step 1: Install 7-Zip

This tool will be needed to uncompress bz2 files (like compressed OSM data).

Install from local copy:

  1. installers/7z920.exe

Or install from the web:

  1. http://downloads.sourceforge.net/sevenzip/7z920.exe

Setup file extensions

  1. Go the Start Menu > 7-Zip > 7-Zip File Manager
  2. Within the 7-Zip File Manager choose Tools > Options
  3. Under the system tab choose 'bz2' (or Select all')
  4. Press OK: now any bz2 files should automatically open in 7-Zip

Step 2: Install QuantumGIS

Install from local copy:

  1. installers/QGIS-OSGeo4W-1.7.4-d211b16-Setup.exe

Or install from the web:

  1. Google for “QGIS” to find http://www.qgis.org/
  2. Click Download, then choose Current Version/Software
  3. Find the Windows "Standalone” installer
  4. Right click the installer and choose "Run as Administrator"
  5. QGIS should then appear in your program menu

Customize QuantumGIS (Optional)

  1. Go to Plugins > Fetch Python Plugins...
  2. Wait for Python repositories to be fetched.
  3. Go to the Options Tab
  4. Check the box next to "Check for updates on startup"
  5. Change the drop down-menu to "every time QGIS starts"
  6. Click "Close"
  7. Go to Settings > Options
  8. "General" tab, "Application" section: change the "Icon theme" dropdown menu from "default" to "gis"
  9. "Rendering" tab, "Rendering quality" section: make sure "Make lines appear less jagged at the expense..." is checked.
  10. "CRS" tab, Coordinate Reference System..." section: make sure "Prompt for CRS" is checked
  11. Click "OK"

Step 3: Install TileMill

Install from local copy:

  1. installers/TileMill-v0.9.1-Setup.exe

Or install from the web:

Grab the latest version from http://mapbox.com/tilemill

Run the installer as an administrator. It will put a TileMill start shortcut in your Start Menu.

Step 4: Install PostgreSQL and PostGIS

Install from local copy:

  1. First install PostgresSQL via: installers/postgresql-9.1.3-2-windows.exe
  2. Next, install PostGIS via: installers/postgis_2_0_pg91.exe

Then see set-up details below.

Or install from the web:

  1. Go to http://www.postgresql.org/download/windows
  2. Click on the link to "Download the one click installer," which will take you to: http://www.enterprisedb.com/products-services-training/pgdownload#windows. Note: this is a different site from the postgres site, but that's ok.
  3. Click on the Version 9.1.3 (or whatever is latest in the 9.1.x series) download button for your version of Windows.
  4. Click "Save" (or whatever you browser supports)
  5. Navigate to where the installation file (named something like postgresql-9.1.3-1-windows.exe) is located and double-click on it to launch the installer.

Set-up Details

  1. Click "Next" through the first few Setup wizard steps.
  2. On the "Password" screen, type "osm". Note: PostgreSQL has its own users and superusers, which are separate and distinct from Windows users and administrators. As a default, PostgreSQL installation sets up both a PostgreSQL superuser named "postgres" and a Windows system user account named "postgres" that will use the same password. Confused yet? Who's on first?
  3. Click "Next" through the rest of the Setup wizard steps, leaving all values as their defaults.
  4. The last step of the Setup wizard should ask if you want to launch something called "Stack Builder." If you are online, you do! If you are offline, you will want to use the standalone installer identified above to install PostGIS.
  5. Make sure that the check box is checked.
  6. Click "Finish", which both finishes PostgreSQL installation and launches the Stack Builder.
  7. On the first page of the "Stack Builder 3.1.0" window, in the drop down, select "PostgreSQL on port 5432"
  8. Click "Next"
  9. On the next page, click on the "+" sign next to "Spatial Extensions"
  10. Check the box next to "PostGIS 2.0 for PostgreSQL 9.1 v2.0.0
  11. Click "Next" until presented with the "PostGIS 2.0.0" setup window.

PostGIS Install Details

  1. Start the installer through the Stack Builder or the standalone installer.
  2. Click "I Agree"
  3. Click "Next" until you get to the "Database Connection" window
  4. In the "Password" field, type "osm"
  5. Click "Next"
  6. On the "Database Name" window, in the "Database Name:" field, type "osm" (TODO: is this right?)
  7. Click "Install"
  8. Click "Yes" to the "Would you like us to register the GDAL_DATA environment variable (see Appendix C), click "Yes"
  9. Click "Close" on the "Installation Complete" window.
  10. If you're in the Stack Builder, click "Finish." If using the standalone installer, you won't need this step.

Troubleshooting

If you encounter an error when creating the database involving other connections or users, right click on the other databases in pgAdmin and select disconnect.


Verify PostgreSQL and PostGIS installation success:

  1. Find the "pgAdmin III" application in your Start menu.
  2. This should bring up an administrative window, with an "Object Browser" on the left side of the window.
  3. In the Object browser, double click on the icon with the red "X" labeled "PostgreSQL (localhost:5432)"
  4. Enter your password, "osm"
  5. The red "X" should disappear and a set of items should be listed below the "PostgresSQL" label.
  6. Click on the "+" sign next to the "Databases" icon
  7. There should be an icon with a red X labeled "postgis" or something like "template_postgis" (databases created by postgis install)
  8. Click on the "+" sign next to the "postgis" icon
  9. There should be an icon labeled "Extensions"
  10. Click on the "+" sign next to the "Extensions" icon
  11. If you see the items "postgis" and "postgis_topology" listed under the extensions, congrats! PostGIS 2.0 is installed.

Troubleshooting

If on Windows Vista or Windows 7, and your local account is not an administrator, you may need to give the "postgres" user administration privileges Otherwise you will may see errors about "permission denied" when trying to start the server.

  1. Windows 7: Start > Control Panel > User Accounts
  2. Manage another account, Pick Postgres
  3. Change Account Type, make administrator
  4. Go back to User Accounts view
  5. Change User Account Control Settings
  6. Lower the UAV settings to the lowest setting
  7. Restart your machine, login as your normal user
  8. Now the PostgreSQL server should be automatically running
  9. Now you may need to restart your machine to trigger the PostgreSQL Server to restart.

If you get an error about access denied, then go back and repeat the above user account steps


Connect to your (empty) PostGIS database from within QGIS

This step is just to test PostGIS is functioning okay for QGIS.

  1. Open QGIS
  2. Go to Layers > Add PostGIS Layers...
  3. Click "New"
  4. Then in the connections window type:
  Name: OSM  
  Service: leave blank  
  Host: leave blank  
  Port: 5432  
  Database: osm  
  SSL mode: leave as "disable"  
  Username: postgres  
  Password: osm
  1. Check "Save Username"
  2. Check "Save Password"
  3. Click "Test Connect" and you should should get a message that the connection was successful
  4. Click "OK" to return you to the previous menu
  5. Click "Close" to leave the "Add PostGIS Layers..." menu

Note: again, there is no data yet in this database so it is okay if you see a message from QGIS complaining about there being no accessible tables.

Step 6: Configure the "osm" PostGIS database

Now we need to do a few customizations to our postgis database to ensure that osm2pgsql works with PostGIS 2.0.

These workarounds are needed largely because the osm2pgsql version available for windows is quite old (and hopefully in the future these will not be needed).

Initialize the database with a custom projection

The reason for this step is to workaround this bug: http://trac.osgeo.org/postgis/ticket/1805

A file containing this custom projection can be found locally:

  1. data/900913.sql

Or downloaded from:

  1. Download http://svn.openstreetmap.org/applications/utils/export/osm2pgsql/900913.sql

To add to your database do:

  1. Go to pgAdmin III
  2. Drill down into the Object Browser until you reach the "osm" database. You may need to login to the database again.
  3. Select the "osm" database
  4. Click on the "Execute arbitrary SQL queries" button (it looks like a magnifying glass)
  5. File > Open
  6. Browse to the data/900913.sql file and select it
  7. Click "Open"
  8. Click on the "Execute Query" button (it looks like a green triangle pointed to the right or a green "Play" icon)
  9. The "Output pane" should display a message that reads "Query returned successfully...."
  10. Close the "Query" window

Initialize the database with legacy sql functions

The reason for this step is that the osm2pgsql build for windows is very old and still uses functions that have been removed from PostGIS 2.0 by default.

A file containing these legacy functions can be found locally:

data/legacy.sql

Or downloaded from:

http://trac.osgeo.org/postgis/browser/trunk/postgis/legacy_minimal.sql.in.c?format=txt

To add to your database do:

  1. Go to pgAdmin III
  2. Drill down into the Object Browser until you reach the "osm" database
  3. Select the "osm" database
  4. Click on the "Execute arbitrary SQL queries" button (it looks like a magnifying glass)
  5. File > Open
  6. Browse to the data/legacy.sql file and select it
  7. Click "Open"
  8. Click on the "Execute Query" button (it looks like a green triangle pointed to the right or a green "Play" icon)
  9. The "Output pnae" should display a message that reads "Query returned succesfully...."
  10. Close the "Query" window

Initialize the databse with legacy spatial indexing keyword

A file containing this hotfix can be found locally:

data/gistfix.sql

Or can be downloaded from:

http://trac.osgeo.org/postgis/ticket/1287#comment:8

To add to your database do:

  1. Go to pgAdmin III
  2. Drill down into the Object Browser until you reach the "osm" database
  3. Select the "osm" database
  4. Click on the "Execute arbitrary SQL queries" button (it looks like a magnifying glass)
  5. File > Open
  6. Browse to the data/gist.sql file and select it
  7. Click "Open"
  8. Click on the "Execute Query" button (it looks like a green triangle pointed to the right or a green "Play" icon)
  9. The "Output pnae" should display a message that reads "Query returned succesfully...."
  10. Close the "Query" window

Step 7: Install osm2pgsql

Osm2pgsql is included in the "HOTOSM Installer"

Install from local copy:

installers/HOT_Installer.exe

Or install from the web:

Download the "HOT_Installer" from: https://github.com/hotosm/installer/downloads

  1. Navigate to the folder where you've stored HOT_Installer.exe and click on it.
  2. When prompted, reboot your machine.

For downloading and installing osm2pgsql manually see Appendix F.

Step 8: Install the OSM Tools Plugin for QGIS

If online, use the following steps. If offline skip to the offline installation section below.

Online Installation

  1. Open QGIS
  2. Plugins > Fetch Python Plugins
  3. Click “Repositories” tab
  4. Click Add...
  5. Then type:
Name: dbsgeo.com plugins
URL: http://qgis.dbsgeo.com
  1. Check "Enabled"
  2. Click "OK"
  3. Click on the “Plugins” tab and you should should see “OSM Tools” available to install
  4. Highlight “OSM Tools”
  5. Click “Install Plugin”
  6. You should see a message that the "Plugin installed successfully"
  7. Click "OK"
  8. Click "Close"
  9. You should now have a menu item called "OSM Tools" in your top-level QGIS menu bar.

Offline Installation

A file containing the OSM Tools plugin functions can be found locally:

installers/osm_tools.zip

  1. Extract the .zip file to a local directory named osm_tools
  2. Copy the entire osm_tools directory to c:\Users\[your_username]\.qgis\python\plugins\ (You may need to create the "python" and "plugins" subdirectories)
  3. Open QGIS
  4. Plugins -> Manage Plugins
  5. In the "Filter" field, type osm
  6. Check the box next to "OSM Tools (Version 0.1.7)"
  7. Click "OK"
  8. You should now have a menu item called "OSM Tools" in your top-level QGIS menu bar.

Importing OSM data

This workshop provides two sample osm files locally, compressed in bz2 format.

See the data/ directory for files with the .osm.bz2 extention.

Step 1: Load an OSM file into PostGIS

  1. Open QGIS
  2. OSM Tools > Import into PostGIS (osm2pgsql)
  3. This will create another panel on the right-hand side of your QIS window.
  4. Click "Input" to choose an .osm file (of your choosing)
  5. IGNORE the "Style" option for now.
  6. In the "Database" section, make sure "osm" is selected in the drop-down menu.
  7. Keep all other defaults
  8. Click "Run"
  9. You may see lots of output in the bottom window
  10. There should be a green "Finished!" in the bottom window of the "Import OSM data..." panel.
  11. Unless you see an error, then the data should now be inside PostGIS

Note: This is a plugin written by Dane (dane@dbsgeo.com), if you have any problems email me.

Optional: import data on the command line (see Appendix H)

Step 2: View the PostGIS layers in the map

  1. Open QGIS
  2. Go to Layers > Add PostGIS Layer...
  3. Select "isn" from the drop down menu
  4. Click Connect
  5. Then select each table that is listed, for example: planet_osm_line, planet_osm_road, planet_osm_point, and planet_osm_polygon
  6. Click "Add"
  7. Close the window
  8. The layers should be viewable in the main QGIS map window
  9. If you get a prompt for the Projection of each layer, keep the default of WGS 84
  10. Now you should be able to browse and query these tables of osm data just as if they were shapefiles or other tabular GIS data

Step 9: Load a Shapefile into PostGIS and then view from QGIS

  1. Go to your \data folder
  2. Right click on 10m-admin-0-countries.zip
  3. Select "7-Zip > Extract files..."
  4. Click "OK"

To add to your database do:

  1. Go to pgAdmin III
  2. Select the "osm" database
  3. Select Plugins > PostGIS Shapefile and DBF loader 2.0
  4. In the "PostGIS Shapefile..." window, click "View Connection Details" to ensure the plugin has good connectivity to the database.
  5. On the "Import" tab, click "Add File"
  6. In the "Select a Shape File" window, navigate to the folder where you extracted the .zip file in the previous step.
  7. You should see ne_10m_admin_0_countries.shp - select this and click "Open"
  8. Click "Options"
  9. In the "DBF file character encoding" field, type windows-1252 - ref: https://github.com/mapbox/tilemill/issues/547#issuecomment-1722581
  10. Click "OK"
  11. Click "Import"

To view the imported shapefiles do:

  1. Go to QGIS
  2. Go to Layer > Add PostGIS Layer
  3. Double-check your connection to the "osm" database
  4. Click "Connect"
  5. Select the "ne_10m_admin_0_countries" table
  6. Click "Add"
  7. Click "OK" in the "Coordinate Reference System Selector Window" - it should have WGS84 highlighted already.
  8. You should now see an "ne_10m_admin_0_countries" layer in the GQIS Layers panel and a nice map of the world in the display window.

Appendices

Appendix A: Enable showing file extensions

Windows 7:

  1. Start > Control Panel > Folder Options
  2. Click View Tab
  3. Check “Show hidden files, folders, and drives”
  4. Check "Uncheck Hide extensions for known file types."

Appendix B: Working with OSM Extracts - Handling osm.bz2 files

  1. Download bzip2 windows installer from: http://gnuwin32.sourceforge.net/packages/bzip2.htm
  2. Click on “Complete package, except sources”
  3. Run the installer
  4. Then right-click on an osm.bz2 file and choose “Open With... > Choose default Program”
  5. Navigate and choose: C:\Program Files (x86)\GnuWin32\bin
  6. Now you should be able to double click any osm.bz2 file and it will uncompress to a file without the bz2 extension, which makes it possible to open directly by QGIS

Appendix C: How to edit system Path environment settings

Windows 7:

  1. Start > Control Panel > System
  2. Click Advanced System Settings
  3. On the "Advanced" Tab, at the bottom of the tab, click the "Environment Variables" buttons.
  4. In the "System variables" section - NOT the "User variables" section - select the "Path" variable
  5. Click Edit
  6. Put your cursor in the "Variable Value" input area
  7. Move the cursor to the far right and add a ; if the line does not already end with one
  8. Then add the custom path to a directory needed like "C:\Program Files (x86)\GnuWin32\bin"
  9. Click "OK"
  10. Click "OK" again.
  11. You should be done

Appendix D: Working with the command prompt on Windows

Windows 7:

  1. Navigate to a directory using Windows Explorer
  2. Then press shift and right-click on white space
  3. Choose "Open Command window here"

Other Windows:

  1. Install http://www.microsoft.com/windowsxp/Downloads/powertoys/Xppowertoys.mspx and follow above recommendations

Or:

  1. Open the start menu
  2. In the search bar, type "cmd"
  3. To move into a new directory, type: cd <dir name>
  4. To list items in a directory type: dir

Appendix F: Installing osm2pgsql manually

Download osm2pgsql:

  1. Download osm2pgsql windows binary from: http://tile.openstreetmap.org/osm2pgsql.zip
  2. Move the file to the root of the C drive, so the file will be C:\osm2pgsql.zip. Note: Moving a file to the root directory of your c: drive will require Administrator privileges.
  3. Unzip with 7-zip: Right click on the .zip file and choose 7-zip > Extract here

Fix "Path" environment settings (see Appendix C) to include the following:

C:\osm2pgsql
C:\Program Files\PostgreSQL\9.1\bin

Note: If you have a 64 bit OS and installed the 32 bit version of PostgreSQL, you may need to use C:\Program Files (x86)\PostgreSQL\9.1\bin instead of the path listed above.

You should now test that the "Path" setting is working:

  1. Open a command prompt (See Appendix E)
  2. Type into the command prompt: osm2pgsql
  3. You should receive a usage error stating the osm2pgsql version, which is good. If not, go back and re-confirm your Path settings.

Appendix H: Using osm2pgsql on the command line

Open a command prompt in the directory of an osm file (See Appendix E).

Then, assuming the osm file is called 'latest.osm', type:

osm2pgsql -U postgres -W -d osm -S C:\osm2pgsql\default.style latest.osm

Appendix I: create a new postgis-enabled database via the psql shell

  1. Open the PostgreSQL shell, aka pqsl:
  2. Go to Start > Programs > PostgreSQL > SQL Shell (psql)
  3. You will get a command prompt asking for input of connection parameters
  4. Hit return to accept the default for all items, until you get to the password where you should type the password "osm"
  5. Now, you should be inside the postgres shell
  6. Type (note, make sure to finish any commands you see below with the ";" !!!)
CREATE DATABASE osm WITH TEMPLATE postgis;
  1. While we are inside the shell lets learn a few commands:
  2. Switch into this new database called "osm": \c osm
  3. List the tables inside this database: \d
  4. Get details about a specific table: \d geometry_columns
  5. Exit the database and shell: \q

References

General:

QGIS:

PostGIS:

Something went wrong with that request. Please try again.