Oracle Spatial - Geometry Metadata Views and Index

About

The geometry metadata describe for each dimensions (axis):

• the lower bound
• the upper bound,
• and the tolerance.

This data are stored in a global table owned by MDSYS (which users should never directly update).

Once the metadata are filled, you can and must create the index.

Articles Related

Metadata

Views

Each Spatial user has the following views available in the schema associated with that user:

• USER_SDO_GEOM_METADATA contains metadata information for all spatial tables owned by the user (schema). This is the only view that you can update, and it is the one in which Spatial users must insert metadata related to spatial tables.
• ALL_SDO_GEOM_METADATA contains metadata information for all spatial tables on which the user has SELECT permission.

Spatial users are responsible for populating these views. For each spatial column, you must insert an appropriate row into the USER_SDO_GEOM_METADATA view.

Oracle Spatial ensures that the ALL_SDO_GEOM_METADATA view is also updated to reflect the rows that you insert into USER_SDO_GEOM_METADATA.

View Definition

Each metadata view has the following definition:

(
  TABLE_NAME   VARCHAR2(32),
  COLUMN_NAME  VARCHAR2(32),
  DIMINFO      SDO_DIM_ARRAY,
  SRID         NUMBER
);

where:

• The TABLE_NAME column contains the name of a feature table, such as COLA_MARKETS, that has a column of type Oracle Spatial - The geometric description (SDO_GEOMETRY DataType).
• The COLUMN_NAME column contains the name of the column of type SDO_GEOMETRY.
• The DIMINFO column is a varying length array of an object type, ordered by dimension, and has one entry for each dimension.
• The SRID column should contain either of the following: the SRID value for the coordinate system for all geometries in the column, or NULL if no specific coordinate system should be associated with the geometries.

DIMINFO

The DIMINFO column (Dimension info):

• is used to define the Lower and Upper Boundary, the tolerance by dimension
• and then has one entry for each dimension.
• store this element in a varying length array of an object type, ordered by dimension.

The SDO_DIM_ARRAY type is defined as follows:

Create Type SDO_DIM_ARRAY as VARRAY(4) of SDO_DIM_ELEMENT; 

Create Type SDO_DIM_ELEMENT as OBJECT (
  SDO_DIMNAME VARCHAR2(64),
  SDO_LB NUMBER,
  SDO_UB NUMBER,
  SDO_TOLERANCE NUMBER);

The SDO_DIM_ARRAY instance is of size n if there are n dimensions. Example, DIMINFO contains :

• 2 SDO_DIM_ELEMENT instances for two-dimensional geometries,
• 3 SDO_DIM_ELEMENT instances for three-dimensional geometries,
• 4 SDO_DIM_ELEMENT instances for four-dimensional geometries.

Each SDO_DIM_ELEMENT instance in the array must have valid (not null) values for the:

• SDO_LB (Lower Boundary),
• SDO_UB (Upper Boundary),
• and SDO_TOLERANCE attributes.

Example

SQL

• From the documentation without SRID

INSERT INTO user_sdo_geom_metadata
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID)
  VALUES (
  'cola_markets',
  'shape',
  SDO_DIM_ARRAY(   -- 20X20 grid
    SDO_DIM_ELEMENT('MyDimension1', 0, 20, 0.005),
    SDO_DIM_ELEMENT('MyDimension2', 0, 20, 0.005)
     ),
  NULL   -- SRID
);

• with this function that return a point SDO_GEOMETRY datatype

-- Set up the metadata entry for this table.
-- note that the column name sets up the function on top
-- of the two columns used in this function,
-- along with the owner of the function.
insert into user_sdo_geom_metadata values(
   'LONG_LAT_TABLE', -- table
   'scott.get_long_lat_pt(longitude,latitude)', -- function
   mdsys.sdo_dim_array(
      mdsys.sdo_dim_element('Longitude', -180, 180, 0.005),
      mdsys.sdo_dim_element('Latitude', -90, 90, 0.005)
   ),
   8307  -- SRID
    );

• with a SRID (for instance 90112). It must be the same than in the database and a index must be created.

insert into USER_SDO_GEOM_METADATA values (
        'MY_TABLE', -- Table
        'MY_COLUMN', -- Column of type SDO_GEOMETRY
        MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, 10), -- 10 meters tolerance
                            MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, 10)), -- 10 meters tolerance
        8307); -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
);

commit;

CREATE INDEX MY_TABLE_sdx
   ON MY_TABLE(MY_COLUMN)
   INDEXTYPE IS MDSYS.SPATIAL_INDEX;

SQL Developer

The same example than above but from SQL Developer.

Support

ORA-13203: failed to read USER_SDO_GEOM_METADATA view

This error occurs when you try to create an index. It says that it miss something in the metadata. You have forgotten to add an entry in the metadata USER_SDO_GEOM_METADATA