This discussion is archived
1 Reply Latest reply: Aug 7, 2013 1:12 PM by B Hall RSS

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

MRoche Newbie
Currently Being Moderated

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 Explorer
    Currently Being Moderated

    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

Legend

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