Go to content Go to navigation and search

Home

Current Oracle Spatial Blogs

Search

RSS / Atom

Email me

textpattern

Detecting sdo_geometries with compound (3-point arc'd) segments

· Nov 3, 12:50 pm by Simon Greener

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 is described by, or contains, 3-point arc elements, its sdo_interpretation field in the sdo_elem_info array will contain the value “2” which means (from the Oracle Spatial documentation):

I have encapsulated my needs into a robust function isCompound that pivots the data in the sdo_elem_info array for ease of query and understanding, as follows.

create or replace Function isCompound(p_elem_info in mdsys.sdo_elem_info_array)
    return integer deterministic
  Is
    v_compound_element_count number := 0;
  Begin
    SELECT count(*) as c_element_count
      INTO v_compound_element_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_elem_info) sei
                     GROUP BY trunc((rownum - 1) / 3,0)
                    ) e
           ) i
     WHERE ( i.etype = 2 AND i.interpretation = 2 )
        OR ( i.etype in (1003,2003) AND i.interpretation IN (2,4) );
    Return case when v_compound_element_count > 0 then 1 else 0 end;
  End isCompound;

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 d as description,
       isCompound(a.g.sdo_elem_info) as isCompound,
       g as geom
  from OracleTestGeometries a 
 where d like '%Arc%' 
    or d like '%Compou%' 
    or d like '%Circle%';

DESCRIPTION           ISCOMPOUND GEOM
--------------------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
Arc segment           1          MDSYS.SDO_GEOMETRY(2002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,2),MDSYS.SDO_ORDINATE_ARRAY(10,15,15,20,20,15))
Arc string            1          MDSYS.SDO_GEOMETRY(2002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,2),MDSYS.SDO_ORDINATE_ARRAY(10,35,15,40,20,35,25,30,30,35))
Compound line string  1          MDSYS.SDO_GEOMETRY(2002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,4,3,1,2,1,3,2,2,7,2,1),MDSYS.SDO_ORDINATE_ARRAY(10,45,20,45,23,48,20,51,10,51))
Arc polygon           1          MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,2),MDSYS.SDO_ORDINATE_ARRAY(15,115,20,118,15,120,10,118,15,115))
Compound polygon      1          MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1005,2,1,2,1,7,2,2),MDSYS.SDO_ORDINATE_ARRAY(10,128,10,125,20,125,20,128,15,130,10,128))
Circle                1          MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,4),MDSYS.SDO_ORDINATE_ARRAY(15,145,10,150,20,150))

6 rows selected

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Newsvinepost this at Ma.gnoliapost this at Furlpost this at Blinklistpost this at Spurlpost this at Wistspost this at Simpypost this at Redditpost this at Farkpost this at Blogmarkspost this at Yahoo! my webpost this at Mr. Wongpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

  1. 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    Dec 16, 11:55 pm    #
  Textile Help