Db2 Spatial Extender sample program

The Db2 Spatial Extender sample program, runGseDemo, has two purposes. You can use the sample program to become familiar with application programming for Db2 Spatial Extender, and you can use the program to verify the Db2 Spatial Extender installation.

The location of the runGseDemo program varies and depends on the operating system where Db2 Spatial Extender is installed.

On UNIX, you can locate the runGseDemo program in the following path:
```
$HOME/sqllib/samples/extenders/spatial
```
where $HOME is the instance owner's home directory.
On Windows®, you can locate the runGseDemo program in the following path:
```
c:\Program Files\IBM\sqllib\samples\extenders\spatial
```
where c:\Program Files\IBM\sqllib is the directory in which you installed Db2 Spatial Extender.

The Db2 Spatial Extender runGseDemo sample program makes application programming easier. Using this sample program, you can enable a database for spatial operations and perform spatial analysis on data in that database. This database will contain tables with fictitious information about customers and flood zones. From this information you can experiment with Spatial Extender and determine which customers are at risk of suffering damage from a flood.

With the sample program, you can:

See the steps typically required to create and maintain a spatially-enabled database.
Understand how to call spatial stored procedures from an application program.
Cut and paste sample code into your own applications.

Use the following sample program to code tasks for Db2 Spatial Extender. For example, suppose that you write an application that uses the database interface to call Db2 Spatial Extender stored procedures. From the sample program, you can copy code to customize your application. If you are unfamiliar with the programming steps for Db2 Spatial Extender, you can run the sample program, which shows each step in detail. For instructions on running the sample program, see Related tasks at the end of this topic.

The following table describes each step in the sample program. In each step you will perform an action and, in many cases, reverse or undo that action. For example, in the first step you will enable the spatial database and then disable the spatial database. In this way, you will become familiar with many of the Spatial Extender stored procedures.

Table 1. Db2 Spatial Extender sample program steps
Steps	Action and description
Enable or disable the spatial database	Enable the spatial database 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, new index types, and a set of spatial catalog tables and views. Disable the spatial database This step is usually performed when you have enabled spatial capabilities for the wrong database, or you no longer need to perform spatial operations in this database. When you disable a spatial database, you remove the set of spatial types, the set of spatial functions, the set of spatial predicates, new index types, and the set of spatial catalog tables and views associated with that database. Enable the spatial database Same as the previous action.
Create or drop a coordinate system	Create a coordinate system named NORTH_AMERICAN This step creates a new coordinate system in the database. Drop the coordinate system named NORTH_AMERICAN This step drops the coordinate system NORTH_AMERICAN from the database. Create a coordinate system named KY_STATE_PLANE This step creates a new coordinate system, KY_STATE_PLANE, which will be used by the spatial reference system created in the next step.
Create or drop a spatial reference system	Create a spatial reference system named SRSDEMO1 This step defines a new spatial reference system (SRS) that is used to interpret the coordinates. The SRS 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 spatial column, the coordinates that are applicable to that spatial column can be stored in the associated column of the CUSTOMERS table. Drop the SRS named SRSDEMO1 This step is performed if you no longer need the SRS in the database. When you drop an SRS, you remove the SRS definition from the database. Create the SRS named KY_STATE_SRS
Create and populate the spatial tables	Create the CUSTOMERS table Populate the CUSTOMERS table The CUSTOMERS table represents business data that has been stored in the database for several years. Alter the CUSTOMERS table by adding the LOCATION column 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. Create the OFFICES table 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 subsequent step involves importing attribute data into the OFFICES table from a shape file.
Populate the columns	Geocode the addresses data for the LOCATION column of the CUSTOMERS table with the geocoder named KY_STATE_GC This step performs batch spatial 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. Load the previously-created OFFICES table from the shape file using spatial reference system KY_STATE_SRS This step loads the OFFICES table with existing spatial data in the form of a shape file. Because the OFFICES table exists, the LOAD utility will append the new records to an existing table. Create and load the FLOODZONES table from the shape file using spatial reference system KY_STATE_SRS This step loads the FLOODZONES table with existing data in the form of a shape file. Because the table does not exist, the LOAD utility will create the table before the data is loaded. Create and load the REGIONS table from the shape file using spatial reference system KY_STATE_SRS
Register or unregister the geocoder	Register the geocoder named SAMPLEGC Unregister the geocoder named SAMPLEGC Register the geocoder KY_STATE_GC These steps register and unregister the geocoder named SAMPLEGC and then create a new geocoder, KY_STATE_GC, to use in the sample program.
Create spatial indexes	Create the spatial grid index for the LOCATION column of the CUSTOMERS table Drop the spatial grid index for the LOCATION column of the CUSTOMERS table Create the spatial grid index for the LOCATION column of the CUSTOMERS table Create the spatial grid index for the LOCATION column of the OFFICES table Create the spatial grid index for the LOCATION column of the FLOODZONES table Create the spatial grid index for the LOCATION column of the REGIONS table These steps create the spatial grid index for the CUSTOMERS, OFFICES, FLOODZONES, and REGIONS tables.
Enable automatic geocoding	Set up geocoding for the LOCATION column of the CUSTOMERS table with geocoder KY_STATE_GC This step associates the LOCATION column of the CUSTOMERS table with geocoder KY_STATE_GC and sets up the corresponding values for geocoding parameters. Enable automatic geocoding for the LOCATION column of the CUSTOMERS table This step turns on the automatic invocation of the geocoder. Using automatic geocoding causes the LOCATION, LATITUDE, and LONGITUDE columns of the CUSTOMERS table to be synchronized with each other for subsequent insert and update operations.
Perform insert, update, and delete operations on the CUSTOMERS table	These steps demonstrate insert, update, and delete operations on the LATITUDE, LONGITUDE, STREET, CITY, STATE, and ZIP columns of the CUSTOMERS table. After the automatic geocoding is enabled, data that is inserted or updated in these columns is automatically geocoded into the LOCATION column. This process was enabled in the previous step. Insert some records with a different street Update some records with a new address Delete all records from the table
Disable automatic geocoding	These steps disable the automatic invocation of the geocoder and the spatial index in preparation for the next step. The next step involves re-geocoding the entire CUSTOMERS table. Disable automatic geocoding for the LOCATION column in the CUSTOMERS table Remove the geocoding setup for the LOCATION column of the CUSTOMERS table Drop the spatial index for the LOCATION column of the CUSTOMERS table Recommendation: If you are loading a large amount of geodata, drop the spatial index before you load the data, and then re-create it after the data is loaded.
Create a view and register the spatial column in the view	These steps create a view and register its spatial column. Create a view called HIGHRISKCUSTOMERS based on the join of the CUSTOMERS table and the FLOODZONES table Register the view's spatial column
Perform spatial analysis	These steps perform spatial analysis using the spatial predicates and functions in Db2 SQL. The Db2 query optimizer exploits the spatial index on the spatial columns to improve the query performance whenever possible. Find the number of customers served by each region (ST_Within) For offices and customers with the same region, find the number of customers that are within a specific distance of each office (ST_Within, ST_Distance) For each region, find the average income and premium of each customer (ST_Within) Find the number of flood zones that each office zone overlaps (ST_Overlaps) Find the nearest office from a specific customer location, assuming that the office is located in the centroid of the office zone (ST_Distance) Find the customers whose location is close to the boundary of a specific flood zone (ST_Buffer, ST_Intersects) Find those high-risk customers within a specified distance from a specific office (ST_Within) All of these steps use the gseRunSpatialQueries internal function.
Export spatial data into shape files	This step shows an example of exporting the HIGHRISKCUSTOMERS view to shape files. Exporting data from a database format to another file format enables the information to be used by other tools (such as ArcExplorer for Db2). Export the HIGHRISKCUSTOMERS view to shape files