English

How To: Update an st_geometry attribute's spatial reference metadata after using st_transform in Oracle

Summary

When converting an st_geometry attribute using the sde.st_transform operator from one spatial reference to another, the st_geometry_columns table must be updated to reflect the new spatial reference.

Instructions provided describe how to transform the contours table's st_geometry attribute to a new spatial reference.

Code:
SQL> UPDATE contours SET shape = sde.st_transform(shape,3);

83 rows updated.

Once complete, the metadata for the table in the ArcSDE st_geometry_columns table is no longer correct and can lead to errors when working with the table; for example, when creating a spatial index. This value must then be updated to reflect the new spatial reference.

Procedure

After performing the transformation, it is necessary to update the ArcSDE st_geometry_columns table to reflect the new spatial reference.

Updating the st_geometry_columns table requires the SDE user to connect to Oracle using SQL*Plus and directly update the table. Using the example of transforming the contours table, the SDE user would execute the following update statement:

Code:
SQL> UPDATE st_geometry_columns SET srid = 3 WHERE table_name = 'CONTOURS';

1 row updated.

The value for SRID should match the value specified when using the st_transform operator.

Note:
Tables with st_geometry attributes registered with the geodatabase should never be transformed to a new spatial reference. These operations should be performed through ArcGIS to ensure all metadata about the feature class remains consistent.