Is This Content Helpful?
We're glad to know this article was helpful.
When creating a query layer that references an Oracle view containing an Oracle Spatial attribute, the attribute must have a spatial index. Without the spatial index, spatial queries cannot be executed.
If the view derives an Oracle Spatial attribute, then a function-based index must be created on the attributes on the table that are used to derive the spatial attribute.
In the following example, there is a table named 'ellipse', which does not have a spatial attribute. The view, named sdo_ellipse, creates the SDO_Geometry attribute. A function-based spatial index, named sdo_ellipse_spindx, is created on the base table.
DROP TABLE ellipse;
CREATE TABLE ellipse (
id NUMBER(38) NOT NULL UNIQUE,
INSERT INTO ellipse VALUES (1, 'ellipse 1', 10, 10, 500, 250, 0);
INSERT INTO ellipse VALUES (2, 'ellipse 2', 10, 10, 500, 250, 90);
INSERT INTO ellipse VALUES (3, 'ellipse 3', 10, 10, 500, 250, 180);
INSERT INTO ellipse VALUES (4, 'ellipse 4', 10, 10, 500, 250, 45);
DROP VIEW sdo_ellipse;
CREATE VIEW sdo_ellipse AS
DROP INDEX sdo_ellipse_spindx;
DELETE FROM user_sdo_geom_metadata WHERE table_name = 'ELLIPSE';
INSERT INTO user_sdo_geom_metadata VALUES (
SDO_DIM_ELEMENT('Lat', -90, 90, 0.05),
SDO_DIM_ELEMENT('Long', -180, 180, 0.05)),
CREATE INDEX sdo_ellipse_spindx
INDEXTYPE IS MDSYS.SPATIAL_INDEX;