Overview

Skill Level: Any Skill Level

Experience using SQL and DB2 is helpful

uDig 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 uDig 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 uDig on Ubuntu Linux. The appearance will be similar on the other supported platforms, Windows and Mac OS/X.

udig-2nd-map2

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 uDig

    This example uses Ubuntu Linux with a local DB2 LUW database but the operation is very similar on Windows and Mac OS/X.

    1. Download and install uDig using the instructions at the download link in the Ingredients section.

    2. When starting uDig for the first time, you need to specify the location of the JDBC driver files for DB2. Access the preferences by selecting Window->Preferences from the menu bar, then expand the Catalog item and select DB2.

    3. Use the Browse buttons to navigate to the DB2 installation java directory and select the db2jcc.jar and db2jcc_license_cu.jar for your DB2 release.. The actual path and DB2 version will depend on where you installed DB2 and the version installed. uDig should work correctly with currently supported versions of DB2. This will result in a window like the following:

      udig-pref2


    4. Click on Apply which will prompt you to allow uDig to be restarted.
  3. Select and display spatial data

    1. To add spatial tables from DB2 as layers, from the menu bar select Layer->Add and then select DB2 which will show a dialog to add the connection information similar to the image below. Enter the connection information and then click the Get schemas button to get a list of the schemas with spatial tables. Select the desired schema and then click the Finish button.

      udig-newlayer2

    2. This will display a list of tables that can be included in the map. Select the BANKS, CUSTOMERS, and SJSTREETS tables and click Finish.

    3. A map like the following will be displayed.

      udig-initial-map2

  4. Customize the map display

    uDig 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 a layer name and select Change Style…

      udig-customize0
    2. 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 Apply.

      udig-style0

     

  5. Conclusion

    With uDig you can integrate spatial data from many different sources – other databases, files and web services. Use uDig 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