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

CarelBodegraven · ‎10-31-2012

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

BenLin · ‎10-31-2012

Hi Carel,

What was your exact "sdelayer -o register" command syntax?

Have you tried using "sdelayer -o alter -E CALC" command to recalculate the layer envelop after you registered the SDO layer with SDE?

Thanks,
Ben L.

CarelBodegraven · ‎11-01-2012

Hi Carel,

What was your exact "sdelayer -o register" command syntax?

Have you tried using "sdelayer -o alter -E CALC" command to recalculate the layer envelop after you registered the SDO layer with SDE?

Thanks,
Ben L.

Hi Ben,

Thanks for your reaction!

I used: sdelayer -o register -i 5151 -u wshd -p password -s isp -l KERINGEN_SVW_ZONERINGEN_LN,geometrie -e nl+ -t SDO_GEOMETRY -k SDO_GEOMETRY -C GZN_ID,USER -P HIGH -x 0,0,1000,0.001 -G 28992

I just tried sdelayer -o alter -E CALC -i 5151 -u wshd -p password -s isp -l KERINGEN_SVW_ZONERINGEN_LN,geometrie

The result was "Successfully Modified the Layer"

Describe_long gives:
Layer Description ....: <None>
Table Owner ..........: WSHD
Table Name ...........: KERINGEN_SVW_ZONERINGEN_LN
Spatial Column .......: GEOMETRIE
Layer Id .............: 3790
SRID .................: 813
Minimum Shape Id .....: 1
Offset ...............:
falsex:          0.000000
falsey:          0.000000
System Units .........:       1000.000000
Z Offset..............:      -1000.000000
Z Units ..............:      10000.000000
Measure Offset .......: <None>
Measure Units ........: <None>
XY Cluster Tolerance .:          0.001
Spatial Index ........:
parameter:    SPIDX_RTREE
exist:        Yes
array form:   -2,0,0
Layer Envelope .......:
minx:     49185.56300,        miny:    407891.90600
maxx:    111731.14200,        maxy:    439662.59400
Entities .............: nl+
Layer Type ...........: In-Line Spatial Type
Creation Date ........: 10/26/12 12:09:08
I/O Mode .............: NORMAL
Autolocking ..........: Enabled
Precision.............: High
User Privileges ......: SELECT
Coordinate System ....: PROJCS["RD_New",GEOGCS["GCS_Amersfoort",DATUM["D_Amersfo
ort",SPHEROID["Bessel_1841",6377397.155,299.1528128]],PRIMEM["Greenwich",0.0],UN
IT["Degree",0.0174532925199433]],PROJECTION["Double_Stereographic"],PARAMETER["F
alse_Easting",155000.0],PARAMETER["False_Northing",463000.0],PARAMETER["Central_
Meridian",5.38763888888889],PARAMETER["Scale_Factor",0.9999079],PARAMETER["Latit
ude_Of_Origin",52.15616055555555],UNIT["Meter",1.0]]

Layer Configuration ..: SDO_GEOMETRY

However: the layer still does not draw in ArcCatalog

VinceAngelo · ‎11-01-2012

Do the geometries contain Z values (elevations)? The coordref has a Z origin and scale,
but the entity flags don't support elevations ("nl+" vs. "nl+3")

CarelBodegraven · ‎11-02-2012

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.

TravisVal · ‎11-02-2012

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 · ‎11-05-2012

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