English

Bug: Incorrect spatial index entries are created because of rounding conditions when generating gx, gy cell values in Oracle with a st_spatial_index

Description

Incorrect spatial index entries are created because of rounding conditions when generating gx, gy cell values.

If using ArcGIS 9.2 Service Pack 5 with a spatial index created prior to service pack 5 under certain conditions, geometries existing near grid cell boundaries are not returned in the queries result set.

The problem does not occur in all cases.

Symptoms of the problem can include:

1. When the spatial index is used as the access path for spatial queries (panning and zooming in ArcMap or identify), features can fail to display at one map extent but draw at other map extents.

2. ArcGIS selections result in a selection set slightly smaller than the selection envelope used to perform the selection.

Cause

Additional st_spatial_index domain index entries are being generated because of rounding conditions when converting a variable type of NUMBER to an Oracle type INTEGER.

In sde.spx_util.compute_feat_grid_envp, a type of NUMBER is passed as an argument for generating a feature envelope's gx, gy cell values. When calculated, the outbound variable g_minx, g_miny, etc. is cast to an Oracle INTEGER. By default, Oracle rounds the input value to the closest INTEGER value, which in turn can generate a larger or smaller spatial index envelope than the actual feature. Under the conditions explained above, queries that use the spatial index as the access path might not return an accurate result set.

With ArcSDE 9.2 Service Pack 5 (SP5), a change was made to sde.spx_util.compute_feat_grid_envp to correct the rounding condition. The change impacts spatial indexes built prior to ArcGIS 9.2 Service Pack 5. It affects queries using spatial indexes created prior to SP5 and when inserting new geometries that are indexed.

This issue has no impact on SDEBINARY layers.

Workaround

To correct the problem, install ArcSDE 9.2 Service Pack 5 and rebuild all spatial indexes using one of the following two methods.

· Use ArcCatalog to rebuild the spatial index.

· Use SQL to rebuild the indexes.

The following example demonstrates using an anonymous PL/SQL block in SQL*Plus to rebuild all st_geometry spatial_indexes in the connected user's schema.

Code:
DECLARE

CURSOR st_idx IS
SELECT index_name
FROM user_indexes
WHERE ityp_name = 'ST_SPATIAL_INDEX';

BEGIN

FOR rebuild_idx IN st_idx LOOP
EXECUTE IMMEDIATE 'ALTER INDEX '||rebuild_idx.index_name||' REBUILD';
END LOOP;

END;
/


Or rebuild an individual index by executing the following command.

Code:
SQL> ALTER INDEX <indexname> REBUILD;

Note:
Only the owner of the index or someone who has been granted the Oracle system privilege ALTER ANY INDEX may rebuild an index.