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

• Well-known text (WKT) representation
• Well-known binary (WKB) representation
• ESRI shape representation

ArcSDE geodatabases in Informix databases use the ESRI shape representation.

Accessing the spatially enabled tables through the ArcSDE server allows you to write applications using the existing tools offered by the GIS software or create applications using the SDE C API. An experienced ODBC programmer can also make calls to Informix Spatial DataBlade spatial functions. The majority of this document is devoted to discussing and applying these spatial functions.

After integrating spatial data into your database, you can include Spatial DataBlade 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 Geospatial Consortium to refer to two-dimensional spatial data. All spatial reference systems known to the database are stored in the spatial_references table. The spatial_references table stores a record for each spatial reference in the database.

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, then scales by multiplying by the x,y units, adds a half unit, and truncates 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.

SRID, the spatial_references primary key, contains a unique number for each spatial reference system.

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

Whenever you create a feature class, ArcSDE searches the spatial_references table 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 spatial_references table and assigns it to the feature class.

When you import data to a feature class from another data source, such as a coverage or shapefile, the coordinates of the data must fit within the extent of the spatial reference system. Each feature found to lie outside the spatial reference system's extent is rejected.

An Informix 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 Informix Spatial DataBlade's UDTs. 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 an Informix table with a spatial column, it also creates an se_row_id integer column. The se_row_id column is required by ArcSDE client applications to keep track of selection sets; more specifically, it is used in ArcSDE log files.

ArcSDE adds a record to the geometry_columns table whenever it creates a feature class in an Informix database. Applications using Informix Spatial DataBlade are responsible for inserting a record into the geometry_columns table each time they add a spatial column to the database.

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.

Manually registering tables as layers

You can use the ArcSDE administration command sdelayer –o register to manually register a table as a layer. Manually registering tables as layers gives you more control over how a table is registered. Here is an example of registering a table called TBL containing point geometries (–e p) in a spatial column called SHAPE. The table has an integer column called PID that will be used as a unique feature identifier column maintained by ArcSDE (–C pid,SDE).

sdelayer –o register –l tbl,shape –e p –C pid,SDE –u <user> –p <pw>

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 tbl,shape –e p –C pid,USER –u <user> –p <pw>

However, if you register the feature identifier 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.

Autoregistering tables as layers

Autoregistration is controlled by the system configuration parameter DISABLEAUTOREG, which is set to TRUE by default. To turn on autoregistration, use the following administration command:

sdeconfig –o alter –v DISABLEAUTOREG=FALSE –u sde –p <sde_password>

Once you enable autoregistration, whenever an ArcSDE client lists the feature classes stored in the database, ArcSDE automatically searches the Informix system tables for new tables with spatial columns. When a new table is discovered, it is registered with ArcSDE and made available to applications.

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 administration command with the –e option to alter the geometry type definition.

NOTE: For information on using ArcSDE commands, consult the command reference provided with ArcSDE. This file can be obtained in PDF format from the Geodatabase Resource Center.

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 constraints. If such a column is found, the name of the column is recorded in the ArcSDE table_registry system table along with the table name. If a qualifying row ID column is not found, the table is registered, but operations requiring a row ID are unavailable.

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, Informix Spatial DataBlade provides support for building a spatial index called an R-tree spatial index.

The R-tree spatial index differs from the traditional hierarchical btree index provided by Informix Dynamic Server software.

The btree index cannot be applied to a spatial column because the two-dimensional characteristic of the spatial column requires an R-tree index. For the same reason, you can't apply R-tree indexes to a nonspatial column or a composite column.

The R-tree index's "create index" syntax includes the additional "using rtree" clause to create an R-tree index rather than a btree index. The full syntax is as follows:

create index <index> on <table> (<spatial column> ST_Geometry_Ops) using rtree (<parameters>) <index options>;

The ST_Geometry_Ops is the Informix Spatial DataBlade operator class. ST_Geometry_Ops manages the R-tree index.

ArcSDE creates a spatial index when a feature class is first created and when it is switched from load_only_io mode to normal_io mode. The spatial index is created with default parameter bottom_up_build = 'yes' and 'no' index options.

You do not ever need to tune the spatial index for performance since this is all handled through the R-tree index. Therefore, you will never have to experiment with the spatial index by trying different cell sizes and different grid-level configurations. ArcSDE for Informix does not require specifying a spatial index or defining spatial grid sizes. This means you don't need to specify the –g Spatial Index flag if you use the ArcSDE administration commands for data management, such as shp2sde. Setting the –g flag will not hurt anything, but the –g flag is ignored for geodatabases stored in Informix and, therefore, will not be used.

For details on ArcSDE administration commands, see the ArcSDE Administration Command Reference installed with ArcSDE.

The Informix optimizer will not use the R-tree index unless the statistics on the table are up-to-date. If the R-tree index is created after the data has been loaded, the statistics are up-to-date and the optimizer will use the index. However, if the index is created and data is loaded afterwards, the optimizer will not use the R-tree index because the statistics will be out-of-date. To update the statistics, use the update statistics Informix SQL statement.

update statistics for table <table_name>

The primary IBM document you should be aware of is the IBM Informix Spatial DataBlade Module User's Guide, which can be found at http://publib.boulder.ibm.com/epubs/pdf/9119.pdf. 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.