2.8 Geometry Metadata Views

The geometry metadata describing the dimensions, lower and upper bounds, and tolerance in each dimension is stored in a global table owned by MDSYS (which users should never directly update). 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. However, effective with Release 23ai, if you do not update the spatial metadata, then Oracle Spatial will automatically create it when you create the spatial index, using a default tolerance value of 0.05. Note that the spatial table needs to be populated with at least one non-NULL geometry row for Oracle Spatial to create the required metadata. It is also ensured that the ALL_SDO_GEOM_METADATA view is updated to reflect the rows that are inserted in 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
);

In addition, the ALL_SDO_GEOM_METADATA view has an OWNER column identifying the schema that owns the table specified in TABLE_NAME.

The following considerations apply to schema, table, column, and index names, and to any SDO_DIMNAME values, that are stored in any Oracle Spatial metadata views:

• They must contain only letters, numbers, and underscores. For example, such a name cannot contain a space ( ), an apostrophe ('), a quotation mark ("), or a comma (,).

• All letters in the names are converted to uppercase before the names are stored in geometry metadata views or before the tables are accessed. This conversion also applies to any schema name specified with the table name.

  Note:

  Letter case conversion does not apply if you use mixed case (“CamelCase”) names enclosed in quotation marks. However, be aware that many experts recommend against using mixed-case names.

• TABLE_NAME
  
• COLUMN_NAME
  
• DIMINFO
  
• SRID
  

2.8.1 TABLE_NAME

The TABLE_NAME column contains the name of a feature table, such as COLA_MARKETS, that has a column of type SDO_GEOMETRY.

The table name is stored in the spatial metadata views in all uppercase characters.

The table name cannot contain spaces or mixed-case letters in a quoted string when inserted into the USER_SDO_GEOM_METADATA view, and it cannot be in a quoted string when used in a query (unless it is in all uppercase characters).

The spatial feature table cannot be an index-organized table if you plan to create a spatial index on the spatial column.

2.8.2 COLUMN_NAME

The COLUMN_NAME column contains the name of the column of type SDO_GEOMETRY. For the COLA_MARKETS table, this column is called SHAPE.

The column name is stored in the spatial metadata views in all uppercase characters.

The column name cannot contain spaces or mixed-case letters in a quoted string when inserted into the USER_SDO_GEOM_METADATA view, and it cannot be in a quoted string when used in a query (unless it is in all uppercase characters).

2.8.3 DIMINFO

The DIMINFO column is a varying length array of an object type, ordered by dimension, and has one entry for each dimension. The SDO_DIM_ARRAY type is defined as follows:

Create Type SDO_DIM_ARRAY as VARRAY(4) of SDO_DIM_ELEMENT; 

The SDO_DIM_ELEMENT type is defined as:

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. That is, DIMINFO contains 2 SDO_DIM_ELEMENT instances for two-dimensional geometries, 3 instances for three-dimensional geometries, and 4 instances for four-dimensional geometries. Each SDO_DIM_ELEMENT instance in the array must have valid (not null) values for the SDO_LB, SDO_UB, and SDO_TOLERANCE attributes.

Note:

The number of dimensions reflected in the DIMINFO information must match the number of dimensions of each geometry object in the layer.

For an explanation of tolerance and how to determine the appropriate SDO_TOLERANCE value, see Tolerance, especially Tolerance in the Geometry Metadata for a Layer.

Spatial assumes that the varying length array is ordered by dimension. The DIMINFO varying length array must be ordered by dimension in the same way the ordinates for the points in SDO_ORDINATES varying length array are ordered. For example, if the SDO_ORDINATES varying length array contains {X1, Y1, ..., Xn, Yn}, then the first DIMINFO entry must define the X dimension and the second DIMINFO entry must define the Y dimension.

Simple Example: Inserting, Indexing, and Querying Spatial Data shows the use of the SDO_GEOMETRY and SDO_DIM_ARRAY types. That example demonstrates how geometry objects (hypothetical market areas for colas) are represented, and how the COLA_MARKETS feature table and the USER_SDO_GEOM_METADATA view are populated with the data for those objects.

• SQL Functions for Min/Max of X/Y/Z Dimensions of a Geometry
  

2.8.3.1 SQL Functions for Min/Max of X/Y/Z Dimensions of a Geometry

You can use the following SQL functions to find the minimum and maximum X, Y, and Z dimension values for data in a spatial column in a table:

SDO_GEOM_MIN_X(<column-name>)
SDO_GEOM_MIN_Y(<column-name>)
SDO_GEOM_MIN_Z(<column-name>)
SDO_GEOM_MAX_X(<column-name>)
SDO_GEOM_MAX_Y(<column-name>)
SDO_GEOM_MAX_Z(<column-name>)

The following examples return the minimum and maximum X dimension values, and the minimum and maximum Y dimension values, of the geometry object named cola_c in the COLA_MARKETS table:

SQL> select SDO_GEOM_MIN_X(SHAPE), SDO_GEOM_MAX_X(SHAPE) from cola_markets where name = 'cola_c';

SDO_GEOM_MIN_X(SHAPE) SDO_GEOM_MAX_X(SHAPE)
--------------------- ---------------------
             3.0E+000              6.0E+000

SQL> select SDO_GEOM_MIN_Y(SHAPE), SDO_GEOM_MAX_Y(SHAPE) from cola_markets where name = 'cola_c';

SDO_GEOM_MIN_Y(SHAPE) SDO_GEOM_MAX_Y(SHAPE)
--------------------- ---------------------
             3.0E+000              5.0E+000

2.8.4 SRID

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.