Contents


Overview

Skill Level: Any Skill Level

Experience using SQL and DB2 is helpful

QGIS is a full-function GIS tool for displaying and analyzing spatial data.

This recipe takes you through the steps to load spatial data into a DB2 for z/OS, DB2 LUW or DashDB database, connect QGIS to DB2 and visualize the spatial tables.

Ingredients


With this recipe you will create a map of San Jose streets, two bank branches and a number of sample customers that looks like the following screen capture from QGIS. The current QGIS support for DB2 is only the Windows environment.

qgis-2nd-map0

Step-by-step

  1. Setup DB2 and sample data

    1. Note: this recipe uses the database name OSTEST and the database connection userid OSUSER. Change these values in the examples to your database name and userid.
    2. Download and unzip the sample data to a convenient directory.
    3. Open a DB2 command window where you can execute SQL statements. If you are using a remote database, make sure it is cataloged locally. You can use the scripts catalog-luw.sql or catalog-zos.sql as appropriate and make changes for the remote server location.

      You can execute the script with a command like:
      db2 -tvf catalog-zos.sql

    4. Import sample spatial for banks, customers and San Jose streets.
      – Modify the scripts import-luw.sql or import-zos.sql as appropriate to change the userid in the stored procedure. (The scripts contain descriptions of each of the steps and any modifications needed.
      – Execute the script with a command like:
      db2 -tvf import-zos.sql
      – Check that there were no processing errors
    5. Verify the imported data with the SQL statement (change “osuser” to your connection userid):
      db2 select name, street, varchar(db2gse.st_astext(geom),32) from osuser.banks
      which should return the name, street address and location of the Meridian and San Carlos branches
  2. Install and setup QGIS

    This example uses QGIS on Microsoft Windows to connect to a remote DB2 for z/OS database.

    1. Download and install QGIS using the instructions at the download link in the Ingredients section.
    2. In order to use QGIS with DB2, there is a manual installation step to ensure the DB2 code is in the Windows PATH. Create a file with the name db2.bat which contains the lines:
      REM Uncomment line below for Windows 32-bit
      REM SET gskpath=C:\Program Files (x86)\ibm\gsk8\lib
      REM Uncomment line below for Windows 64-bit
      REM SET gskpath=C:\Program Files\ibm\gsk8\lib64
      SET Path=%db2path%\BIN;%db2path%\FUNCTION;%gskpath%;%path%

      Put this file in the \etc\ini subdirectory under the QGIS installation directory.

    3. Start QGIS Desktop from the Windows Start menu.
    4. There are several ways to add the DB2 connection but the easiest is the menu bar; select Layer -> Add Layer -> Add DB2 Spatial Layer… and click New. Choose a “Name” that is meaningful, like the database name. The “Driver” should generally be “IBM DB2 ODBC DRIVER”. Fill in the “Host”, “Port”, “Database”, “Username” and “Password” to connect to your system. The Test connection button can be used to verify the database connection parameters.

      The Save check boxes can be used to save the Username and Password values for subsequent connections but this can pose a security risk as they are stored in plain text. If you don't save these values, QGIS will prompt for them when you connect to the database.

      Click OK to save the connection information.

      qgis-connect2

    5. Click “Connect” to display the list of spatial tables in DB2 as shownbelow. Multiple tables can be selected by clicking on the rows and then click Add to add and display the map.

      qgis-add-data2

    6. Initial map display

      qgis-initial-map2

  3. Customize the map display

    QGIS allows you to customize the style for symbols, color and labels based on table attributes.

    1. To change the style of a layer, right-click on the layer name and select Properties. This will display a window like the following where you can change aspects of the style such as the symbol to be displayed, its size and color and labels to be associated with each feature. Make your changes and click OK.

      qgis-properties

    2.  This is an  example of the map after adding labels to the customers, banks and streets and the symbols associated with customers and banks.

      qgis-2nd-map0

  4. Integrate a web base map

    A more attractive representation can be produced by incorporating a base map from a Web Map Server (WMS). In this step, you will integrate a base map using the Open Street Map data and hosted by ows.terrestris.de.

    1. From the menu bar, select Layer -> Add Layer -> Add WMS/WMTS Layer… and click New.
    2. In the Connection details section, enter http://ows.terrestris.de/osm/service and click OK.
    3. Select the OSM-WMS entry and click Add.
      qgis-select-wms
    4. This will re-display your data with a nice base map background. Make sure that OpenStreetMap WMS is the last layer listed – drag it down to reposition it if necessary.
      qgis-wms

     

     

  5. Conclusion

    With QGIS you can integrate spatial data from many different sources – other databases, files and web services. Use QGIS to gain insight into spatial relationships of your data.

    The current map state can be saved as a project for subsequent use and also printed or exported.

    You may also find these tutorials on Developer Works useful:

Join The Discussion