Hi nwingfield,
You don't mention the database you are using and its hard to guess now that everything is lumped together into this single "geodatabase" mish-mash of a forum. My experience is on Oracle so I hope it applies to your situation.
The SDE.ST_GEOMETRY is just a sequence number that starts with 1 and increments each and every time a new coordinate system with new attributes is encountered by a given installation of SDE. This goes far beyond just the projection basics such as NAD83 or WGS84 but also covers the offsets, the grid size and the extents (check out the SDE.ST_SPATIAL_REFERENCES table for an idea of the possible things SDE tracks).
So taking a brand new empty installation of ArcSDE, let's say you run sdeimport and load some NAD83 data as the SDE.ST_GEOMETRY datatype. ArcSDE will store the import's cs details and assign the ST_GEOMETRY SRID to be 1. Then you next come along with some WGS84 data - okay that becomes SRID 2.
You might then come along with more NAD83 data and you'd imagine this layer would be loaded and assigned to be the previously mentioned SRID 1. But that's only if EVERY gritty detail matches. As mentioned, any difference in offsets, scale or the extent will cause ArcSDE to say that the cs is new and assign it as SRID 3. I just looked at one of my ST_SPATIAL_REFERENCES table and I have 56 versions of GCS_North_American_1983. 🙂
Now how do you keep SRID 1 on server A equal to SRID 1 on server B? Other than cloning the entire SDE schema there is no way I know of to do this. As far as I know you need to use ESRI tools (ArcCatalog, sdeimport, sdeexport, shp2sde, etc) to properly move things back and forth amongst servers. They do the work of figuring out what SRID 1 on server A is equal to on server B and possibly as a result creating a new SRID on server B if there is no match.
I've often thought that possibly you could define a universal coordinate system: cs, offset, scale, extents - whatever you chose would need to work for ALL your data universally. Then you could reassign that SRID from 1 to say, 4269. Then you would need to carefully police all your SDE data to fit in those parameters (easier said than done). You would then do this on all your servers. Again you'd need to be vigilant to always make sure data came into your system with the exact parameters preset to match up with your stable 4269. One degree of difference in the extent or using an offset of -180 instead of -200 and you will instead get 4 or 37 or whatever is next in the sequence. Honestly this seems like more trouble than its worth.
Anyone else have any thoughts? As mentioned I've always followed the party line on this one. I have five servers that I commonly move the same data back and forth on. There is nothing in common between any of them in terms of ST_GEOMETRY SRID values. In fact recently we rebuilt the layers to use the ArcCatalog-ish -200 offsets and a smaller XYscale. So that createda whole new sets of SRIDs. I don't think you can do much about it.
Cheers,
Paul
