Spatial Reference Consistency question (0 or 4326)?

[MartinAmeskamp]

Hi,

we have some problems with the GDB_ITEMS SRID (master and user GDB) when upgrading (Oracle) enterprise geodatabases, e.g. from 10.0 to 10.2.2, so naturally, we're familiar with the spatial reference consistency patch. This doesn't seem to address all problems in this area, however, and in trying to figure out whether the upgraded database is "clean" or not, we came across the following question:

When a database that was created at or before 10.0 (i.e. with SRID 0 for GDB_ITEMS) has been treated with the spatial reference consistency patch and the corresponding upgrades, what state should it be in to be safe for use with ArcGIS 10.2.2 or later:

1. All SRIDs in all GDB_ITEMS (SDE and user schema) MUST use SRID 4326  OR
2. The GDB_ITEMS SRID may either be 0 or 4326, and the (patched) software will keep things safe and consistentent either way?

Thanks very much, Martin

[George_Thompson]

Hi Martin,

The patch that you reference is ONLY for Oracle geodatabases that were upgraded to/created at version 10.0, at some point. There was no known issue with 9.3.x or 10.1 or newer geodatabases that never went to version 10.0.

As I understand it the patch should drop/recreate the spatial index on the GDB_Items table (update to the correct SRID) and prevent any other spatial inconsistency referenced in this KB: 42461 - Unable to insert or update Oracle st_geometry feature classes, encountering ORA-20085 errors​

I would say that as long as you have applied the patch to the clients and upgraded the Oracle geodatabase with the patched client that you will be good to move forward.

Hope this helps with some of the confusion.

-George

Managing Data​ Enterprise GIS​

[MartinAmeskamp]

Hi George,

basically your description matches my expectations. The databases I'm talking about (Oracle all of them) were either created at 9.3.1 and then upgraded to 10.0, or they were created at 10.0. Now, we need to take them to 10.2.2. Doing the upgrade from a client that has the SRC Patch installed results in a situation where ST_GEOMETRY_COLUMNS shows an SRID of 0 for GDB_ITEMS and all the features in GDB_ITEMS have an SRID of 4326. If we perform some schema operation in ArcCatalog that inserts a shape into GDB_ITEMS (e.g. define an extent for a parcel feature class), this shape has SRID 0, i.e. GDB_ITEMS has inconsistent SRIDs. Also, the upgrade loses the spatial index of GDB_ITEMS.

All these effects are described in great detail in the tech support case I mentioned, my question here is whether anybody know for sure whether having SRID 0 on GDB_ITEMS is a "safe" state or not?

Martin

[VinceAngelo]

Please be very careful with your spatial reference IDs.  It's almost never as simple as just changing an ID value somewhere.

In the Esri realm, a "spatial reference" is much more than just a coordinate system.  Every spatial reference has a coordinate system, but it also has the false X/Y origin, and XY scale factor, and Z origin, and Z scale factor, and M origin, and M scale factor, and tolerances for all three (plus a precision flag). 

Changing SRIDs is unsupported because the data itself is encoded with the contents of these parameters, so changing between two SRIDs in metadata will generate incorrect decoding of the coordinate values, causing them to shift and/or scale within the common coordinate system.

Personally, I find the default XY scales chosen by both Desktop and Server to be far too precise for most data encoded in the default GCS_WGS_1984 coordsys.  Most data collected above 1:1m doesn't need sub-millimeter precision, and storing it with an XY scale that preserves such precision increases the storage requirements of linear and areal data (one customer was amazed to see they could double their layer performance just by using a reasonable precision).  I would encourage you to evaluate the benefits of using a 1,000,000 scalefactor (~11cm precision), and abandoning "0" and "4326" for 300006 (or whatever sequence-generated value is rolled up).  This will have the side-effect of freeing you from the vaguarities of "default" SRID changes.

- V

[MartinAmeskamp]

Vince,

thanks for your reply. I almost entirely agree with what you say (except that our customers create large-scale utility and cadastral maps, and sub-millimetre accurracy is essential). But my point isn't the "user" SRIDs, but rather very specifically the SRID that Esri predefines for GDB_ITEMS - that and nothing else. When we upgrade geodatabases from 10.0 to 10.2.2, we tend to come across situations where there are inconsistencies between ST_GEOMETRY_COLUMNS and the SRIDs of the features in (user schema) GDB_ITEMS. We then toggle GDB_ITEMS to load_only_io and back to normal_io using SDE commandline tools (sdelayer) on a machine that has a specific hotfix (QFE302140) installed, and this seems to result in a consistent state with SRID 0.

Question: Is this OK, or should it really be 4326? (And no, I don't intend to fiddle around in SDE metadata tables!)

Martin

[VinceAngelo]

I've always avoided both "0" and "4326" on principle, so I've never had to deal with fixing this problem.  Go with George's answer on this.

FWIW: I find sub-millimeter accuracy to be an even bigger lie than sub-millimeter precision, especially in geodetic coordinates -- One skateboarder brushing concrete could invalidate the entire curbline layer!

- V

[MartinAmeskamp]

OK, I mixed up accuracy and precision here, sorry about that (I'm only a lowly computing person). However, the input data for our databases is from tachymetric measurements (not GPS), and these people get really nervous if the millimeters they fought very hard to nail down get lost in the GIS. Also, trying to fix topological problems with parcel boundaries that include arcs can be a major pain in the sub-millimetre area...

Martin

[George_Thompson]

Martin,

I am not sure about using the sdelayer to "toggle" between load_only and normal_io and any potential issues. That could cause an issue in itself.

You also mention that you have a specific hotfix installed and may need to get a case with support created to investigate the issue in more detail. This would probably include a full backup of the database in question (SDE and other data schemas).