A Picture’s Worth A Thousand Words: The DB2 Universal Database Spatial Extender

by Paul C. Zikopoulos

I know it’s an old saying, but it certainly is an accurate one in this case. Visualizing data makes it easier to understand and act on. In fact, quite often, patterns or correlations that you never thought existed suddenly become apparent with visualization. The IBM DB2 Universal Database Version 8.2 product (DB2 UDB) comes with free spatial analysis capabilities in the form of the DB2 UDB Spatial Extender. In previous releases, the DB2 UDB Spatial Extender was a chargeable add-on product, but now, with every server edition of DB2 UDB, you can leverage this capability for free. Spatially analyzing your data is not mainstream…yet. In this article, I give you some details about spatial analysis, its history, and its role in DB2 UDB.

The Next Frontier of Business Intelligence – A Little More About Spatial Data

When most people think about spatial data, they think of maps and latitude and longitude (LAT/LONG) coordinates. Spatial components encompass these but so much more. Think about it for a moment. We all live somewhere and that somewhere typically has an address. Addresses can be graphically represented and spatial operations can be performed on them. A typical large distribution warehouse has many aisles of various products that are part of a just-in-time (JIT) distribution channel. These products are quickly located by some sort of placement code. For example, A23D may indicate that the required part is housed in Building A, in aisle 23, on shelf D. This too can be spatially viewed and provide a heartbeat of inventory levels to avoid stock-outs.

Here are just a few examples of industries where spatial analysis can provide strategic or competitive advantage:

• Banking/Finance
  
  
  
  • Branch locations
    
  • Merchant locations
  
  
• Government
  
  
  
  • Fire station locations
    
  • Police station locations
  
  
• Utilities
  
  
  
  • Locations of lines/pipes
    
  • Locations of offices/personnel
  
  
• Health-care
  
  
  
  • Hospital and doctors’ office locations
    
  • Specialized care facility locations, such as sports injury or physiotherapy clinics
  
  
• Retail
  
  
  
  • Store locations
    
  • Supplier locations
    
  • Customer addresses
    
  • Billboard locations
  
  
• Manufacturing
  
  
  
  • Plant location
    
  • Nearest railhead/transportation
  
  
• Insurance
  
  
  
  • Accident locations
  
  
• Airlines
  
  
  
  • Address of ticket agencies

There are many other less traditional ways to leverage spatial data. For example, the following figure might depict the progress of a customer’s steps through a retail store. The customer’s shopping cart is equipped with a Wi-Fi device that receives informational ads based on their location in the store, and broadcasts the path-to-purchase for the store’s top selling products. As a customer makes different turns through the store, cross-selling promotional advertisements are displayed on the screen to try and bring the customer back to the high-margin items.

The following figure might represent the signal strength of a cellular phone provider in a specific coverage area based on continual health-probing information returned to broadcast towers from the device itself:

It would be a lot easier to spot the problem areas in this diagram than it would to inspect thousands of credits that were paid back to customers because of weak signal strength and poor service.

And a business initiative that caters to higher income families might leverage census and income distribution data to help them figure out where to open their new location:

So, almost all data has the potential to be spatially analyzed. Here is a list of some of the most common types of spatially analyzed objects:

• Information about anything that can be located on the earth’s surface:
  
  
  
  • Natural objects: rivers, lakes, mountains, etc.
    
  • Man-made objects: buildings, utility facilities, cell phones, etc.
    
  • Cadastral: property boundary, voting districts, etc.
  
  
• Anything represented by a geometry:
  
  
  
  • Points: the location of a specific entity such as an ATM
    
  • Lines: the distance between two cities
    
  • Polygons: the areas subject to flooding
  
  
• Location and geometry defined by:
  
  
  
  • Coordinates: LAT/LONG or X/Y coordinates
    
  • Specific addresses: Use a geocoder to generate the map location
    
  • Landmarks: Use a gazetteer to get coordinates of the White House, for example

Types of Spatial Queries

Being able to spatially analyze data means making it available to end users for query. Clearly, the possibilities are endless, but the following examples give you an idea of the kind of business-related questions you can ask when you are able to think spatially. (I will give you some examples specific to DB2 UDB later in this article.)

• Simple “where” questions (simple predicates)
  
  
  
  • Who lives close enough to this group of employees that have set up a carpool?
    
  • Which of my insured properties are subject to the flood zone warning recently issued by the municipality?
  
  
• Location with other attributes (complex predicates)
  
  
  
  • Show me the videos of the areas around transmission towers near which new tall buildings are proposed.
    
  • Show me all the cities that this waterway touches.
  
  
• Spatial coincidence (spatial joins)
  
  
  
  • Which sets of employees could be eligible for a carpool?
    
  • Which of my insured properties are at risk because an overflowing river intersects them?

As disk storage becomes cheaper and cheaper, and companies collect more data, competitive advantage can be leveraged in new approaches to data analysis. Since almost every type of data that you can imagine has some sort of spatial component to it, chances are that your data could reveal more to you if it were spatially extended.