ArcSDE Oracle Spatial Registration

CurtisRuck · ‎04-08-2010

Alright,

I have a view that contains some numbers, varchar2s, dates, clobs and a SDO_GEOMETRY type with SRID 4326.

It is properly registered with user_sdo_geom_metadata. with a DIM array ('longitude',-180,180)('latitude',-90,90)

Everything at the oracle side works great (loading, etc).

I am now trying to register it with ArcSDE so ArcGIS Desktop clients can use the data.

I can see it in the layer list in ArcGIS Desktop as a table. When i right click -> Properties i get "Failed to edit the selected objects(s). Abstract Data Types not supported[FEED.TESTSHAPE][STATE_ID=0]

When i try to right click -> Register with geodatabase i get "Abstract Data Types not supported[FEED.TESTSHAPE][STATE_ID=0]"

Looking at the SDE.LAYERS and SDE.GDB tables its definitely not registered.

Trying sdetable -o register just changes the error to "Abstract column not found[FEED.TESTSHAPE][STATE_ID=0]"

Trying sdelayer -o register doesn't work:

sdelayer -o register -l TESTSHAPE,SHAPE -e np+ -C ID,USER -u feed

ArcSDE 9.3.1 for Oracle11g Build 2784 Tue Oct 27 10:51:14 2009
Layer Administration Utility
-----------------------------------------------------
Error: Coordref or Layer precision not OK for requested operation (-375).
Error: Cannot Create Layer.

is there ArcSDE 9.3 sdelayer documentation anywhere? The only version i can find is the ArcSDE 9.1 documentation.

VinceAngelo · ‎04-08-2010

You can't use Desktop to do native geometry registration; you must use 'sdelayer -o register'.

You should always specify coordinate reference parameters, either with '-x/-m/-z/-P' or by
using '-R' with the registration ID of a layer in the same coordsys.

Starting with 9.3.1, the -t option is required ('-t sdo_geometry').

The user-set registered rowid column must map to an INTEGER [e.g., NUMBER(38) or NUMBER(6)-NUMBER(10)]

Did you really mean to register the layer as multi-point ('-e np+') or did you mean multi-part
polygon ('-e na+')?

All the documentation since 9.0 is online, though it's sometimes hard to find it; right now I
can see the 10.0 documents, such as:

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//002900000077000000.htm

- V

CurtisRuck · ‎04-08-2010

So i tried adding -P HIGH -g NONE and got a few steps further.

sdelayer -o register -l TESTSHAPE,SHAPE -e np+ -C ID,USER -u feed -P HIGH -g NONE 
 
Enter Database User password: 

ArcSDE 9.3.1  for Oracle11g Build 2784 Tue Oct 27 10:51:14  2009
Layer    Administration Utility
-----------------------------------------------------
Error: Underlying DBMS error (-51).
Error: Cannot Create Layer.
ORA-01720: grant option does not exist for 'TESTSHAPE.MESSAGE_TEXT'

Its a view though. Why is sde attempting to perform grants for me. I just want to register the layer with the sde tables.

VinceAngelo · ‎04-08-2010

Add a '-t sdo_geometry'. Don't use '-g', because it does not apply to SDO_GEOMETRY,
only ST_GEOMETRY.

You really ought to *explicitly* specify the x/y coordinate reference, instead of giving
'sdelayer' the option of choosing any values it wants (try '-x -210,-120,1000000').

- V

CurtisRuck · ‎04-08-2010

You should always specify coordinate reference parameters, either with '-x/-m/-z/-P' or by
using '-R' with the registration ID of a layer in the same coordsys.

I just tried -P and got a few steps futher but what are the differences between -P High an a -R?

Starting with 9.3.1, the -t option is required ('-t sdo_geometry').

Hmm, it would be nice to see 9.3.1 docs stating this. It would of been better if ESRI hadn't redone their web servers and broken all fun the google links.

The user-set registered rowid column must map to an INTEGER [e.g., NUMBER(38) or NUMBER(6)-NUMBER(10)]

Its defined as NUMBER, but it contains very large integers (up to 38 digits).

Did you really mean to register the layer as multi-point ('-e np+') or did you mean multi-part
polygon ('-e na+')?

Yes, my data is multipoint.

VinceAngelo · ‎04-08-2010

There's an entire whitepaper on coordref computation, so I won't go into detail,
but the -x/-m/-z/-P/-G options are one way to define coordref, and -R is the
other (but it assumes you have a coordref you want to match already defined
in the database instance).

You will *not* be able to successfully register a layer unless the registered rowid
column maps to a 4-byte integer with positive, non-zero values [1 - 2^31-1].

If 'sdetable -o describe' doesn't report SE_INT32, registration will not succeed.

- V

CurtisRuck · ‎05-20-2010

Alright i have tried and tried figuring out the -x parameters to give sdelayer for registration. I simply just have a oracle spatial layer with an SRID of 4326. How freaking complicated is it for ESRI to support OGC SRIDs.

So, i have a layer with 26 valid sdo_geometry points in the 4326 srid. In ArcGIS Desktop, they do not show on the map, but if i select them i see the highlighted teal location where they should be. Also, there are no other layers on the map for the points to be hiding under.

Shouldn't this be a fairly static set of -x values?

Currently i am attemping to use -x -180,-90,25000000000000,0.00002 to register the layer. This makes ArcCatalog show the XY domain as
                  270.28...
-180                                 180.28...
                  -90

My XY Resolution is 0.00000000000004
My XY Tolerance is 0.00002

And it shows my XY Coordinate System as:
Angular Unit: Decimal Degree (0.017453292519943282)
Prime Meridian: Greenwich (0.000000000000000000)
Datum: World Geodetic System 1984 [EPSG ID 6326]
Spheroid: WGS 84 [EPSG ID 7030]
    Semimajor Axis: 6378137.000000000000000000
    Semiminor Axis: 6356752.314245179300000000
    Inverse Flattening: 298.257223563000030000

VinceAngelo · ‎05-20-2010

Best practice is to maintain a buffer around your data window, so that search circles
which exceed the boundaries don't produce errors. Therefore you should not use
an origin of {-180,-90}. I prefer {-210,-120} and the ArcGIS standard is {-400,-400}.

Your scalefactor puts shape precision in the 4.5 nanometer range (roughly), allowing
you to map the transistor gates of older CPUs in latitude/longitude. Your X/Y tolerance
is a little more than two meters (~2.2224m), which is a lot closer to my usual
1000000 scalefactor (0.11m) and a good deal larger than the ArcGIS scalefactor
default of 1111948722.222223 (0.1mm).

Once you've settled on a viable set of coordref parameters, you can use the -R option
to specify the SRID in future layer creation.

- V

CurtisRuck · ‎05-20-2010

Ok, so now that i've changed my parameters per your suggestions to roughly -500,-500 -> +500,+500 (still high precision). I adjusted my scalefactor down to < ~.1m . I still don't see the points in ArcMap, but they can be selected from Attribute Table and the highlights show up where they properly should, but the points are essentially invisible.

VinceAngelo · ‎05-20-2010

Does the spatial index on the geometry column exist?

Try letting ArcSDE construct it by "bouncing" the layer:

sdelayer -o load_only_io -l mytab,geometry -u user -p pass
sdelayer -o normal_io    -l mytab,geometry -u user -p pass

- V