1 Reply Latest reply on Aug 7, 2013 8:12 PM by B Hall

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

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

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

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