User's Guide and Reference

How to create and use a DB2 Spatial Extender GIS

You create a DB2 Spatial Extender GIS by setting up DB2 Spatial Extender and developing GIS projects within the combined environments of DB2 Spatial Extender and its underlying DB2 RDBMS. You use the GIS by implementing these projects; that is, by generating and analyzing the information--both spatial and traditional--that they are designed to provide. The entire effort involves performing several sets of tasks. This section introduces the interfaces with which you can perform these tasks, provides an overview of the tasks, and presents a scenario to illustrate them.

Interfaces to DB2 Spatial Extender and associated functionality

This section surveys the interfaces by means of which you can create a DB2 Spatial Extender GIS (that is, set up resources for it, obtain spatial data, and so on) and use it (that is, generate and analyze information about geographic features).

You can create a DB2 Spatial Extender GIS by:

• Using the DB2 Control Center's DB2 Spatial Extender windows and menu choices. For instructions, see:
  • Setting up resources
  • Defining spatial columns, registering them as layers, and enabling a geocoder to maintain them
  • Populating spatial columns
  • Creating spatial indexes
• Running an application program that calls DB2 Spatial Extender stored procedures. For guidelines on developing such a program, see Writing applications for DB2 Spatial Extender.
• Using both the Control Center and an application program. For example, you can use the Control Center to invoke the default geocoder. If, in addition, you want to use another geocoder, you must first register it to DB2 Spatial Extender by invoking the db2gse.gse_register_gc stored procedure in an application program. (For information about non-default geocoders, see About geocoding. For information about the db2gse.gse_register_gc stored procedure, see db2gse.gse_register_gc.)
• Using the Control Center, an application program, or both, in combination with other interfaces. For example, to create a table to hold data that is to be generated by a spatial function, such as a geocoder, you could use either the Command Line Processor or the Control Center interfaces.

You can use a DB2 Spatial Extender GIS by:

• Rendering information graphically with a geobrowser; for example, ArcExplorer, which is offered by the Environmental Systems Research Institute (ESRI)
• Submitting SQL queries explicitly from the DB2 Control Center or Command Line Processor
• Submitting SQL queries from an application program

Tasks you perform to create and use a DB2 Spatial Extender GIS

This section provides an overview of the tasks through which you create and use a DB2 Spatial Extender GIS. The tasks through which you create the GIS involve setting up DB2 Spatial Extender and developing GIS projects. The tasks through which you use the GIS involve implementing the projects. This overview begins with setting up DB2 Spatial Extender, and then moves on to developing and implementing a GIS project. The section concludes by indicating how the tasks described in the overview can vary in actual practice.

Setting up DB2 Spatial Extender

To set up DB2 Spatial Extender:

1. Plan and make preparations (decide what GIS projects to develop, decide what database to enable for DB2 Spatial Extender, select personnel to administer DB2 Spatial Extender and develop the projects, and so on).
2. Install DB2 Spatial Extender.
3. Put resources in place to support GIS projects; for example:

   Resources supplied by DB2 Spatial Extender

   These include a system catalog, spatial data types, spatial functions (including a default geocoder), and so on. The task of setting up these resources is referred to as enabling the database for spatial operations.

   Geocoders developed by users, vendors, or both.

   The default geocoder translates United States addresses into spatial data. Your organization and others can provide geocoders that translate foreign addresses and other kinds of attribute data into spatial data.

For instructions on installing DB2 Spatial Extender, see Installing DB2 Spatial Extender. For instructions on using the Control Center to put resources in place, see Setting up resources. For guidelines on using an application program for this purpose, see Writing applications for DB2 Spatial Extender. For a scenario that illustrates the overall effort of setting up DB2 Spatial Extender, see A system to integrate spatial and traditional data.

Developing and implementing a GIS project

To develop and implement a GIS project:

1. Plan and make preparations (set goals for the project, decide what tables and data you need, determine what coordinate system or systems to use, and so on).
2. Decide what spatial reference system or systems to use. Coordinate values typically include positive integers, negative numbers, and decimal numbers. DB2 Spatial Extender, however, must store all coordinate values in the form of positive integers. A spatial reference system is a set of parameters that defines how negative and decimal numbers in a specific coordinate system are to be converted into positive integers, so that DB2 Spatial Extender can store them. After you decide what coordinate system to use for a spatial column, you need to specify the spatial reference system by which the necessary conversion can take place for that column. If an existing spatial reference system meets your requirements, you can use it; otherwise, you can create one.
3. Define one or more columns to contain spatial data, register them to DB2 Spatial Extender, and enable a geocoder to maintain them automatically.

   Registering a spatial column involves recording it in the DB2 Spatial Extender catalog. From the time that you register it, it is called a layer, because information generated from it will add a stratum, or layer, to the virtual geographic landscape that your GIS creates for you. After you register it, you can perform spatial operations on it; for example, you can populate it and define a spatial index on it.

4. Populate spatial columns:
   • For a project that requires a geocoder, set parameters for the geocoder. Then, run it so that, in a single operation, it geocodes all available source data and loads the resulting coordinates into a layer.
   • For a project that requires spatial data to be imported, import the data.
5. Facilitate access to spatial columns. Specifically, this involves defining indexes that enable DB2 to access spatial data quickly, and defining views that enable users to retrieve interrelated data efficiently. After you define such a view, you need to register its spatial columns as layers.
6. Generate and analyze spatial information and related business information. This involves querying spatial columns and related attribute columns. In such queries, you can include DB2 Spatial Extender functions that return a wide variety of information; for example, the minimum distance between two geographic features, or coordinates that define an area that surrounds a geographic feature. For information about the function that returns such coordinates, ST_Buffer, see Using other spatial data as source data and ST_Buffer. For examples of queries that use spatial functions, see Retrieving and analyzing spatial information and Spatial functions for SQL queries.

For instructions on using the Control Center to perform the tasks involved in developing a GIS project, see:

• Setting up resources
• Defining spatial columns, registering them as layers, and enabling a geocoder to maintain them
• Populating spatial columns
• Creating spatial indexes

For guidelines on using the Control Center to implement a GIS project, see Retrieving and analyzing spatial information.

For guidelines on using an application program to develop and implement a GIS project, see Writing applications for DB2 Spatial Extender.

For a scenario that illustrates the overall effort, see A project to establish offices and adjust premiums.

How the sets of tasks can vary

The sets of tasks that you perform to create and use a DB2 Spatial Extender GIS can vary in content and sequence, depending on your requirements and on the interfaces that you use. For example, consider the tasks of defining columns to contain spatial data, registering them as layers, and enabling a geocoder to maintain them automatically. With the Control Center, you can perform these tasks together, from a single window. If you are invoking stored procedures from a program, however, you can perform these tasks separately, and you can time them at your discretion.

Scenario: An insurance company updates its GIS

This section presents a scenario to illustrate the sets of tasks that are described in the preceding section.

The Safe Harbor Real Estate Insurance Company's information systems environment includes a DB2 Universal Database system and a separate GIS database management system. To an extent, queries can retrieve combinations of data from the two systems. For example, a DB2 table stores information about revenue, and a GIS table stores the locations of the company's branch offices. Therefore, it is possible to find out the locations of offices that bring in revenues of specified amounts. But data from the two systems cannot be integrated (for example, users cannot join DB2 columns with GIS columns) and DB2 services such as query optimization are unavailable to the GIS. To overcome these disadvantages, Safe Harbor acquires DB2 Spatial Extender and establishes a new GIS development department. The following sections describe how the department sets up DB2 Spatial Extender and carries out its first project.

A system to integrate spatial and traditional data

To set up DB2 Spatial Extender, Safe Harbor's GIS development department proceeds as follows:

1. The department prepares to include DB2 Spatial Extender in its DB2 environment. For example:
   1. The department's management team appoints a spatial administration team to install and implement DB2 Spatial Extender, and a spatial analysis team to generate and analyze spatial information.
   2. Because Safe Harbor's business decisions are driven primarily by customers' requirements, the management team decides to install DB2 Spatial Extender in the database that contains information about its customers. Most of this information is stored in a table called CUSTOMERS.

      As a convenient way to refer to the selected database, the members of the GIS development department call it a GIS database. They are aware, however, that it is not reserved for GIS projects only; non-spatial applications can continue to use it, as before.

2. The spatial administration team installs DB2 Spatial Extender.
3. The spatial administration team sets up resources that GIS projects will require:
   • The team uses the Control Center to supply the resources that enable the GIS database for spatial operations. These resources include the DB2 Spatial Extender catalog, spatial data types, spatial functions, and so on.
   • Because Safe Harbor is starting to extend its business into Canada, the spatial administration team begins soliciting Canadian vendors for geocoders that translate Canadian addresses into spatial data.

A project to establish offices and adjust premiums

To carry out its first GIS project under DB2 Spatial Extender, the GIS development department proceeds as follows:

1. The department prepares to develop the project; for example:
   • The management team sets goals for the project:
     • To determine where to establish new branch offices.
     • To adjust premiums on the basis of customers' proximity to hazardous areas (areas with high rates of traffic accidents, areas with high rates of crime, flood zones, earthquake faults, and so on).
   • The GIS project will be concerned with customers and offices in the United States. Therefore, the spatial administration team decides to:
     • Use coordinate systems that accurately define locations in the parts of the United States in which Safe Harbor does business.
     • Use the default geocoder, because it is designed to geocode United States addresses.
   • The spatial administration team decides what data is needed to meet the project's goals and what tables will contain this data.
2. Using the Control Center, the spatial administration team creates two spatial reference systems. One determines how coordinates that define offices' locations are to be converted to data items that DB2 Spatial Extender can store. The other determines how coordinates that define locations of customers' residences are to be converted to data items that DB2 Spatial Extender can store.
3. Using the Control Center, the spatial administration team defines columns to contain spatial data, registers them as layers, and enables a geocoder to maintain them automatically:
   • The team adds a LOCATION column to the CUSTOMERS table. The table already contains customers' addresses. The default geocoder will translate them into spatial data and load this data into the LOCATION column.
   • The team creates an OFFICES table to contain the data that is now stored in the separate GIS. This data includes the addresses of Safe Harbor's branch offices, spatial data that was derived from these addresses by a geocoder, and spatial data that defines a zone within a five-mile radius around each office. The data generated by the geocoder will go into a LOCATION column. The data that defines the zones will go into a SALES_AREA column.
   • The team registers the two LOCATION columns and the SALES_AREA columns as layers.
   • The team enables the default geocoder to automatically maintain the two LOCATION columns.
4. The spatial administration team populates the CUSTOMER table's LOCATION column, the entire OFFICES table, and a new HAZARD_ZONES table:
   • The team uses the Control Center to populate the CUSTOMER table's LOCATION column:
     1. The team instructs the geocoder to insert spatial data for an address into the LOCATION column only under the following condition: a match between the address and its counterpart in the United States Census Bureau's records must be 100 percent accurate. (A file of addresses that are supplied by the Census Bureau is shipped with DB2 Spatial Extender. Before the geocoder can translate an address in the source data into spatial data, the geocoder must try to match this address with a counterpart in the file. Users specify what percentage of the match must be accurate in order for the spatial data to be placed in a table. This percentage is called a precision.)
     2. The team runs the geocoder in batch mode, so that it can geocode all the addresses in the table in one operation. To the team's dismay, the geocoder rejects about one out of every ten addresses!
     3. The team surmises that the rejects must be new addresses that have no exact matches in the Census Bureau's records. To resolve the problem, the team reduces the precision to 85.
     4. The team runs the geocoder in batch mode again. The rate at which addresses are rejected falls to an acceptable level.
   • Using a utility that is provided by the separate GIS, the team loads the office data into a file. Then the team uses the Control Center to import this data from the file to the new OFFICES table.
   • Using the Control Center, the team creates a HAZARD ZONES table, registers its spatial columns as layers, and imports data to it. The data comes from a file supplied by a map vendor.
5. Using the Control Center, the spatial administration team facilitates access to the new layers:
   • The team creates indexes for them.
   • The team creates a view that joins columns from the CUSTOMERS and HAZARD ZONES tables. The team then registers the views' spatial columns as layers.
6. The spatial analysis team runs queries to obtain information that will help it meet the original objectives: to determine where to establish new branch offices, and to adjust premiums on the basis of customers' proximity to hazard areas.