The significance of sdo_lb/sdo_ub in USER_SDO_GEOM_METDATA: Do I need it?

Questions are often raised about the significance of the SDO_LB and SDO_UB fields of the SDO_DIM_ELEMENT structures that define the spatial extent of the dimensions (SDO_DIM_ARRAY) of a table/column in Oracle’s metadata tables (accessed via the USER_SDO_GEOM_METADATA and ALL_SDO_GEOM_METADATA views).

The structure of the the ALL_SDO_GEOM_METADATA view is:

 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- -------------------  
 OWNER                                                                   NOT NULL VARCHAR2(32)
 TABLE_NAME                                                              NOT NULL VARCHAR2(32)
 COLUMN_NAME                                                             NOT NULL VARCHAR2(1024)
 DIMINFO                                                                          MDSYS.SDO_DIM_ARRAY
 SRID                                                                             NUMBER

An MDSYS.SDO_DIM_ARRAY is an array of MDSYS.SDO_DIM_ELEMENTs as follows:

 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------
 SDO_DIMNAME                                                                      VARCHAR2(64)
 SDO_LB                                                                           NUMBER
 SDO_UB                                                                           NUMBER
 SDO_TOLERANCE                                                                    NUMBER

Note, an SDO_DIM_ELEMENT structure is created for each dimension of the data eg 2 for 2D data, 3 for 3D data etc.

An example is:

  ) VALUES (
     MDSYS.SDO_DIM_ELEMENT('Latitude' ,-90,90,0.05)),

These questions are often raised in the context of spatial indexing and also their use in overloaded functions in Oracle’s SDO_GEOM package which can take either an SDO_DIM_ARRAY structure or an tolerance (cf SDO_TOLERANCE) value such as in the following versions of the geometry validation functions:

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 THEGEOMETRY                    SDO_GEOMETRY            IN
 THEDIMINFO                     SDO_DIM_ARRAY           IN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 THEGEOMETRY                    SDO_GEOMETRY            IN
 TOLERANCE                      NUMBER                  IN

When Oracle Spatial predominantly used QuadTree indexing it was necessary to ensure that the area to be tesselated covered the entire contents of the table/column. Thus, one entered in extents to MDSYS.SDO_DIM_ARRAY that were sensible (ie just covered the area) such that the tesselation could occur effectively and efficiently at the chosen tiling level as expressed by SDO_LEVEL in the PARAMETERS clause of the spatial index creation statement.

If your MDSYS.SDO_DIM_ARRAY SDO_LB/SDO_UBs do not cover the area to be indexed then when you try to create a Quad Tree index with a statement like this:

CREATE INDEX projline2d_geom
          ON projline(geom)
          INDEXTYPE IS mdsys.spatial_index

You will get this error if the extent of the data in projline2d(geom) includes data outside of the extent recorded in the sdo_dim_array structure:

SQL Error: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed
ORA-13206: internal error [Tessellate] while creating the spatial index
ORA-13249: Error in spatial index: [mdpridxtessellate]
ORA-13200: internal error [ROWID:AAAPofAAJAAAPNIAAA] in spatial indexing.
ORA-13019: coordinates out of bounds
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_9I", line 7
ORA-06512: at line 1
29855. 00000 - "error occurred in the execution of ODCIINDEXCREATE routine"

Now RTree indexing does not require a definition of the entire extent of the area to be indexed as the indexing is based on the MBR of individual sdo_geometries and not the regular tesselation of a defined space.

So, nowadays, where RTree indexing is used (and is the recommended spatial indexing method since Oracle Spatial 9.2) the values in the MDSYS.SDO_DIM_ELEMENT’s SDO_LB/SDO_UB fields are irrelevant for spatial indexing. So, if one rebuilt the above index using RTree indexing the index would be created:

CREATE INDEX projline2d_geom
          ON projline(geom)
          INDEXTYPE IS mdsys.spatial_index
          PARAMETERS('sdo_indx_dims=2, layer_gtype=MULTILINE');

So what is the use of putting values into these fields when using RTree indexing? Geospatial practitioners tend to use the SDO_DIM_ARRAY to define the extent of the data because some GIS packages do actually use it for “Zoom to Extent” type operations (which would be far faster than SELECT SDO_AGGR_MBR(geom) FROM table!).

Use in Overloaded Functions
Now that cleans up the sdo_lb/sdo_ub elements of the sdo_dim_array structure. But what about tolerance vs MDSYS.SDO_DIM_ARRAYy using in functions?

Now, I tend to avoid hardcoding tolerance values in queries that involve the tolerance parameter (and not the MDSYS.SDO_DIM_ARRAY form) unless it is different from the SDO_TOLERANCE value in the SDO_DIM_ARRAY). Where the tolerance is the same, I always use the MDSYS.SDO_DIM_ARRAY form as it is easier to use in queries.

Compare this:

SELECT mdsys.sdo_geom.validate_geometry(a.geom,asgm.diminfo)
  FROM all_sdo_geom_metadata asgm,
       projline2d a
 WHERE asgm.owner = 'CODESYS'
   AND asgm.table_name = 'PROJLINE2D'
   AND asgm.column_name = 'GEOM';

To this:

SELECT mdsys.sdo_geom.validate_geometry(a.geom,b.sdo_tolerance)
  FROM (SELECT t.sdo_tolerance
          FROM all_sdo_geom_metadata asgm,
               TABLE(asgm.diminfo) t
         WHERE asgm.owner = 'CODESYS'
           AND asgm.table_name = 'PROJLINE2D'
           AND asgm.column_name = 'GEOM'
           AND t.sdo_dimname = 'X'
       ) b,
       projline2d a;

And I think you can see why!

I hope this explanation is of some use.

If you want to learn more about spatial indexing click here