DB2 Spatial Extender -- Working With

DB2 Spatial Extender enables users to visualize and analyze operational and business data spatially. Spatial location information, such as latitude and longitude, as well as many other coordinate systems, can be derived automatically from existing data, stored in an industry-standard spatial format, and retrieved for analysis with the aid of spatial indexes. DB2 Spatial Extender was developed in collaboration with the Environmental Systems Research Institute (ESRI), the market leader in Geographic Information Systems (GISs). All ESRI tools and applications, as well as all applications that use the ESRI SDE API, will work as-is with DB2 Spatial Extender.

The following steps explain one way to spatially enable a database and create spatial columns and data using the Control Center.

Step 1: Spatially enable a database

To enable a database for spatial operations, right-click the database object and click Spatial Extender --> Enable Spatial Extender in the pop-up menu. DB2 creates the Spatial Extender catalog, spatial data types, a spatial index type, and spatial functions in the DB2GSE schema.

Step 2: Create a spatial reference system

To ensure that all the spatial data is processed in a consistent manner, you must create a spatial reference system. A spatial reference system specifies how the data will be scaled, translated, and projected from three dimensions onto two dimensions before it is stored in the database. All spatial data in the database must be associated with a spatial reference system to help ensure spatial data integrity.

To create a spatial reference system, right-click the database object and click Spatial Extender --> Spatial References in the pop-up menu, then click Create in the Spatial References window.

Step 3: Create a spatial layer

Each table and view column that will contain spatial data must be registered as an object called a spatial layer. A spatial layer is associated with:

• A spatial reference system, as described in Step 2.
• Optionally one or more columns to use as input. For example, you could define an address column (which contains street addresses) as input to a spatial data column.
• Optional instructions for automatically invoking a function to convert an input column's data (often called address data) into spatial data when SQL INSERT and UPDATE operations are performed against the input column.

To create a spatial layer, right-click the table or view object and click Spatial Extender --> Spatial Layers in the pop-up menu, then click Create in the Spatial Layers window.

Step 4: Create spatial data

You can add spatial data to your table in several ways:

• As described in Step 3, you can convert existing address data into spatial data when SQL INSERT and UPDATE operations are performed against the input column. This process is known as incremental geocoding.

  To enable incremental geocoding, select the Enable automatic geocoder check box in the Create Spatial Layer window.

• You can automatically convert existing address data to spatial data by using the geocoder in batch mode. After creating the spatial layer for a table (Step 3), you can run the geocoder to convert all address data to spatial data and to populate the spatial layer with spatial data.

  To run the geocoder in batch mode, select the spatial layer in the Spatial Layers window and click Run Geocoder.

  Generally, it is more efficient to run the geocoder in batch mode on a large set of records than it is to trigger the geocoder each time a row is modified with a SQL INSERT or UPDATE operation.

• You can import spatial data into a spatial layer column in a table. As an alternate means of loading spatial data into DB2, spatial data can be represented in either the industry-standard Shape file format, or in ESRI's proprietary SDE transfer file format. To load other ESRI spatial formats, such as ArcInfo Coverage files, first load the data into one or more SDE transfer files, and then import from the SDE transfer files.

  To import spatial data, right-click either the table object or the database object and click Spatial Extender --> Import Spatial Data in the pop-up menu.

• You can use certain spatial functions to derive new spatial data from existing spatial data; for example, to derive data about an area surrounding a building from data about the building's location.

Step 5: Create a spatial index

After the spatial data is created, you can create a spatial index on the spatial data column of the spatial layer. The DB2 Spatial Extender provides a proprietary, high-performance spatial indexing technology known as a "grid index." The grid index supports up to three grid levels, and is designed to provide good performance over a wide range of object sizes, distributions, and query types. Experienced users can design a set of grid sizes that optimize performance for the expected workload.

To create a spatial index, right-click the table object and click Spatial Extender --> Spatial Indexes from the pop-up menu, then click Create in the Spatial Indexes window.

Step 6: Perform spatial analysis

After the spatial data is created and indexed, the database is ready to perform spatial analysis. You can use the ArcExplorer sample application to start visualizing your data spatially. Powerful spatial analysis tools that work with the DB2 Spatial Extender are available from ESRI and from other third-party vendors.