Overview

Skill Level: Any Skill Level

GeoServer is an open source Web Map and Web Feature server.

This recipe takes you through the steps to load spatial data into a DB2 for z/OS, DB2 LUW or DashDB database, and setup GeoServer to serve spatial data.

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

Step-by-step

  1. Setup DB2 and sample data

    1. Note: this recipe uses GeoServer running on Ubuntu Linux connecting to a DashDB database. Change the database connection 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. (Use catalog-luw.sql for DashDB)

      You can execute the script with a command like:
      db2 -tvf catalog-luw.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-luw.sql
      – Check that there were no processing errors
    5. Verify the imported data with the SQL statement (change “dash5510” to your connection userid):
      db2 select name, street, varchar(db2gse.st_astext(geom),32) from dash5510.banks
      which should return the name, street address and location of the Meridian and San Carlos branches
  2. Install and setup GeoServer

    1. Download and install GeoServer using the instructions at the download link in the Ingredients section. The DB2 support is an extension that needs to be download and installed separately using these directions.
    2. From the Windows start menu, navigate to Geoserver and click on “Start Geoserver. This will open a command window and show startup messages for a minute or so until the server is started. 
    3. From the start menu, navigate to GeoServer and click on “GeoServer Web Admin Page” which will open a window in your default web browser like the following.
      geoserver-login2
    4. Use the default userid ‘admin’ and password “geoserver” to login which will then show the administration window as shown.
      geoserver-logged-in2
  3. Create DB2 Data Source and Vector Layers

    1. Click on “Add stores”. Select DB2 NG and then fill in the connection information similar to that shown below and click Save at the bottom. Note that the schema is case sensitive so it is generally important to make sure that this is entered in upper case which is the database default. On DashDB this should be the same as the login userid.
      geoserver-newds2
    2. This will bring up the New Layer dialog with a list of the tables with spatial columns. In order to make a table available, click on Publish under “Action”.
      geoserver-newlayer2
    3. This will bring up a Layer Edit dialog with many fields. The only essential action is to click on Compute from native bounds as shown below and then Save.
      geoserver-newlayerpublish2
  4. Access the spatial web server

    1. A sample of what the data looks like can be shown by selecting “Layer Preview” from the main window and then selecting the “OpenLayers” format which will result in a display as below for the San Jose streets.
      geoserver-preview2
    2. In general, you will want to access GeoServer through an open source or commercial GIS product that supports data integration using a Web Map Server (WMS) or Web Feature Server (WFS). A WMS provides a map image (bit map) of the data while a WFS provides the feature vector information along with attribute data that can be used for styling and labeling purposes.
    3. To use the Geoserver WMS capability, use a URL like the following from the GIS tool:
      http://localhost:8080/geoserver/cite/wms?service=WMS
    4. To use the Geoserver WMS capability, use a URL like the following from the GIS tool:
      http://localhost:8080/geoserver/cite/wfs?service=WFS
    5. This example uses the QGIS open source GIS product to display the banks, customers and streets from GeoServer using the WFS support.
      geoserver-qgis
  5. Conclusion

    With GeoServer you can make available DB2 spatial data through the Internet in a wide variety of formats, including:

    • WMS
    • WFS
    • KML
    • GML
    • GeoJSON
    • Shapefile
    • and many others

    You may also find these tutorials on Developer Works useful:

Join The Discussion