Elem_Info_Array Processing: An alternative to SDO_UTIL.GetNumRings and querying SDO_ELEM_INFO itself

This article is in two parts: 1) on SDO_UTIL.GetNumRings; 2) and an outline of a function to return the element info array in its normalised form.

1. SDO_UTIL.GETNUMRINGS

Oracle Spatial, well at release 10g, has an undocumented function in the SDO_UTIL package called GetNumRings.

 FUNCTION GETNUMRINGS RETURNS NUMBER
  Argument Name                  Type                   In/Out Default?
  ------------------------------ ----------------------- ------ --------
  GEOM                           SDO_GEOMETRY            IN

This function takes a polygon geometry object and returns the number of rings that compose it. So, if I provide it a simple xD polygon, it will tell me how many rings (outer and inner) compose it. Here is an example:

 select mdsys.sdo_util.getnumrings(SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(1,1,10,10))) as ringcount
   from dual;
 
 RINGCOUNT
 ----------
          1
 
 1 rows selected

I had a quick look at this function and found that it does not handle compound rings.

 select a.polytype,sdo_util.getnumrings(a.geom) as ringcount,a.geom.sdo_elem_info
   from projpoly2d a;
 
 POLYTYPE                                 RINGCOUNT              GEOM.SDO_ELEM_INFO
 ---------------------------------------- ---------------------- ----------------------------
 VERTEXWITHHOLE                           2                      NUMBER(1,1003,1,77,2003,1)
 VERTEXNOHOLE                             1                      NUMBER(1,1003,1)
 VERTEXNOHOLE                             1                      NUMBER(1,1003,1)
 COMPOUNDOUTERSHELL                       0                      NUMBER(1,1005,2,1,2,1,5,2,2)
 VERTEXNOHOLE                             1                      NUMBER(1,1003,1)
 VERTEXWITHARCNOHOLE                      0                      NUMBER(1,1005,2,1,2,1,5,2,2)
 NORMALISEDRECTANGLENOHOLE                1                      NUMBER(1,1003,3)
 CURVEPOLYWITHHOLE                        2                      NUMBER(1,1003,4,7,2003,4)
 
 8 rows selected

If you are worried about this, try just using straight SQL.

 select polytype,count(b.column_value)
   from projpoly2d a,
        table(a.geom.sdo_elem_info) b
  where b.column_value in (1003,1005,2003,2005)
 group by polytype, b.column_value;
 
 POLYTYPE                                 COUNT(B.COLUMN_VALUE)
 ---------------------------------------- ----------------------
 VERTEXNOHOLE                             3
 COMPOUNDOUTERSHELL                       1
 NORMALISEDRECTANGLENOHOLE                1
 VERTEXWITHHOLE                           1
 CURVEPOLYWITHHOLE                        1
 VERTEXWITHHOLE                           1
 CURVEPOLYWITHHOLE                        1
 VERTEXWITHARCNOHOLE                      1
 
 8 rows selected

Or, as I have not published in this article how to construct and populate the projpoly2d table, here is something more explicit you can try yourself:

 select sdo_util.getnumrings(a.geom) as ringcount
   from (SELECT SDO_GEOMETRY(2007,  -- two-dimensional multi-part polygon with hole
                             NULL,
                             NULL,
                             SDO_ELEM_INFO_ARRAY(1,1005,2, 1,2,1, 5,2,2,
                                                11,2005,2, 11,2,1, 15,2,2,
                                                21,1005,2, 21,2,1, 25,2,2),
                             SDO_ORDINATE_ARRAY(  6,10, 10,1, 14,10, 10,14, 6,10,
                                                 13,10, 10,2, 7,10, 10,13, 13,10,
                                               106,110, 110,101, 114,110, 110,114,106,110)) as geom
           FROM dual) a;
 
 RINGCOUNT
 ----------------------
 0
 
 1 rows selected

Which is wrong. So, let’s try pure SQL:

 select count(*) as ringcount
   from TABLE(SELECT SDO_GEOMETRY(2007,  -- two-dimensional multi-part polygon with hole
                             NULL,
                             NULL,
                             SDO_ELEM_INFO_ARRAY(1,1005,2, 1,2,1, 5,2,2,
                                                11,2005,2, 11,2,1, 15,2,2,
                                                21,1005,2, 21,2,1, 25,2,2),
                             SDO_ORDINATE_ARRAY(  6,10, 10,1, 14,10, 10,14, 6,10,
                                                 13,10, 10,2, 7,10, 10,13, 13,10,
                                               106,110, 110,101, 114,110, 110,114,106,110)).sdo_elem_info
           FROM dual) a
  where a.column_value in (1003,1005,2003,2005);
 
 RINGCOUNT
 ----------------------
 3
 
 1 rows selected

Which is correct.

The SQL examples above are simplified and will not produce a correct in all cases. The reason for this is that the predicate:

 ...
  where b.column_value in (1003,1005,2003,2005)
 ...

Does not discriminate between the ordinate offset, etype and interpretation triplet that is a standard entry for sdo_elem_info_array. So, if an ordinate offset was either 1003, 1005, 2003 or 2005 it would incorrectly add to the count of rings.

The way to correctly query the sdo_elem_info_array is via a SQL pivot as follows:

 SELECT count(*) as ringcount
   FROM (SELECT e.id,
                 e.etype,
                 e.offset,
                 e.interpretation
           FROM (SELECT trunc((rownum - 1) / 3,0) as id,
                         sum(case when mod(rownum,3) = 1 then sei.column_value else null end) as offset,
                         sum(case when mod(rownum,3) = 2 then sei.column_value else null end) as etype,
                         sum(case when mod(rownum,3) = 0 then sei.column_value else null end) as interpretation
                   FROM TABLE(SDO_GEOMETRY(2007,  -- two-dimensional multi-part polygon with hole
                                           NULL,
                                           NULL,
                                           SDO_ELEM_INFO_ARRAY(1,1005,2, 1,2,1, 5,2,2,
                                                              11,2005,2, 11,2,1, 15,2,2,
                                                              21,1005,2, 21,2,1, 25,2,2),
                                           SDO_ORDINATE_ARRAY(  6,10, 10,1, 14,10, 10,14, 6,10,
                                                               13,10, 10,2, 7,10, 10,13, 13,10,
                                                             106,110, 110,101, 114,110, 110,114,106,110)).sdo_elem_info
                          ) sei
               GROUP BY trunc((rownum - 1) / 3,0)
              ) e
         ) i
  WHERE i.etype in (1003,1005,2003,2005);
 
 RINGCOUNT
 ----------
          3
 
 1 rows selected

To create your own function – in case you are worried about use of an undocumented Oracle function – using the above approach, is fairly trivial:

 create or replace Function GetNumRings( p_geometry in mdsys.sdo_geometry )
   Return Number Deterministic
 Is
   v_ring_count number := 0;
 Begin
   SELECT count(*) as ringcount
     INTO v_ring_count
     FROM (SELECT e.id,
                   e.etype,
                   e.offset,
                   e.interpretation
             FROM (SELECT trunc((rownum - 1) / 3,0) as id,
                           sum(case when mod(rownum,3) = 1 then sei.column_value else null end) as offset,
                           sum(case when mod(rownum,3) = 2 then sei.column_value else null end) as etype,
                           sum(case when mod(rownum,3) = 0 then sei.column_value else null end) as interpretation
                     FROM TABLE(p_geometry.sdo_elem_info) sei
                    GROUP BY trunc((rownum - 1) / 3,0)
                   ) e
           ) i
    WHERE i.etype in (1003,1005,2003,2005);
   Return v_ring_count;
 End GetNumRings;
 /
 SHOW ERRORS

And, testing it:

 SELECT GetNumRings(NULL) As RingCount FROM DUAL;
 
 RINGCOUNT
 ----------------------
 0
 
 1 rows selected
 
 SELECT GetNumRings(SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(10,10,10),NULL,NULL)) As RingCount FROM DUAL;
 
 RINGCOUNT
 ----------------------
 0
 
 1 rows selected
 
 SELECT GetNumRings(SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(1,1,10,10))) As RingCount FROM DUAL;
 
 RINGCOUNT
 ----------------------
 0
 
 1 rows selected
 
 SELECT GetNumRings(SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(1,1,10,10))) As RingCount FROM DUAL;
 
 RINGCOUNT
 ----------------------
 1
 
 1 rows selected
 
 SELECT GetNumRings(SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3,5,2003,3),SDO_ORDINATE_ARRAY(1,1,100,100,20,20,40,40))) As RingCount FROM DUAL;
 
 RINGCOUNT
 ----------------------
 2
 
 1 rows selected
 
 SELECT GetNumRings(SDO_GEOMETRY(2007,  -- two-dimensional multi-part polygon with hole
                                 NULL,
                                 NULL,
                                 SDO_ELEM_INFO_ARRAY(1,1005,2, 1,2,1, 5,2,2,
                                                    11,2005,2, 11,2,1, 15,2,2,
                                                    21,1005,2, 21,2,1, 25,2,2),
                                 SDO_ORDINATE_ARRAY(  6,10, 10,1, 14,10, 10,14, 6,10,
                                                     13,10, 10,2, 7,10, 10,13, 13,10,
                                                    106,110, 110,101, 114,110, 110,114,106,110)
                                )
                    )
         as RingCount
   FROM DUAL;
 
 RINGCOUNT
 ----------------------
 3
 
 1 rows selected

Note that the function did not need EXCEPTION handling for NULL or non-polygon geometries as the answer returned from the SQL is correct.

2. Function Returning Normalised Element Info Array

The Oracle sdo_geometry ordinates and element info arrays are simple, one dimensional (denormalised) arrays that, for the newcomer is, strangly, a little daunting. But we can approach them more easily. Oracle 10g has the SDO_UTIL.GetNumVertices function that returns a more natrual MDSYS.Vertex_Type (see documentation). But there are not many functions that present the elements of the sdo_elem_info_array in a normalised form.

To do so, we will need to write a function that can be used in a query. I prefer PIPELINE functions in this case as they are more efficient on memory use and are more “native” to the way the Oracle database itself works.

Let’s start with a traditional coding to access the SDO_ELEM_INFO_ARRAY varray.

First we need to create an appropriate data types:

 create or replace type t_ElemInfoType As Object (
    offset           NUMBER,
    etype            NUMBER,
    interpretation   NUMBER);
 
 create or replace type T_ElemInfoSetType as Table of T_ElemInfoType;

Now we can code a function, in the traditional way, that returns many (a set) of t_ElemInfoTypes.

 Create Or Replace Function GetElemInfo(
   p_geometry in mdsys.sdo_geometry)
   Return T_ElemInfoSetType pipelined
 Is
   v_elements number;
 Begin
   If ( p_geometry is not null ) Then
     If ( p_geometry.sdo_elem_info is not null) Then
       v_elements := ( ( p_geometry.sdo_elem_info.COUNT / 3 ) - 1 );
       <<element_extraction>>
       for v_i IN 0 .. v_elements LOOP
          PIPE ROW ( T_ElemInfoType(
                     p_geometry.sdo_elem_info(v_i * 3 + 1),
                     p_geometry.sdo_elem_info(v_i * 3 + 2),
                     p_geometry.sdo_elem_info(v_i * 3 + 3) ) );
       end loop element_extraction;
     End If;
   End If;
   Return;
 End GetElemInfo;

Which we can use as follows:

 SELECT e.*
   FROM TABLE(GetElemInfo(SDO_GEOMETRY(2007,  -- two-dimensional multi-part polygon with hole
                                       NULL,
                                       NULL,
                                       SDO_ELEM_INFO_ARRAY(1,1005,2, 1,2,1, 5,2,2,
                                                          11,2005,2, 11,2,1, 15,2,2,
                                                          21,1005,2, 21,2,1, 25,2,2),
                                       SDO_ORDINATE_ARRAY(  6,10, 10,1, 14,10, 10,14, 6,10,
                                                           13,10, 10,2, 7,10, 10,13, 13,10,
                                                          106,110, 110,101, 114,110, 110,114,106,110)
                                      )
                          )
             ) e;
 
 OFFSET                 ETYPE                  INTERPRETATION
 ---------------------- ---------------------- ----------------------
 1                      1005                   2
 1                      2                      1
 5                      2                      2
 11                     2005                   2
 11                     2                      1
 15                     2                      2
 21                     1005                   2
 21                     2                      1
 25                     2                      2
 
 9 rows selected

Now, let’s re-write the function using the SQL approach we introduced in part 1.

 Create Or Replace Function GetElemInfo(
   p_geometry in mdsys.sdo_geometry)
   Return T_ElemInfoSetType pipelined
 Is
   v_elements number;
   CURSOR c_elements( p_geometry in mdsys.sdo_geometry) Is
   SELECT e.id,
           e.etype,
           e.offset,
           e.interpretation
     FROM (SELECT trunc((rownum - 1) / 3,0) as id,
                   sum(case when mod(rownum,3) = 1 then sei.column_value else null end) as offset,
                   sum(case when mod(rownum,3) = 2 then sei.column_value else null end) as etype,
                   sum(case when mod(rownum,3) = 0 then sei.column_value else null end) as interpretation
             FROM TABLE(p_geometry.sdo_elem_info) sei
            GROUP BY trunc((rownum - 1) / 3,0)
           ) e;
 Begin
   If ( p_geometry is not null ) Then
     If ( p_geometry.sdo_elem_info is not null) Then
     <<element_extraction>>
     for rec IN c_elements(p_geometry) LOOP
        PIPE ROW ( T_ElemInfoType(
                     rec.offset,
                     rec.etype,
                     rec.interpretation ) );
       end loop element_extraction;
     End If;
   End If;
   Return;
 End GetElemInfo;

Again, testing:

 SELECT e.*
   FROM TABLE(GetElemInfo(SDO_GEOMETRY(2007,  -- two-dimensional multi-part polygon with hole
                                       NULL,
                                       NULL,
                                       SDO_ELEM_INFO_ARRAY(1,1005,2, 1,2,1, 5,2,2,
                                                          11,2005,2, 11,2,1, 15,2,2,
                                                          21,1005,2, 21,2,1, 25,2,2),
                                       SDO_ORDINATE_ARRAY(  6,10, 10,1, 14,10, 10,14, 6,10,
                                                           13,10, 10,2, 7,10, 10,13, 13,10,
                                                          106,110, 110,101, 114,110, 110,114,106,110)
                                      )
                          )
             ) e;
 
 OFFSET                 ETYPE                  INTERPRETATION
 ---------------------- ---------------------- ----------------------
 1                      1005                   2
 1                      2                      1
 5                      2                      2
 11                     2005                   2
 11                     2                      1
 15                     2                      2
 21                     1005                   2
 21                     2                      1
 25                     2                      2
 
 9 rows selected

Note they are identical which shows that there are many ways to code an algorithm in PL/SQL. I personally prefer an SQL approach but others prefer a programmatic approach. One day I will compare the two (as in the above) using Tom Kyte’s RUNSTATS package and the PL/SQL PROFILER.

Till then, I hope this article is of use to someone out there.

Leave a Reply

Your email address will not be published. Required fields are marked *