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

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

    MRoche

      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    

       

      Thanks in advance

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

          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
            MRoche

            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
              Fa

              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
                Paul Dziemiela

                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
                  MRoche

                  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
                    Paul Dziemiela

                    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
                      MRoche

                      Paul,

                      Thanks for your help and advice so far.

                       

                      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
                        Paul Dziemiela

                        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
                          MRoche


                          Hi Paul,

                          Thanks for that it is working great! Much Appreciated!

                          Regards

                          Mark