sdelayer based on SDO_GEOMETRY view not shown in ArcGIS: spatial index does not exist

Question

We need to use the outlines of a multipolygon layer SDO_GEOMETRY in an Oracle database as a multiline layer in SDE. We would like to maintain a dynamic relationship with the original polygons-layer.

Therefor in Oracle we made a database view, changing the the geometrytype to line using "sdo_util.polygontoline(shape)", which resulted in correct values in the Shape-column: MDSYS.SDO_GEOMETRY(2006... and MDSYS.SDO_GEOMETRY(2002...

After that we applied "insert into user_sdo_geom_metadata values...." to the view

And we registered the view as a layer in SDE: sdelayer -o register...

In ArcCatalog the table is now correctly presented in ArcCatalog as line-layer. The lines, however, are not visible in ArcMap. Just when you select a feature in the feature table it does show exactly like a selected feature in any other (valid) layer.

When using a tool on the layer, I get the error: Spatial index does not exist [ORA-13226.......

We tried several things like sdelayer -o normal_io

Does anyone have a suggestion to get the layer working properly (other then creating a materialized view)

Thanks, 
Carel

CarelBodegraven · Answer

Thanks a lot Travis! this does the trick: a dynamic line-layer view on a sdo_geometry polygon-layer-table!

TravisVal · Answer

To get this to work, I think you have to create a function-based index on the output of the SDO_UTIL.POLYGONTOLINE function.

What tripped me up was that you have to fully qualify the function name when you inset the row into the sdo_geom_metadata view.  If you don't, you can't create the index.

good luck
Travis
create view view_outline as select objectid, sdo_util.polygontoline(shape) shape from data_table;

View created.

select count(*) from view_outline where sdo_filter(shape, 
 SDO_GEOMETRY(2003, NULL, null,  SDO_ELEM_INFO_ARRAY(1,1003,2),  SDO_ORDINATE_ARRAY (1, 1, 30, 30))) = 'TRUE';

ERROR at line 1:
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 1248
ORA-06512: at "MDSYS.SDO_3GL", line 1393

insert into user_sdo_geom_metadata values ('data_table', 'MDSYS.SDO_UTIL.POLYGONTOLINE(SHAPE)', SDO_DIM_ARRAY(SDO_DIM_ELEMENT('Lat', -180, 180, 0.05), SDO_DIM_ELEMENT('Long', -90, 90, 0.05)), NULL);

1 row created.

create index base_table_fidx on base_table(sdo_util.polygontoline(shape)) indextype is mdsys.spatial_index;

Index created.

select count(*) from view_outline where sdo_filter(shape, 
 SDO_GEOMETRY(2003, NULL, null,  SDO_ELEM_INFO_ARRAY(1,1003,2),  SDO_ORDINATE_ARRAY (1, 1, 30, 30))) = 'TRUE';

COUNT(*)
----------
         9

CarelBodegraven · Answer

The geometry does not contain Z-values:
MDSYS.SDO_GEOMETRY(2002,28992,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(51455.605,426561.375,51447.652,426558.813,51427.699,426553.625,51413.66,426551.031,51405.637,426548.5,51396.461,426548 etc.

However, neither does the original polygon layer (gw_gzn) from which the view was derived, and that layer was also registered with: 
Z Offset..............:      -1000.000000
Z Units ..............:      10000.000000

I think I forgot to mention the -z -1000,10000 in my earlier statement, sorry about that.