Go to content Go to navigation and search

Current Oracle Spatial Blogs

Search

RSS / Atom

Email me

textpattern

The significance of sdo_lb/sdo_ub in USER_SDO_GEOM_METDATA: Do I need it? · May 10, 10:50 pm by Simon Greener

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: 
SQL> describe ALL_SDO_GEOM_METADATA
 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:

SQL> describe MDSYS.SDO_DIM_ARRAY
 MDSYS.SDO_DIM_ARRAY VARRAY(4) OF MDSYS.SDO_DIM_ELEMENT
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------
 SDO_DIMNAME                                                                      VARCHAR2(64)
 SDO_LB                                                                           NUMBER
 SDO_UB                                                                           NUMBER
 SDO_TOLERANCE                                                                    NUMBER

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

An example is:

  INSERT INTO ALL_SDO_GEOM_METADATA (
   OWNER,
   TABLE_NAME,
   COLUMN_NAME,
   DIMINFO,
   SRID
   ) VALUES (
   'CODESYS',
   'GEODLINE2D',
   'GEOM', 
   MDSYS.SDO_DIM_ARRAY(
      MDSYS.SDO_DIM_ELEMENT('Longitude',-180,180,0.05),
      MDSYS.SDO_DIM_ELEMENT('Latitude' ,-90,90,0.05)),
  8311);

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:

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

Indexing
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 
          PARAMETERS('SDO_LEVEL=8');

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

regards Simon
It’s very intresting. I admire you because it’s briliant artice
— Adaddm    Sep 23, 09:24 pm    #
  Textile Help

<<Oracle Spatial Forum - Melbourne April 2007 >>Oracle Spatial Mapping and Map Rendering Performance Tips