1

I have an sdo_geometry table that I created using ArcGIS Desktop (by converting an esri.st_geometry table to sdo_geometry). I'm using Oracle Spatial's sdo_geometry datatype because it has linear referencing functions (unlike esri.st_geometry).

I'm able to create a simple view on the table, and use it in ArcMap:

--Query #1
SELECT
    OBJECTID
    ,SHAPE
FROM
    USER1.SDO_ROAD

However, when I create a view that constructs new geometry, I'm unable to use it in ArcMap:

--Query #2
SELECT
    OBJECTID
     --simplified for Stack Exchange:
    ,SDO_LRS.CLIP_GEOM_SEGMENT(SHAPE, 0, 10) AS SHAPE_FIRST_10_METRES  
FROM
    USER1.SDO_ROAD

I get this error when I open the attribute table:

Error reading OID from table. Reading rows has been stopped. Check that the datasource is valid.
Underlying DBMS 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 1278]
THE operation is not supported by this implementation.

I'm guessing that the main problem is: interface not supported without a spatial index.

To be clear, I am able to create the view successfully, and even add it to ArcMap. But the lines do not draw, and I get the above error when I open the attribute table.

Why does ArcMap and/or Oracle know how to handle Query #1, but not Query #2?

Why can't Query #2 use the spatial index from the underlying table, just like Query #1 does?

  • 2
    That's an Oracle error. You might get an ArcGIS error if the same ObjectID value is returned by multiple clipped segments (if SDO_LRD.CLIP_GEOM_SEGMENT is an iterator) – Vince Feb 19 '17 at 2:38
2

You have to create a function based index over the function:

SDO_LRS.CLIP_GEOM_SEGMENT(SHAPE, 0, 10)

On the base table:

  1. Create sdo geom metadata for the function:

Insert into user_sfo_geom_metada values(
'SDO_ROAD',
'SDO_LRS.CLIP_GEOM_SEGMENT(SHAPE, 0, 10)',
SDI_dimarray.....,
SRID);
  1. Create index:

Create index sdo_road_lrs_spx 
On sdo_roadS
(SDO_LRS.CLIP_GEOM_SEGMENT(SHAPE, 0, 10))
Indextype is mdysy spatial_index
Parameters('layer_gtype=MULTILINE');

You will need sdo Geo metadata for the view and the SHAPE_FIRST_10_METRES column.

1

I have a full worked SQL Script if you wish to see how it is done fully.

  • This isn't an answer by itself, and would be better suited as an addendum to your other answer. Links to external sites are discouraged because they can easily go stale. – Vince Feb 19 '17 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.