1 Reply Latest reply: Aug 7, 2013 3:12 PM by B Hall RSS

    Convert and reorder a 2006 multiline geometry to a 2002 singleline geometry

    MRoche

      I was wondering if it is at all possible to convert and reorder a 2006 geometry type to a 2002 geometry type.

      Here is a very simple example of what I mean

       

      MDSYS.SDO_GEOMETRY(2006,81989,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1, 5,2,1, 9,2,1),MDSYS.SDO_ORDINATE_ARRAY(0,0,1,1, 3,0,2,1, 1,1,2,1))

       

      and convert it to something like

       

      MDSYS.SDO_GEOMETRY(2002,81989,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(0,0,1,1, 1,1,2,1, 2,1,3,0))

       

      Thanks in advanced

        • 1. Re: Convert and reorder a 2006 multiline geometry to a 2002 singleline geometry
          B Hall

          Roche,

           

          Yes this is certainly possible. Here is a crude function that will take your simple multi-line example and make it a single line. Note, it assumes a lot (such as sorting by value, single segment lines, etc) - so it is not a general solution by any means:

           

          CREATE OR REPLACE FUNCTION multiline_to_single (

             in_geom    SDO_GEOMETRY)

             RETURN SDO_GEOMETRY

          IS

             v_ptr         NUMBER := 1;

             v_dimention   NUMBER;

             v_ordinates   sdo_ordinate_array;

          BEGIN

             v_dimention   := SUBSTR (in_geom.sdo_gtype, 1, 1);

             v_ordinates   := NEW mdsys.sdo_ordinate_array (1);

             v_ordinates.delete;

           

           

             FOR i IN (  SELECT UNIQUE t.x x, t.y y

                           FROM TABLE (SDO_UTIL.getvertices (in_geom)) t

                       ORDER BY x, y)

             LOOP

                v_ordinates.EXTEND (v_dimention);

                v_ordinates (v_ptr)       := i.x;

                v_ordinates (v_ptr + 1)   := i.y;

                v_ptr                     := v_ptr + v_dimention;

             END LOOP;

           

           

             RETURN sdo_geometry (TO_NUMBER (v_dimention || '002'),

                                  in_geom.sdo_srid,

                                  NULL,

                                  sdo_elem_info_array (1, 2, 1),

                                  v_ordinates);

          END;

          /


          Calling it with this:


          SELECT multiline_to_single (sdo_geometry (2006,

                                                             81989,

                                                             NULL,

                                                             mdsys.sdo_elem_info_array (

                                                                1,2,1,

                                                                5,2,1,

                                                                9,2,1),

                                                             mdsys.sdo_ordinate_array (

                                                                0,0,

                                                                1,1,

                                                                3,0,

                                                                2,1,

                                                                1,1,

                                                                2,1)))

            FROM DUAL;

           

          Will give you this:

           

          SDO_GEOMETRY(2002, 81989, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0, 0, 1, 1, 2, 1, 3, 0))

           

          Bryan