Top 5 Recent Articles

ARTICLES CATEGORIES

Detecting sdo_geometries with compound (3-point Arcs) segments

Often I have need to know if the linestrings describing an sdo_geometry line or polygon object contain 3-point arc segments or elements.

If a linestring or polygon is described by, or contains, 3-point circular arc elements when:

  • Its sdo_interpretation field in the sdo_elem_info array will contain the values 2 or 4
  • Or its sdo_etype is 4, 1005 or 2005

See Table 2-2 Values and Semantics in SDO_ELEM_INFO in the Oracle documentation.

I have encapsulated my needs into some functions that are included below. The ones returning BOOLEAN are there for use in PL/SQL programming as the BOOLEAN data type is not supported in SQL for Oracle.

 --
 -- isCompoundElement
 CREATE FUNCTION isCompoundElement(p_elem_type IN NUMBER)
   RETURN BOOLEAN
 IS
 BEGIN
   RETURN ( p_elem_type IN (4,5,1005,2005) );
 END isCompoundElement;
 --
 -- hasCircularArcs
 CREATE FUNCTION hasCircularArcs(p_elem_info IN mdsys.sdo_elem_info_array)
   RETURN BOOLEAN
 IS
    v_elements  NUMBER;
 BEGIN
    v_elements := ( ( p_elem_info.COUNT / 3 ) - 1 );
    <<element_extraction>>
    FOR v_i IN 0 .. v_elements LOOP
       IF ( ( /* etype */         p_elem_info(v_i * 3 + 2) = 2 AND
              /* interpretation*/ p_elem_info(v_i * 3 + 3) = 2 )
            OR
            ( /* etype */         p_elem_info(v_i * 3 + 2) IN (1003,2003) AND
              /* interpretation*/ p_elem_info(v_i * 3 + 3) IN (2,4) ) ) THEN
              RETURN TRUE;
       END IF;
    END loop element_extraction;
    RETURN FALSE;
 END hasCircularArcs;
 --
 -- isCompound
 CREATE FUNCTION isCompound(p_elem_info IN mdsys.sdo_elem_info_array)
   RETURN INTEGER
 IS
 BEGIN
   RETURN CASE WHEN hasCircularArcs(p_elem_info) THEN 1 ELSE 0 END;
 END isCompound;
 --
 -- hasArc
 FUNCTION hasArc(p_elem_info IN mdsys.sdo_elem_info_array)
   RETURN INTEGER
 IS
 BEGIN
   RETURN CASE WHEN hasCircularArcs(p_elem_info) THEN 1 ELSE 0 END;
 END hasArc;

To test, we will use the Arc/Circle/Compound sdo_geometry objects in 2.5 Geometry Examples of the Oracle Spatial documentation (that I have written to a table called OracleTestGeometries and which ships with my free PL/SQL packages):

 SELECT name AS description,
        hasArc(a.geometry.sdo_elem_info) AS hasArc,
        geometry
   FROM codesys.Oracle_Test_Geometries a
  WHERE a.name LIKE '%Arc%'
     OR a.name LIKE '%Compou%'
     OR a.name LIKE '%Circle%';
 .
 DESCRIPTION          HASARC GEOMETRY
 -------------------- ------ ------------------------------------------------------------------------------------------------------------------------------------ Arc segment          1      SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,2),SDO_ORDINATE_ARRAY(10,15,15,20,20,15)) Arc string           1      SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,2),SDO_ORDINATE_ARRAY(10,35,15,40,20,35,25,30,30,35)) Compound line string 1      SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,4,3,1,2,1,3,2,2,7,2,1),SDO_ORDINATE_ARRAY(10,45,20,45,23,48,20,51,10,51)) Arc polygon          1      SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,2),SDO_ORDINATE_ARRAY(15,115,20,118,15,120,10,118,15,115)) Compound polygon     1      SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1005,2,1,2,1,7,2,2),SDO_ORDINATE_ARRAY(10,128,10,125,20,125,20,128,15,130,10,128)) Circle               1      SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,4),SDO_ORDINATE_ARRAY(15,145,10,150,20,150)) Compound geometry    0      SDO_GEOMETRY(2004,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3,5,1,1,9,2,1),SDO_ORDINATE_ARRAY(0,0,100,100,50,50,0,0,100,100)) .
  7 ROWS selected