Analyze DB2 spatial data with a free geobrowser

Use the IBM Data Management geobrowser for DB2, Informix and Netezza to visualize relational table data

A geobrowser for IBM® DB2®, Informix® and Netezza® is now available as a free download. You can easily list tables containing spatial data and select tables to display as a map using a combination of points, lines, and polygons. The color, symbols, line style, and shading are user-selectable. Map navigation tools allow you to zoom in and out, pan, and select and display the alphanumeric values associated with each graphic object. The geobrowser can render the results of spatial analysis using DB2, Informix or Netezza. For example, the results of a spatial query that identifies customers within a flood zone can be visualized. The geobrowser is based on components from IBM's ILOG® JViews Maps. Use these components to construct custom spatial visualization applications. This tutorial shows how to use the free geobrowser to visualize data from DB2 tables.

David Adler (dadler@us.ibm.com), Senior Software Engineer, IBM China

David AdlerDavid Adler has been responsible for the development of spatial database technology in IBM for over 20 years, the past 10 years in DB2 Spatial Extender Development.



01 November 2012 (First published 24 March 2011)

Also available in Chinese Vietnamese Portuguese

Before you start

For many years, DB2, Informix and Netezza have provided the capability of managing and analyzing spatial data in relational tables. Table columns can be defined with spatial types for points, lines, and polygons, which can represent objects like:

  • Point locations — Customers, retail stores, electrical transformers, cell towers
  • Lines — Highways, coastline, delivery routes, electrical transmission lines
  • Polygons — Sales/service territories, flood/fire risk, states/provinces/counties

SQL queries can incorporate spatial functions to analyze spatial relationships, such as finding customers within a flood zone. For example:

SELECT cust_name, cust_addr
FROM customers, flood zones
WHERE ST_Within(cust_loc, flood_loc) = 1

The result of this query would produce a set of customer names and addresses whose houses could be at risk of flooding. This might be useful in calculating insurance rates or sending out an offer to purchase flood insurance.

In many cases, additional insight can be derived by visualizing the result in a mapping tool. As they say, "A picture is worth a thousand words." The rest of this tutorial will show you how to use the free geobrowser to visualize this data from DB2 tables.

Figure 1. Kentucky customers highlighting in red those in a flood zone
A map of Kentucky customers highlighting in red those in a flood zone

About this tutorial

This tutorial takes you through setting up DB2 with a sample database, setting up the geobrowser, and running the geobrowser to visualize spatial tables and the results of spatial analysis. Suggestions are provided to efficiently work with spatial data and explore additional spatial visualization techniques.

Although this tutorial is based on DB2 for Linux®, UNIX®, and Windows®, the same operations are possible, with minor changes, using DB2 for z/OS®, Informix or Netezza.

Prerequisites

This tutorial is written for spatial application developers and database application developers. You should have some familiarity with spatial data and working with SQL queries.

System requirements

To run the examples, you need a Windows or Linux environment with about 500 MB available to install DB2 and the geobrowser. If DB2 is already installed, you only need about 20 MB.


Getting started

Overview

The easiest way to get started is to download the Express-C versions of DB2 for Windows or Linux, then run the spatial demo program to create a number of tables containing spatial data. The geobrowser can be downloaded to view the data in these tables and the results of spatial queries. See the Resources section for the code.

The DB2 Information Center includes a section for "Spatial data," which provides information about installing and working with DB2 Spatial Extender (see Resources).

Installing DB2 and running sample program

After downloading and unpacking the install images, run the setup.exe (Windows) or db2setup (Linux) for the DB2 server. The custom option in the setup process should be used to select the Spatial Extender server and client components.

In the Windows environment, it is necessary to increase the database manager configuration parameter for the agent stack size with the following commands issued from a DB2 command window:

db2 update dbm cfg using agent_stack_sz 100
db2stop
db2start

In Linux, it is necessary to set the DB2PATH environment variable to point to the DB2 instance installation directory prior to running the spatial demo program with a command like:

export DB2PATH=/home/db2inst1/sqllib

If your DB2 instance is different, modify the command to point to the correct location.

Before running the spatial demo program, you need to create a database. The easiest way to do this is to create the default DB2 sample database with the command db2sampl, then change to the spatial samples directory:

cd /home/db2inst1/sqllib/extenders/samples/spatial (Linux)
or
cd c:\program files\IBM\sqllib\extenders\samples\spatial (Windows)

and run the command runGseDemo sample userid password. This will enable the database for spatial processing, create a number of spatial tables, and execute spatial queries with output to the terminal window.

Installing the geobrowser

The geobrowser is simply installed by unzipping the downloaded file to a convenient location on your system. The batch files to start the geobrowser — run_geobrowser.cmd (Windows) and run_geobrowser.sh (Linux) — may need to be modified according to information in the batch file to set the Java™ classpath appropriately.


Working with the geobrowser

Starting the geobrowser

Start the geobrowser by going to the directory where it was installed and running the command run_geobrowser.cmd (Windows) or run_geobrowser.sh (Linux). The geobrowser initially displays a map of the world based on a file in the installation. (The screen captures show a smaller map area due to the tutorial formatting requirements.)

Use Help > Help Contents to access the online documentation for the JViews Map Viewer sample application on which the geobrowser is based.

Figure 2. Initial screen when geobrowser started
Initial screen of geobrowser

Displaying spatial tables

Use File > Add Map Data to bring up a dialog to select the spatial data to be added to the map. The default selection is to access DB2 for Linux, UNIX, and Windows. Files of Type allows for the selection of an Informix or Netezza database connection or accessing data in shapefile format from the file system.

Fill in the appropriate DB2 connection information. You should use the same user ID and password specified during the setup process for running runGseDemo. If you are running on the same system where DB2 is installed, the host name should be specified as localhost. The default port is 50000. Click Connect, which will display a list of tables with a spatial column as shown below. Select all the tables and click Open.

Figure 3. DB2 connection dialog
DB2 connection dialog

To zoom in on the region of interest, click on the left side of the expand button in the Map Layers tab to show the list of tables loaded. Then select the REGIONS table and click the zoom button (zoom layer tool) on the tool bar, which will result in a map display like the following.

Figure 4. Customers in Kentucky
customers in Kentucky

Other buttons on the toolbar (tool bar) can be used for zooming in/out, panning, selecting an object, etc. Hovering the mouse over a button will pop up a description of the button.

Changing symbology

The default symbology for the map will display all point objects as hollow green squares and polygons with a blue boundary. We want to highlight the customers in the flood zone as red filled diamonds and draw the flood zone polygons in red.

The symbology is controlled by the Dynamic Style Setting pane below the list of map layers. Select a map layer, and you are able to set color, labeling, marker symbol, and symbol size.

To highlight the customers in a flood zone, select HIGHRISKCUSTOMERS in the Map Layers tab, then click the color bar next to Point Color and select a red color from the palette. Click in the field next to Point Type and select Filled Diamond. Click in the field next to Point Size and change the size to 4. Now click in the field next to Labeling and select the NAME.

To highlight the flood zone polygons, select FLOODZONES in the Map Layers tab, then click on the color bar next to Line Color and select a red color from the palette.

To differentiate the office locations, select the OFFICES in the Map Layers tab, then click on the color bar next to Point Color and select a blue color from the palette. Click in the field next to Point Type and select Filled Square. Now click in the field next to Point Size and change the size to 4. If you zoom in, you should see something like the figure below.

Figure 5. Highlighted flood zone customers in Kentucky
Highlighted flood zone customers in Kentucky

Saving a map

All the information about the map you are working on can be saved and restored in a subsequent session. Use File > Save Map As to bring up a dialog to save a map as shown in the figure below.

Figure 6. Saving a map document
Save map dialog

The following options are available in this dialog:

Save Theme Only
Selecting this will save the database connection information, the layers selected, and the associated symbology information. When the map document is reloaded, the database tables will be read to restore the map. If this option is not selected, all of the graphic information will be saved to the file so the map can be restored without a database connection. Not selecting this option can result in a very large file.
Save as Binary
If this is selected, the map document is stored in a binary representation. Otherwise, it is saved as a readable text file.

Save the map so you can use it again in the next section.


Spatial analysis

Working with spatial queries

Sample spatial data background

In the previous section, you simply selected all of the tables and views created by the spatial sample application. Now we should discuss the underlying tables and what useful operations can be done with them.

The following map layers correspond directly to DB2 tables:

  • CUSTOMERS— Point values with the coordinates corresponding to customer addresses
  • OFFICES— Point values with the coordinates corresponding to branch office addresses
  • FLOODZONES— Polygons defining regions of high flood risk
  • REGIONS— Polygons representing sales regions in Kentucky
  • HIGHRISKCUSTOMERS is actually a DB2 view with the following definition:
                CREATE VIEW HIGHRISKCUSTOMERS (ID, NAME, ADDRESS, CITY, STATE, ZIP,
                                               INCOME, PREMIUM, CATEGORY, LOCATION)
                AS (SELECT C.ID, C.NAME, C.ADDRESS, C.CITY, C.STATE, C.ZIP,
                           C.INCOME, C.PREMIUM, C.CATEGORY, C.LOCATION
                FROM CUSTOMERS C, FLOODZONES F
                WHERE DB2GSE.ST_WITHIN(C.LOCATION, F.LOCATION) = 1)

This view implements a spatial join, joining together the rows in the CUSTOMER table with the corresponding rows of the FLOODZONES table where the customer location is within a flood zone polygon. See Resources for the DB2 InfoCenter, which contains information on all the spatial functions that can be used in a WHERE clause.

Some of the most commonly used spatial functions include:

  • ST_Contains— The inverse of ST_Within
  • ST_Intersects— Tests whether any part of one spatial value intersects another
  • ST_Touches— Tests whether one spatial value touches another in one place but doesn't overlap
  • ST_Distance— Tests whether the closest distance between two spatial values is less than a specified distance

Distance analysis

Another scenario we may be interested in is finding all the customers within a certain distance of branch offices in order to highlight them on the map and to draw a circle of the same distance. Execute the SQL statements below to create the views:

CREATE VIEW NEARCUSTOMERS (ID, NAME, ADDRESS, CITY, STATE, ZIP,
                               INCOME, PREMIUM, CATEGORY, LOCATION)
AS (SELECT C.ID, C.NAME, C.ADDRESS, C.CITY, C.STATE, C.ZIP,
           C.INCOME, C.PREMIUM, C.CATEGORY, C.LOCATION
FROM CUSTOMERS C, OFFICES O
WHERE DB2GSE.ST_DISTANCE(C.LOCATION, O.LOCATION,'STATUTE MILE') < 10.0)
CREATE VIEW OFFICE_CIRCLE (ID, CIRCLEPOLY)
AS (SELECT O.ID, 
    TREAT (DB2GSE.ST_BUFFER(O.LOCATION, 5.0, 'STATUTE MILE') AS db2gse.ST_Polygon)
FROM OFFICES O
    )

The TREAT operator in the above view is needed to inform DB2 that the result of the ST_BUFFER function will be a polygon spatial type. Otherwise, the result is considered to be a generic ST_Geometry spatial type.

To see the results in the geobrowser:

  1. Start the geobrowser. If it was already active, exit and restart it to make sure that the new spatial views will be picked up.
  2. Use the File > Open Prepared Map dialog to select the map document you saved earlier.
  3. Use the File > Add Map Data dialog to connect to the database and select NEARCUSTOMERS and OFFICE_CIRCLE.
  4. Uncheck the map layers for HIGHRISKCUSTOMERS and FLOODZONES.
  5. Select NEARCUSTOMERS, setting the color to yellow, the symbol to filled square, and the size to 4.
  6. Select OFFICE_CIRCLE, setting the color to yellow.

This should result in a map like the following.

Figure 7. Map with customers within 5 miles of a branch office highlighted
Distance map

Permissions and schemas

The operations above assume you are connected to the database as the same user who originally ran the runGseDemo program. This simplifies table access in the geobrowser and creating the additional database views.

To view spatial tables created by someone else, you need to ensure that the user ID making the connection in the geobrowser has been given SELECT permission on the spatial tables because the geobrowser will only list tables you are authorized to access.

When you create the views, unless you specify otherwise, DB2 assumes that you created and own the tables being referenced. To reference tables created by someone else, you need to explicitly prefix the table name with its schema name — usually the creator name, unless otherwise specified. You can also specify a schema for the view you are creating. In this case, we would express the view as:

CREATE VIEW MYSCHEMA.NEARCUSTOMERS (ID, NAME, ADDRESS, CITY, STATE, ZIP,
                               INCOME, PREMIUM, CATEGORY, LOCATION)
AS (SELECT C.ID, C.NAME, C.ADDRESS, C.CITY, C.STATE, C.ZIP,
           C.INCOME, C.PREMIUM, C.CATEGORY, C.LOCATION
FROM DAVEA5.CUSTOMERS C, DAVEA5.OFFICES O
WHERE DB2GSE.ST_DISTANCE(C.LOCATION, O.LOCATION,'STATUTE MILE') < 10.0)

Custom application visualization

Building a custom JViews Maps application

The geobrowser application used in this tutorial is based on the JViews Maps mapbuilder sample application. The main differences are the support for a limited number of data source types (DB2, Informix, Netezza shapefile) and the Options > Refresh All capability. Refresh All is useful for reloading the map display from the current database table contents when another application may be making changes to the table.

The mapbuilder sample is a Java application that integrates many of the JViews Maps components into a comprehensive end-user application. You can modify this to include just the capabilities that you need and add your own custom functionality. Or you can start from scratch to build an application based on the JViews Maps components.

Download a trial copy of JViews Maps (see Resources). To deploy your own application, you would need to acquire a regular license for JViews Maps.

Dynamic symbols with JViews Designer

JViews Maps also includes components of JViews Designer, which allow more sophisticated and dynamic symbology than is provided by the JViews Maps map layer symbology. You can have symbols that change size, color, or icon based on attribute values.

When you install JViews Maps, a sample you can run out of the box is loaddiag, which shows moving vehicles of different types over a map of the United States. This uses an XML data source and Java coding to simulate vehicle movement.

It is also possible to use a DB2 JDBC data source connecting to a DB2 spatial table. An example based on the database and data we used in previous sections is shown in the following figure, where each office is represented by a circle whose size is based on the number of employees in the OFFICES table.

Figure 8. Map using JViews Designer with variable symbols
Image shows JViews Designer map

The sample code to implement this is available in the Download section. Unzip it into the JViews Maps samples directory.

Sample code explanation

The general approach to the JViews visualization application is to minimize actual coding requirements and use a descriptive approach to the contents and visualization. Each of the significant files involved are described below.

src\dw-demo1.java

This is a simple Java AWT application to build the GUI, using JViews Maps components for the toolbar and for the map area. The loading of the dw-demo1.idpr file starts off the visualization process. The code is pretty straight-forward.

resources\data\dw-demo1.idpr

This file specifies the styling to be applied, the database connection information, the query to get columns used in the application, and the mapping of the database column names to the property names used by the stylesheet.

JViews expects the location information to be provided in properties with the names latitude and longitude. The derivation of these values is a little more complicated than usual. JViews works with coordinates expressed in radians and not the more commonly used degrees, so we need the expression * 3.14159 / 180.0 to convert from degrees to radians.

The coordinates in the LOCATION column are actually stored using the state-plane coordinate system used for high-precision mapping applications, and the units are feet. We use the DB2 Spatial Extender st_transform(1) function to convert from feet to degrees in the coordinate system commonly used for North American data.

<diagrammer style="dw-demo1.css">
  <datasource class="ilog.views.diagrammer.datasource.IlvJDBCDataSource">
    <connection id="basic"
      url="jdbc:db2://localhost:50000/sample"
      user="davea5"
      passwd="my_password"
      driver="com.ibm.db2.jcc.DB2Driver"/>
    <query connection="basic"
      value="SELECT id, name, employees, 
             location..st_transform(1)..st_miny * 3.14159 / 180.0 as latitude, 
             location..st_transform(1)..st_minx * 3.14159 / 180.0 as longitude 
             FROM davea5.offices 
             where location is not null"
      tag="node"
      links="false">
<map columns="ID" property="id"/>
<map columns="LATITUDE" property="latitude"/>
<map columns="LONGITUDE" property="longitude"/>
<map columns="NAME" property="name"/>
<map columns="EMPLOYEES" property="employees"/>
   </query>
  </datasource>
</diagrammer>

resources\data\dw-demo1.css

This file specifies the background map to be used and the styling of the nodes returned from the query in the dw-demo1.idpr file. The Map section references the specific map to be used, dw-demo1.ivl.

Map {
   _rule_name : "Background Map" ;
   autoRegionOfInterest : "true" ;
   map : "url(dw-demo1.ivl)" ;
   regionOfInterest : "" ;
   regionOfInterestMargin : "5.0" ;
}

You should replace the dw-demo1.ivl file name with the name of the map file you created in the geobrowser.

In the node section, name : "@name" specifies that the name property should be used for the name value shown with the symbol. class : "@|symbolResource(ilog/views/palettes/shared/symbols/SizedRound.css,Symbol)" specifies the symbol palette to be used and the particular symbol: SizedRound. size : "@employees" specifies that the employees property should be used to control the size of the symbol.

node {
   Layer : "20" ;
   Interactor : "ilog.views.sdm.IlvSDMCompositeInteractor" ;
   fill : "false" ;
   name : "@name" ;
   class : "@|symbolResource(ilog/views/palettes/shared/symbols/SizedRound.css,Symbol)" ;
   size : "@employees" ;
}

resources\data\dw-demo1.ivl

This is the map file produced by the geobrowser. You should replace it with the file you created.


Conclusion

You should now have a reasonable idea of how to access DB2 tables to visualize as a map the contents of spatial columns and the results of database queries using spatial predicates. The points, lines, and polygons can be labeled using database column values as well as styled with different colors, symbols, line types, and line widths. You can build custom visualization applications using Java technology and the JViews Maps components. With the JViews Designer, you can create applications with sophisticated symbols that can vary in color, size, and image based on database values. By changing the database connection parameters, these same capabilities are available with DB2 for z/OS, Informix and Netezza databases.

Acknowledgements

I thank Robert Samuel [proberts@in.ibm.com] from the IBM India Software Labs for his review and suggestions to improve this tutorial and his enhancement of the geobrowser to include support for the Netezza database.


Download

DescriptionNameSize
Source code for DB2 and JViews Maps sampleDB2JviewsMapsDemo.zip30KB

Resources

Learn

Get products and technologies

  • Download DB2 Express-C. This is a fully functional no-charge version of DB2 which includes Spatial Extender.
  • Download DB2 Spatial Extender that corresponds to an existing DB2 server installation. Sample spatial data in shapefile format for use with Spatial Extender is also available via this link.
  • Download the Geobrowser for DB2, Informix and Netezza. This is free for you to use with any version of DB2, Informix or Netezza with the spatial feature installed.
  • Download IBM Data Studio, which provides developers and DBAs with basic capabilities for database management and development at no charge for DB2, Informix and Netezza.
  • Download a trial copy of JViews Enterprise. (This includes JViews Maps functionality.)
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, WebSphere
ArticleID=642280
ArticleTitle=Analyze DB2 spatial data with a free geobrowser
publish-date=11012012