Once DB2 Spatial Extender is installed, you can create spatially enabled tables that include spatial columns. Geographic features can be inserted into the spatial columns. DB2 Spatial Extender converts spatial data into its storage format from one of the following external formats:

• Well-known text (WKT) representation
• Well-known binary (WKB) representation
• Geography Markup Language (GML) representation
• ESRI shape representation

ArcSDE uses the ESRI shape representation.

Accessing the spatially enabled tables through the ArcSDE server can be done by applications using the existing tools offered by the GIS software or by creating applications using the SDE C API. An experienced SQL programmer can also make calls to DB2 Spatial Extender spatial functions.

After spatially enabling and loading data into your database, you can include Spatial Extender functions in your SQL statements, comparing the values of spatial columns, transforming the values into other spatial data, and describing the properties of the data.

The spatial reference system identifies the coordinate transformation matrix for each geometry. Geometry is the term adopted by the Open GIS Consortium to refer to two-dimensional spatial data. All spatial reference systems known to the database are stored in the DB2GSE.ST_SPATIAL_REFERENCE _SYSTEMS catalog view. Each spatial reference is one record in the DB2GSE.ST_SPATIAL_REFERENCE _SYSTEMS catalog view.

The following is the DB2GSE.ST_SPATIAL_REFERENCE _SYSTEMS catalog view schema:

Field name	Field type	Description
SRS_ID	INTEGER	Contains the unique ID that identifies each SRID in the database NOT NULL
SRS_NAME	VARCHAR(128)	The name of the spatial reference system NOT NULL
X_OFFSET	DOUBLE	The x-value offset or the minimum allowable x-coordinate value NOT NULL
X_SCALE	DOUBLE	Scale factor by which to multiply the figure that results when an offset is subtracted from an x-coordinate NOT NULL
Y_OFFSET	DOUBLE	The y-value offset or the minimum allowable y-coordinate value NOT NULL
Y_SCALE	DOUBLE	Scale factor by which to multiply the figure that results when an offset is subtracted from a y-coordinate NOT NULL
Z_OFFSET	DOUBLE	The z-value offset or the minimum allowable z-coordinate value NOT NULL
Z_SCALE	DOUBLE	Scale factor by which to multiply the figure that results when an offset is subtracted from a z-coordinate NOT NULL
M_OFFSET	DOUBLE	The m-value offset or the minimum allowable m-coordinate value NOT NULL
M_SCALE	DOUBLE	Scale factor by which to multiply the figure that results when an offset is subtracted from a measure NOT NULL
MIN_X	DOUBLE	Minimum possible value for x-coordinates; value derived from the values in the x_offset and x_scale columns NOT NULL
MAX_X	DOUBLE	Maximum possible value for x-coordinates; value derived from the values in the x_offset and x_scale columns NOT NULL
MIN_Y	DOUBLE	Minimum possible value for y-coordinates; value derived from the values in the y_offset and y_scale columns NOT NULL
MAX_Y	DOUBLE	Maximum possible value for y-coordinates; value derived from the values in the y_offset and y_scale columns NOT NULL
MIN_Z	DOUBLE	Minimum possible value for z-coordinates; value derived from the values in the z_offset and z_scale columns NOT NULL
MAX_Z	DOUBLE	Maximum possible value for z-coordinates; value derived from the values in the z_offset and z_scale columns NOT NULL
MIN_M	DOUBLE	Minimum possible value for measures; value derived from the values in the m_offset and m_scale columns NOT NULL
MAX_M	DOUBLE	Maximum possible value for measures; value derived from the values in the m_offset and m_scale columns NOT NULL
COORDSYS_NAME	VARCHAR(128)	The name of the coordinate system on which the spatial reference system is based NOT NULL
COORDSYS_TYPE	VARCHAR(128)	The type of the coordinate system on which the spatial reference system is based NOT NULL
ORGANIZATION	VARCHAR(128)	Name of the organization that defines the coordinate system on which the spatial reference is based; set to NULL if organization is NULL
ORGANIZATION_COORDSYS_ID	INTEGER	Name of the organization that defines the coordinate system on which the spatial reference is based; set to NULL if organization is NULL
DEFINITION	VARCHAR(2048)	Well-known text representation of the defined coordinate system NOT NULL
DESCRIPTION	VARCHAR(256)	Description of the spatial reference system

Internal functions use the parameters of a spatial reference system to translate and scale each floating-point coordinate of the geometry into positive integers prior to storage. Upon retrieval, the coordinates are restored to their external floating-point format.

The floating-point coordinates are converted to integers by subtracting the false x- and false y-values, which translates to the false origin; they scale by multiplying by the x,y units, adding a half unit, and truncating the remainder.

The optional z-coordinates and measures are dealt with similarly, except that they are translated with false z and false m and scaled with z-units and m-units, respectively.

The spatial reference identifier, the primary key, contains a unique number for each spatial reference system.

The spatial reference system is assigned to geometry during its construction. The spatial reference system must exist in the spatial reference table. All geometries in a column must have the same spatial reference system.

Whenever you create a feature class, ArcSDE searches the ST_SPATIAL_REFERENCE_SYSTEMS catalog view in an attempt to locate a matching spatial reference system. If one is found, the SRID is assigned to the feature class; otherwise, ArcSDE adds a new spatial reference system to the ST_SPATIAL_REFERENCE_SYSTEMS view and assigns it to the feature class.

When you import data from one format (such as a coverage or shapefile) to a feature class in a geodatabase in DB2, the coordinates of the data being imported must fit within the extent of the spatial reference system. Each feature found to lie outside the spatial reference system's extent is rejected.

A DB2 spatial table can include one or more spatial columns, although ArcSDE restricts a feature class to a single spatial column. Spatial columns are defined with one of the DB2 Spatial Extender spatial types. A spatial column can only accept data of the type required by the spatial column. For example, an ST_Polygon column rejects integers, characters, and even other types of nonpolygon geometry.

When ArcSDE creates a DB2 table with a spatial column, it also creates an OBJECTID integer column. The OBJECTID column is required by ArcSDE client applications to keep track of selection sets; more specifically it is used in ArcSDE log files.

A record is added to the ST_GEOMETRY_COLUMNS catalog view whenever ArcSDE creates a feature class in a DB2 database. This record is added to the view automatically when a table is created with a column defined with a spatial type.

Tables with spatial columns can be created by other applications. ArcSDE has been designed to use tables containing spatial columns that were created by other applications or using SQL (sometimes referred to as third-party tables) as long as the tables meet certain prerequisites including the following:

• It must be owned by the user registering the table.
• It must have a single ST_Geometry.
• It must have no other columns of a user-defined type.
• It must have an integer, not null, unique column to use as a row ID column.

Automatically registering tables as layers

Whenever an ArcSDE client lists the feature classes stored in the database, ArcSDE can automatically search the DB2 system tables for new tables with spatial columns. When a new table is discovered, it is registered with ArcSDE and made available to applications. Automatic discovery of tables with spatial columns, often referred to as autoregistration, is disabled by default. To enable this feature, use the sdeconfig –o alter command and set DISABLEAUTOREG to FALSE. For example:

sdeconfig –o alter –v DISABLEAUTOREG=FALSE –u sde –p sde 
–D mydb –I 5151

ArcSDE uses the first record in a newly discovered table to establish the ArcSDE geometry type. If the table contains multiple geometry types, you can use the sdelayer command with the –e option to alter the geometry type definition.

ArcSDE searches for a column in the table to use as a row ID column. To qualify, the column must be defined as INTEGER, NOT NULL, and UNIQUE. If such a column is found, the column name is recorded in the ArcSDE TABLE_REGISTRY system table along with the table name. If a row ID column is not found, the table is registered, but operations requiring a row ID are unavailable.

Manually registering tables as layers

If you decide not to use autoregistration, you can manually register tables as feature classes using the ArcSDE administration command sdelayer –o register. Manually registering tables as feature classes gives you more control over how a table is registered. Here is an example of registering a table called SITES containing polygon geometries (–e a) in a spatial column called SHAPE. The table has an integer row ID column called FID that will be used as a unique feature identifier column maintained by ArcSDE (–C fid,SDE).

sdelayer –o register –l sites,shape –e a –C fid,SDE 
–u cyrril –p bahramewe

For tables that contain a large number of records, registration may take less time if you register the row ID column as user maintained.

sdelayer –o register –l sites,shape –e a –C fid,USER 
–u cyrril –p bahramewe

However, if you register the row ID column as user maintained and subsequently register the feature class with the geodatabase, ArcGIS will add an additional feature identifier column, object_ID. ArcGIS will maintain the values of this column. If the table contains a large number of records, adding this additional object_ID column may take some time.

For details on the use of ArcSDE administration commands, consult the command reference provided with ArcSDE. PDF versions of this file can be obtained from the Geodatabase Resource Center.

For a discussion of row ID columns, see Registering tables to be used by ArcGIS Desktop.

Spatial columns contain two-dimensional geographic data, and applications querying those columns require an index strategy that will quickly identify all geometries that lie within a given extent. For this reason, DB2 Spatial Extender provides support for the creation of a three-level grid spatial index.

DB2 Spatial Extender provides a utility, Index Advisor, that will analyze the spatial column data and suggest appropriate grid sizes; for example:

Gseidx connect to sde user sde using arcsde                                                                      
get geometry information                                                                                                    
for column sde.valve(shape)                                                                                           
advise grid sizes                                                                                                                          analyze 10 percent

Refer to the DB2 Spatial Extender User's Guide and Reference for more details on this utility.

The primary IBM document you should be aware of is the IBM DB2 Spatial Extender and Geodetic Extender User's Guide and Reference, which can be found at ftp://ftp.software.ibm.com/software/data/spatial/db2sb.pdf. (Copy the URL and paste it in a browser.) The main IBM spatial Web site also has numerous links related to using the spatial type at http://www-306.ibm.com/software/data/spatial.