12 Replies Latest reply: Apr 1, 2009 2:06 PM by user597594 RSS

    select/update sdo_elem_info

    user597594
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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