SDO_GEOMETRY view in ArcGIS Desktop

Question

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.

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?

Answer 1

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

2. 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.

Answer 2

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

drop table sdo_road purge;

CREATE TABLE SDO_ROAD ( 
  OBJECTID   INTEGER, 
    ROUTE_NAME VARCHAR2(32), 
    SHAPE      SDO_GEOMETRY
);

alter table sdo_road add constraint sdo_road_pk PRIMARY KEY (OBJECTID);

Insert into SDO_ROAD (OBJECTID,ROUTE_NAME,SHAPE) values (1,'Route1',MDSYS.SDO_GEOMETRY(3302,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(2,2,0,2,4,3.218,8,4,12.872,12,4,19.308,12,10,28.962,8,10,35.398,5,14,43.443)));
Insert into SDO_ROAD (OBJECTID,ROUTE_NAME,SHAPE) values (11,'result_geom_1',MDSYS.SDO_GEOMETRY(3302,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(2,2,0,2,4,2,5,4,5)));
Insert into SDO_ROAD (OBJECTID,ROUTE_NAME,SHAPE) values (12,'result_geom_2',MDSYS.SDO_GEOMETRY(3302,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(5,4,5,8,4,8,12,4,12,12,10,18,8,10,22,5,14,27)));
Insert into SDO_ROAD (OBJECTID,ROUTE_NAME,SHAPE) values (13,'result_geom_3',MDSYS.SDO_GEOMETRY(3302,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(2,2,0,2,4,2,5,4,5,8,4,8,12,4,12,12,10,18,8,10,22,5,14,27)));
commit;

delete from user_sdo_geom_metadata where TABLE_NAME = 'SDO_ROAD' and column_name = 'SHAPE';
commit;
Insert into user_sdo_geom_metadata(TABLE_NAME,COLUMN_NAME,DIMINFO,SRID) 
values ('SDO_ROAD','SHAPE',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',0,20,0.005),MDSYS.SDO_DIM_ELEMENT('Y',0,20,0.005),MDSYS.SDO_DIM_ELEMENT('M',0,20,0.005)),null);
commit;

select * from user_sdo_geom_metadata where table_name = 'SDO_ROAD';

CREATE INDEX SDO_ROAD_SPX ON SDO_ROAD(SHAPE) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('layer_gtype=LINE');

-- Now create view with clip shape
Create or replace force View VW_SDO_ROAD
AS
SELECT
    OBJECTID,Route_name,MDSYS.SDO_LRS.CLIP_GEOM_SEGMENT(SHAPE,0,10,0.005) AS SHAPE_FIRST_10_METRES  
FROM SDO_ROAD;

-- Create sdo geom metadata for view:
delete from user_sdo_geom_metadata where TABLE_NAME = 'VW_SDO_ROAD' and column_name = 'SHAPE_FIRST_10_METERS';
commit;
Insert into user_sdo_geom_metadata(TABLE_NAME,COLUMN_NAME,DIMINFO,SRID) 
values ('SDO_ROAD','SHAPE_FIRST_10_METERS',
MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',0,20,0.005),MDSYS.SDO_DIM_ELEMENT('Y',0,20,0.005),MDSYS.SDO_DIM_ELEMENT('M',0,20,0.005)),
null);
commit;
select * from user_sdo_geom_metadata where table_name = 'SDO_ROAD' and column_name = 'MDSYS.SDO_LRS.CLIP_GEOM_SEGMENT(SHAPE,0,10,0.005)';

-- To access the view spatially in ArcGIS, we need some things:

-- 1. Must have spatial index on function.
-- 1.1 Function must be DETERMINISTIC.
--    FUNCTION clip_geom_segment(geom_segment  IN MDSYS.SDO_GEOMETRY,
--                start_measure IN NUMBER,
--                end_measure   IN NUMBER,
--                tolerance     IN NUMBER DEFAULT 1.0e-8)
--    RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC PARALLEL_ENABLE;
-- Which it is
--
-- 1.2 Sdo geom metadata
delete from user_sdo_geom_metadata where TABLE_NAME = 'SDO_ROAD' and column_name = 'MDSYS.SDO_LRS.CLIP_GEOM_SEGMENT(SHAPE,0,10,0.005)';
commit;
Insert into user_sdo_geom_metadata(TABLE_NAME,COLUMN_NAME,DIMINFO,SRID) 
values ('SDO_ROAD','MDSYS.SDO_LRS.CLIP_GEOM_SEGMENT(SHAPE,0,10,0.005)',
MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',0,20,0.005),MDSYS.SDO_DIM_ELEMENT('Y',0,20,0.005),MDSYS.SDO_DIM_ELEMENT('M',0,20,0.005)),
null);
commit;
select * from user_sdo_geom_metadata where table_name = 'SDO_ROAD' and column_name = 'MDSYS.SDO_LRS.CLIP_GEOM_SEGMENT(SHAPE,0,10,0.005)';
-- 1.3 Create Function based index
CREATE INDEX SDO_ROAD_CLIP_SHAPE_SPX ON SDO_ROAD(MDSYS.SDO_LRS.CLIP_GEOM_SEGMENT(SHAPE,0,10,0.005)) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('layer_gtype=LINE');