Go to content Go to navigation and search

Home

Current Oracle Spatial Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Detecting sdo_geometries with compound (3-point Arcs) segments

Tuesday November 03 2009 at 12:50

Keywords3 point circular arc detection hasArc hasCircularArcs isCompound
Summary

A set of functions for detecting both compound and simple linestring/polygon geometries composed of circular arcs.

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.

  1.    CREATE FUNCTION isCompoundElement(p_elem_type IN NUMBER)
  2.     RETURN BOOLEAN
  3.   IS
  4.   BEGIN
  5.     RETURN ( p_elem_type IN (4,5,1005,2005) );
  6.   END isCompoundElement;
  7. .
  8.   CREATE FUNCTION hasCircularArcs(p_elem_info IN mdsys.sdo_elem_info_array)
  9.     RETURN BOOLEAN
  10.   IS
  11.      v_elements  NUMBER;
  12.   BEGIN
  13.      v_elements := ( ( p_elem_info.COUNT / 3 ) - 1 );
  14.      <<element_extraction>>
  15.      FOR v_i IN 0 .. v_elements LOOP
  16.         IF ( ( /* etype */         p_elem_info(v_i * 3 + 2) = 2 AND
  17.                /* interpretation*/ p_elem_info(v_i * 3 + 3) = 2 )
  18.              OR
  19.              ( /* etype */         p_elem_info(v_i * 3 + 2) IN (1003,2003) AND
  20.                /* interpretation*/ p_elem_info(v_i * 3 + 3) IN (2,4) ) ) THEN
  21.                RETURN TRUE;
  22.         END IF;
  23.      END loop element_extraction;
  24.      RETURN FALSE;
  25.   END hasCircularArcs;
  26. .
  27.   CREATE FUNCTION isCompound(p_elem_info IN mdsys.sdo_elem_info_array)
  28.     RETURN INTEGER
  29.   IS
  30.   BEGIN
  31.     RETURN CASE WHEN hasCircularArcs(p_elem_info) THEN 1 ELSE 0 END;
  32.   END isCompound;
  33. .
  34.   FUNCTION hasArc(p_elem_info IN mdsys.sdo_elem_info_array)
  35.     RETURN INTEGER
  36.   IS
  37.   BEGIN
  38.     RETURN CASE WHEN hasCircularArcs(p_elem_info) THEN 1 ELSE 0 END;
  39.   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):

  1. SELECT name AS description,
  2.        hasArc(a.geometry.sdo_elem_info) AS hasArc,
  3.        geometry
  4.   FROM codesys.Oracle_Test_Geometries a
  5.  WHERE a.name LIKE '%Arc%'
  6.     OR a.name LIKE '%Compou%'
  7.     OR a.name LIKE '%Circle%';
  8. .
  9. DESCRIPTION          HASARC GEOMETRY
  10. -------------------- ------ ------------------------------------------------------------------------------------------------------------------------------------
  11. Arc segment          1      SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,2),SDO_ORDINATE_ARRAY(10,15,15,20,20,15))
  12. 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))
  13. 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))
  14. 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))
  15. 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))
  16. Circle               1      SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,4),SDO_ORDINATE_ARRAY(15,145,10,150,20,150))
  17. 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))
  18. .
  19.  7 ROWS selected

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment [2]

Thanks this helped me quite a lot today. Although I implemented the function differently I used your logic so I decided to share it but I don’t know the textile for source code, but in short I loop over the (sdo_elem_info varray)

create or replace function has_compound_curves ( geom_in in sdo_geometry)
return integer as
etype number;
interpret number;
begin
for i in geom_in.sdo_elem_info.first .. geom_in.sdo_elem_info.last loop
case
when mod(i,3) = 1 then continue;
when mod(i,3) = 2 then etype := geom_in.sdo_elem_info(i);
when mod(i,3) = 0
then
interpret := geom_in.sdo_elem_info(i);
if((etype = 2 and interpret = 2) or (etype in (1003,2003) and interpret in (2,4))) then
return 1;
end if;
end case;
end loop;
return 0;
end has_compound_curves;
This runs about 20-30 times faster and answers the same question.

I have no idea if your query up there is what you use in practice or if you simply stated it here for clarity, either way it helped me and since you share I’ll do it as well :)

— Sveinn R. Joelsson · 16 December 2009, 23:55 · #

Sveinn,

I updated the article to replace the SQL based original function with non-SQL based ones similar to your own.

regards
Simon

— Simon Greener · 29 December 2011, 12:28 · #