|
|
SDE_MERGE Download pageScroll to the bottom for a zip. Header:
create or replace type SDE_MERGE_i as object
(
geom mdsys.sdo_geometry,
STATIC FUNCTION ODCIAggregateInitialize
( sctx IN OUT SDE_MERGE_i
) RETURN number,
MEMBER FUNCTION ODCIAggregateIterate
( self IN OUT SDE_MERGE_i
, item IN mdsys.sdo_geometry
) RETURN number,
MEMBER FUNCTION ODCIAggregateTerminate
( self IN SDE_MERGE_i
, ret OUT mdsys.sdo_geometry
, flgs IN number
) RETURN number,
MEMBER FUNCTION ODCIAggregateMerge
( self IN OUT SDE_MERGE_i
, ctx2 IN SDE_MERGE_i
) RETURN number
);
/
Body:
CREATE OR REPLACE TYPE BODY SDE_MERGE_i
IS
STATIC FUNCTION ODCIAggregateInitialize (sctx IN OUT SDE_MERGE_i)
return number
IS
BEGIN
sctx := SDE_MERGE_i( geom => SDO_GEOMETRY( null, NULL, NULL, null, null) );
return( ODCIConst.Success);
END;
MEMBER FUNCTION ODCIAggregateIterate
(
self IN OUT SDE_MERGE_i
, item IN mdsys.sdo_geometry
) return number
IS
BEGIN
/* --- First time we get here we just copy item to self (20170706 SDE) */
if( self.geom.SDO_ORDINATES is null)
then
SELF.geom := item;
return( ODCIConst.Success);
end if;
SELF.geom := sdo_util.Append ( SELF.geom, item);
return( ODCIConst.Success);
END;
MEMBER FUNCTION ODCIAggregateTerminate
(
self IN SDE_MERGE_i
, ret OUT mdsys.sdo_geometry
, flgs IN number
) return number
IS
cC0 CONSTANT int := 0;
cC1 CONSTANT int := 1;
cC2 CONSTANT int := 2;
cC3 CONSTANT int := 3;
cC4 CONSTANT int := 4;
tBuf varchar( 1000);
TYPE t_Numbers IS TABLE OF integer INDEX BY BINARY_INTEGER;
TYPE t_geomInfoRecord IS RECORD
(
geom mdsys.sdo_geometry
, hash int
, Prev int
, prevItems t_Numbers
, Next int
, nextItems t_Numbers
, isStartingGeo int
);
TYPE t_geomInfo IS TABLE OF t_geomInfoRecord INDEX BY BINARY_INTEGER;
geomInfo t_geomInfo;
/**
* @name getHashCode
* @author S.Deckers
* @date 26-07-2017
* @description
*/
FUNCTION GETHASHCODE( theGeom mdsys.sdo_geometry)
return int
AS
p1 mdsys.sdo_geometry;
p2 mdsys.sdo_geometry;
hash int;
BEGIN
p1 := SDO_LRS.GEOM_SEGMENT_START_PT ( theGeom);
p2 := SDO_LRS.GEOM_SEGMENT_END_PT ( theGeom);
hash := p1.sdo_ordinates( 1) + p1.sdo_ordinates( 2) + p2.sdo_ordinates( 1) + p2.sdo_ordinates( 2);
return( hash);
END;
/**
* @name Swap
* @author S.Deckers
* @date 08-AUG-2017
* @description Swaps geometry
*/
FUNCTION Swap( g mdsys.sdo_geometry) return mdsys.sdo_geometry
as
swapped mdsys.sdo_geometry := new mdsys.sdo_geometry( SDO_GTYPE => g.SDO_GTYPE
, SDO_SRID => g.SDO_SRID
, SDO_POINT => g.SDO_POINT
, SDO_ELEM_INFO => g.SDO_ELEM_INFO
, SDO_ORDINATES => new mdsys.SDO_ORDINATE_ARRAY( ) );
k int := 1;
dims int := 3;
cnt int;
x int;
y int;
z int;
begin
cnt := g.SDO_ORDINATES.COUNT / dims;
for i in REVERSE 1..cnt
loop
x := (i-1)*dims+1;
y := (i-1)*dims+2;
z := (i-1)*dims+3;
swapped.sdo_ordinates.Extend( dims);
swapped.sdo_ordinates( k) := g.sdo_ordinates( x);
k:=k+1;
swapped.sdo_ordinates( k) := g.sdo_ordinates( y);
k:=k+1;
swapped.sdo_ordinates( k) := g.sdo_ordinates( z);
k:=k+1;
end loop;
return( swapped);
END;
/**
* @name getStartingGeo
* @author S.Deckers
* @date 08-AUG-2017
* @description return index starting geo
*/
FUNCTION getStartingGeo return int
AS
BEGIN
for i in 1..geomInfo.COUNT
loop
if( geomInfo( i).isStartingGeo = 1) then
return( i);
end if;
end loop;
raise_application_error( -20101, 'getStartingGeo:Unsupported situation');
END;
/**
* @name Sort
* @author S.Deckers
* @date 09-08-2017
* @description Sorts geometries and swaps direction if needed
*/
PROCEDURE Sort
AS
n int := 1; -- Loop counter
i int;
tNext int;
tNextNext int;
tVal int;
/**
* @name simplifyStructure
* @author S.Deckers
* @date 01-Sept-2017
* @description The initial structure contains geometries pointing to connected geometries. If a geometry is connected
* to multiple geometries, its previousItems/nextItems have more then 1 member. If this is the case, the set of
* geometries cannot be merged. At this point in code, only single connections exist and we can simplify the structure
* to make manipulaton of it easier. Since the previous-/nextItems collection only contain a single item, we store it
* in Next/Prev members
*/
PROCEDURE simplifyStructure
AS
BEGIN
for x in 1..geomInfo.COUNT
loop
/* --- Previous items (20170901 SDE) */
if( geomInfo( x).prevItems.COUNT > 1) then
tBuf := utl_lms.format_message( 'simplifyStructure:prevItems contains %s items',
TO_CHAR( geomInfo( x).prevItems.COUNT));
raise_application_error( -20102, tBuf);
end if;
if( geomInfo( x).prevItems.COUNT = 0) then
geomInfo( x).Prev := 0;
else
geomInfo( x).Prev := geomInfo( x).prevItems( 1);
end if;
/* --- Next items (20170901 SDE) */
if( geomInfo( x).nextItems.COUNT > 1) then
tBuf := utl_lms.format_message( 'simplifyStructure:nextItems contains %s items',
TO_CHAR( geomInfo( x).nextItems.COUNT));
raise_application_error( -20103, tBuf);
end if;
if( geomInfo( x).nextItems.COUNT = 0) then
geomInfo( x).Next := 0;
else
geomInfo( x).Next := geomInfo( x).nextItems( 1);
end if;
end loop;
/* --- At this point the prev-/nextItems collections have no more use (20170901 SDE)*/
for x in 1..geomInfo.COUNT
loop
geomInfo( x).prevItems.Delete;
geomInfo( x).nextItems.Delete;
end loop;
END;
/**
* @name setStartingGeo
* @author S.Deckers
* @date 01-Sept-2017
* @description Set starting geometry for creating a sorted collection of geometries
*/
PROCEDURE setStartingGeo
AS
BEGIN
for x in 1..geomInfo.COUNT
loop
if( geomInfo( x).Prev=0) then
geomInfo( x).isStartingGeo := 1;
exit;
end if;
if( geomInfo( x).Next=0) then
geomInfo( x).isStartingGeo := 1;
exit;
end if;
end loop;
END;
BEGIN
simplifyStructure ( );
setStartingGeo ( );
i := getStartingGeo();
/* --- Begin at Starting geo and get next until all geometries are retrieved (20170808 SDE) */
LOOP
tNext := geomInfo( i).Next;
if( n = 1)
then
-- Swap first item
if( tNext = 0) then
geomInfo( i).geom := Swap( geomInfo( i).geom);
tVal := geomInfo( i).Next;
geomInfo( i).Next := geomInfo( i).Prev;
geomInfo( i).Prev := tVal;
tNext := geomInfo( i).Next;
n := n+1;
end if;
else
-- End of list
if( tNext = 0) then
return;
end if;
end if;
tNextNext := geomInfo( tNext).Next;
if( i = tNextNext) then
-- Swap item
geomInfo( tNext).geom := Swap( geomInfo( tNext).geom);
tVal := geomInfo( tNext).Next;
geomInfo( tNext).Next := geomInfo( tNext).Prev;
geomInfo( tNext).Prev := tVal;
end if;
i := geomInfo( i).Next;
n := n+1;
END LOOP;
END;
/**
* @name dumpOords
* @author S.Deckers
* @date 08-08-2017
* @description
*/
PROCEDURE dumpOords( sdo_ordinates SDO_ORDINATE_ARRAY)
as
begin
for i in 1..sdo_ordinates.COUNT
loop
tBuf := utl_lms.format_message( 'i=%s v=%s', TO_CHAR( i), TO_CHAR( sdo_ordinates( i)));
dbms_output.put_line( tBuf);
end loop;
END;
/**
* @name dumpCoordinates
* @author S.Deckers
* @date 08-08-2017
* @description
*/
PROCEDURE dumpCoordinates( sdo_ordinates SDO_ORDINATE_ARRAY)
as
x number;
y number;
z number;
dims number := 3;
BEGIN
for i in 1..sdo_ordinates.COUNT / dims
loop
x := sdo_ordinates( i*3-2);
y := sdo_ordinates( i*3-1);
z := sdo_ordinates( i*3);
tBuf := utl_lms.format_message( 'i=%s, x=%s, y=%s, z=%s', TO_CHAR( i), TO_CHAR( x), TO_CHAR( y), TO_CHAR( z) );
dbms_output.put_line( tBuf);
end loop;
END;
/**
* @name dumpElemInfo
* @author S.Deckers
* @date 08-08-2017
* @description
*/
procedure dumpElemInfo( sdo_elem_info SDO_ELEM_INFO_ARRAY)
as
begin
for i in 1..sdo_elem_info.COUNT
loop
tBuf := utl_lms.format_message( 'i=%s v=%s', TO_CHAR( i), TO_CHAR( sdo_elem_info( i)));
dbms_output.put_line( tBuf);
end loop;
end;
/**
* @name getPrevUnConnectedCount
* @description return # of geometries connected on 'Prev'-side
*/
function getPrevConnectedCount( pos int) return int
as
tResult int := 0;
tVal int := 0;
begin
for n in 1..geomInfo( pos).prevItems.COUNT
loop
tVal := geomInfo( pos).prevItems( n);
tResult := tResult+1;
end loop;
return( tResult);
end;
/**
* @name getNextConnectedCount
* @description return # of geometries connected on 'Next'-side
*/
function getNextConnectedCount( pos int) return int
as
tResult int := 0;
tVal int := 0;
begin
for n in 1..geomInfo( pos).nextItems.COUNT
loop
tResult := tResult+1;
end loop;
return( tResult);
end;
/**
* @name dumpUnSimplified
* @purpose dumpUnSimplified structure
*/
PROCEDURE DumpSingleConnected
AS
res1 int;
res2 int;
BEGIN
dbms_output.put_line( 'dumpSingleConnected');
for i in 1..geomInfo.COUNT
loop
res1 := getPrevConnectedCount( pos => i);
res2 := getNextConnectedCount( pos => i);
if( res1 != 0 and res2 != 0)
then
tBuf := utl_lms.format_message( 'i=%s, hash=%s, nextCount=%s, prevCount=%s',
TO_CHAR( i), TO_CHAR( geomInfo( i).hash), TO_CHAR( res1), TO_CHAR( res2));
dbms_output.put_line( tBuf);
continue;
end if;
tBuf := utl_lms.format_message( 'i=%s, hash=%s', TO_CHAR( i), TO_CHAR( geomInfo( i).hash));
dbms_output.put_line( tBuf);
end loop;
END;
/**
* @name Dump GeoInfo structure
* @author S.Deckers
* @date 16-07-2017
* @description
*/
PROCEDURE Dump
AS
tPrevUnConnectedCount int := 0;
tNextUnConnectedCount int := 0;
/**
* @name collectionIsSimplified
* @purpose If prev-/nextItems not exists the collection is simplified
*/
FUNCTION collectionIsSimplified return BOOLEAN
AS
BEGIN
for i in 1..geomInfo.COUNT
loop
if( geomInfo( i).nextItems.COUNT > 0) then
return( FALSE);
end if;
if( geomInfo( i).prevItems.COUNT > 0) then
return( FALSE);
end if;
end loop;
return( TRUE);
END;
/**
* @name DumpPrev
* @purpose Dump previous members
*/
PROCEDURE DumpPrev( pos int)
AS
tVal int;
BEGIN
tBuf := utl_lms.format_message( ' DumpPrev:%s items for pos:%s', TO_CHAR( geomInfo( pos).prevItems.COUNT), TO_CHAR( pos));
dbms_output.put_line( tBuf);
for n in 1..geomInfo( pos).prevItems.COUNT
loop
tVal := geomInfo( pos).prevItems( n);
tBuf := utl_lms.format_message( ' n=%s, Prev=%s', TO_CHAR( n), TO_CHAR( tVal));
dbms_output.put_line( tBuf);
end loop;
END;
/**
* @name DumpNext
* @purpose Dump next members
*/
PROCEDURE DumpNext( pos int)
AS
tVal int;
BEGIN
tBuf := utl_lms.format_message( ' DumpNext:%s items for pos:%s', TO_CHAR( geomInfo( pos).nextItems.COUNT), TO_CHAR( pos));
dbms_output.put_line( tBuf);
for n in 1..geomInfo( pos).nextItems.COUNT
loop
tVal := geomInfo( pos).nextItems( n);
tBuf := utl_lms.format_message( ' n=%s, Next_=%s', TO_CHAR( n), TO_CHAR( tVal));
dbms_output.put_line( tBuf);
end loop;
END;
/**
* @name dumpUnSimplified
* @purpose dumpUnSimplified structure
*/
PROCEDURE dumpUnSimplified
AS
BEGIN
dbms_output.put_line( 'dumpUnSimplified');
for i in 1..geomInfo.COUNT
loop
tPrevUnConnectedCount := getPrevConnectedCount( pos => i);
tNextUnConnectedCount := getNextConnectedCount( pos => i);
tBuf := utl_lms.format_message( 'i=%s', TO_CHAR( i));
tBuf := tBuf || utl_lms.format_message( ', prevCount=%s', TO_CHAR( tPrevUnConnectedCount));
tBuf := tBuf || utl_lms.format_message( ', nextCount=%s', TO_CHAR( tNextUnConnectedCount));
tBuf := tBuf || utl_lms.format_message( ', isStartingGeo=%s', TO_CHAR( geomInfo( i).isStartingGeo));
dbms_output.put_line( tBuf);
DumpPrev( pos => i);
DumpNext( pos => i);
end loop;
END;
/**
* @name dumpSimplified
* @purpose dumpSimplified structure
*/
PROCEDURE dumpSimplified
AS
BEGIN
dbms_output.put_line( 'Simplified');
for i in 1..geomInfo.COUNT
loop
tBuf := utl_lms.format_message( 'i=%s', TO_CHAR( i));
tBuf := tBuf || utl_lms.format_message( ', hash=%s', TO_CHAR( geomInfo( i).hash));
tBuf := tBuf || utl_lms.format_message( ', next=%s', TO_CHAR( geomInfo( i).next));
tBuf := tBuf || utl_lms.format_message( ', previous=%s', TO_CHAR( geomInfo( i).Prev));
tBuf := tBuf || utl_lms.format_message( ', isStartingGeo=%s', TO_CHAR( geomInfo( i).isStartingGeo));
dbms_output.put_line( tBuf);
end loop;
END;
BEGIN
if( collectionIsSimplified() = TRUE) then
dumpSimplified();
return;
end if;
dumpUnSimplified( );
END;
/**
* @name getConnectionInfo
* @description Given 2 connected geometries determine how they are connected, t.i. start connected to endpoint etc..
*/
FUNCTION getConnectionInfo
(
a in mdsys.sdo_geometry
, b in mdsys.sdo_geometry
) return int
AS
p1 mdsys.sdo_geometry;
p2 mdsys.sdo_geometry;
tResult varchar( 20);
cTol constant number := 0.2;
BEGIN
/* --- C1: A o----o o----o B A drawn left to right, B left to right -> endpoint A connected to startpoint B (20170726 SDE)
1 -> 2 1 -> 2 */
select SDO_LRS.GEOM_SEGMENT_END_PT( a) into p1 from dual;
select SDO_LRS.GEOM_SEGMENT_START_PT( b) into p2 from dual;
select sdo_geom.relate( SDO_CS.MAKE_2D( p1), 'DETERMINE', SDO_CS.MAKE_2D( p2), cTol) into tResult from dual;
if( tResult = 'EQUAL')
then
return( cC1);
end if;
/* --- C2: A o----o o----o B A drawn right to left, B right to left -> startpoint A connected to endpoint B (20170726 SDE)
2 <- 1 2 <- 1 */
select SDO_LRS.GEOM_SEGMENT_START_PT( A) into p1 from dual;
select SDO_LRS.GEOM_SEGMENT_END_PT( b) into p2 from dual;
select sdo_geom.relate( SDO_CS.MAKE_2D( p1), 'DETERMINE', SDO_CS.MAKE_2D( p2), cTol) into tResult from dual;
if( tResult = 'EQUAL')
then
return( cC2);
end if;
/* --- C3: A o----o o----o B A drawn left to right, B right to left -> Endpoints connected (20170726 SDE)
1 -> 2 2 <- 1 */
select SDO_LRS.GEOM_SEGMENT_END_PT( A) into p1 from dual;
select SDO_LRS.GEOM_SEGMENT_END_PT( b) into p2 from dual;
select sdo_geom.relate( SDO_CS.MAKE_2D( p1), 'DETERMINE', SDO_CS.MAKE_2D( p2), cTol) into tResult from dual;
if( tResult = 'EQUAL')
then
return( cC3);
end if;
/* --- C4: A o----o o----o B A drawn right to left, B left to right -> Startpoints connected (20170726 SDE)
2 <- 1 1 -> 2 */
select SDO_LRS.GEOM_SEGMENT_START_PT( A) into p1 from dual;
select SDO_LRS.GEOM_SEGMENT_START_PT( b) into p2 from dual;
select sdo_geom.relate( SDO_CS.MAKE_2D( p1), 'DETERMINE', SDO_CS.MAKE_2D( p2), cTol) into tResult from dual;
if( tResult = 'EQUAL')
then
return( cC4);
end if;
/* --- No connection (20170929 SDE) */
return( cC0);
END;
/**
* @name setConnectionInfo
* @description Fill structure with info how geometries are connected
*/
PROCEDURE setConnectionInfo
as
k int := 1;
theCase int;
BEGIN
for i in 1..geomInfo.COUNT
loop
for n in 1..geomInfo.COUNT
loop
/* --- Skip self (20170804 SDE) */
if( n = i) then
continue;
end if;
/* --- Only continue if we have connected geometries (20170804 SDE) */
if( getConnectionInfo( a=>geominfo( i).geom, b=>geominfo( n).geom) = cC0 ) then
continue;
end if;
/* --- Get connectioninfo (20170804 SDE) */
theCase := getConnectionInfo( A=>geomInfo( i).geom, b=>geomInfo( n).geom);
CASE( theCase)
/* --- C1: A o----o o----o B A drawn left to right, B left to right -> endpoint A connected to startpoint B (20170726 SDE)
1 -> 2 1 -> 2 */
WHEN( cC1) THEN
BEGIN
geomInfo( i).nextItems( geomInfo( i).nextItems.COUNT+1) := n;
END;
/* --- C2: A o----o o----o B A drawn right to left, B right to left -> startpoint A connected to endpoint B (20170726 SDE)
2 <- 1 2 <- 1 */
WHEN( cC2) then
BEGIN
geomInfo( i).prevItems( geomInfo( i).prevItems.COUNT+1) := n;
END;
/* --- C3: A o----o o----o B A drawn left to right, B right to left -> Endpoints connected (20170726 SDE)
1 -> 2 2 <- 1 */
WHEN( cC3) THEN
BEGIN
geomInfo( i).nextItems( geomInfo( i).nextItems.COUNT+1) := n;
END;
/* --- C4: A o----o o----o B A drawn right to left, B left to right -> Startpoints connected (20170726 SDE)
2 <- 1 1 -> 2 */
WHEN( cC4) THEN
BEGIN
geomInfo( i).prevItems( geomInfo( i).prevItems.COUNT+1) := n;
END;
/* --- Can't handle this (20170726 SDE) */
END CASE;
k := k + 1;
end loop;
end loop;
END;
/**
* @name multipleConnectedGeometryCount
* @description return # of geometries with more then 2 connections on Prev/Next. If these geometries
* exists a Merged geometry cannot be created :
1 2 3
o-->--o o-->--o-->--o
\ |
\ |
5 \ | 4
\ |
\|
o
*/
FUNCTION multipleConnectedGeometryCount return integer
AS
prev_cnt integer := 0;
next_cnt integer := 0;
theCount integer := 0;
geoCount integer := 0;
BEGIN
for i in 1..geomInfo.COUNT
loop
prev_cnt := getPrevConnectedCount( pos => i);
if( prev_cnt > 1) then
theCount := theCount + 1;
geoCount := geoCount + 1;
continue;
end if;
next_cnt := getNextConnectedCount( pos => i);
if( next_cnt > 1) then
theCount := theCount + 1;
geoCount := geoCount + 1;
end if;
end loop;
return( geoCount);
END;
/**
* @name getSingleConnectedCount
* @description return # of geometries connected on a single side. We need 2 for sorting, the start- and end geometry
*/
FUNCTION getSingleConnectedCount return integer
as
res1 integer := 0;
res2 integer := 0;
theCount integer := 0;
BEGIN
for i in 1..geomInfo.COUNT
loop
res1 := getPrevConnectedCount( pos => i);
if( res1 = 0) then
theCount := theCount + 1;
end if;
res2 := getNextConnectedCount( pos => i);
if( res2 = 0) then
theCount := theCount + 1;
end if;
end loop;
return( theCount);
END;
/**
* @name fillTempStructure
* @description Fills temporary structure with geometries
*/
PROCEDURE fillTempStructure
(
theGeom IN mdsys.sdo_geometry
, geoCount OUT integer
)
AS
cnt int;
gtype int := theGeom.sdo_gtype;
tripletCount int;
BEGIN
geoCount := 0;
/* --- If our temp geometry is a linestring/curve it can only be a single element (20170808 SDE) */
if( gtype = 3002) then
tBuf := utl_lms.format_message( 'gtype=3002, single geometry encountered');
dbms_output.put_line( tBuf);
geoCount := 1;
return;
end if;
cnt := theGeom.sdo_elem_info.COUNT;
tripletCount := cnt / 3;
DECLARE
elemInfoCounter int := 1;
triplet int := 0;
sdo_starting_offset number;
sdo_etype number;
sdo_interpretation number;
tGeom mdsys.sdo_geometry;
BEGIN
while elemInfoCounter < theGeom.sdo_elem_info.COUNT
loop
triplet := elemInfoCounter;
if( triplet > 1) then
triplet := (triplet - 1)/3 + 1;
end if;
-- get the sdo_elem_info elements (20170706 SDE)
sdo_starting_offset := theGeom.sdo_elem_info( elemInfoCounter);
sdo_etype := theGeom.sdo_elem_info( elemInfoCounter+1);
sdo_interpretation := theGeom.sdo_elem_info( elemInfoCounter+2);
/* --- If we encounter a Compound line the # of geometries making up this linestring is at the second position.*/
if( sdo_etype = 4) then
DECLARE
geoCount2 number := sdo_interpretation; -- # geometries in compound linestring
firstOffset number := elemInfoCounter+3;
lastOffset number := (geoCount2-1)*3+firstOffset;
firstValue number := theGeom.sdo_elem_info( firstOffset);
lastValue number := theGeom.sdo_elem_info( lastOffset);
BEGIN
elemInfoCounter := lastOffset+3;
END;
geoCount := geoCount + 1;
select SDO_UTIL.EXTRACT( theGeom, geoCount) into tGeom from dual;
geomInfo( geoCount).geom := tGeom;
geomInfo( geoCount).isStartingGeo := 0;
geomInfo( geoCount).hash := GETHASHCODE( tGeom);
continue;
end if;
-- Increase elemInfoCounter (20170706 SDE)
elemInfoCounter := elemInfoCounter+3;
geoCount := geoCount + 1;
select SDO_UTIL.EXTRACT( theGeom, geoCount) into tGeom from dual;
geomInfo( geoCount).geom := tGeom;
geomInfo( geoCount).isStartingGeo := 0;
geomInfo( geoCount).hash := GETHASHCODE( tGeom);
end loop;
end;
END;
/**
* @name collectionIs3D
* @description return TRUE if all the geometries are 3d
*/
FUNCTION collectionIs3D return boolean
as
BEGIN
for i in 1..geomInfo.COUNT
loop
if( geomInfo( i).geom.get_dims() = 2) then
return( FALSE);
end if;
end loop;
return( TRUE);
END;
/**
* @name Merge
* @author S.Deckers
* @date 09-Aug-2017
* @description Merge 2 geometries
*/
function Merge( geom1 mdsys.sdo_geometry, geom2 mdsys.sdo_geometry)
return mdsys.sdo_geometry
as
v_o1 number; -- sdo_starting_offset
v_e1 number; -- sdo_etype
v_i1 number; -- sdo_interpretation
v_o2 number;
v_e2 number;
v_i2 number;
elemInfo mdsys.sdo_elem_info_array := new mdsys.sdo_elem_info_array ( );
ordinates mdsys.sdo_ordinate_array := new mdsys.sdo_ordinate_array ( );
merged mdsys.sdo_geometry;
/**
* @name MergeOrdinates
* @author S.Deckers
* @date 01-Sept-2017
* @description
*/
function mergeOrdinates( n1 mdsys.sdo_ordinate_array, n2 mdsys.sdo_ordinate_array)
return mdsys.sdo_ordinate_array
as
n3 mdsys.sdo_ordinate_array := new mdsys.sdo_ordinate_array( );
begin
for i in 1..n1.COUNT - 3
loop
n3.extend();
n3( n3.COUNT) := n1( i);
end loop;
for i in 1..n2.COUNT
loop
n3.extend();
n3( n3.COUNT) := n2( i);
end loop;
return( n3);
end;
/**
* @name isSupportedGeometry
* @author S.Deckers
* @date 01-Sept-2017
* @return
* o TRUE supported geometry
* o FALSE unsupported geometry
*/
function isSupportedGeometry( etype int, interpretation int) return boolean
as
begin
/* --- Line */
if( etype = 2 and interpretation = 1 ) then
return( TRUE);
end if;
/* --- Arc */
if( etype = 2 and interpretation = 2 ) then
return( TRUE);
end if;
/* --- Compound linestring */
if( etype = 4 ) then
return( TRUE);
end if;
tBuf := utl_lms.format_message( 'Unsupported:etype=%s, interpretation=%s', TO_CHAR( etype), TO_CHAR( interpretation));
dbms_output.put_line( tBuf);
return( FALSE);
end;
/**
* @name getMergedElemInfo
* @author S.Deckers
* @date 01-Sept-2017
* @description get resulting elemInfo for 2 merging eleminfos. We need to support for the following merge-cases :
* # elem1 elem2 merged
* -- ----- ----- ------
* 1 line line CS
* 2 line CS CS
* 3 CS line CS
* 4 Arc Line CS
* 5 Line Arc CS
* 6 Arc Arc CS
* 7 CS CS CS
* 8 CS Arc CS
* 9 Arc CS CS
*/
procedure getMergedElemInfo
(
e1 in mdsys.sdo_elem_info_array
, e2 in mdsys.sdo_elem_info_array
, e3 out mdsys.sdo_elem_info_array
)
AS
etype1 int := 0;
interpr1 int := 0;
etype2 int := 0;
interpr2 int := 0;
BEGIN
/* --- Line + Line -> Line (20170901 SDE) */
etype1 := e1( 2);
interpr1 := e1( 3);
etype2 := e2( 2);
interpr2 := e2( 3);
if( (etype1 = 2 and interpr1 = 1) AND (etype2 = 2 and interpr2 = 1) ) then
e3 := new mdsys.sdo_elem_info_array( );
e3.extend( 3);
e3( 1) := 1; -- offset=1
e3( 2) := 2; -- etype=2
e3( 3) := 1; -- interpr= 1
return;
end if;
/* --- Line + Compound linestring -> Compound linestring (20170901 SDE) */
if( (etype1 = 2 and interpr1 = 1) AND (etype2 = 4)) then
DECLARE
elemCount int := e2( 3) + 1;
offSet int := geom1.sdo_ordinates.COUNT() - 3;
newSize int := e1.COUNT() + e2.COUNT();
n int;
BEGIN
e3 := new mdsys.sdo_elem_info_array( );
e3.extend( newSize);
e3( 1) := 1;
e3( 2) := 4;
e3( 3) := elemCount;
-- the Line (20170901 SDE)
e3( 4) := 1;
e3( 5) := 2;
e3( 6) := 1;
-- Copy Compound Linestring (20170901 SDE)
n := 7;
for i in 4..e2.COUNT() loop
if (MOD( n-1, 3) = 0 ) then
e3( n) := e2( i) + offSet;
n := n + 1;
continue;
end if;
e3( n) := e2( i);
n := n + 1;
end loop;
END;
return;
end if;
/* --- Compound linestring + Line -> Compound linestring (20170901 SDE) */
if( (etype1 = 4) AND (etype2 = 2 and interpr2 = 1) ) then
e3 := geom1.sdo_elem_info;
e3.extend( 3);
e3( 3) := geom1.sdo_elem_info( 3) + 1; --- Increase # of elements with 1
e3( e3.COUNT() - 2 ) := geom1.sdo_ordinates.COUNT() - 2; --- Set new offset
e3( e3.COUNT() - 1 ) := 2; --- It is a line
e3( e3.COUNT()) := 1;
return;
end if;
/* --- ARC + LINE -> Compound linestring (20170912 SDE)
type elem_info ordinates
----- ------------------- -----------------------------------
Arc 1,2,2 1,10,0, 10,8,0, 15,10,0
line 1,2, 15,10,0, 20,10,0
merged 1,4,2 1,2,2, 7,2,1 1,10,0, 10,8,0, 15,10,0, 20,10,0
*/
if( (etype1 = 2 and interpr1 = 2) AND (etype2 = 2 and interpr2 = 1) ) then
e3 := new mdsys.sdo_elem_info_array( );
e3.extend( 9);
e3( 1) := 1;
e3( 2) := 4;
e3( 3) := 2;
-- Arc
e3( 4) := 1;
e3( 5) := 2;
e3( 6) := 2;
-- Linestring
e3( 7) := 7;
e3( 8) := 2;
e3( 9) := 1;
return;
end if;
/* --- LINE + ARC -> Compound linestring (20170912 SDE) */
if( (etype1 = 2 and interpr1 = 1) AND (etype2 = 2 and interpr2 = 2) ) then
e3 := new mdsys.sdo_elem_info_array( );
e3.extend( 9);
e3( 1) := 1;
e3( 2) := 4;
e3( 3) := 2;
-- Linestring
e3( 4) := 1;
e3( 5) := 2;
e3( 6) := 1;
-- Arc
e3( 7) := geom1.sdo_ordinates.COUNT() - 2;
e3( 8) := 2;
e3( 9) := 2;
return;
end if;
/* --- ARC + ARC -> Compound linestring (20170912 SDE) */
if( (etype1 = 2 and interpr1 = 2) AND (etype2 = 2 and interpr2 = 2) ) then
e3 := new mdsys.sdo_elem_info_array( );
e3.extend( 9);
e3( 1) := 1;
e3( 2) := 4;
e3( 3) := 2;
-- Arc1
e3( 4) := 1;
e3( 5) := 2;
e3( 6) := 2;
-- Arc2
e3( 7) := geom1.sdo_ordinates.COUNT() - 2;
e3( 8) := 2;
e3( 9) := 2;
return;
end if;
/* --- CS + CS -> CS (20170901 SDE) */
if( (etype1 = 4) AND (etype2 = 4) ) then
DECLARE
elemCount int := e1( 3) + e2 ( 3);
offSet int := geom1.sdo_ordinates.COUNT() - 3;
newSize int := e1.COUNT() + e2.COUNT() - 3;
n int;
BEGIN
e3 := new mdsys.sdo_elem_info_array( );
e3.extend( newSize);
e3( 1) := 1;
e3( 2) := 4;
e3( 3) := elemCount;
for i in 4..e1.COUNT() loop
e3( i) := e1( i);
n := i;
end loop;
n:=n+1;
for i in 4..e2.COUNT() loop
-- Offset, we need to modify this (20170901 SDE)
if( MOD( n-1, 3) = 0) then
e3( n) := e2( i) + offSet;
n:=n+1;
continue;
end if;
-- The element, just copy it (20170901 SDE)
e3( n) := e2( i);
n := n + 1;
end loop;
END;
return;
end if;
/* --- Compound linestring + Arc -> Compound linestring (20170901 SDE) */
if( (etype1 = 4) AND (etype2 = 2 and interpr2 = 2) ) then
e3 := geom1.sdo_elem_info;
e3.extend( 3);
e3( 3) := geom1.sdo_elem_info( 3) + 1; --- Increase # of elements with 1
e3( e3.count() - 2 ) := geom1.sdo_ordinates.count() - 2; --- Set new offset
e3( e3.count() - 1 ) := 2; --- It is a Arc
e3( e3.count()) := 2;
return;
end if;
/* --- Arc + Compound linestring -> Compound linestring (20170930 SDE) */
if( (etype1 = 2 and interpr1 = 2) AND (etype2 = 4)) then
DECLARE
elemCount int := e2( 3) + 1;
offSet int := geom1.sdo_ordinates.count() - 3;
newSize int := e1.count() + e2.count();
n int;
BEGIN
e3 := new mdsys.sdo_elem_info_array( );
e3.extend( newSize);
e3( 1) := 1;
e3( 2) := 4;
e3( 3) := elemCount;
-- the Arc (20170930 SDE)
e3( 4) := 1;
e3( 5) := 2;
e3( 6) := 2;
-- Copy Compound Linestring (20170901 SDE)
n := 7;
for i in 4..e2.count() loop
if (MOD( n-1, 3) = 0 ) then
e3( n) := e2( i) + offSet;
n := n + 1;
continue;
end if;
e3( n) := e2( i);
n := n + 1;
end loop;
END;
return;
end if;
/* --- Unsupported combination (20170901 SDE) */
tBuf := utl_lms.format_message( 'Unsupported eleminfo merge operation:etype1=%s, interpr1=%s, etype2=%s, interpr2=%s',
TO_CHAR( etype1), TO_CHAR( interpr1), TO_CHAR( etype2), TO_CHAR( interpr2));
dbms_output.put_line( tBuf);
return;
END;
/* --- Merge Begin */
BEGIN
if( geom1 is null) then return( geom2); end if;
if( geom2 is null) then return( geom1); end if;
/* --- Geometries need to have the same gtype (20170808 SDE) */
if( geom1.sdo_gtype != geom2.sdo_gtype) then
tBuf := utl_lms.format_message( 'gtype not equal:%s != %s', TO_CHAR( geom1.sdo_gtype), TO_CHAR( geom2.sdo_gtype));
dbms_output.put_line( tBuf);
return( null);
end if;
/* --- If the dimension is specified it needs to be the same (20170808 SDE) */
if( geom1.sdo_srid is null) then
if( geom2.sdo_srid is not null) then
tBuf := utl_lms.format_message( 'srid1 is null srid2 not');
dbms_output.put_line( tBuf);
return( null);
end if;
end if;
if( geom2.sdo_srid is null) then
if( geom1.sdo_srid is not null) then
tBuf := utl_lms.format_message( 'srid2 is null srid1 not');
dbms_output.put_line( tBuf);
return( null);
end if;
end if;
if( geom1.sdo_srid != geom2.sdo_srid) then
tBuf := utl_lms.format_message( 'srid not equal:%s != %s', TO_CHAR( geom1.sdo_srid), TO_CHAR( geom2.sdo_srid));
dbms_output.put_line( tBuf);
return( null);
end if;
/* --- Merge it (20170808 SDE) */
v_o1 := geom1.sdo_elem_info( 1);
v_e1 := geom1.sdo_elem_info( 2);
v_i1 := geom1.sdo_elem_info( 3);
v_o2 := geom2.sdo_elem_info( 1);
v_e2 := geom2.sdo_elem_info( 2);
v_i2 := geom2.sdo_elem_info( 3);
if( isSupportedGeometry( v_e1, v_i1) = FALSE) then
tBuf := utl_lms.format_message( 'unsupported elem_info1:%s %s', TO_CHAR( v_e1), TO_CHAR( v_i1));
dbms_output.put_line( tBuf);
return( null);
end if;
if( isSupportedGeometry( v_e2, v_i2) = FALSE) then
tBuf := utl_lms.format_message( 'unsupported elem_info2:%s %s', TO_CHAR( v_e1), TO_CHAR( v_i1));
dbms_output.put_line( tBuf);
return( null);
end if;
ordinates := mergeOrdinates( n1 => geom1.sdo_ordinates, n2 => geom2.sdo_ordinates);
getMergedElemInfo ( e1 => geom1.sdo_elem_info, e2 => geom2.sdo_elem_info, e3 => elemInfo );
merged := new mdsys.sdo_geometry( SDO_GTYPE => 3002
, SDO_SRID => geom1.sdo_srid
, SDO_POINT => null
, SDO_ELEM_INFO => elemInfo
, SDO_ORDINATES => ordinates);
return( merged);
end;
/**
* @name SortAndMerge
* @author S.Deckers
* @date 26-07-2017
* @description Sorts and Merges the geometries and return result
*/
FUNCTION SortAndMerge return mdsys.sdo_geometry
AS
l_cnt int;
sorted mdsys.sdo_geometry;
/**
* @name mergeSortedGeometries
* @purpose Merge set of sorted geometries into a single geometry
*/
FUNCTION getSortedMergedGeometries return mdsys.sdo_geometry
as
i int := getStartingGeo();
tNext int;
merged mdsys.sdo_geometry;
begin
LOOP
tNext := geomInfo( i).Next;
merged := Merge( merged, geomInfo( i).geom);
if( tNext = 0) then
return( merged);
end if;
i := geomInfo( i).Next;
END LOOP;
end;
BEGIN -- Sort --
if( collectionIs3D() = FALSE) then
tBuf := utl_lms.format_message( 'Only 3d geometries are supported');
dbms_output.put_line( tBuf);
return( null);
end if;
l_cnt := multipleConnectedGeometryCount( );
/* --- If we have geometries with more then a single geometry connected on either side we cannot do the sorting (20170806 SDE) */
if( l_cnt > 0) then
return( null);
end if;
l_cnt := getSingleConnectedCount();
/* --- We need 2 geometries connected on a single side to do the sorting (20170806 SDE) */
if( l_cnt != 2) then
return( null);
end if;
Sort( );
/* --- Merge the sorted geometries (20170808 SDE) */
sorted := getSortedMergedGeometries( );
return( sorted);
END; -- Sort --
/**
* @name sortGeometries
* @author S.Deckers
* @date 06-07-2017
* @description Sort set of geometries
*/
FUNCTION SortAndMergeGeometries
(
unsorted in mdsys.sdo_geometry
) return mdsys.sdo_geometry
AS
valid int;
sorted mdsys.sdo_geometry;
tGeoCount int;
BEGIN
/* --- Only sort valid geometries (20170720 SDE) */
select SDO_CS.MAKE_2D( unsorted).st_isValid() into valid from dual;
if( valid = 0 ) then
dbms_output.put_line( utl_lms.format_message( 'Invalid geometry', TO_CHAR( valid) ));
return( null);
end if;
/* --- Enumerate all geometries encountered and fill in temp structure (20170901 SDE) */
fillTempStructure( theGeom =>self.geom, geoCount => tGeoCount);
/* --- If there's only a single item, return it (20170808 SDE) */
if( tGeoCount = 1 ) then
tBuf := utl_lms.format_message( 'Single geometry');
dbms_output.put_line( tBuf);
return( self.geom);
end if;
/* --- Put all geometries in structure for processing (20170720 SDE) */
setConnectionInfo ( );
sorted := SortAndMerge( );
return( sorted);
end;
BEGIN
ret := SortAndMergeGeometries( unsorted => self.geom);
return( ODCIConst.Success);
END;
/**
* @name ODCIAggregateMerge
*/
MEMBER FUNCTION ODCIAggregateMerge
(
self IN OUT SDE_MERGE_i
, ctx2 IN SDE_MERGE_i
) RETURN number
IS
BEGIN
return( ODCIConst.Success);
END;
END;
/
CREATE or replace FUNCTION SDE_MERGE (input mdsys.sdo_geometry)
RETURN mdsys.sdo_geometry PARALLEL_ENABLE AGGREGATE USING SDE_MERGE_i;
/
|
|