This discussion is archived
12 Replies Latest reply: Apr 1, 2009 12:06 PM by user597594 RSS

select/update sdo_elem_info

user597594 Newbie
Currently Being Moderated
Hi,
I am trying to select/update the sdo_elem_info in the query where clause from a spatial table. Example:

select a.sdo_elem_info from cloa a where a.sdo_elem_info_array(1,4,1);

Then I would like to update this array. Your help on this will be appreciated.
Thanks, MK
  • 1. Re: select/update sdo_elem_info
    Simon Greener Journeyer
    Currently Being Moderated
    MK,

    SDO_ELEM_INFO is a VARRAY. The only way to query its contents in SQL is to use a table function.
    drop table cloa;
    create table cloa ( fid number, geom mdsys.sdo_geometry);
    insert into cloa values (1, mdsys.sdo_geometry(2002,null,null,mdsys.sdo_elem_info_array(1,2,1),mdsys.sdo_ordinate_array(1,1,2,2)));
    insert into cloa values (2, mdsys.sdo_geometry(2002,null,null,mdsys.sdo_elem_info_array(1,2,2),mdsys.sdo_ordinate_array(2,2,3,3,4,2)));
    commit;
    SELECT fid,
           trunc((rownum - 1) / 3,0) as rid,
           sum(case when mod(rownum,3) = 1 then e.column_value else null end )as offset,
           sum(case when mod(rownum,3) = 2 then e.column_value else null end) as etype,
           sum(case when mod(rownum,3) = 0 then e.column_value else null end) as interpretation
      FROM cloa l, 
           TABLE(l.geom.sdo_elem_info) e
     group by fid, trunc((rownum - 1) / 3,0)
     order by 1,2;
    
    FID                    RID                    OFFSET                 ETYPE                  INTERPRETATION         
    ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
    1                      0                      1                      2                      1                      
    2                      1                      1                      2                      2                      
    
    2 rows selected
    If you wanted all fids whose interpretation is 2 (ie circular arc) then you can query as follows:
    with eqry as (
    SELECT fid,
           trunc((rownum - 1) / 3,0) as rid,
           sum(case when mod(rownum,3) = 1 then e.column_value else null end )as offset,
           sum(case when mod(rownum,3) = 2 then e.column_value else null end) as etype,
           sum(case when mod(rownum,3) = 0 then e.column_value else null end) as interpretation
      FROM cloa l, 
           TABLE(l.geom.sdo_elem_info) e
     group by fid, trunc((rownum - 1) / 3,0)
     order by 1,2
    )
    select *
      from eqry
     where interpretation = 2;
    
    FID                    RID                    OFFSET                 ETYPE                  INTERPRETATION         
    ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
    2                      1                      1                      2                      2                      
    
    1 rows selected
    You can also see an article I wrote on querying and processing SDO_ELEM_INFO_ARRAY.

    Hope this answers your question.

    regards
    Simon
  • 2. Re: select/update sdo_elem_info
    user597594 Newbie
    Currently Being Moderated
    Hi Simon:
    Thanks for the information. I have read your article as well as your replay to this post. I am only intrested in records with sdo_elem_info(1, 4,1). But after I modify your query and run it, I am receiving so many null value for the etype. Is there any way to use the Table function in the where clause of the select statement?
    thanks again for you advise on this.
    Thanks, MK
  • 3. Re: select/update sdo_elem_info
    Simon Greener Journeyer
    Currently Being Moderated
    MK,

    The answer is, AFAIK, that Oracle does not offer a direct way to compare two arrays.

    If you want to find those geometry with sdo_elem_info 1,4,1 I would do this:
    with eqry /* pivot array to reconstruct logical element info triplet in one row */ as (
    SELECT fid,
           trunc((rownum - 1) / 3,0) as rid,
           sum(case when mod(rownum,3) = 1 then e.column_value else null end )as offset,
           sum(case when mod(rownum,3) = 2 then e.column_value else null end) as etype,
           sum(case when mod(rownum,3) = 0 then e.column_value else null end) as interpretation
      FROM cloa l, 
           TABLE(l.geom.sdo_elem_info) e
     group by fid, trunc((rownum - 1) / 3,0)
     order by 1,2
    )
    select fid,
             (select geom from cloa c where c.fid = e.fid)
      from eqry e
     where offset = 1
         and etype = 4
         and interpretation = 1;
    I know it looks involved but you have to remember that the SDO_ELEM_INFO_ARRAY is a flat VARRAY of numbers and not a VARRAY of a logically correct SDO_ELEM_INFO_TYPE that has three elements:

    CREATE OR REPLACE TYPE SDO_Elem_Info_Type AS OBJECT (
    offset NUMBER,
    etype NUMBER,
    interpretation NUMBER);

    So, if you want to query an SDO_ELEM_INFO triplet with the current implementation you have to normalise it through pivoting to be able to do so.

    (It would have been nice that the SDO_ELEM_INFO_ARRAY had then been defined as CREATE OR REPLACE TYPE SDO_Elem_Info_Set_Type AS TABLE OF SDO_Elem_Info_Type; but it wasn't so what we have to work with is what has been given to us.)


    I hope this helps.

    regards
    Simon
  • 4. Re: select/update sdo_elem_info
    Paul Dziemiela Journeyer
    Currently Being Moderated
    Hi folks,

    I don't really recommend this method as its quite easy to overload your memory settings with a large geometry, but if you know your varrays to compare are modest in size and/or you have a lot of memory to spare, you can flatten any varrays within an object into XML easily and then directly compare the resulting CLOBs to each other. I use this type of thing when doing tests where function A version 1 produces SDOs and then function A version 2 should produce the same SDOs.

    FUNCTION sdo_cmp (
    p_input_1 IN MDSYS.SDO_GEOMETRY,
    p_input_2 IN MDSYS.SDO_GEOMETRY
    ) RETURN VARCHAR2
    AS
    clb_input_1 CLOB;
    clb_input_2 CLOB;
    BEGIN

    clb_input_1 := XMLTYPE(p_input_1).getClobVal();
    clb_input_2 := XMLTYPE(p_input_2).getClobVal();

    IF clb_input_1 = clb_input_2
    THEN
    RETURN 'TRUE';
    ELSE
    RETURN 'FALSE';
    END IF;

    END sdo_cmp;

    But note again the varray needs to be inside an object, in this case the SDO_GEOMETRY.

    Cheers,

    Paul

    p.s. does anyone else have a more robust way of doing this in a simple manner?
  • 5. Re: select/update sdo_elem_info
    Simon Greener Journeyer
    Currently Being Moderated
    Paul,

    I'm interested in learning what you mean by:

    "I don't really recommend this method as its quite easy to overload your memory settings with a large geometry"

    What overloading occurs? What memory settings are affected?

    Sdo_Elem_Info's for most of the sdo_geometries I have seen over the past 10+ years don't have large sdo_elem_info_arrays (and pivoting/unpivoting and disaggregating a collection via a table function is well handled by the query optimizer): I could have suggested a pipelined function but for most questioners on this forum I try not to suggest functions/procedures as PL/SQL can be scary for some people.

    regards
    Simon
  • 6. Re: select/update sdo_elem_info
    user597594 Newbie
    Currently Being Moderated
    Gentlmen:
    Thank you very much for your feedback. I am still working on this issue. I will let you know as soon as I cleanup the data.
    Thanks again.
    MK
  • 7. Re: select/update sdo_elem_info
    Paul Dziemiela Journeyer
    Currently Being Moderated
    Simon,

    I mean if you take an SDO GEOMETRY with a 1/2 million vertices which are packed tightly in a SDO_ORDINATE_ARRAY VARRAY and then try the XML flattening trick, you end up with one copy in memory as the XML object and a second copy in memory as the output CLOB (and won't the original VARRAY also be in memory during part of this time?). The XML/XML AS CLOB data structures are much larger then the original arrays. I've seen my smaller machines run out of memory doing this kind of serialization.

    If the idea is to ONLY compare the SDO_ELEM_INFO_ARRAYs, you'd need a different object to stick the VARRAY into to allow the flattening. Its doubtful that would ever overload things but that object wouldn't be particularly useful for anything else.

    Hmm, I am not sure how you can do much with Oracle Spatial without PL/SQL.

    Paul
  • 8. Re: select/update sdo_elem_info
    user597594 Newbie
    Currently Being Moderated
    Simon:
    Thanks for your information. I was able to find the bad geometry records. I am just wondering if it is possible to use the WITH CLAUS to update the sdo_elem_info array?
    Thanks again.
    MK
  • 9. Re: select/update sdo_elem_info
    Simon Greener Journeyer
    Currently Being Moderated
    MK

    Can you give me an example of the sdo_elem_info changes you want to make (before and after)?

    regards
    Simon
  • 10. Re: select/update sdo_elem_info
    user597594 Newbie
    Currently Being Moderated
    Hi Simon:
    I would like to change some records with the wrong SDO_ELEM_INFO_ARRAY. Example:
    I need to change the sdo_elem_info (1,4,1) to sdo_elem_info (1, 2, 1).
    Thanks, Mk
  • 11. Re: select/update sdo_elem_info
    Simon Greener Journeyer
    Currently Being Moderated
    MK,

    Sorry the the delay: been away consulting.

    Here is a non-SQL approach to the Update.
    create or replace
    Function UpdateElemInfo(
        p_elem_info          in mdsys.sdo_elem_info_array,
        p_offset             in number,
        p_etype              in number,
        p_interpretation     in number,
        p_new_offset         in number,
        p_new_etype          in number,
        p_new_interpretation in number)
        Return mdsys.SDO_Elem_Info_Array 
      Is
        v_elements  number;
        v_elem_info mdsys.sdo_elem_info_array;
      Begin
        If ( p_elem_info is not null And
             p_elem_info.COUNT > 0 ) Then
            v_elem_info := new mdsys.sdo_elem_info_array();
            v_elements := ( ( p_elem_info.COUNT / 3 ) - 1 );
            <<element_extraction>>
            for v_i IN 0 .. v_elements LOOP
                v_Elem_Info.extend(3);
                IF ( p_elem_info(v_i * 3 + 1) = p_offset 
                     AND
                     p_elem_info(v_i * 3 + 2) = p_etype
                     AND
                     p_elem_info(v_i * 3 + 3) = p_interpretation ) THEN
                  v_Elem_Info(v_Elem_Info.count-2) := p_new_offset;
                  v_Elem_Info(v_Elem_Info.count-1) := p_new_etype;
                  v_Elem_Info(v_Elem_Info.count  ) := p_new_interpretation;
                ELSE
                  v_Elem_Info(v_Elem_Info.count-2) := p_elem_info(v_i * 3 + 1);
                  v_Elem_Info(v_Elem_Info.count-1) := p_elem_info(v_i * 3 + 2);
                  v_Elem_Info(v_Elem_Info.count  ) := p_elem_info(v_i * 3 + 3);
                END IF;
              end loop element_extraction;
        End If;
        Return case when v_elem_info is null then p_elem_info else v_elem_info end;
      End UpdateElemInfo;
    
    
    select l.geom.sdo_elem_info, 
           UpdateElemInfo(l.geom.sdo_elem_info,1,4,1,1,2,1) as new_elem_info
    /*       sdo_geometry(l.geom.sdo_gtype, l.geom.sdo_srid, l.geom.sdo_point, UpdateElemInfo(l.geom.sdo_elem_info,1,4,1,1,2,1),l.geom.sdo_ordinates) as geom*/
      FROM (Select MDSYS.SDO_GEOMETRY(2002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,4,1,5,2,1),MDSYS.SDO_ORDINATE_ARRAY(1,1,2,2,3,3,4,2)) as geom from dual) l;
    
    GEOM.SDO_ELEM_INFO   NEW_ELEM_INFO
    -------------------- -------------------
    NUMBER(1,4,1,5,2,1)  NUMBER(1,2,1,5,2,1)
    
    1 rows selected
    Now, if you want a pure SQL approach with no programming, then try this.
    drop table cloa;
    create table cloa ( fid number, geom mdsys.sdo_geometry);
    insert into cloa values (1, mdsys.sdo_geometry(2002,null,null,mdsys.sdo_elem_info_array(1,2,1),mdsys.sdo_ordinate_array(1,1,2,2)));
    insert into cloa values (2, mdsys.sdo_geometry(2002,null,null,mdsys.sdo_elem_info_array(1,2,2),mdsys.sdo_ordinate_array(2,2,3,3,4,2)));
    insert into cloa values(3, MDSYS.SDO_GEOMETRY(2002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,4,1,5,2,1),MDSYS.SDO_ORDINATE_ARRAY(1,1,2,2,3,3,4,2)));
    commit;
    
    with eqry /* pivot array to reconstruct logical element info triplet in one row */ as (
    SELECT fid,
           trunc((rownum - 1) / 3,0) as rid,
           sum(case when mod(rownum,3) = 1 then e.column_value else null end )as offset,
           sum(case when mod(rownum,3) = 2 then e.column_value else null end) as etype,
           sum(case when mod(rownum,3) = 0 then e.column_value else null end) as interpretation
      FROM cloa l, 
           TABLE(l.geom.sdo_elem_info) e
     group by fid, trunc((rownum - 1) / 3,0)
     order by 1,2
    )
    SELECT sdo_geometry(c.geom.sdo_gtype,
                        c.geom.sdo_srid,
                        c.geom.sdo_point,
                        CAST(MULTISET(SELECT case when mod(rownum,3)=1 
                                     then case when offset = 1 and etype = 4 then 1 else offset end 
                                     else case when mod(rownum,3)=2 
                                               then case when offset = 1 and etype = 4 then 2 else etype end
                                               else case when mod(rownum,3)=0 
                                                         then case when offset = 1 and etype = 4 then 1 else interpretation end
                                                         else null 
                                                     end 
                                           end 
                                 end 
                           FROM eqry e,
                                (select LEVEL FROM DUAL connect by level < 4)
                          WHERE e.fid = c.fid
                                      ) as mdsys.sdo_elem_info_array
                            ),
                          c.geom.sdo_ordinates) as geom
      from cloa c;
    
    GEOM
    -------------------------------------------------------------------------------------------------------------------
    MDSYS.SDO_GEOMETRY(2002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1,1,2,2))
    MDSYS.SDO_GEOMETRY(2002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,2),MDSYS.SDO_ORDINATE_ARRAY(2,2,3,3,4,2))
    MDSYS.SDO_GEOMETRY(2002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,5,2,1),MDSYS.SDO_ORDINATE_ARRAY(1,1,2,2,3,3,4,2))
    
    3 rows selected
    I hope this helps.

    regards
    Simon
  • 12. Re: select/update sdo_elem_info
    user597594 Newbie
    Currently Being Moderated
    Hi Simon,
    Thank you very much for your help. I am still in the process of testing the update. I will let you know as soon as I get some result.
    Thanks again,
    MK

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points