Configuration
Spatial Extender can be configured using either the command line or the DB2 Control Center. To enter the CLP environment, type db2se. This is similar to the database CLP environments. You can enter Spatial Extender commands from this prompt or precede any command with the db2se from a regular prompt.
Each database in the database must be enabled to use Spatial Extender before a spatial column can be created. Enabling a database creates distinct types (UDTs), stored procedures, administration tables, and views needed for Spatial Extender to work. After a database is enabled, it will stay enabled until issuing the disable command. The selected database can be enabled and disabled with the following commands:
db2se ENABLE_DB database-name db2se DISABLE_DB database-name
The database also can be enabled and disabled using the DB2 Control Center. Right-click on the database and select Spatial Extender. A popup is displayed to enable or disable the database. See Figure 19.1 for an example.
Figure 19.1. Enabling and disabling Spatial Extender.
There are three steps to creating a Spatial Extender project. The first step is to select a coordinate system or define your own. The second step is to create spatial references. The final step is to define spatial columns. A coordinate system is needed to project coordinates defined by latitude and longitude onto the earth's surface. A spatial reference system is a set of parameter values that define properties of the coordinate system. Creating spatial columns enforces data constraints defined by the spatial reference system and enables an administration view of the registered columns from the Control Center.
Coordinate Systems
More than 2,000 coordinate systems are shipped with Spatial Extender. Coordinate systems are used to define the relationship between points and their projections on the earth's surface. Because the earth is round, lines of longitude are closer together as you approach the poles. Lines of latitude are shorter around the poles than they are around the equator. If there is relation between the locations and the earth's surface, a coordinate system of “Unspecified” can be selected.
The coordinate system can be defined using the command processor, stored procedures, or the Control Center. From the Control Center, right-click on the database and select Spatial Extender. A popup appears to define the coordinate system. Refer to Figure 19.1 for an example. From the command processor, the coordinate system can be created or dropped with the following commands:
db2se CREATE_CS database-name –coordinatesysName coordinate-system –definitioncoordinate-definition db2se DROP_CS database-name –coordinatesysName coordinate-system
Spatial Reference Systems
The spatial reference system stores properties of a coordinate system. For example, they define the maximum extent of space that is referenced by a given range of coordinates. The spatial reference system can be selected by systems shipped with Spatial Extender, or a new system can be created. The default reference system intended for use with the “Unspecified” coordinate system is called DEFAULT_SRS.
The spatial reference system can be defined using the command processor, stored procedures, or the Control Center. From the Control Center, right-click on the database and select Spatial Extender. A popup appears to define the spatial reference system. Refer to Figure 19.1 for an example. From the command processor, the processor can be created or dropped with the following commands:
db2se CREATE_SRS database-name –srsName reference-system -srsID reference-system-ID
Spatial Columns
Spatial columns are created like other traditional SQL data types inside the CREATE TABLE statement. There are data types for single-unit, multiunit, and mixed-unit features. These data types are defined under the db2gse schema. Table 19.1 lists the single-unit feature data types. Table 19.2 lists the multiunit feature data types. For mixed-unit features, use the ST_Geometry data type. This data type is the base spatial data type from which all other data types are derived. This data type can store all types of features.
| Data Type | Description |
|---|---|
| ST_Point | Used to store a point in space defined with an X and Y coordinate |
| ST_Linestring | Used to represent a space that is occupied by a linear feature such as a road |
| ST_Polygon | Used to represent an area contained by a multi-sided feature such as a forest |
| Data Type | Description |
|---|---|
| ST_MultiPoint | Used to store a group of points in space defined with an X and Y coordinate |
| ST_MultiLineString | Used to represent a feature made up of a group of linear features such as a river that has many tributaries |
| ST_MultiPolygon | Used to represent a feature made up of a group of multisided features |
Spatial columns are registered to enforce data constraints for the selected spatial reference system. Registering a column allows spatial data to be summarized in a view in the Control Center. Spatial data can be registered from the Control Center from the Spatial Reference System window, the CLP, or by a stored procedure. Here is the syntax to register a spatial column from the command processor:
db2se REGISTER_SPATIAL_COLUMN database-name –tableName table-name –columnName
Geocoding
The geocoder is a scalar function used to translate existing data into spatial data. For example, the DB2SE_USA_GEOCODER is used to translate an address into a ST_Point containing the coordinates. You can set up the geocoding operations from the Control Center from the Geocoders window or the CLP. Here is the syntax to set up a geocoder from the command processor:
db2se SETUP_GC database-name –tableName table-name –columnName column-name