7 Replies Latest reply: Apr 11, 2013 4:27 AM by 1002464 RSS

    populate sdo_ordinate_array from select

    461293
      I'm new so please forgive...

      I've seen other forums on this issue, but they are convoluted and have not helped.

      I've been able to create an Oracle Spatial line layer:

      insert into tracks values(
      1,
      sdo_geometry(
      2002,
      8307,
      null,
      sdo_elem_info_array(1,2,1),
      sdo_ordinate_array(-105,39,-103,40,-101,38,-98,43)
      )
      );

      I now want to be able to populate the sdo_ordinate_array with values from another table, kind of like:

      sdo_ordinate_array((select lat, lon from trackinfo where track = 1))

      My trackinfo table looks like:

      TRACK LAT LON
      ---------- ---------- ----------
      1 102 -4
      1 98 -6
      1 95 -13
      1 96 -16
      1 98 -19

      I know I need to return these records in a comma delimited format that the sde_ordinate_array will like (string, array?) -- I just don't know how.

      Any help is appreciated, thanks.
        • 1. Re: populate sdo_ordinate_array from select
          193996
          SQL> create table junk1 (g mdsys.sdo_geometry);

          Table created.

          SQL> create table junk2 (x number, y number);

          Table created.


          SQL> insert into junk2 values (102, -4);

          1 row created.

          SQL> insert into junk2 values (98, -6);

          1 row created.

          SQL> insert into junk2 values (95, -13);

          1 row created.

          SQL> declare
          2 stmt varchar2(1000) :=
          3 'insert into junk1 values ('||
          4 'mdsys.sdo_geometry(2002,8307,null,'||
          5 'mdsys.sdo_elem_info_array(1,2,1),'||
          6 'mdsys.sdo_ordinate_array(';
          7 begin
          8 for rec in (select * from junk2) loop
          9 stmt := stmt||rec.x||','||rec.y||',';
          10 end loop;
          11 stmt := rtrim(stmt,',')||')))';
          12 execute immediate stmt;
          13 end;
          14 /

          PL/SQL procedure successfully completed.

          SQL> select * from junk1;

          G(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
          --------------------------------------------------------------------------------

          SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(102, -4, 98, -6, 95, -13))

          Hope that helps.
          --David                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
          • 2. Re: populate sdo_ordinate_array from select
            461293
            David,

            Fantastic! It works like a charm -- thank you very much!

            As my table has more than one line segment (defined by points) in it, I was successful in adding in a where clause:

            for rec in (select x, y from junk2 where i =1) loop

            code:
            -----------------------
            create table junk1 (g mdsys.sdo_geometry);

            create table junk2 (i number, x number, y number);
            insert into junk2 values (1, 102, -4);
            insert into junk2 values (1, 98, -6);
            insert into junk2 values (1, 95, -13);
            insert into junk2 values (2, 100, -22);
            insert into junk2 values (2, 102, -28);
            insert into junk2 values (2, 108, -29);

            declare
            stmt varchar2(1000) :=
            'insert into junk1 values ('||
            'mdsys.sdo_geometry(2002,8307,null,'||
            'mdsys.sdo_elem_info_array(1,2,1),'||
            'mdsys.sdo_ordinate_array(';
            begin
            for rec in (select x, y from junk2 where i = 1) loop
            stmt := stmt||rec.x||','||rec.y||',';
            end loop;
            stmt := rtrim(stmt,',')||')))';
            execute immediate stmt;
            end;
            /

            declare
            stmt varchar2(1000) :=
            'insert into junk1 values ('||
            'mdsys.sdo_geometry(2002,8307,null,'||
            'mdsys.sdo_elem_info_array(1,2,1),'||
            'mdsys.sdo_ordinate_array(';
            begin
            for rec in (select x, y from junk2 where i = 2) loop
            stmt := stmt||rec.x||','||rec.y||',';
            end loop;
            stmt := rtrim(stmt,',')||')))';
            execute immediate stmt;
            end;
            /
            -----------------------

            As opposed to above, is it possible to wrap a loop around this, iterating through based on the number of unique values in a column (i)?

            Thanks,
            Glen
            • 3. Re: populate sdo_ordinate_array from select
              193996
              Nest the loops:

              for rec1 in (select distinct i from junk2) loop
              for rec2 in (select * from junk2 where i=rec1.i) loop
              ...construct geometry per distinct i...
              end loop;
              ...insert geometry...
              end loop;
              • 4. Re: populate sdo_ordinate_array from select
                461293
                Beautiful! Thanks David.
                • 5. Re: populate sdo_ordinate_array from select
                  920209
                  just awesom........ thanks so much !!!!!!!!!
                  • 6. Re: populate sdo_ordinate_array from select
                    John O'Toole
                    Hi 917206,

                    You've just replied to a 7 year old thread. Awesome!

                    John
                    • 7. Re: populate sdo_ordinate_array from select
                      1002464
                      Hi
                      Im trying to do the same, but I got error:

                      SQL execution error, ORA-6550: ORA-06550: line 3, column 1:
                      PLS-00201: identifier 'STMT' must be declared
                      ORA-06550: line 3, column 1:
                      PL/SQL: Statement ignored
                      ORA-06550: line 5, column 1:
                      PLS-00201: identifier 'STMT' must be declared
                      ORA-06550: line 5, column 1:
                      PL/SQL: Statement ignored

                      Can anyone help med to solve this?