ArcGIS Server Feature Envelope Repair Utility for Oracle Rounding Bug

This utility is for updating the geometry envelopes in SDE.ST_GEOMETRY feature classes that have rounding errors due to an Oracle bug (Oracle document #6756089).

An Oracle RDBMS bug has been encountered that can cause spatial selections to be wrong for feature classes using SDE.ST_GEOMETRY (ESRI’s Spatial Data Type for Oracle.) This Oracle bug affects all users of ArcSDE 9.2 (including service packs) for Oracle 10g and 11g and using the SDE.ST_GEOMETRY spatial type to store their features. When a feature is inserted into a table, its geometry envelope calculation can be truncated, leading to incorrect results with a spatial selection. Geometry data that has an x, y, z or measure decimal precision greater than five will be truncated. For example, 11.1234567 will be truncated to 11.12345.

To correct this problem, Oracle is releasing a patch. The patch must be downloaded from Oracle Metalink and installed on the Oracle RDBMS. After the patch is installed, new features inserted into new or existing feature classes will have correct envelopes.

Note: If Oracle has not yet released a patch for your platform, please contact Oracle to request the patch. Reference Oracle Document #6756089.

After the Oracle patch is installed, use this utility to recalculate the geometry envelopes for your existing SDE.ST_GEOMETRY feature classes. This utility is a stored procedure that repairs each feature. This procedure reads the geometry from the POINTS attribute of the type, calculates the envelope of that geometry, and stores the calculated values in the envelope attributes of the ST_GEOMETRY column (MINX, MAXY, MINY, and so on).

This procedure updates the geometry column in all rows in the table. As with any update, the indexes on the table will also be updated. To improve performance, you may choose to drop the SPATIAL index on your table before running this procedure.

If the envelope attributes already match the actual envelope of the geometry stored in the POINTS attribute, running this utility on a table will update the envelope attributes with the values they already hold, with no net change to the data.

For versioned feature classes, this utility must be run on both the business table and the “Adds” table of the feature class.

Running this utility
The utility is a stored procedure called st_repair_envelope. To use the utility, log in to Oracle as the SDE user and create the utility as a stored procedure by executing the script st_repair_envelope.pls.

1. Download the zipped script below to a location such as a folder or directory.
2. Open a command prompt (Windows) or terminal window (Unix) and change the current directory to the download location.
3. Start SQL*Plus, connecting to the Oracle server as the user SDE.
4. Execute the script using the following command: SQL> @st_repair_envelope.pls
5. Exit from SQL*Plus.

The result should look something like this.

- - - - - - - - - - - - - - - - - - - - -

C:\downloads\: sqlplus sde/sde
SQL*Plus: Release 10.2.0.3.0 - Production on Wed May 14 13:28:09 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - ProductionWith the Partitioning, OLAP and Data Mining options

SQL> @st_repair_envelope.pls

Procedure created.

No errors.

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - ProductionWith the Partitioning, OLAP and Data Mining options

- - - - - - - - - - - - - - - - - - - - -

After creating stored procedure, execute it for each table that has an SDE.ST_GEOMETRY spatial column. The prerequisites to running this procedure are:

1. The caller must have SELECT and UPDATE privileges on the table to be updated.
2. The geometry column specified in the third parameter must be defined as the SDE.ST_GEOMETRY spatial data type.
3. The caller must have SELECT permissions on the table SDE.ST_GEOMETRY_COLUMNS. In standard ArcSDE installation, SELECT privilege is usually granted to PUBLIC, so this is generally not a problem.

The calling syntax is as follows:

SQL> EXEC SDE.ST_REPAIR_ENVELOPE('ownername','table_name','column_name');

The following files are included in this distribution:

• st_repair_envelope.txt: This file.
• st_repair_envelope.pls: The SQL script to create the utility as a stored procedure.
• exec_repair_one_schema.sql: A sample SQL script showing how to execute st_repair_envelope on all feature classes in a specific schema that have an SDE.ST_GEOMETRY column.

Download ID:1391