How To: Update an st_geometry attribute's spatial reference metadata after using st_transform in Oracle
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.
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.
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:
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.
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.