ArcGIS Server Banner

The DB2 Spatial Extender geometry type

The DB2 Spatial Extender geometry type

Release 9.3 E-mail This TopicPrintable VersionGive Us feedback
Note:This topic was updated for 9.3.1.

ArcSDE for DB2 stores spatial data in DB2 Spatial Extender data types. Therefore, before spatial data can be stored in a DB2 database, Spatial Extender must be installed, and the database must be spatially enabled.

DB2 Spatial Extender embeds a GIS into your DB2 database. The DB2 Spatial Extender module implements the Open Geospatial Consortium, Inc. (OGC), Simple Features for SQL with Types and Functions specification, with columns capable of storing spatial data such as the location of a landmark, a street, or a parcel of land. For more information about DB2 Spatial Extender, see the IBM DB2 Spatial Extender and Geodetic Extender User's Guide and Reference. For information on installing the DB2 Spatial Extender, see chapter 4, "Getting started with DB2 Spatial Extender," of the same book.

You can use these links to navigate to each section of this topic:

How the DB2 Spatial Extender works

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:

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.

Adding records to the DB2GSE.ST_SPATIAL_REFERENCE_SYSTEMS catalog view

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.

Creating feature classes in a DB2 database

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.

How to use existing DB2 tables with ArcSDE

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:

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.

Creating a spatial index

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.

Where to find IBM documentation

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.

See Also

  • The ST_Geometry storage type