User's Guide and Reference

The sample program steps

Table 5 shows the sample program steps, the associated stored procedures, and a description of each step. The C functions for invoking the stored procedures is displayed in the Action column of Table 5 and is enclosed in parentheses. For more information about the stored procedures, see Stored procedures. The sample program is based on scenarios that are introduced in Scenario: An insurance company updates its GIS.

Table 5. DB2 Spatial Extender sample program

Sample program steps	Action	Description
Enable/disable spatial database	Enable the spatial database (gseEnableDB) Disable the spatial database (gseDisableDB) Enable the spatial database (gseEnableDB)	This is the first step needed to use DB2 Spatial Extender. A database that has been enabled for spatial operations has a set of spatial types, a set of spatial functions, a set of spatial predicates, a new index type, and a set of administration tables and views. This step is usually performed when you have enabled spatial capabilities for the wrong database. When you disable a spatial database, you remove a set of spatial types, a set of spatial functions, a set of spatial predicates, a new index type, and a set of administration tables and views. Note: The disable database will fail if there are objects created that depend on the objects created by the enable database procedure. For example, creating a table with a spatial column of the type ST_Point will cause the disable database to fail. This occurs because the table depends on the type ST_Point which is intended to be dropped by the disable database procedure. Same as 1.
Register spatial reference systems	Register the spatial reference system for the LOCATION column of the CUSTOMERS table (gseEnableSref) Register the spatial reference system for the LOCATION column of OFFICES table (gseEnableSref) Unregister the spatial reference system for the LOCATION column of OFFICES table (gseDisableSref) Re-register the spatial reference system for the ZONE columns of the OFFICES table (gseEnableSref)	This step defines a new spatial reference system (SRS) intended to be used to interpret the spatial data of the CUSTOMERS table. A spatial reference system includes geometry data in a form that can be stored in a column of a spatially enabled database. After the SRS is registered to a specific layer, the coordinates applicable to that layer can be stored in the associated CUSTOMERS table column. This step defines a new spatial reference system (SRS) intended to be used to interpret the spatial data of the OFFICES layer. Each table layer must have an SRS defined to it. The OFFICES table layers might require a different associated SRS than the CUSTOMERS table layer. This step is performed if you specify the wrong SRS parameters to the layer or spatial column. When you unregister an SRS for the OFFICES table layer, you remove the definition with its associated parameters. This step defines a new spatial reference system (SRS) intended to be used to interpret the spatial data of the OFFICES layer.
Create the spatial tables	Alter the CUSTOMERS table by adding the LOCATION column (gseSetupTables) Create the OFFICES table (gseSetupTables)	The CUSTOMERS table represents business data that has been stored in the database for several years. The ALTER TABLE statement adds a new column (LOCATION) of type ST_Point. This column will be populated by geocoding the address columns in a subsequent step. The OFFICES table represents, among other data, the sales zone for each office of an insurance company. The entire table will be populated with the attribute data from a non-DB2 database in a subsequent step. This step involves importing attribute data into the OFFICES table from a SHAPE file.
Register the spatial layers	Register the LOCATION column in the CUSTOMERS table as a layer (gseRegisterLayer) Register the ZONE column of the OFFICES table as a layer (gseRegisterLayer)	These steps register the LOCATION and ZONE columns as layers to DB2 Spatial Extender. Before a spatial column can be populated or accessed by the DB2 Spatial Extender utilities (for example, the geocoder), you need to register it as a layer.
Populate the spatial layers	Geocode the addresses data for the LOCATION column of CUSTOMERS table (gseRunGC) Load the OFFICES table using append mode (gseImportShape) Load the HAZARD_ZONE table using create mode (gseImportShape)	This step performs batch geocoding by invoking the geocoder utility. Batch geocoding is usually performed when a significant portion of the table needs to be geocoded or re-geocoded. This step loads the OFFICES table with spatial data existing in the form of a SHAPE file. Because the OFFICES table exists and the OFFICES/ZONE layer is registered, the load utility will append the new records to an existing table. This step loads the HAZARD_ZONE layer with spatial data existing in the form of a SHAPE file. Because the table and layer do not exist, the load utility will create the table and register the layer before the data is loaded.
Enable spatial indexes	Enable the spatial index for the LOCATION column of the CUSTOMERS table (gseEnableIdx) Enable the spatial index for the ZONE column of the OFFICES table (gseEnableIdx) Enable the spatial index for the LOCATION column of the OFFICES table (gseEnableIdx) Enable the spatial index for the BOUNDRY column of the HAZARD_ZONE table (gseEnableIdx)	These steps enable the spatial index for the CUSTOMERS, OFFICES and HAZARD_ZONE table.
Enable automatic geocoding	Enable automatic geocoding for the LOCATION and ADDRESS columns of CUSTOMERS table (gseEnableAutoGC)	This step turns on the automatic invocation of the geocoder. Using automatic geocoding causes the LOCATION and ADDRESS columns of the CUSTOMERS table to be synchronized with each other for subsequent insert and update operations.
Insert/update the CUSTOMERS table	Insert some records with a different street (gseInsDelUpd) Update some records with a new address (gseInsDelUpd)	These steps demonstrate an insert and update on the LOCATION column of the CUSTOMERS table. Once the automatic geocoding is enabled, information from the ADDRESS column is automatically geocoded when it is inserted or updated in the LOCATION column. This process was enabled in the previous step.
Disable the automatic geocoding	Disable the automatic geocoding for the CUSTOMERS layer (gseDisableAutoGC) Disable the spatial index for the CUSTOMERS layer (gseDisableIdxCustomersLayer)	These steps disable the automatic invocation of the geocoder and the spatial index in preparation for the next step (the next step involves the re-geocoding of the entire CUSTOMERS table). If you are loading a large amount of geodata, it is recommend that you disable the spatial index before you load the data and then enable it after the load is complete.
Re-geocode the CUSTOMERS table	Geocode the CUSTOMERS layer again with a lower precision level - 90% instead of 100% (gseRunGC) Re-enable the spatial index for the CUSTOMERS layer (gseEnableIdx) Re-enable the automatic geocoding with a lower precision level - 90% instead of 100% (gseEnableAutoGC)	These steps runs the geocoder in batch mode again, re-enable the automatic geocoding with a new precision level, and re-enable the spatial index and the automatic geocoding. This action is recommended when the spatial administrator notices a high failure rate in the geocoding process. If the precision level is set to 100%, it may fail to geocode an address because it cannot find a matching address in the reference data. By reducing the precision level, the geocoder has a better chance of finding matching data. After the table is re-geocoded in batch mode, both the automatic geocoding and the spatial index are enabled again to facilitate the incremental maintenance of the spatial index and the spatial column for subsequent inserts and updates.
Create a view and register its spatial columns as view layers	Create a view, HIGHRISK_CUSTOMERS, based on the join of the CUSTOMERS table and the HAZARD_ZONE table (gseCreateView) Register the view's spatial columns as view layers (gseRegisterLayer)	These steps create a view and register its spatial columns as view layers.
Perform spatial analysis	Find the average customer distance from each office (ST_Within, ST_Distance) Find the average customer income and premium for each office (ST_Within) Find customers who are not covered by any existing office (ST_Within) Find the number of hazards zones that each office zone overlaps (ST_Overlaps) Find the nearest office from a particular customer location assuming that the office is located in the centriod of the office zone (ST_Distance, ST_Centroid) Find the customers whose location is close to the boundry of a particular hazard zone (ST_Buffer, ST_Overlaps) Find those high risk customers who are covered by a particular office (All steps utilize gseRunSpatialQueries)	These steps perform spatial analysis using the spatial predicates and functions in DB2 SQL language. The DB2 query optimizer exploits the spatial index on the spatial columns to improve the query performance whenever possible.
Export spatial layers into files	Export the highRiskCustomers layer (gseExportShape)	The step shows an example of exporting the results of your query to a SHAPE file. Exporting query results to another file format allows the information to be used by a third party tool (for example, ESRI ArcInfo).