SpatialDB Advisor

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.