topic Re: How to ensure consistent ST_Geometry SRIDs when importing feature classes? in Data Management Questions

How to ensure consistent ST_Geometry SRIDs when importing feature classes?

NathanielWingfield — Wed, 28 Apr 2010 13:09:03 GMT

When importing feature classes from other sources, how can I make sure the ST_Geometry SRIDs are consistent? I'm having trouble with certain database-level operations because the SRIDs between feature classes do not match, even though the projection and coordinate system are identical.

Re: How to ensure consistent ST_Geometry SRIDs when importing feature classes?

TravisVal — Wed, 05 May 2010 22:22:33 GMT

I would try importing the spatial reference from the same source feature class or feature dataset when you are creating the feature classes or feature datasets. Then load the data to these empty feature classes.

I'm not sure if this is always going to work though, especially if you are using different keywords or datatypes.

Good luck

Re: How to ensure consistent ST_Geometry SRIDs when importing feature classes?

PaulDziemiela — Thu, 06 May 2010 21:37:31 GMT

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

Re: How to ensure consistent ST_Geometry SRIDs when importing feature classes?

NathanielWingfield — Thu, 22 Jul 2010 12:41:33 GMT

I just Googled "st_geometry srid" and discovered these great answers to my own thread! Thanks for the thorough explanation. At least I better understand the problem now...

Re: How to ensure consistent ST_Geometry SRIDs when importing feature classes?

runegullstrom — Fri, 23 Jul 2010 03:55:02 GMT

Funny, I was just about to add an entry with the same question. I've got the same
problem, 37 entries in the spatial_references table for basically 3 different coord systems.
Same problems too with copying and developers asking me which SRID to use.

I can image writing some procedure to get all the fc's down to the 3 coord systems, but what
then? Can I just delete the rows from the spatial_references table if no fc references it?
I can't see any command line tools to do it.

cheers

Re: How to ensure consistent ST_Geometry SRIDs when importing feature classes?

VinceAngelo — Fri, 23 Jul 2010 10:15:37 GMT

The SRID model does not support a delete operation. I can't imagine how deleting
unused entries could hurt (if they are truly unused, of course), but I can't imagine
how leaving them in place would hurt either.

- V

Re: How to ensure consistent ST_Geometry SRIDs when importing feature classes?

runegullstrom — Sat, 24 Jul 2010 03:13:08 GMT

Of course you're right Vince, but the developers are a bunch of hackers.
So they've found out about this table (they need to SRID for st_geometry functions) and
get confused about which one to use (they hard code it!). So having 3 rows there for
our 3 projections which would be consistent over all boxes would be nice,

cheers