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?

1
  • 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.

0
1

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');
1
  • 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.