9 Replies Latest reply: Sep 3, 2013 5:15 AM by MRoche

# How to bridge (fill) gaps when converting a 2006 geometry  into a 2002 geometry

Is it at all possible to bridge (fill) gaps when converting a 2006 geometry into a single 2002 geometry. I have a solution for the conversion from 2006 to 2002 provided by BHall but upon further investigation investigation of my dataset some of the multi line polygons have gaps which I need to fill and I am not sure how to go about this.

Here is a simple example of what I am trying to achieve

Before

SELECT (sdo_geometry(2006, 81989, NULL,

mdsys.sdo_elem_info_array(1,2,1,5,2,1,9,2,1,13,2,1),

mdsys.sdo_ordinate_array(16,0.999,16.998,-0.001,17.253,-0.001,18.003,0.999,18.003,0.999,19.001,0.999,19.001,0.999,19.999,-0.001)))

FROM dual

After

SELECT (sdo_geometry(2006, 81989,NULL,

mdsys.sdo_elem_info_array(1,2,1),

mdsys.sdo_ordinate_array(16,0.999,17.253,-0.001,18.003,0.999,19.001,0.999,19.999,-0.001))) FROM dual

• ###### 1. Re: How to bridge (fill) gaps when converting a 2006 geometry  into a 2002 geometry

Roché,

Your question is vague and unclear. However if you want to convert Multi-Part linestring (2006) to single part linestring(2002) you can you this function:

```create or replace type sdo_geometry_array as varray (1000000) of mdsys.sdo_geometry;
/
CREATE OR REPLACE
FUNCTION split_multilines(
geom sdo_geometry)
RETURN sdo_geometry_array pipelined
IS
BEGIN
IF geom IS NOT NULL THEN
IF geom.sdo_gtype IN (2006) THEN
FOR i           IN 1 .. (sdo_util.getnumelem(geom))
LOOP
pipe row (sdo_util.extract (geom, i));
END LOOP;
ELSE
pipe row (geom);
END IF;
END IF;
RETURN;
END;
/
--use them with these queries
select split_multilines(
sdo_geometry(2006, 81989, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,5,2,1,9,2,1,13,2,1),
MDSYS.SDO_ORDINATE_ARRAY(16,0.999,16.998,-0.001,17.253,-0.001,18.003,0.999,18.003,0.999,19.001,0.999,19.001,0.999,19.999,-0.001))
) FROM DUAL;
select *  from table(split_multilines(
sdo_geometry(2006, 81989, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,5,2,1,9,2,1,13,2,1),
MDSYS.SDO_ORDINATE_ARRAY(16,0.999,16.998,-0.001,17.253,-0.001,18.003,0.999,18.003,0.999,19.001,0.999,19.001,0.999,19.999,-0.001))
)) t
;
```
• ###### 2. Re: How to bridge (fill) gaps when converting a 2006 geometry  into a 2002 geometry

I do apologise, I am new to the world of spatial so let me try again.

I have a multi line geometry which I would like to convert into a line string which I am able to do with a solution give to me from a previous post. However some of the multi segment geometries appear to have gaps like in my BEFORE example. Is there any way to fill there GAPS before converting to a line string.

So that in my example where the last coordinate of the first segment of the multiline becomes (or has the same value) the first coordinate of the second segment e.g. (16,0.999,16.998,-0.001)  becomes (16,0.999,17.253,-0.001)

I hope this is clearer

• ###### 3. Re: How to bridge (fill) gaps when converting a 2006 geometry  into a 2002 geometry

By doing so you will ruin your geometry. If that is your case, here is a quick solution:

```CREATE OR REPLACE
FUNCTION join_line_parts(
geom sdo_geometry)
RETURN sdo_geometry
IS
GEOM2 SDO_GEOMETRY:=NULL;
ELEM_IDX PLS_INTEGER;
X PLS_INTEGER;
IDX PLS_INTEGER;
TYPE ELEM_IDX_ARRAY
IS
TABLE OF BOOLEAN INDEX BY pls_integer;
ordinate_indices_to_be_removed ELEM_IDX_ARRAY;
ORDS SDO_ORDINATE_ARRAY:=SDO_ORDINATE_ARRAY();
BEGIN
--    ords.extend();
IF GEOM.SDO_GTYPE IN (2006) THEN
--loop over the elements info
FOR X IN 1..(GEOM.SDO_ELEM_INFO.COUNT/3)
LOOP
IF X !=1 THEN
ELEM_IDX := (X * 3 - 2);
ORDINATE_INDICES_TO_BE_REMOVED(GEOM.SDO_ELEM_INFO(ELEM_IDX)-2) := TRUE;
ordinate_indices_to_be_removed(GEOM.SDO_ELEM_INFO(ELEM_IDX)-1) := true;
END IF;
END LOOP ;
--loop over ordinate
IDX := 1;
FOR X IN 1..GEOM.SDO_ORDINATES.COUNT
LOOP
IF not (ORDINATE_INDICES_TO_BE_REMOVED.EXISTS(X)) THEN
ords.extend;
ORDS(IDX) :=GEOM.SDO_ORDINATES(X);
IDX       := IDX+1;
END IF;
END LOOP;
GEOM2 := SDO_UTIL.EXTRACT (GEOM, 1);
GEOM2.SDO_ORDINATES := ORDS;
return geom2;
ELSE
RETURN geom;
END IF;
RETURN geom;
END ;
```

And to test the function with your sample geometry,

```select join_line_parts(sdo_geometry(2006, 81989, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,5,2,1,9,2,1,13,2,1),
MDSYS.SDO_ORDINATE_ARRAY(
16,0.999,
16.998,-0.001,
17.253,-0.001,
18.003,0.999,
18.003,0.999,
19.001,0.999,
19.001,0.999,
19.999,-0.001))) geom
FROM DUAL
UNION all
select sdo_geometry(2006, 81989, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,5,2,1,9,2,1,13,2,1),
MDSYS.SDO_ORDINATE_ARRAY(
16,0.999,
16.998,-0.001,
17.253,-0.001,
18.003,0.999,
18.003,0.999,
19.001,0.999,
19.001,0.999,
19.999,-0.001)) geom
from dual;

```
• ###### 4. Re: How to bridge (fill) gaps when converting a 2006 geometry  into a 2002 geometry

Hi Roché,

Usually we ask folks for sample data but in this case your sample data is rather confusing.  You provide a multistring that looks something like this (S is start point and E is end point)

S1++++E1  S2+++E2S3+++E3S4+++E4

and you say what you want is

S1++++++++<>+++++<>+++<>+++++E1

Note that you seem to want the original E1 (16.998,-0.001) to go away.  Is that a typo (you did bold it)?  I am confused as far as I can see for your example Bryan's solution will work just fine unless you really do need that E1 endpoint to go away for some reason. You probably need to clarify things more with a better geometry example of your problem.  But I can blather a bit in the meantime. As Bryan mentioned, his solution will run into trouble if the ordinates are not naturally ordered start to end, start to end and if the geometry does not in fact have a clear gap between the two strings.

Your gap could also occur between two end points (or two start points):

S1++++E1  E2++++S2

Or run parallel (which gap to fill?)

S1++++++++E1

S2++++++E2

Or touch other than on the ends

`  S1++++++`

`         +S2++++++++E2`

`         +`

`         +`

`         E1 `

Or get just not have a fillable gap

S2++++++

+

++++++++++ +

+        + +

S1    E1 + +

++++ +

+++E2

Or three or more linestrings could touch at a single point

S1+++++E1S2+++++E2

S3

+

+

E3

Forcing things into a single linestring may create spaghetti or an invalid geometry.

I do have some solutions that are more robust than Bryan's (filling start to start or end to end and choosing the best gap) but for the more extreme examples I put above I don't think there is a solution.  If you can explain things more I can provide some code.

You might want to back up a step and look more closely at why these multistrings are coming about in the first place.

Cheers,

Paul

• ###### 5. Re: How to bridge (fill) gaps when converting a 2006 geometry  into a 2002 geometry

It might be best if I try an give you some background information on my problem.

The examples I have provided are a simplification of a problem that me and my collogues have recently been experiencing. Theses multilines are a representation of pipes in a hydraulic network which would normally be a singleline from point to point (meter to meter in reality) but for a few pipes their geometries have been provided to us as multilines we believe this is because these lines (pipes) have been drawn manually and been drawn badly as they contain gaps. It is theses gaps which I would like to remove so that the multilines can become a singleline.

The scenarios I need to try and resolve are

Multiline with none sequential segments, reverse ordering and gaps

s1++++++e1e3++++++s3(GAP)s4++++++e4s2++++++e2

line made up of multiple correctly ordered segments but the end point of one segment does not have the same coordinates as the start point of the next segment

s1++++++e1(GAP)s2++++++e2s3++++++e3s4++++++e4

or am I going about this the wrong way and would converting a multiline into a single line which takes into account ordering of the ordinates be a better solution??

Regards

Roche

• ###### 6. Re: How to bridge (fill) gaps when converting a 2006 geometry  into a 2002 geometry

Okay Roché,

You might need to break this problem down into smaller parts for the forum.  All your examples show the gap being replaced by a single vertice - e.g. you want to "snap" the gap together.  I'd suggest this is just complicating your question.  Once the gap is filled (with a line) then you can run SDO_UTIL.REMOVE_DUPLICATE_VERTICES afterwards to remove the new line if its below your tolerance.  I think that Bryan's code wrapped in remove duplicate vertices will solve your second scenario.  But overall I think it would be helpful to focus just on the filling and leave the snapping for a follow-up question.

So back to scenario #1, below is some code I wrote a while back that might do what you want or at least get you started.     Note that the input must be a multistring and the multistring cannot be "spaghetti".  In other words each line in the multistring must be disjoint or at most touch other lines only at endpoints. The goal is to sift through the lines and create a single linestring using the smallest gaps.  It's not subtle and will indeed produce bad geometries if the inputs are setup in an impossible manner.  There are also some rare geodetic bugs with SDO_GEOM.RELATE that crop up.  So you'll note I test both distance and relate in some places.  That's intentional though kind of dumb.

Hopefully this helps.  If you improve the code please shoot a copy back to me.

Cheers,

Paul

```CREATE OR REPLACE PACKAGE dz_gap_fill
AUTHID CURRENT_USER
AS

FUNCTION linear_gap_filler(
p_input            IN  MDSYS.SDO_GEOMETRY
,p_tolerance        IN  NUMBER DEFAULT 0.05
) RETURN MDSYS.SDO_GEOMETRY;

END dz_gap_fill;

CREATE OR REPLACE PACKAGE BODY dz_gap_fill
AS

FUNCTION fast_point(
p_x             IN  NUMBER
,p_y             IN  NUMBER
,p_z             IN  NUMBER DEFAULT NULL
,p_m             IN  NUMBER DEFAULT NULL
,p_srid          IN  NUMBER DEFAULT 8265
) RETURN MDSYS.SDO_GEOMETRY
AS
BEGIN

--------------------------------------------------------------------------
-- Step 10
-- Check over incoming parameters
--------------------------------------------------------------------------
IF p_x IS NULL
OR p_y IS NULL
THEN
RAISE_APPLICATION_ERROR(-20001,'x and y cannot be NULL');

END IF;

--------------------------------------------------------------------------
-- Step 20
-- Do the simplest solution first
--------------------------------------------------------------------------
IF  p_z IS NULL
AND p_m IS NULL
THEN
RETURN SDO_GEOMETRY(
2001
,p_srid
,SDO_POINT_TYPE(
p_x
,p_y
,NULL
)
,NULL
,NULL
);

END IF;

--------------------------------------------------------------------------
-- Step 30
-- Do the other wilder choices
--------------------------------------------------------------------------
IF p_z IS NULL
AND p_m IS NOT NULL
THEN
RETURN SDO_GEOMETRY(
3301
,p_srid
,SDO_POINT_TYPE(
p_x
,p_y
,p_m
)
,NULL
,NULL
);

ELSIF p_z IS NOT NULL
AND   p_m IS NULL
THEN
RETURN SDO_GEOMETRY(
3001
,p_srid
,SDO_POINT_TYPE(
p_x
,p_y
,p_z
)
,NULL
,NULL
);

ELSIF p_z IS NOT NULL
AND   p_m IS NOT NULL
THEN
RETURN SDO_GEOMETRY(
4401
,p_srid
,NULL
,SDO_ELEM_INFO_ARRAY(1,1,1)
,SDO_ORDINATE_ARRAY(p_x,p_y,p_z,p_m)
);

ELSE
RAISE_APPLICATION_ERROR(-20001,'ERR!');
END IF;

END fast_point;

FUNCTION get_start_point(
p_input        IN  MDSYS.SDO_GEOMETRY
) RETURN MDSYS.SDO_GEOMETRY
AS
int_dims PLS_INTEGER;
int_gtyp PLS_INTEGER;
int_lrs  PLS_INTEGER;

BEGIN

--------------------------------------------------------------------------
-- Step 10
-- Check over incoming parameters
--------------------------------------------------------------------------
IF p_input IS NULL
THEN
RETURN NULL;

END IF;

--------------------------------------------------------------------------
-- Step 20
-- Gather information about the geometry
--------------------------------------------------------------------------
int_dims := p_input.get_dims();
int_gtyp := p_input.get_gtype();
int_lrs  := p_input.get_lrs_dim();

--------------------------------------------------------------------------
-- Step 30
-- Handle point and multipoint inputs
--------------------------------------------------------------------------
IF int_gtyp = 1
THEN
RETURN p_input;

ELSIF int_gtyp = 5
THEN
RETURN SDO_UTIL.EXTRACT(p_input,1);

END IF;

--------------------------------------------------------------------------
-- Step 40
-- Return results
--------------------------------------------------------------------------
IF int_dims = 2
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(1)
,p_input.SDO_ORDINATES(2)
,NULL
,NULL
,p_input.SDO_SRID
);

ELSIF  int_dims = 3
AND int_lrs = 3
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(1)
,p_input.SDO_ORDINATES(2)
,NULL
,p_input.SDO_ORDINATES(3)
,p_input.SDO_SRID
);

ELSIF  int_dims = 3
AND int_lrs = 0
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(1)
,p_input.SDO_ORDINATES(2)
,p_input.SDO_ORDINATES(3)
,NULL
,p_input.SDO_SRID
);

ELSIF  int_dims = 4
AND int_lrs IN (4,0)
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(1)
,p_input.SDO_ORDINATES(2)
,p_input.SDO_ORDINATES(3)
,p_input.SDO_ORDINATES(4)
,p_input.SDO_SRID
);

ELSIF  int_dims = 4
AND int_lrs = 3
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(1)
,p_input.SDO_ORDINATES(2)
,p_input.SDO_ORDINATES(4)
,p_input.SDO_ORDINATES(3)
,p_input.SDO_SRID
);

ELSE
RAISE_APPLICATION_ERROR(-20001,'ERR!');

END IF;

END get_start_point;

FUNCTION get_end_point(
p_input        IN  MDSYS.SDO_GEOMETRY
) RETURN MDSYS.SDO_GEOMETRY
AS
int_dims PLS_INTEGER;
int_gtyp PLS_INTEGER;
int_lrs  PLS_INTEGER;
int_len  PLS_INTEGER;

BEGIN

--------------------------------------------------------------------------
-- Step 10
-- Check over incoming parameters
--------------------------------------------------------------------------
IF p_input IS NULL
THEN
RETURN NULL;

END IF;

--------------------------------------------------------------------------
-- Step 20
-- Gather information about the geometry
--------------------------------------------------------------------------
int_dims := p_input.get_dims();
int_gtyp := p_input.get_gtype();
int_lrs  := p_input.get_lrs_dim();
int_len  := p_input.SDO_ORDINATES.COUNT();

--------------------------------------------------------------------------
-- Step 30
-- Handle point and multipoint inputs
--------------------------------------------------------------------------
IF int_gtyp = 1
THEN
RETURN p_input;
ELSIF int_gtyp = 5
THEN
RETURN SDO_UTIL.EXTRACT(
p_input
,SDO_UTIL.GETNUMELEM(p_input)
);
END IF;

--------------------------------------------------------------------------
-- Step 40
-- Return results
--------------------------------------------------------------------------
IF int_dims = 2
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(int_len - 1)
,p_input.SDO_ORDINATES(int_len)
,NULL
,NULL
,p_input.SDO_SRID
);

ELSIF  int_dims = 3
AND int_lrs = 3
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(int_len - 2)
,p_input.SDO_ORDINATES(int_len - 1)
,NULL
,p_input.SDO_ORDINATES(int_len)
,p_input.SDO_SRID
);

ELSIF  int_dims = 3
AND int_lrs = 0
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(int_len - 2)
,p_input.SDO_ORDINATES(int_len - 1)
,p_input.SDO_ORDINATES(int_len)
,NULL
,p_input.SDO_SRID
);

ELSIF  int_dims = 4
AND int_lrs IN (4,0)
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(int_len - 3)
,p_input.SDO_ORDINATES(int_len - 2)
,p_input.SDO_ORDINATES(int_len - 1)
,p_input.SDO_ORDINATES(int_len)
,p_input.SDO_SRID
);

ELSIF  int_dims = 4
AND int_lrs = 3
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(int_len - 3)
,p_input.SDO_ORDINATES(int_len - 2)
,p_input.SDO_ORDINATES(int_len)
,p_input.SDO_ORDINATES(int_len - 1)
,p_input.SDO_SRID
);

ELSE
RAISE_APPLICATION_ERROR(-20001,'ERR!');

END IF;

END get_end_point;

FUNCTION is_spaghetti(
p_input             IN  MDSYS.SDO_GEOMETRY
,p_tolerance         IN  NUMBER DEFAULT 0.05
) RETURN VARCHAR2
AS
num_tolerance    NUMBER := p_tolerance;
ary_strings      MDSYS.SDO_GEOMETRY_ARRAY := MDSYS.SDO_GEOMETRY_ARRAY();
ary_starts       MDSYS.SDO_GEOMETRY_ARRAY := MDSYS.SDO_GEOMETRY_ARRAY();
ary_ends         MDSYS.SDO_GEOMETRY_ARRAY := MDSYS.SDO_GEOMETRY_ARRAY();
int_count        PLS_INTEGER;
ary_start_count  MDSYS.SDO_NUMBER_ARRAY := MDSYS.SDO_NUMBER_ARRAY();
ary_end_count    MDSYS.SDO_NUMBER_ARRAY := MDSYS.SDO_NUMBER_ARRAY();
ary_inside_count MDSYS.SDO_NUMBER_ARRAY := MDSYS.SDO_NUMBER_ARRAY();

BEGIN

--------------------------------------------------------------------------
-- Step 10
-- Check over incoming parameters
--------------------------------------------------------------------------
IF p_input IS NULL
THEN
RETURN NULL;

ELSIF p_input.get_gtype = 2
THEN
RETURN 'FALSE';

ELSIF p_input.get_gtype <> 6
THEN
RAISE_APPLICATION_ERROR(-20001,'input gtype must be 2 or 6');

END IF;

IF num_tolerance IS NULL
THEN
num_tolerance := 0.05;

END IF;

--------------------------------------------------------------------------
-- Step 20
-- Break multistring into single linestrings with nodes
--------------------------------------------------------------------------
int_count := SDO_UTIL.GETNUMELEM(p_input);
ary_strings.EXTEND(int_count);
ary_starts.EXTEND(int_count);
ary_ends.EXTEND(int_count);
ary_start_count.EXTEND(int_count);
ary_end_count.EXTEND(int_count);
ary_inside_count.EXTEND(int_count);
FOR i IN 1 .. int_count
LOOP
ary_strings(i) := SDO_UTIL.EXTRACT(p_input,i);
ary_starts(i)  := get_start_point(ary_strings(i));
ary_ends(i)    := get_end_point(ary_strings(i));

END LOOP;

--------------------------------------------------------------------------
-- Step 30
-- Loop through and count the nodes connections
--------------------------------------------------------------------------
FOR i IN 1 .. int_count
LOOP
ary_start_count(i)  := 0;
ary_end_count(i)    := 0;
ary_inside_count(i) := 0;

FOR j IN 1 .. int_count
LOOP
IF i != j
THEN
IF SDO_GEOM.RELATE(
ary_starts(i),
'DETERMINE',
ary_strings(j),
num_tolerance
) IN ('TOUCH','CONTAINS','COVERS','ON')
THEN
ary_start_count(i) := ary_start_count(i) + 1;

ELSIF SDO_GEOM.RELATE(
ary_ends(i),
'DETERMINE',
ary_strings(j),
num_tolerance
) IN ('TOUCH','CONTAINS','COVERS','ON')
THEN
ary_end_count(i) := ary_end_count(i) + 1;

ELSIF SDO_GEOM.RELATE(
ary_strings(i),
'DETERMINE',
ary_strings(j),
num_tolerance
) IN ('TOUCH','CONTAINS','COVERS','OVERLAPBYINTERSECT')
THEN
ary_inside_count(i) := ary_inside_count(i) + 1;

END IF;

END IF;

END LOOP;

IF ary_start_count(i) > 1
OR ary_end_count(i) > 1
OR ary_inside_count(i) > 0
THEN
RETURN 'TRUE';
END IF;

END LOOP;

RETURN 'FALSE';

END is_spaghetti;

-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
FUNCTION points2segment(
p_point_one              IN  MDSYS.SDO_POINT_TYPE
,p_point_two              IN  MDSYS.SDO_POINT_TYPE
,p_srid                   IN  NUMBER
) RETURN MDSYS.SDO_GEOMETRY
AS
BEGIN

IF ( p_point_one.Z IS NULL AND p_point_two.Z IS NOT NULL )
OR ( p_point_one.Z IS NOT NULL AND p_point_two.Z IS NULL )
THEN
RAISE_APPLICATION_ERROR(
-20001,
'both points must have the same number of dimensions, point_one Z is ' ||
NVL(TO_CHAR(p_point_one.Z),'') ||
' and point_two Z is ' ||
NVL(TO_CHAR(p_point_two.Z),'')
);

END IF;

IF p_point_one.Z IS NULL
THEN
RETURN SDO_GEOMETRY(
2002
,p_srid
,NULL
,SDO_ELEM_INFO_ARRAY(1,2,1)
,SDO_ORDINATE_ARRAY(p_point_one.X,p_point_one.Y,p_point_two.X,p_point_two.Y)
);

ELSE
RETURN SDO_GEOMETRY(
3002
,p_srid
,NULL
,SDO_ELEM_INFO_ARRAY(1,2,1)
,SDO_ORDINATE_ARRAY(p_point_one.X,p_point_one.Y,p_point_one.Z,p_point_two.X,p_point_two.Y,p_point_two.Z)
);

END IF;

END points2segment;

-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
FUNCTION points2segment(
p_point_one              IN  MDSYS.SDO_GEOMETRY
,p_point_two              IN  MDSYS.SDO_GEOMETRY
) RETURN MDSYS.SDO_GEOMETRY
AS
int_gtype1 PLS_INTEGER;
int_dims1  PLS_INTEGER;
int_gtype2 PLS_INTEGER;
int_dims2  PLS_INTEGER;
point_one  MDSYS.SDO_POINT_TYPE;
point_two  MDSYS.SDO_POINT_TYPE;

BEGIN

int_gtype1 := p_point_one.get_gtype();
int_dims1  := p_point_one.get_dims();
int_gtype2 := p_point_two.get_gtype();
int_dims2  := p_point_two.get_dims();

IF  int_gtype1 = 1
AND int_gtype2 = 1
AND int_dims1  = int_dims2
AND p_point_one.SDO_SRID = p_point_two.SDO_SRID
THEN
NULL;  -- Good

ELSE
RAISE_APPLICATION_ERROR(
-20001
,'both point objects must be points and have the same number of dimensions and SRIDs'
);

END IF;

IF int_dims1 = 4
THEN
RETURN SDO_GEOMETRY(
4402
,p_point_one.SDO_SRID
,NULL
,SDO_ELEM_INFO_ARRAY(1,2,1)
,SDO_ORDINATE_ARRAY(
p_point_one.SDO_ORDINATES(1)
,p_point_one.SDO_ORDINATES(2)
,p_point_one.SDO_ORDINATES(3)
,p_point_one.SDO_ORDINATES(4)
,p_point_two.SDO_ORDINATES(1)
,p_point_two.SDO_ORDINATES(2)
,p_point_two.SDO_ORDINATES(3)
,p_point_two.SDO_ORDINATES(4)
)
);

ELSE
-- Use the sdo_point_type method for the rest
IF p_point_one.SDO_POINT IS NOT NULL
THEN
point_one := p_point_one.SDO_POINT;

ELSE
IF int_dims1 = 3
THEN
point_one := SDO_POINT_TYPE(
p_point_one.SDO_ORDINATES(1)
,p_point_one.SDO_ORDINATES(2)
,p_point_one.SDO_ORDINATES(3)
);

ELSE
point_one := SDO_POINT_TYPE(
p_point_one.SDO_ORDINATES(1)
,p_point_one.SDO_ORDINATES(2)
,NULL
);

END IF;

END IF;

IF p_point_two.SDO_POINT IS NOT NULL
THEN
point_two := p_point_two.SDO_POINT;

ELSE
IF int_dims1 = 3
THEN
point_two := SDO_POINT_TYPE(
p_point_two.SDO_ORDINATES(1)
,p_point_two.SDO_ORDINATES(2)
,p_point_two.SDO_ORDINATES(3)
);

ELSE
point_two := SDO_POINT_TYPE(
p_point_two.SDO_ORDINATES(1)
,p_point_two.SDO_ORDINATES(2)
,NULL
);

END IF;

END IF;

RETURN points2segment(
p_point_one   => point_one
,p_point_two   => point_two
,p_srid        => p_point_one.SDO_SRID
);

END IF;

END points2segment;

FUNCTION linear_gap_filler(
p_input            IN  MDSYS.SDO_GEOMETRY
,p_tolerance        IN  NUMBER DEFAULT 0.05
) RETURN MDSYS.SDO_GEOMETRY
AS
sdo_input     MDSYS.SDO_GEOMETRY := p_input;
num_tolerance NUMBER;
int_counter   PLS_INTEGER;
ary_edges     MDSYS.SDO_GEOMETRY_ARRAY;
ary_starts    MDSYS.SDO_GEOMETRY_ARRAY;
ary_ends      MDSYS.SDO_GEOMETRY_ARRAY;
ary_nearest   MDSYS.SDO_NUMBER_ARRAY;
ary_distance  MDSYS.SDO_NUMBER_ARRAY;
num_temp      NUMBER;
num_nearest   NUMBER;
int_winner    PLS_INTEGER;
int_winner2   PLS_INTEGER;
sdo_point1    MDSYS.SDO_GEOMETRY;
sdo_point2    MDSYS.SDO_GEOMETRY;
boo_done      BOOLEAN;
num_one       NUMBER;
num_two       NUMBER;
int_looper    PLS_INTEGER := 1;

BEGIN

--------------------------------------------------------------------------
-- Step 10
-- Check over incoming parameters
--------------------------------------------------------------------------
IF num_tolerance IS NULL
THEN
num_tolerance := 0.05;

END IF;

IF sdo_input IS NULL
OR sdo_input.get_gtype() <> 6
THEN
RETURN sdo_input;

END IF;

IF is_spaghetti(sdo_input,p_tolerance) = 'TRUE'
THEN
RETURN sdo_input;

END IF;

<>      ary_edges     := MDSYS.SDO_GEOMETRY_ARRAY();
ary_starts    := MDSYS.SDO_GEOMETRY_ARRAY();
ary_ends      := MDSYS.SDO_GEOMETRY_ARRAY();
ary_nearest   := MDSYS.SDO_NUMBER_ARRAY();
ary_distance  := MDSYS.SDO_NUMBER_ARRAY();

--------------------------------------------------------------------------
-- Step 20
-- Break multistring into edges and start and end nodes
--------------------------------------------------------------------------
int_counter := SDO_UTIL.GETNUMELEM(sdo_input);
ary_edges.EXTEND(int_counter);
ary_starts.EXTEND(int_counter);
ary_ends.EXTEND(int_counter);
FOR i IN 1 .. int_counter
LOOP
ary_edges(i)  := SDO_UTIL.EXTRACT(sdo_input,i);
ary_starts(i) := get_start_point(ary_edges(i));
ary_ends(i)   := get_end_point(ary_edges(i));

END LOOP;

--------------------------------------------------------------------------
-- Step 30
-- Determine the closest endpoints
--------------------------------------------------------------------------
ary_nearest.EXTEND(int_counter);
ary_distance.EXTEND(int_counter);
FOR i IN 1 .. int_counter
LOOP
num_nearest := NULL;
int_winner := NULL;
FOR j IN 1 .. int_counter
LOOP
IF j != i
THEN
num_temp := SDO_GEOM.SDO_DISTANCE(
ary_edges(i)
,ary_edges(j)
,num_tolerance
);

IF num_nearest IS NULL
OR num_temp < num_nearest
THEN
num_nearest := num_temp;
int_winner := j;

END IF;

END IF;

END LOOP;

ary_nearest(i) := int_winner;
ary_distance(i) := num_nearest;

END LOOP;

--------------------------------------------------------------------------
-- Step 40
-- Find the smallest gap
--------------------------------------------------------------------------
int_winner := NULL;
num_nearest := NULL;
FOR i IN 1 .. int_counter
LOOP
IF num_nearest IS NULL
OR ary_distance(i) < num_nearest
THEN
int_winner := i;
num_nearest := ary_distance(i);
int_winner2 := ary_nearest(i);

END IF;

END LOOP;

--------------------------------------------------------------------------
-- Step 50
-- Determine the endpoints to connect
--------------------------------------------------------------------------
num_one := SDO_GEOM.SDO_DISTANCE(
get_start_point(ary_edges(int_winner)),
ary_edges(int_winner2),
num_tolerance
);
num_two := SDO_GEOM.SDO_DISTANCE(
get_end_point(ary_edges(int_winner)),
ary_edges(int_winner2),
num_tolerance
);

IF ( num_one = 0 AND SDO_GEOM.RELATE(
get_start_point(ary_edges(int_winner)),
'ANYINTERACT',
ary_edges(int_winner2),
num_tolerance
) = 'TRUE' )
OR ( num_two = 0 AND SDO_GEOM.RELATE(
get_end_point(ary_edges(int_winner)),
'ANYINTERACT',
ary_edges(int_winner2),
num_tolerance
) = 'TRUE' )
THEN
sdo_point1 := NULL;

ELSIF num_one < num_two
THEN
sdo_point1 := get_start_point(ary_edges(int_winner));

ELSE
sdo_point1 := get_end_point(ary_edges(int_winner));

END IF;

num_one := SDO_GEOM.SDO_DISTANCE(
get_start_point(ary_edges(int_winner2)),
ary_edges(int_winner),
num_tolerance
);
num_two := SDO_GEOM.SDO_DISTANCE(
get_end_point(ary_edges(int_winner2)),
ary_edges(int_winner),
num_tolerance
);

IF ( num_one = 0 AND SDO_GEOM.RELATE(
get_start_point(ary_edges(int_winner2)),
'ANYINTERACT',
ary_edges(int_winner),
num_tolerance
) = 'TRUE' )
OR ( num_two = 0 AND SDO_GEOM.RELATE(
get_end_point(ary_edges(int_winner2)),
'ANYINTERACT',
ary_edges(int_winner),
num_tolerance
) = 'TRUE' )
THEN
sdo_point2 := NULL;

ELSIF num_one < num_two
THEN
sdo_point2 := get_start_point(ary_edges(int_winner2));

ELSE
sdo_point2 := get_end_point(ary_edges(int_winner2));

END IF;

--------------------------------------------------------------------------
-- Step 50
-- Smash together
--------------------------------------------------------------------------
IF sdo_point1 IS NULL
OR sdo_point2 IS NULL
THEN
sdo_input := SDO_UTIL.CONCAT_LINES(
ary_edges(int_winner),
ary_edges(int_winner2)
);

ELSE
sdo_input := SDO_UTIL.CONCAT_LINES(
SDO_UTIL.CONCAT_LINES(
ary_edges(int_winner),
points2segment(sdo_point1,sdo_point2)
),
ary_edges(int_winner2)
);

END IF;

boo_done := TRUE;
FOR i IN 1 .. int_counter
LOOP
IF i NOT IN (int_winner,int_winner2)
THEN
sdo_input := SDO_UTIL.APPEND(sdo_input,ary_edges(i));
boo_done := FALSE;

END IF;

END LOOP;

--------------------------------------------------------------------------
-- Step 60
-- Check if valid if returning
--------------------------------------------------------------------------
IF sdo_input.get_gtype() = 2
OR boo_done = TRUE
THEN
RETURN sdo_input;

END IF;

int_looper := int_looper + 1;
GOTO TOP_OF_IT;

END linear_gap_filler;

END dz_gap_fill;

```
• ###### 7. Re: How to bridge (fill) gaps when converting a 2006 geometry  into a 2002 geometry

Paul,

Upon looking into my data further I believe that the gaps are not really the issue (please correct me if I am wrong) but my understanding is when converting from a multiline to a line the gaps will be filled anyway so there is no point to remove or bridge them is this correct?

Our main issue now is the way in which some of the multilines have not been drawn end to end. The example provided to my BHall has solved probably 90%-95% of the issues we were experiencing.

I suppose my question is now moving away from the bridging of gaps and more towards the reordering of multlines which have not been drawn end to end.

I have provided some real example to better explain my problem.

e.g. 1

SELECT (sdo_geometry(2006, 27700, NULL,

mdsys.sdo_elem_info_array(1, 2, 1, 9, 2, 1, 13, 2, 1),

mdsys.sdo_ordinate_array(424293.914, 561667.249,

424262.272, 561708.48,

424257.364, 561715.09,

424276.277, 561729.695,

424276.277, 561729.695,

424278.652, 561731.529,

424278.652, 561731.529,

424280.048, 561732.607,

424275.459, 561738.582)))

FROM dual

e.g.2

SELECT (sdo_geometry(2006, 27700, NULL,

mdsys.sdo_elem_info_array(1, 2, 1, 7, 2, 1, 11, 2, 1, 33,

2, 1, 37, 2, 1, 41, 2, 1, 45, 2,

1, 69, 2, 1, 73, 2, 1, 95, 2, 1,

99, 2, 1),

mdsys.sdo_ordinate_array(426330.88, 561051.648,

426306.674, 561041.018,

426271.798, 561033.06,

426271.798, 561033.06,

426268.873, 561032.393,

426268.873, 561032.393,

426232.096, 561024, 426185.216,

561013.248, 426173.984,

560998.72, 426173.726,

560997.729, 426186.325,

560932.766, 426204.084,

560842.463, 426215.795,

560779.935, 426222.293,

560747.134, 426228.333,

560713.027, 426226.898,

560710.844, 426226.898,

560710.844, 426225.596,

560708.864, 426225.596,

560708.864, 426225.534,

560708.769, 426225.534,

560708.769, 426223.886,

560706.262, 426223.886,

560706.262, 426219.463,

560699.535, 426216.416,

560698.048, 426215.04, 560697.28,

426213.344, 560696.128,

426210.91, 560694.083,

426208.736, 560692.224,

426206.532, 560689.799,

426192.886, 560663.124,

426170.081, 560620.377,

426150.424, 560583.577,

426144.765, 560572.803,

426144.765, 560572.803,

426143.37, 560570.147, 426143.37,

560570.147, 426141.687,

560566.943, 426137.51, 560560.44,

426122.966, 560539.029,

426118.336, 560532.227,

426108.726, 560523.164,

426103.758, 560517.884,

426075.031, 560487.106,

426038.941, 560451.366,

426024.336, 560437.499,

426023.776, 560437.028,

426023.776, 560437.028,

426021.48, 560435.096, 426021.48,

560435.096, 426017.344,

560431.616)))

FROM dual

e.g.3

SELECT (sdo_geometry(2006, 27700, NULL,

mdsys.sdo_elem_info_array(1, 2, 1, 13, 2, 1, 25, 2, 1,

75, 2, 1, 79, 2, 1),

mdsys.sdo_ordinate_array(421041.524, 561427.677,

421001.536, 561426.816,

420999.168, 561425.728,

420998.688, 561425.472,

420997.792, 561424.768,

420996.98, 561423.704, 420996.98,

561423.704, 420996.864,

561423.552, 420996.836,

561423.509, 420996, 561422.208,

420995.36, 561420.736,

420994.935, 561419.374,

420994.935, 561419.374,

420994.915, 561419.31, 420994.88,

561419.2, 420994.624, 561417.152,

420994.688, 561414.976,

420994.88, 561412.928,

420995.232, 561410.688,

420999.552, 561389.312,

421000.337, 561385.478,

421005.641, 561332.669,

421005.12, 561316.992,

421004.864, 561316.096,

421004.288, 561315.456,

421003.68, 561315.008,

421003.022, 561314.716,

421000.938, 561314.349,

420994.547, 561313.587,

420990.949, 561313.152,

420988.889, 561311.436,

420987.345, 561309.549,

420986.227, 561307.659,

420985.541, 561305.858,

420985.197, 561304.141,

420985.722, 561292.232,

420987.107, 561264.506,

420987.107, 561264.506,

420987.257, 561261.51,

420987.257, 561261.51,

420987.303, 561260.59)))

FROM dual

Regards

Mark

• ###### 8. Re: How to bridge (fill) gaps when converting a 2006 geometry  into a 2002 geometry

Hi Roche,

Well I ran all three examples you sent through my code and it seemed to work!  I pasted the code above into a "pre" html tag and I see it still munged things.  Lemme try this again with different syntax.  You know this new forum software just has not grown on me.  It's just clunky and overblown.  And those odd people at the top.  What are they looking up at?  I imagine Larry on some pulpit above.

Anyhow, you are correct that when using Bryan's code to convert the multistring, the gaps are filled.  But that is because he wrote it that way to do that.

There really is no generic way to convert a multistring to a line as we are discussing.  If you use SDO_UTIL.CONCATENATE_LINES it will leave the gaps.

Cheers,

Paul

```CREATE OR REPLACE PACKAGE dz_gap_fill
AUTHID CURRENT_USER
AS

FUNCTION linear_gap_filler(
p_input            IN  MDSYS.SDO_GEOMETRY
,p_tolerance        IN  NUMBER DEFAULT 0.05
) RETURN MDSYS.SDO_GEOMETRY;

END dz_gap_fill;

CREATE OR REPLACE PACKAGE BODY dz_gap_fill
AS

FUNCTION fast_point(
p_x             IN  NUMBER
,p_y             IN  NUMBER
,p_z             IN  NUMBER DEFAULT NULL
,p_m             IN  NUMBER DEFAULT NULL
,p_srid          IN  NUMBER DEFAULT 8265
) RETURN MDSYS.SDO_GEOMETRY
AS
BEGIN

--------------------------------------------------------------------------
-- Step 10
-- Check over incoming parameters
--------------------------------------------------------------------------
IF p_x IS NULL
OR p_y IS NULL
THEN
RAISE_APPLICATION_ERROR(-20001,'x and y cannot be NULL');

END IF;

--------------------------------------------------------------------------
-- Step 20
-- Do the simplest solution first
--------------------------------------------------------------------------
IF  p_z IS NULL
AND p_m IS NULL
THEN
RETURN SDO_GEOMETRY(
2001
,p_srid
,SDO_POINT_TYPE(
p_x
,p_y
,NULL
)
,NULL
,NULL
);

END IF;

--------------------------------------------------------------------------
-- Step 30
-- Do the other wilder choices
--------------------------------------------------------------------------
IF p_z IS NULL
AND p_m IS NOT NULL
THEN
RETURN SDO_GEOMETRY(
3301
,p_srid
,SDO_POINT_TYPE(
p_x
,p_y
,p_m
)
,NULL
,NULL
);

ELSIF p_z IS NOT NULL
AND   p_m IS NULL
THEN
RETURN SDO_GEOMETRY(
3001
,p_srid
,SDO_POINT_TYPE(
p_x
,p_y
,p_z
)
,NULL
,NULL
);

ELSIF p_z IS NOT NULL
AND   p_m IS NOT NULL
THEN
RETURN SDO_GEOMETRY(
4401
,p_srid
,NULL
,SDO_ELEM_INFO_ARRAY(1,1,1)
,SDO_ORDINATE_ARRAY(p_x,p_y,p_z,p_m)
);

ELSE
RAISE_APPLICATION_ERROR(-20001,'ERR!');
END IF;

END fast_point;

FUNCTION get_start_point(
p_input        IN  MDSYS.SDO_GEOMETRY
) RETURN MDSYS.SDO_GEOMETRY
AS
int_dims PLS_INTEGER;
int_gtyp PLS_INTEGER;
int_lrs  PLS_INTEGER;

BEGIN

--------------------------------------------------------------------------
-- Step 10
-- Check over incoming parameters
--------------------------------------------------------------------------
IF p_input IS NULL
THEN
RETURN NULL;

END IF;

--------------------------------------------------------------------------
-- Step 20
-- Gather information about the geometry
--------------------------------------------------------------------------
int_dims := p_input.get_dims();
int_gtyp := p_input.get_gtype();
int_lrs  := p_input.get_lrs_dim();

--------------------------------------------------------------------------
-- Step 30
-- Handle point and multipoint inputs
--------------------------------------------------------------------------
IF int_gtyp = 1
THEN
RETURN p_input;

ELSIF int_gtyp = 5
THEN
RETURN SDO_UTIL.EXTRACT(p_input,1);

END IF;

--------------------------------------------------------------------------
-- Step 40
-- Return results
--------------------------------------------------------------------------
IF int_dims = 2
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(1)
,p_input.SDO_ORDINATES(2)
,NULL
,NULL
,p_input.SDO_SRID
);

ELSIF  int_dims = 3
AND int_lrs = 3
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(1)
,p_input.SDO_ORDINATES(2)
,NULL
,p_input.SDO_ORDINATES(3)
,p_input.SDO_SRID
);

ELSIF  int_dims = 3
AND int_lrs = 0
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(1)
,p_input.SDO_ORDINATES(2)
,p_input.SDO_ORDINATES(3)
,NULL
,p_input.SDO_SRID
);

ELSIF  int_dims = 4
AND int_lrs IN (4,0)
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(1)
,p_input.SDO_ORDINATES(2)
,p_input.SDO_ORDINATES(3)
,p_input.SDO_ORDINATES(4)
,p_input.SDO_SRID
);

ELSIF  int_dims = 4
AND int_lrs = 3
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(1)
,p_input.SDO_ORDINATES(2)
,p_input.SDO_ORDINATES(4)
,p_input.SDO_ORDINATES(3)
,p_input.SDO_SRID
);

ELSE
RAISE_APPLICATION_ERROR(-20001,'ERR!');

END IF;

END get_start_point;

FUNCTION get_end_point(
p_input        IN  MDSYS.SDO_GEOMETRY
) RETURN MDSYS.SDO_GEOMETRY
AS
int_dims PLS_INTEGER;
int_gtyp PLS_INTEGER;
int_lrs  PLS_INTEGER;
int_len  PLS_INTEGER;

BEGIN

--------------------------------------------------------------------------
-- Step 10
-- Check over incoming parameters
--------------------------------------------------------------------------
IF p_input IS NULL
THEN
RETURN NULL;

END IF;

--------------------------------------------------------------------------
-- Step 20
-- Gather information about the geometry
--------------------------------------------------------------------------
int_dims := p_input.get_dims();
int_gtyp := p_input.get_gtype();
int_lrs  := p_input.get_lrs_dim();
int_len  := p_input.SDO_ORDINATES.COUNT();

--------------------------------------------------------------------------
-- Step 30
-- Handle point and multipoint inputs
--------------------------------------------------------------------------
IF int_gtyp = 1
THEN
RETURN p_input;
ELSIF int_gtyp = 5
THEN
RETURN SDO_UTIL.EXTRACT(
p_input
,SDO_UTIL.GETNUMELEM(p_input)
);
END IF;

--------------------------------------------------------------------------
-- Step 40
-- Return results
--------------------------------------------------------------------------
IF int_dims = 2
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(int_len - 1)
,p_input.SDO_ORDINATES(int_len)
,NULL
,NULL
,p_input.SDO_SRID
);

ELSIF  int_dims = 3
AND int_lrs = 3
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(int_len - 2)
,p_input.SDO_ORDINATES(int_len - 1)
,NULL
,p_input.SDO_ORDINATES(int_len)
,p_input.SDO_SRID
);

ELSIF  int_dims = 3
AND int_lrs = 0
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(int_len - 2)
,p_input.SDO_ORDINATES(int_len - 1)
,p_input.SDO_ORDINATES(int_len)
,NULL
,p_input.SDO_SRID
);

ELSIF  int_dims = 4
AND int_lrs IN (4,0)
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(int_len - 3)
,p_input.SDO_ORDINATES(int_len - 2)
,p_input.SDO_ORDINATES(int_len - 1)
,p_input.SDO_ORDINATES(int_len)
,p_input.SDO_SRID
);

ELSIF  int_dims = 4
AND int_lrs = 3
THEN
RETURN fast_point(
p_input.SDO_ORDINATES(int_len - 3)
,p_input.SDO_ORDINATES(int_len - 2)
,p_input.SDO_ORDINATES(int_len)
,p_input.SDO_ORDINATES(int_len - 1)
,p_input.SDO_SRID
);

ELSE
RAISE_APPLICATION_ERROR(-20001,'ERR!');

END IF;

END get_end_point;

FUNCTION is_spaghetti(
p_input             IN  MDSYS.SDO_GEOMETRY
,p_tolerance         IN  NUMBER DEFAULT 0.05
) RETURN VARCHAR2
AS
num_tolerance    NUMBER := p_tolerance;
ary_strings      MDSYS.SDO_GEOMETRY_ARRAY := MDSYS.SDO_GEOMETRY_ARRAY();
ary_starts       MDSYS.SDO_GEOMETRY_ARRAY := MDSYS.SDO_GEOMETRY_ARRAY();
ary_ends         MDSYS.SDO_GEOMETRY_ARRAY := MDSYS.SDO_GEOMETRY_ARRAY();
int_count        PLS_INTEGER;
ary_start_count  MDSYS.SDO_NUMBER_ARRAY := MDSYS.SDO_NUMBER_ARRAY();
ary_end_count    MDSYS.SDO_NUMBER_ARRAY := MDSYS.SDO_NUMBER_ARRAY();
ary_inside_count MDSYS.SDO_NUMBER_ARRAY := MDSYS.SDO_NUMBER_ARRAY();

BEGIN

--------------------------------------------------------------------------
-- Step 10
-- Check over incoming parameters
--------------------------------------------------------------------------
IF p_input IS NULL
THEN
RETURN NULL;

ELSIF p_input.get_gtype = 2
THEN
RETURN 'FALSE';

ELSIF p_input.get_gtype <> 6
THEN
RAISE_APPLICATION_ERROR(-20001,'input gtype must be 2 or 6');

END IF;

IF num_tolerance IS NULL
THEN
num_tolerance := 0.05;

END IF;

--------------------------------------------------------------------------
-- Step 20
-- Break multistring into single linestrings with nodes
--------------------------------------------------------------------------
int_count := SDO_UTIL.GETNUMELEM(p_input);
ary_strings.EXTEND(int_count);
ary_starts.EXTEND(int_count);
ary_ends.EXTEND(int_count);
ary_start_count.EXTEND(int_count);
ary_end_count.EXTEND(int_count);
ary_inside_count.EXTEND(int_count);
FOR i IN 1 .. int_count
LOOP
ary_strings(i) := SDO_UTIL.EXTRACT(p_input,i);
ary_starts(i)  := get_start_point(ary_strings(i));
ary_ends(i)    := get_end_point(ary_strings(i));

END LOOP;

--------------------------------------------------------------------------
-- Step 30
-- Loop through and count the nodes connections
--------------------------------------------------------------------------
FOR i IN 1 .. int_count
LOOP
ary_start_count(i)  := 0;
ary_end_count(i)    := 0;
ary_inside_count(i) := 0;

FOR j IN 1 .. int_count
LOOP
IF i != j
THEN
IF SDO_GEOM.RELATE(
ary_starts(i),
'DETERMINE',
ary_strings(j),
num_tolerance
) IN ('TOUCH','CONTAINS','COVERS','ON')
THEN
ary_start_count(i) := ary_start_count(i) + 1;

ELSIF SDO_GEOM.RELATE(
ary_ends(i),
'DETERMINE',
ary_strings(j),
num_tolerance
) IN ('TOUCH','CONTAINS','COVERS','ON')
THEN
ary_end_count(i) := ary_end_count(i) + 1;

ELSIF SDO_GEOM.RELATE(
ary_strings(i),
'DETERMINE',
ary_strings(j),
num_tolerance
) IN ('TOUCH','CONTAINS','COVERS','OVERLAPBYINTERSECT')
THEN
ary_inside_count(i) := ary_inside_count(i) + 1;

END IF;

END IF;

END LOOP;

IF ary_start_count(i) > 1
OR ary_end_count(i) > 1
OR ary_inside_count(i) > 0
THEN
RETURN 'TRUE';
END IF;

END LOOP;

RETURN 'FALSE';

END is_spaghetti;

-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
FUNCTION points2segment(
p_point_one              IN  MDSYS.SDO_POINT_TYPE
,p_point_two              IN  MDSYS.SDO_POINT_TYPE
,p_srid                   IN  NUMBER
) RETURN MDSYS.SDO_GEOMETRY
AS
BEGIN

IF ( p_point_one.Z IS NULL AND p_point_two.Z IS NOT NULL )
OR ( p_point_one.Z IS NOT NULL AND p_point_two.Z IS NULL )
THEN
RAISE_APPLICATION_ERROR(
-20001,
'both points must have the same number of dimensions, point_one Z is ' ||
NVL(TO_CHAR(p_point_one.Z),'<NULL>') ||
' and point_two Z is ' ||
NVL(TO_CHAR(p_point_two.Z),'<NULL>')
);

END IF;

IF p_point_one.Z IS NULL
THEN
RETURN SDO_GEOMETRY(
2002
,p_srid
,NULL
,SDO_ELEM_INFO_ARRAY(1,2,1)
,SDO_ORDINATE_ARRAY(p_point_one.X,p_point_one.Y,p_point_two.X,p_point_two.Y)
);

ELSE
RETURN SDO_GEOMETRY(
3002
,p_srid
,NULL
,SDO_ELEM_INFO_ARRAY(1,2,1)
,SDO_ORDINATE_ARRAY(p_point_one.X,p_point_one.Y,p_point_one.Z,p_point_two.X,p_point_two.Y,p_point_two.Z)
);

END IF;

END points2segment;

-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
FUNCTION points2segment(
p_point_one              IN  MDSYS.SDO_GEOMETRY
,p_point_two              IN  MDSYS.SDO_GEOMETRY
) RETURN MDSYS.SDO_GEOMETRY
AS
int_gtype1 PLS_INTEGER;
int_dims1  PLS_INTEGER;
int_gtype2 PLS_INTEGER;
int_dims2  PLS_INTEGER;
point_one  MDSYS.SDO_POINT_TYPE;
point_two  MDSYS.SDO_POINT_TYPE;

BEGIN

int_gtype1 := p_point_one.get_gtype();
int_dims1  := p_point_one.get_dims();
int_gtype2 := p_point_two.get_gtype();
int_dims2  := p_point_two.get_dims();

IF  int_gtype1 = 1
AND int_gtype2 = 1
AND int_dims1  = int_dims2
AND p_point_one.SDO_SRID = p_point_two.SDO_SRID
THEN
NULL;  -- Good

ELSE
RAISE_APPLICATION_ERROR(
-20001
,'both point objects must be points and have the same number of dimensions and SRIDs'
);

END IF;

IF int_dims1 = 4
THEN
RETURN SDO_GEOMETRY(
4402
,p_point_one.SDO_SRID
,NULL
,SDO_ELEM_INFO_ARRAY(1,2,1)
,SDO_ORDINATE_ARRAY(
p_point_one.SDO_ORDINATES(1)
,p_point_one.SDO_ORDINATES(2)
,p_point_one.SDO_ORDINATES(3)
,p_point_one.SDO_ORDINATES(4)
,p_point_two.SDO_ORDINATES(1)
,p_point_two.SDO_ORDINATES(2)
,p_point_two.SDO_ORDINATES(3)
,p_point_two.SDO_ORDINATES(4)
)
);

ELSE
-- Use the sdo_point_type method for the rest
IF p_point_one.SDO_POINT IS NOT NULL
THEN
point_one := p_point_one.SDO_POINT;

ELSE
IF int_dims1 = 3
THEN
point_one := SDO_POINT_TYPE(
p_point_one.SDO_ORDINATES(1)
,p_point_one.SDO_ORDINATES(2)
,p_point_one.SDO_ORDINATES(3)
);

ELSE
point_one := SDO_POINT_TYPE(
p_point_one.SDO_ORDINATES(1)
,p_point_one.SDO_ORDINATES(2)
,NULL
);

END IF;

END IF;

IF p_point_two.SDO_POINT IS NOT NULL
THEN
point_two := p_point_two.SDO_POINT;

ELSE
IF int_dims1 = 3
THEN
point_two := SDO_POINT_TYPE(
p_point_two.SDO_ORDINATES(1)
,p_point_two.SDO_ORDINATES(2)
,p_point_two.SDO_ORDINATES(3)
);

ELSE
point_two := SDO_POINT_TYPE(
p_point_two.SDO_ORDINATES(1)
,p_point_two.SDO_ORDINATES(2)
,NULL
);

END IF;

END IF;

RETURN points2segment(
p_point_one   => point_one
,p_point_two   => point_two
,p_srid        => p_point_one.SDO_SRID
);

END IF;

END points2segment;

FUNCTION linear_gap_filler(
p_input            IN  MDSYS.SDO_GEOMETRY
,p_tolerance        IN  NUMBER DEFAULT 0.05
) RETURN MDSYS.SDO_GEOMETRY
AS
sdo_input     MDSYS.SDO_GEOMETRY := p_input;
num_tolerance NUMBER;
int_counter   PLS_INTEGER;
ary_edges     MDSYS.SDO_GEOMETRY_ARRAY;
ary_starts    MDSYS.SDO_GEOMETRY_ARRAY;
ary_ends      MDSYS.SDO_GEOMETRY_ARRAY;
ary_nearest   MDSYS.SDO_NUMBER_ARRAY;
ary_distance  MDSYS.SDO_NUMBER_ARRAY;
num_temp      NUMBER;
num_nearest   NUMBER;
int_winner    PLS_INTEGER;
int_winner2   PLS_INTEGER;
sdo_point1    MDSYS.SDO_GEOMETRY;
sdo_point2    MDSYS.SDO_GEOMETRY;
boo_done      BOOLEAN;
num_one       NUMBER;
num_two       NUMBER;
int_looper    PLS_INTEGER := 1;

BEGIN

--------------------------------------------------------------------------
-- Step 10
-- Check over incoming parameters
--------------------------------------------------------------------------
IF num_tolerance IS NULL
THEN
num_tolerance := 0.05;

END IF;

IF sdo_input IS NULL
OR sdo_input.get_gtype() <> 6
THEN
RETURN sdo_input;

END IF;

IF is_spaghetti(sdo_input,p_tolerance) = 'TRUE'
THEN
RETURN sdo_input;

END IF;

<<TOP_OF_IT>>
ary_edges     := MDSYS.SDO_GEOMETRY_ARRAY();
ary_starts    := MDSYS.SDO_GEOMETRY_ARRAY();
ary_ends      := MDSYS.SDO_GEOMETRY_ARRAY();
ary_nearest   := MDSYS.SDO_NUMBER_ARRAY();
ary_distance  := MDSYS.SDO_NUMBER_ARRAY();

--------------------------------------------------------------------------
-- Step 20
-- Break multistring into edges and start and end nodes
--------------------------------------------------------------------------
int_counter := SDO_UTIL.GETNUMELEM(sdo_input);
ary_edges.EXTEND(int_counter);
ary_starts.EXTEND(int_counter);
ary_ends.EXTEND(int_counter);
FOR i IN 1 .. int_counter
LOOP
ary_edges(i)  := SDO_UTIL.EXTRACT(sdo_input,i);
ary_starts(i) := get_start_point(ary_edges(i));
ary_ends(i)   := get_end_point(ary_edges(i));

END LOOP;

--------------------------------------------------------------------------
-- Step 30
-- Determine the closest endpoints
--------------------------------------------------------------------------
ary_nearest.EXTEND(int_counter);
ary_distance.EXTEND(int_counter);
FOR i IN 1 .. int_counter
LOOP
num_nearest := NULL;
int_winner := NULL;
FOR j IN 1 .. int_counter
LOOP
IF j != i
THEN
num_temp := SDO_GEOM.SDO_DISTANCE(
ary_edges(i)
,ary_edges(j)
,num_tolerance
);

IF num_nearest IS NULL
OR num_temp < num_nearest
THEN
num_nearest := num_temp;
int_winner := j;

END IF;

END IF;

END LOOP;

ary_nearest(i) := int_winner;
ary_distance(i) := num_nearest;

END LOOP;

--------------------------------------------------------------------------
-- Step 40
-- Find the smallest gap
--------------------------------------------------------------------------
int_winner := NULL;
num_nearest := NULL;
FOR i IN 1 .. int_counter
LOOP
IF num_nearest IS NULL
OR ary_distance(i) < num_nearest
THEN
int_winner := i;
num_nearest := ary_distance(i);
int_winner2 := ary_nearest(i);

END IF;

END LOOP;

--------------------------------------------------------------------------
-- Step 50
-- Determine the endpoints to connect
--------------------------------------------------------------------------
num_one := SDO_GEOM.SDO_DISTANCE(
get_start_point(ary_edges(int_winner)),
ary_edges(int_winner2),
num_tolerance
);
num_two := SDO_GEOM.SDO_DISTANCE(
get_end_point(ary_edges(int_winner)),
ary_edges(int_winner2),
num_tolerance
);

IF ( num_one = 0 AND SDO_GEOM.RELATE(
get_start_point(ary_edges(int_winner)),
'ANYINTERACT',
ary_edges(int_winner2),
num_tolerance
) = 'TRUE' )
OR ( num_two = 0 AND SDO_GEOM.RELATE(
get_end_point(ary_edges(int_winner)),
'ANYINTERACT',
ary_edges(int_winner2),
num_tolerance
) = 'TRUE' )
THEN
sdo_point1 := NULL;

ELSIF num_one < num_two
THEN
sdo_point1 := get_start_point(ary_edges(int_winner));

ELSE
sdo_point1 := get_end_point(ary_edges(int_winner));

END IF;

num_one := SDO_GEOM.SDO_DISTANCE(
get_start_point(ary_edges(int_winner2)),
ary_edges(int_winner),
num_tolerance
);
num_two := SDO_GEOM.SDO_DISTANCE(
get_end_point(ary_edges(int_winner2)),
ary_edges(int_winner),
num_tolerance
);

IF ( num_one = 0 AND SDO_GEOM.RELATE(
get_start_point(ary_edges(int_winner2)),
'ANYINTERACT',
ary_edges(int_winner),
num_tolerance
) = 'TRUE' )
OR ( num_two = 0 AND SDO_GEOM.RELATE(
get_end_point(ary_edges(int_winner2)),
'ANYINTERACT',
ary_edges(int_winner),
num_tolerance
) = 'TRUE' )
THEN
sdo_point2 := NULL;

ELSIF num_one < num_two
THEN
sdo_point2 := get_start_point(ary_edges(int_winner2));

ELSE
sdo_point2 := get_end_point(ary_edges(int_winner2));

END IF;

--------------------------------------------------------------------------
-- Step 50
-- Smash together
--------------------------------------------------------------------------
IF sdo_point1 IS NULL
OR sdo_point2 IS NULL
THEN
sdo_input := SDO_UTIL.CONCAT_LINES(
ary_edges(int_winner),
ary_edges(int_winner2)
);

ELSE
sdo_input := SDO_UTIL.CONCAT_LINES(
SDO_UTIL.CONCAT_LINES(
ary_edges(int_winner),
points2segment(sdo_point1,sdo_point2)
),
ary_edges(int_winner2)
);

END IF;

boo_done := TRUE;
FOR i IN 1 .. int_counter
LOOP
IF i NOT IN (int_winner,int_winner2)
THEN
sdo_input := SDO_UTIL.APPEND(sdo_input,ary_edges(i));
boo_done := FALSE;

END IF;

END LOOP;

--------------------------------------------------------------------------
-- Step 60
-- Check if valid if returning
--------------------------------------------------------------------------
IF sdo_input.get_gtype() = 2
OR boo_done = TRUE
THEN
RETURN sdo_input;

END IF;

int_looper := int_looper + 1;
GOTO TOP_OF_IT;

END linear_gap_filler;

END dz_gap_fill;
```
• ###### 9. Re: How to bridge (fill) gaps when converting a 2006 geometry  into a 2002 geometry

Hi Paul,

Thanks for that it is working great! Much Appreciated!

Regards

Mark