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.

CurtisRuck · ‎05-20-2010

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.

VinceAngelo · ‎05-20-2010

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

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

- V

CurtisRuck · ‎05-21-2010

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.

VinceAngelo · ‎05-21-2010

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

CurtisRuck · ‎05-24-2010

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

TravisVal · ‎05-25-2010

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')

CurtisRuck · ‎05-26-2010

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.

GeorgeSilva · ‎06-22-2011

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!