Some SDO_GEOMETRY/DIMINFO handling functions

While at my current contract employer, two functions were required to handle the conversion of DIMINFO SDO_LB/SDO_UB ranges to optimised rectangles (SDO_GEOMETRY) and the reverse.

Note: GeoRaptor provides such capability for selected DIMINFO objects or to convert a map extent to its equivalent DIMINFO structure.

The functions, written in PL/SQL are:

  • ST_Diminfo2Rectangle
  • ST_Geom2Diminfo

The definition of ST_Diminfo2Rectangle is as follows:

 CREATE OR REPLACE
 FUNCTION ST_Diminfo2Rectangle( p_dim_array IN mdsys.sdo_dim_array,
                                p_srid      IN INTEGER DEFAULT NULL)
   RETURN mdsys.sdo_geometry deterministic
 AS
    v_ords     mdsys.sdo_ordinate_array :=  NEW mdsys.sdo_ordinate_array(NULL);
    v_ord      pls_integer;
    v_dims     pls_integer;
    v_out_geom mdsys.sdo_geometry;
 BEGIN
    IF ( p_dim_array IS NULL ) THEN
       RETURN NULL;
    END IF;
    -- Has p_dim_array sufficient elements
    IF ( p_dim_array.COUNT < 2 ) THEN
       RETURN NULL; -- Or SDO_GEOMETRY(NULL,NULL...)?
    END IF;
    -- Convert
    -- Assume first two sdo_dim_elements are X and Y (or Long/Lat
    -- ie don't bother checking dim name
    --
    v_dims := CASE WHEN p_dim_array.COUNT > 4 THEN 4 ELSE p_dim_array.COUNT END;
    v_ords.DELETE;
    v_ords.EXTEND(v_dims * 2);
    v_ords(1) := p_dim_array(1).sdo_lb; -- X
    v_ords(2) := p_dim_array(2).sdo_lb; -- Y
    v_ord := 3;
    IF ( p_dim_array.COUNT > 2 ) THEN
      v_ords(v_ord) := p_dim_array(3).sdo_lb;
      v_ord := v_ord + 1;
      IF ( p_dim_array.COUNT > 3 ) THEN
        v_ords(v_ord) := p_dim_array(4).sdo_lb;
        v_ord := v_ord + 1;
      END IF;
    END IF;
    v_ords(v_ord) := p_dim_array(1).sdo_ub; -- X
    v_ord := v_ord + 1;
    v_ords(v_ord) := p_dim_array(1).sdo_ub; -- Y
    v_ord := v_ord + 1;
    IF ( p_dim_array.COUNT > 2 ) THEN
      v_ords(v_ord) := p_dim_array(3).sdo_ub;
      v_ord := v_ord + 1;
      IF ( p_dim_array.COUNT > 3 ) THEN
        v_ords(v_ord) := p_dim_array(4).sdo_ub;
      END IF;
   END IF;
    RETURN mdsys.sdo_geometry(v_dims * 1000 + 3, 
                              p_srid,
                              NULL,
                              sdo_elem_info_array(1,1003,3),
                              v_ords);
 END ST_Diminfo2Rectangle;
 /
 SHOW ERRORS

Testing ST_Diminfo2Rectangle generates the following:

 SELECT TABLE_NAME, column_name,
        ST_Diminfo2Rectangle(diminfo,srid) AS dGeom
   FROM user_sdo_geom_metadata;
 -- Results
 TABLE_NAME    COLUMN_NAME DGEOM
 ------------- ----------- ---------------------------------------------------------------------------------------
 WW_ADMIN_AREA GEOM        SDO_GEOMETRY(2003,82469,NULL,
                                        SDO_ELEM_INFO_ARRAY(1,1003,3),
                                        SDO_ORDINATE_ARRAY(401464.379,6383805.94,476727.39089109,476727.39089109))

The definition of ST_Geom2DimInfo is as follows:

 CREATE OR REPLACE
 FUNCTION St_Geom2Diminfo(P_Geom        IN Sdo_Geometry,
                          P_Srid        IN NUMBER DEFAULT NULL,
                          P_Tolerance   IN NUMBER DEFAULT 0.005,
                          p_is_geodetic IN Varchar2 DEFAULT 'N')
 RETURN Mdsys.Sdo_Dim_Array Deterministic
 AS
   V_Dims      Pls_Integer;
   v_tolerance NUMBER      := Nvl(P_Tolerance,0.005);
   v_x_dimname varchar2(5) := CASE WHEN UPPER(NVL(p_is_geodetic,'Y')) IN ('Y','TRUE') THEN 'LONG' ELSE 'X' END;
   v_y_dimname varchar2(5) := CASE WHEN UPPER(NVL(p_is_geodetic,'Y')) IN ('Y','TRUE') THEN 'LAT'  ELSE 'Y' END;
 BEGIN
   IF (P_Geom IS NULL OR P_Geom.Sdo_Ordinates IS NULL) THEN
     RETURN NULL;
   END IF;
   V_Dims := P_Geom.Get_Dims();
   RETURN
   CASE WHEN V_Dims = 2
        THEN Mdsys.Sdo_Dim_Array(
               Mdsys.Sdo_Dim_Element(V_X_Dimname, P_Geom.Sdo_Ordinates(1), P_Geom.Sdo_Ordinates(v_dims+1), v_tolerance),
               Mdsys.Sdo_Dim_Element(V_Y_Dimname, P_Geom.Sdo_Ordinates(2), P_Geom.Sdo_Ordinates(v_dims+2), v_tolerance))
        WHEN V_Dims = 3
        THEN Mdsys.Sdo_Dim_Array(
               Mdsys.Sdo_Dim_Element(V_X_Dimname, P_Geom.Sdo_Ordinates(1), P_Geom.Sdo_Ordinates(v_dims+1), v_tolerance),
               Mdsys.Sdo_Dim_Element(V_Y_Dimname, P_Geom.Sdo_Ordinates(2), P_Geom.Sdo_Ordinates(v_dims+2), v_tolerance),
               Mdsys.Sdo_Dim_Element(CASE WHEN P_Geom.Get_Lrs_Dim() IN (0,4) THEN 'Z' ELSE 'M' END,
                                     P_Geom.Sdo_Ordinates(3), P_Geom.Sdo_Ordinates(v_dims+3), v_tolerance))
        WHEN V_Dims = 4
        THEN Mdsys.Sdo_Dim_Array(
               Mdsys.Sdo_Dim_Element(V_X_Dimname, P_Geom.Sdo_Ordinates(1), P_Geom.Sdo_Ordinates(v_dims+1), v_tolerance),
               Mdsys.Sdo_Dim_Element(V_Y_Dimname, P_Geom.Sdo_Ordinates(2), P_Geom.Sdo_Ordinates(v_dims+2), v_tolerance),
               Mdsys.Sdo_Dim_Element(CASE WHEN P_Geom.Get_Lrs_Dim() IN (0,4) THEN 'Z' ELSE 'M' END,
                                     P_Geom.Sdo_Ordinates(3), P_Geom.Sdo_Ordinates(v_dims+3), v_tolerance),
               Mdsys.Sdo_Dim_Element(CASE WHEN P_Geom.Get_Lrs_Dim() = 4 THEN 'M' ELSE 'Z' END,
                                     P_Geom.Sdo_Ordinates(4), P_Geom.Sdo_Ordinates(v_dims+4), v_tolerance))
        ELSE NULL
    END;
 END St_Geom2Diminfo;
 /
 SHOW ERRORS

Testing ST_Geom2DimInfo generates the following:

 --
 -- Firstly a generic geometry object
 --
 SELECT ST_Geom2Diminfo(
           SDO_GEOMETRY(3003,NULL,NULL,
                        SDO_ELEM_INFO_ARRAY(1,1003,3),
                        SDO_ORDINATE_ARRAY(-0.8736,-0.933335,0.303673, 0.855034,0.571309,0.972036)),
           3112,0.005,'y') AS diminfo
   FROM dual;
 --
 -- Output modified
 --
 DIMINFO
 --------------------------------------------------------------
 SDO_DIM_ARRAY(SDO_DIM_ELEMENT('LONG',-0.8736,0.855034,0.005),
               SDO_DIM_ELEMENT('LAT',-0.933335,0.571309,0.005),
               SDO_DIM_ELEMENT('Z',0.303673,0.972036,0.005))
 --
 -- Secondly, a practical example of how to convert an RTree's sdo_root_mbr into a DIMINFO.
 --
 SELECT sdo_root_mbr, srid,
        st_geom2diminfo(sdo_root_mbr,asgm.srid,0.005,a.SDO_INDEX_GEODETIC) AS diminfo
   FROM MDSYS.SDO_INDEX_METADATA_TABLE a
        INNER JOIN
        all_indexes ai
        ON (  ai.owner      = a.sdo_index_owner
          AND ai.index_name = a.sdo_index_name)
        INNER JOIN
        all_sdo_geom_metadata asgm
        ON (asgm.owner = ai.owner AND asgm.TABLE_NAME = ai.TABLE_NAME AND asgm.column_name = REPLACE(a.sdo_column_name,'"',''))
  WHERE sdo_root_mbr IS NOT NULL
    AND a.sdo_root_mbr.sdo_gtype <> 2000
    AND a.sdo_root_mbr IS NOT NULL;
 --
 -- Output has been modified.
 --
 SDO_ROOT_MBR
 ------------------------------------------------------------------------------------------------------------------------------------------------
 SRID
 ----
 DIMINFO
 ------------------------------------------------------------------------------------------------------------------------------------------------
 SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),
              SDO_ORDINATE_ARRAY(-13874494.858,2820483.008,-7452778.538,6337725.438))
 3785  SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X',-13874494.858,-7452778.538,0.005),
                     SDO_DIM_ELEMENT('Y',2820483.008,6337725.438,0.005))
 SDO_GEOMETRY(3008,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1007,3),
              SDO_ORDINATE_ARRAY(-0.432523148154909,-0.902161835066299,0.414984208015312,0.277693714719153,-0.544627601772466,0.759107954517766))
 4326  SDO_DIM_ARRAY(SDO_DIM_ELEMENT('LONG',-0.432523148154909,0.277693714719153,0.005),
                     SDO_DIM_ELEMENT('LAT',-0.902161835066299,-0.544627601772466,0.005),
                     SDO_DIM_ELEMENT('Z',0.414984208015312,0.759107954517766,0.005))
 SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),
              SDO_ORDINATE_ARRAY(5979762.10717428,2085798.82445402,6024890.06350611,2130875.57353918))
 2872  SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X',5979762.10717428,6024890.06350611,0.005),SDO_DIM_ELEMENT('Y',2085798.82445402,2130875.57353918,0.005))
 SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),
              SDO_ORDINATE_ARRAY(401099.41,6383793.51,476711.23,6483905.25))
 82469 SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X',401099.41,476711.23,0.005),
                     SDO_DIM_ELEMENT('Y',6383793.51,6483905.25,0.005))
   4 ROWS selected

I hope these are of use to someone.