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.