This discussion is archived
9 Replies Latest reply: Sep 3, 2013 3:15 AM by MRoche RSS

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

MRoche Newbie
Currently Being Moderated

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

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

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

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

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

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

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

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

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


    Hi Paul,

    Thanks for that it is working great! Much Appreciated!

    Regards

    Mark

Legend

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