topic ArcSDE Oracle Spatial Registration in Data Management Questions

ArcSDE Oracle Spatial Registration

CurtisRuck — Thu, 08 Apr 2010 14:06:01 GMT

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.

ArcSDE Oracle Spatial Registration

VinceAngelo — Thu, 08 Apr 2010 14:26:13 GMT

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

ArcSDE Oracle Spatial Registration

CurtisRuck — Sun, 12 Dec 2021 07:27:05 GMT

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.

ArcSDE Oracle Spatial Registration

VinceAngelo — Thu, 08 Apr 2010 14:40:46 GMT

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

ArcSDE Oracle Spatial Registration

CurtisRuck — Thu, 08 Apr 2010 14:42:02 GMT

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.

ArcSDE Oracle Spatial Registration

VinceAngelo — Thu, 08 Apr 2010 15:35:52 GMT

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

Re: ArcSDE Oracle Spatial Registration

CurtisRuck — Thu, 20 May 2010 20:36:25 GMT

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

Re: ArcSDE Oracle Spatial Registration

VinceAngelo — Thu, 20 May 2010 23:11:34 GMT

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

Re: ArcSDE Oracle Spatial Registration

CurtisRuck — Fri, 21 May 2010 00:20:27 GMT

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.

Re: ArcSDE Oracle Spatial Registration

VinceAngelo — Sun, 12 Dec 2021 07:27:08 GMT

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

Re: ArcSDE Oracle Spatial Registration

CurtisRuck — Fri, 21 May 2010 01:00:36 GMT

The spatial index exists on the underlying table that contains the sdo_geometry column. The view, of course, doesn't have a spatial index, but it is properly registered with Oracle Spatial's sdo_geom_metadata.

Re: ArcSDE Oracle Spatial Registration

VinceAngelo — Fri, 21 May 2010 02:05:40 GMT

What version of Oracle are you using (11.X.Y.Z)?

Is the base table registered with ArcSDE? Can you bounce that?

- V

Re: ArcSDE Oracle Spatial Registration

CurtisRuck — Fri, 21 May 2010 11:14:25 GMT

I am using Oracle 11.1.0.7.3 (current with April PSU) Linux x86_64.

Alongside ArcSDE 9.3.1 sp1 Linux x86_64.

ArcGIS Desktop is 9.3.1 sp1 on standard win xp.

Bouncing the table registration doesn't help.

Re: ArcSDE Oracle Spatial Registration

VinceAngelo — Fri, 21 May 2010 12:00:51 GMT

Can you provide the outputs of 'sdetable -o describe', 'sdelayer -o describe_long',
and 'sdelayer -o stats' for the view?

If you haven't opened a Tech Support incident, you probably should at this point.

- V

Re: ArcSDE Oracle Spatial Registration

CurtisRuck — Sun, 12 Dec 2021 07:27:10 GMT

sdelayer -o describe -l TESTLAYER,SHAPE -u feed
 

ArcSDE 9.3.1  for Oracle11g Build 2784 Tue Oct 27 10:51:14  2009
Layer    Administration Utility
-----------------------------------------------------

----------------------------------------------------------------
Table Owner        : FEED
Table Name         : TESTLAYER
Spatial Column     : SHAPE
Layer id           : 146
Entities           : np+
Layer Type         : In-Line Spatial Type 
I/O Mode           : NORMAL
Autolocking        : Disabled
Precision          : High
User Privileges    : SELECT
Layer Configuration: DEFAULTS

sdelayer -o describe_long -l TESTLAYER,SHAPE -u feed
 

ArcSDE 9.3.1  for Oracle11g Build 2784 Tue Oct 27 10:51:14  2009
Layer    Administration Utility
-----------------------------------------------------
Layer Description ....: <None>
Table Owner ..........: FEED
Table Name ...........: TESTLAYER
Spatial Column .......: SHAPE
Layer Id .............: 146
SRID .................: 24
Minimum Shape Id .....: 1
Offset ...............:
  falsex:       -500.000000
  falsey:       -500.000000
System Units .........: 8000000000000.000000
Z Offset..............:          0.000000
Z Units ..............:          1.000000
Measure Offset .......: <None>
Measure Units ........: <None>
XY Cluster Tolerance .:          0.0000000002 
Spatial Index ........: 
  parameter:    SPIDX_RTREE 
  exist:        Yes 
  array form:   -2,0,0 
Layer Envelope .......:
  minx:      -180.00000,        miny:       -90.00000
  maxx:       180.00000,        maxy:        90.00000
Entities .............: np+
Layer Type ...........: In-Line Spatial Type 
Creation Date ........: Fri May 21 08:14:16 2010
I/O Mode .............: NORMAL
Autolocking ..........: Disabled
Precision.............: High
User Privileges ......: SELECT
Coordinate System ....: GEOGCS["WGS 84",DATUM["World Geodetic System 1984 [EPSG ID 6326]",SPHEROID["WGS 84 [EPSG ID 7030]",6378137.0,298.257223563]],PRIMEM["Greenwich",0.0],UNIT["Decimal Degree",0.01745329251994328]]

Layer Configuration ..: DEFAULTS

sdelayer -o stats -l TESTLAYER,SHAPE -u feed
 

ArcSDE 9.3.1  for Oracle11g Build 2784 Tue Oct 27 10:51:14  2009
Layer    Administration Utility
-----------------------------------------------------
The requested operation is not supported.

sdelayer -o feature_info -l TESTLAYER,SHAPE -u feed -p `decode $FEED`
 

ArcSDE 9.3.1  for Oracle11g Build 2784 Tue Oct 27 10:51:14  2009
Layer    Administration Utility
-----------------------------------------------------
Row Id,FID,Entity Type,Annotation,Cad Data,Number of Points,Number of Parts,Number of Subparts,Self-Touching Rings,Minimum Precision

1,1,P,F,F,1,1,0,F,High
2,2,P,F,F,1,1,0,F,High
3,3,P,F,F,1,1,0,F,High
4,4,P,F,F,1,1,0,F,High
5,5,P,F,F,1,1,0,F,High
6,6,P,F,F,1,1,0,F,High
7,7,P,F,F,1,1,0,F,High
8,8,P,F,F,1,1,0,F,High
9,9,P,F,F,1,1,0,F,High
10,10,P,F,F,1,1,0,F,High
11,11,P,F,F,1,1,0,F,High
12,12,P,F,F,1,1,0,F,High
13,13,P,F,F,1,1,0,F,High
14,14,P,F,F,1,1,0,F,High
15,15,P,F,F,1,1,0,F,High
16,16,P,F,F,1,1,0,F,High
17,17,P,F,F,1,1,0,F,High
18,18,P,F,F,1,1,0,F,High
19,19,P,F,F,1,1,0,F,High
20,20,P,F,F,1,1,0,F,High
21,21,P,F,F,1,1,0,F,High
22,22,P,F,F,1,1,0,F,High
23,23,P,F,F,1,1,0,F,High
24,24,P,F,F,1,1,0,F,High
25,25,P,F,F,1,1,0,F,High
26,26,P,F,F,1,1,0,F,High

Total rows examined:          26
Total invalid shapes:          0

Re: ArcSDE Oracle Spatial Registration

TravisVal — Sun, 12 Dec 2021 07:27:13 GMT

Have you tried to run any SQL with a spatial where clause against the view in sqlplus?

There was an issue in the Oracle security permissions that prevented spatial indexes from being used if the view being used was based on a table owned by a different user. The workaround was to grant Merge Any View to the view owner. I think that should have been fixed at 10.2.0.4 and 11.1.0.6 though...

You can try running something like this with auto trace on. You should see all your rows returned and the domain index used. SDO can not perform any spatial operations if the spatial index is unavailable. If this works, I would start looking at the SDE logfiles and possibly doing an SDE Intercept, to see if there are any issues being reported.

select ID from FEED.TESTLAYER where SDO_Filter(shape, SDO_Geometry(2003, 4326, null, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(-180,-90, 180, 90))) = 'TRUE';

select ObjectID from testview where SDO_Filter(shape, SDO_Geometry(2003, 4326, null, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(-180,-90, 180, 90))) = 'TRUE';

....

------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                        |     1 |  3848 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SDO_8307_2D_POINTS     |     1 |  3848 |     3   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | SDO_8307_2D_POINT_INDX |       |       |            |          |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("MDSYS"."SDO_FILTER"("SHAPE","MDSYS"."SDO_GEOMETRY"(2003,4326,NULL,"SDO_ELEM_INF
              O_ARRAY"(1,1003,3),"SDO_ORDINATE_ARRAY"((-180),(-90),180,90)))='TRUE')

Re: ArcSDE Oracle Spatial Registration

CurtisRuck — Wed, 26 May 2010 12:51:32 GMT

So... the table and spatial index are partitioned and dba_ind_partitions and dba_indexes showed everything as valid. When running a query on it i received a loading/failed/unusable domain index error.

So rebuilding all partitions remedied the issue. ARGH oracle!

Thanks for the help everyone.

Re: ArcSDE Oracle Spatial Registration

GeorgeSilva — Wed, 22 Jun 2011 14:47:59 GMT

Hello guys,

I'm having the same problem!

Curtis, how did you rebuilt your partitions in Oracle? i've tried Travis test, selecting against my view, and i've got

Error starting at line 1 in command:
select OBJECTID from airway_segments where SDO_Filter(shape, SDO_Geometry(2003, 4326, null, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(-180,-90, 180, 90))) = 'TRUE'
Error report:
SQL Error: ORA-13226: interface not supported without a spatial index
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 8
ORA-06512: at "MDSYS.SDO_3GL", line 1193
ORA-06512: at "MDSYS.SDO_3GL", line 1321
13226. 00000 - "interface not supported without a spatial index"
*Cause:    The geometry table does not have a spatial index.
*Action:   Verify that the geometry table referenced in the spatial operator
           has a spatial index on it.

Any tips on how to proceed to do that? My problem is exactly the same as yours. I have a selectable layer in ArcMap, but all features are invisible. Thanks!