sdelayer -o register on Oracle Spatial View and coordinate systems

Question

So.... after many attemps i have tried making sdelayer -o register give a proper coordinate system for my Oracle Spatial views. The SDO_GEOMETRY data in my Oracle Spatial is EPSG:4326 coordinate system, this should equate to ArcCatalog's GCS_WGS_1984 coordinate system in Geographic Coordinate Systems/World/WGS 1984.prj. I have attempted everything possible but the numbers never match up to an ArcCatalog created layer. I would just change coordinate system through ArcCatalog but my requirements state I need to 100% script the registration of these views on our ArcSDE Linux server. This is my most recent iteration. The .prj file is copy+paste from the ArcGIS Desktop machine but after registering it I get a slightly different coordinate system which ArcMap complains is incompatible with anything GCS_WGS_1984. sdelayer -o register -l points,shape -e np+ -t SDO_GEOMETRY -u -p -C OBJECTID,USER -G file=wgs1984.prj -x -400,-400,1000000000,0.000000008983153 -E -180,-90,180,90

MelitaKennedy · Answer

Argh....

Its things like this that make me despise working with ArcSDE and ESRI products.  How is there two different WGS 84s?  SRID 4326 (and Oracle's coordsys) are geospatial standard WGS 84 definitions.

EPSG:4326 .prj definition:
GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]]

ArcGIS WGS 84 .prj definition:
GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]]

These are identical definitions.  How does ArcSDE get confused?  How does Oracle Spatial screw up 4326?

Here's Oracle's 4326 from 11g:

GEOGCS [ "WGS 84", DATUM ["World Geodetic System 1984 (EPSG ID 6326)", SPHEROID ["WGS 84 (EPSG ID 7030)", 6378137, 298.257223563]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994328]]

Here's ours from ArcGIS 10:

GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984", 6378137.0, 298.257223563]],PRIMEM["Greenwich", 0.0],UNIT["Degree", 0.0174532925199433],AUTHORITY["EPSG",4326]]

We've started including an AUTHORITY tag for the top level object. This would be one of the easier entries to try to "make equal", but it's complicated in code to do it reliably without just taking the SRIDs at face value. The values are all 'equal', but does that really mean that the WKT versions (if you didn't have the SRIDs) represent the same coordinate reference system?

Melita

CurtisRuck · Answer

Argh....

Its things like this that make me despise working with ArcSDE and ESRI products.  How is there two different WGS 84s?  SRID 4326 (and Oracle's coordsys) are geospatial standard WGS 84 definitions.

EPSG:4326 .prj definition:
GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]]

ArcGIS WGS 84 .prj definition:
GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]]

These are identical definitions.  How does ArcSDE get confused?  How does Oracle Spatial screw up 4326?

VinceAngelo · Answer

You can't use Catalog to change an ArcSDE coordsys (can't use an ArcSDE command-line or
API function to do it either; once set, it's immutable).  The Oracle WGS84 string is different 
than the projection engine WGS84 string, and ArcSDE overrides the PE string when it registers
an Orace layer with an SRID.
 
Your only chance to have ArcSDE use the PE WGS84 is to initially define the Oracle table
via SQL without an SRID, register the layer with ArcSDE (with the -G or -R flag), then
alter the USER_SDO_GEOM_METADATA to include the SRID.  If this doesn't work, then
you can go the other way, and define all your ArcGIS layers to use the -R of your 
SDO_GEOMETRY layer.
 
- V