English

How To: Create Oracle Spatial function-based indexing

Summary

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.

Procedure

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.

  1. The first step is to create the table and insert some rows.
    Code:
    DROP TABLE ellipse;
    CREATE TABLE ellipse (
    id NUMBER(38) NOT NULL UNIQUE,
    name VARCHAR2(50),
    x NUMBER,
    y NUMBER,
    semi_major NUMBER,
    semi_minor NUMBER,
    azimuth NUMBER);
    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);


  2. Next, the view is created using the SDO_UTIL.ELLIPSE_POLYGON function. This function returns a polygon that approximates a specified ellipse. Consult the Oracle Spatial User's Guide and Reference for additional information.
    Code:
    DROP VIEW sdo_ellipse;
    CREATE VIEW sdo_ellipse AS
    SELECT
    id,
    name,
    SDO_UTIL.ELLIPSE_POLYGON(x,y,semi_major,semi_minor,azimuth,0.1)
    shape
    FROM ellipse;

  3. At this point, there is no spatial attribute or spatial index. Attempting to draw the layer in ArcMap results in nothing being displayed, but attribute queries complete successfully.

    To create the function based index, a row in the user_sdo_geom_metadata must first be inserted. Be sure to fully qualify the function that is being used.
    Code:
    DROP INDEX sdo_ellipse_spindx;
    DELETE FROM user_sdo_geom_metadata WHERE table_name = 'ELLIPSE';
    INSERT INTO user_sdo_geom_metadata VALUES (
    'ellipse',
    'MDSYS.SDO_UTIL.ELLIPSE_POLYGON(x,y,semi_major,semi_minor,azimuth,0.1)',
    SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('Lat', -90, 90, 0.05),
    SDO_DIM_ELEMENT('Long', -180, 180, 0.05)),
    8307);


  4. Now the spatial index can be created successfully.
    Code:

    CREATE INDEX sdo_ellipse_spindx
    ON ellipse(SDO_UTIL.ELLIPSE_POLYGON(x,y,semi_major,semi_minor,azimuth,0.1))
    INDEXTYPE IS MDSYS.SPATIAL_INDEX;


    After the spatial index is created, the data can be displayed in ArcMap.

Related Information