Thanks for this post. We have a similar issue with Oracle. For various reasons I won't get in to our data is defined as a custom 3TM WGS84 114 but the coordinates are actually NAD83 3TM 114. This issue is not noticed internally as all our data matches. It is only when we share externally that we see the issue. We need to correct the spatial reference of all our data but retain the current correct coordinates.
A re-project is not valid as it would move the data so the DefineProjection tool looks promising at first. However, in testing I have found that it only partially changes the spatial reference of a layer. It seems to update the layer definition in the GDB_ITEMS table but the SRID in the registration tables and on the features still have the old SRID.
I have found that doing a DefineProjection and then a subsequent copy/paste will correct the SRID on the registration tables as well as on the features. Unfortunately, doing a copy/paste on our 4000+ datasets which include complex data types, versioning, archiving, topology, relationship classes and networks would create a total mess. It turns out I came up with the same method as the original poster here, but in my case my testing was done with Oracle. We also have SQL Server but Oracle hosts our most complex data sets.
The attached document details my testing, processes and the various registration values, but I'll include a bit of info in the text here.
As mentioned, the DefineProjection tool only updates the layer definition in GDB_ITEMS but the SRID remains the same. The process below shows proposed method on how to update the SRID without altering geometry for data in Oracle SDE.
Manual update using SQL
This fixes the registration and the geometry without having to alter archiving, versioning, relations classes etc.
Proposed Process:
1) DefineProjection to define as WKID 3776
2) Delete spatial indexes
3) Update SRID on features to same geometry but change the SRID
UPDATE AB_CAL3TM_CHANGE SET SHAPE = SDE.ST_GEOMFROMWKB(SDE.ST_ASBINARY(SHAPE) , 3776);
4) Update the ST_GEOMETRY_COLUMNS to the correct SRID
update SDE.ST_GEOMETRY_COLUMNS SET SRID = 3776 WHERE TABLE_NAME = 'AB_CAL3TM_CHANGE';
- Update SDE.LAYERS to a SRID that has the same SR as well as M, Z tolerance etc, see section below named Gettting Layer SRID in document.
UPDATE SDE.LAYERS SET SRID = 86 WHERE TABLE_NAME = ' AB_CAL3TM_CHANGE’; --(this is the srid of a valid nad83 layer)
So far I have only tested this process on a few layers but the results have been good. The one question I have is how do I know which SRID to use for the SDE.LAYERS table? In my testing I have been reading the SDE.LAYERS table to find a record with the desired spatial reference and matching xyzm values. If there is no matching record my next course of action would be to create a layer that has my desired properties via ArcPy and then find the LAYERS.SRID value from that.
If this process proves valid I would script it to handle versioning, archiving, fabric etc.
See the attached document for the full process and please let me know your thoughts or suggestions on this process.