Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (20)
- All (400)
- Biography (1)
- Blog (45)
- Business Requirements (1)
- Commentary (1)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (4)
- Open Source (18)
- Oracle Spatial and Locator (193)
- Partitioning (1)
- PostGIS (34)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (35)
- Space Curves (9)
- Spatial Database Functions (108)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (15)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
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.