Oracle Spatial - Geometry Metadata Views and Index
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.
Just for info: for the coordinate systems metadata (and then not the geometry metadata), the coordinate systems functions and procedures use information provided in the tables and views supplied with Oracle Spatial. The tables and views are part of the MDSYS schema; however, public synonyms are defined, so you do not need to specify MDSYS. before the table or view name. All tables are explained in this paragraph from the user guide.
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.
Each metadata view has the following definition:
( TABLE_NAME VARCHAR2(32), COLUMN_NAME VARCHAR2(32), DIMINFO SDO_DIM_ARRAY, SRID NUMBER );
- 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.
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.
- 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;
The same example than above but from SQL Developer.
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