This discussion is archived
4 Replies Latest reply: May 1, 2012 10:39 AM by Paul Dziemiela RSS

SDO_LRS.LRS_INTERSECTION returns ORA-13331:  invalid LRS segment

712855 Newbie
Currently Being Moderated
I was experimenting with the LRS_INTERSECTION function by running

select SDO_LRS.LRS_INTERSECTION(
sdo_geometry(3302, 8307, null, sdo_elem_info_array(1,2,1), sdo_ordinate_array(-79, 37, 0, -80, 38, null, -79, 39, null, -80, 48, 100)),
sdo_geometry(3302, 8307, null, sdo_elem_info_array(1,2,1), sdo_ordinate_array(-79, 39, 0, -80, 40, null, -80, 38, null, -79, 37, 100)),
20
)
from dual

I get the following error:

ORA-13331: invalid LRS segment
ORA-06512: at "MDSYS.SDO_LRS", line 8496
13331. 00000 - "invalid LRS segment"

Somehow, I fail to see what is invalid about either of the LRS geometries that I passed into the function. Interestingly enough, if I change the last latitude coordinate in the first geometry argument from '48' to '40', I don't get any errors. The problem is that I really want to compute the intersection of the geometries exactly as shown above. Does anybody have any ideas why SDO_LRS.LRS_INTERSECTION considers either of the geometry parameter values invalid?
  • 1. Re: SDO_LRS.LRS_INTERSECTION returns ORA-13331:  invalid LRS segment
    Paul Dziemiela Journeyer
    Currently Being Moderated
    Hello lowlyscrub,

    You have discovered a "feature" of LRS_INTERSECTION that is kind of a bummer in my opinion. The LRS specification only allows valid LRS geometries to be comprised of either a single string, a multistring or ONE point. If by the vagaries of your intersection you happen to receive back some other set of geometries, then the LRS result is invalid and you get that error message. The easiest way to see what I mean is to run your same query using good old SDO_INTERSECTION instead.

    Your intersection returns a collection of one linestring and one point. Thus this collection is not valid LRS and thus the error. The Oracle Spatial folks have said this is by design. I think I requested they better document this "feature" a while back. LRS_INTERSECTION is a rather new feature that came along in 10.2.0.3 I think. It's main selling point was that it preserves the direction of the intersected line strings. Before that I used a utility wrapper around SDO_INTERSECTION which then tested the results afterward and corrected any backwards line strings and removed any stray points. LRS_INTERSECTION is faster than my old utility. So what I do nowadays is I have a wrapper that submits the inputs to LRS_INTERSECTION, catches any 13331 errors and if it catches one it then submits the same inputs to the old utility.

    I would indeed encourage you to put in some feedback through Oracle support on the matter. It would be nice to have a switch on LRS_INTERSECTION that would either allow back these "bad" LRS geometries for correction by the user or have the utility itself remove those stray points.

    Cheers,

    Paul
  • 2. Re: SDO_LRS.LRS_INTERSECTION returns ORA-13331:  invalid LRS segment
    712855 Newbie
    Currently Being Moderated
    Thanks, Paul! That answer made a lot of sense. Experimentation using SDO_GEOM.SDO_INTERSECTION() produces the results you would have predicted. I can see why Oracle chose such behavior for SDO_LRS.LRS_INTERSECTION; I guess it's due to potential mathematical issues in defining the measure values in a geometry consisting of a line and a point, e.g. geometry type 3304.

    It looks like I'm eventually going to have to write a shell around SDO_LRS.LRS_INTERSECTION to do what you've explained.
  • 3. Re: SDO_LRS.LRS_INTERSECTION returns ORA-13331:  invalid LRS segment
    748291 Newbie
    Currently Being Moderated
    Paul,
    I've just encountered ORA-13331 when using SDO_LRS.LRS_INTERSECTION under the same circumstances when the result geometry is a mix of linear and point portions.
    You mentioned you had written code to trap ORA-13331 errors and then run the standard sdo_geom.sdo_intersection function when this occurs.
    Would you be willing to share the code you have written? - I am particularly interested in how you were able to generate the measure values on the resulting geometry using the non-LRS Oracle Spatial functions.
    Thanks in advance for any suggestions or code you can provide.
    ..Brad
  • 4. Re: SDO_LRS.LRS_INTERSECTION returns ORA-13331:  invalid LRS segment
    Paul Dziemiela Journeyer
    Currently Being Moderated
    Hello Brad,

    Well, I was hoping that more folks might complain to the Spatial team about the matter as it would be nice to have it fixed in the function rather than just letting it lie around as a "feature" we all have to be aware of and code around. But sure. My code was written to intersect a line with one or more polygons. I do not care about those resulting stray points, I just chuck them. I see that lowlyscrub was using two linestrings and I am not sure what you are doing. I guess two lines is fine though it would be interesting to know the business case. I keep the two functions separate in a package but I just nested them here to make one free-standing function.

    See if it works for you and feel free to suggest improvements. As I said I have not tried to do much with comparing two linestrings. Note the input tolerance is just for the intersections. You'd need a second tolerance parameter if you want to control the LRS tolerance. I usually just go with the 1.0e-8 LRS default.

    Cheers,
    Paul
    CREATE OR REPLACE FUNCTION safe_lrs_intersection(
       p_geometry_1   IN SDO_GEOMETRY,
       p_geometry_2   IN SDO_GEOMETRY,
       p_tolerance    IN NUMBER
    ) RETURN SDO_GEOMETRY
    AS
       sdo_initial    SDO_GEOMETRY;
       boo_baddie     BOOLEAN := FALSE;
       sdo_oldinter   SDO_GEOMETRY;
       sdo_newinter   SDO_GEOMETRY;
       num_tolerance  NUMBER := p_tolerance;
       
       --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
       --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
       FUNCTION overlay_lrs_measures(
          p_input_geometry  IN  SDO_GEOMETRY,
          p_lrs_geometry    IN  SDO_GEOMETRY
       ) RETURN SDO_GEOMETRY
       AS
          sdo_input_start SDO_GEOMETRY;
          sdo_input_end   SDO_GEOMETRY;
          num_start_meas  NUMBER;
          num_end_meas    NUMBER;
          sdo_lrs_output  SDO_GEOMETRY;
          int_len         PLS_INTEGER;
          int_dims        PLS_INTEGER;
          
       BEGIN
    
          --------------------------------------------------------------------------
          -- Step 10
          -- Collect the start and end points of the input linestring
          --------------------------------------------------------------------------
          int_len  := p_input_geometry.SDO_ORDINATES.COUNT();
          int_dims := p_input_geometry.get_dims();
          sdo_input_start := SDO_GEOMETRY(
             2001,
             p_input_geometry.SDO_SRID,
             SDO_POINT_TYPE(
                p_input_geometry.SDO_ORDINATES(1),
                p_input_geometry.SDO_ORDINATES(2),
                NULL
             ),
             NULL,
             NULL
          );
          sdo_input_end := SDO_GEOMETRY(
             2001,
             p_input_geometry.SDO_SRID,
             SDO_POINT_TYPE(
                p_input_geometry.SDO_ORDINATES(int_len - (int_dims - 1)),
                p_input_geometry.SDO_ORDINATES(int_len - (int_dims - 2)),
                NULL
             ),
             NULL,
             NULL
          );
    
          --------------------------------------------------------------------------
          -- Step 30
          -- Collect the start and end measure of the input geometry on the lrs
          --------------------------------------------------------------------------
          num_start_meas := SDO_LRS.GET_MEASURE(
             SDO_LRS.PROJECT_PT(
                geom_segment => p_lrs_geometry,
                point        => sdo_input_start
             )
          );
             
          num_end_meas := SDO_LRS.GET_MEASURE(
             SDO_LRS.PROJECT_PT(
                geom_segment => p_lrs_geometry,
                point        => sdo_input_end
             )
          );
    
          --------------------------------------------------------------------------
          -- Step 40
          -- Build the new LRS string from the measures
          --------------------------------------------------------------------------
          sdo_lrs_output := p_input_geometry;
    
          IF sdo_lrs_output.get_dims() = 2
          THEN
             sdo_lrs_output := SDO_LRS.CONVERT_TO_LRS_GEOM(
                standard_geom => sdo_lrs_output
             );
             
          END IF;
    
          SDO_LRS.RESET_MEASURE(
             geom_segment  => sdo_lrs_output
          );
          
          SDO_LRS.REDEFINE_GEOM_SEGMENT(
             geom_segment  => sdo_lrs_output,
             start_measure => num_start_meas,
             end_measure   => num_end_meas
          );
          
          -- This is to fix an old problem with ArcSDE and LRS
          IF sdo_lrs_output.get_lrs_dim() = 0
          THEN
             sdo_lrs_output.SDO_GTYPE := TO_NUMBER(
                sdo_lrs_output.get_dims() ||
                sdo_lrs_output.get_dims() ||
                '0' ||
                sdo_lrs_output.get_gtype()
             );
             
          END IF;
    
          --------------------------------------------------------------------------
          -- Step 40
          -- Check to see if the geometry is backwards
          --------------------------------------------------------------------------
          IF num_start_meas < num_end_meas
          THEN
             sdo_lrs_output := SDO_LRS.REVERSE_GEOMETRY(
                geom => sdo_lrs_output
             );
             
          END IF;
    
          --------------------------------------------------------------------------
          -- Step 50
          -- Return the results
          --------------------------------------------------------------------------
          RETURN sdo_lrs_output;
    
       END overlay_lrs_measures;
       --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
       --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
    
    BEGIN
    
       --------------------------------------------------------------------------
       -- Step 10
       -- Check over incoming parameters
       --------------------------------------------------------------------------
       IF num_tolerance IS NULL
       THEN
          num_tolerance := 0.05;
          
       END IF;
    
       --------------------------------------------------------------------------
       -- Step 20
       -- Run the initial Intersection and hope for the best
       --------------------------------------------------------------------------
       BEGIN
          sdo_initial := SDO_LRS.LRS_INTERSECTION(
             geom_1    => p_geometry_1,
             geom_2    => p_geometry_2,
             tolerance => num_tolerance
          );
          
       EXCEPTION
          WHEN OTHERS
          THEN
             IF SQLCODE = -13331
             THEN
                boo_baddie := TRUE;
             ELSE
                RAISE;
             END IF;
             
       END;
    
       --------------------------------------------------------------------------
       -- Step 30
       -- Dump out the results as we assume things went successfully
       --------------------------------------------------------------------------
       IF boo_baddie = FALSE
       THEN
          RETURN sdo_initial;
          
       END IF;
    
       --------------------------------------------------------------------------
       -- Step 40
       -- Lets do the intersection again with regular SDO_INTERSECTIOM
       --------------------------------------------------------------------------
       sdo_oldinter := SDO_GEOM.SDO_INTERSECTION(
          geom1 => p_geometry_1,
          geom2 => p_geometry_2,
          tol   => num_tolerance
       );
    
       --------------------------------------------------------------------------
       -- Step 50
       -- Now see what we got, only gtypes 2, 5, 6 and 4 are reasonable for this scenario
       -- 5 means we just got back a bunch of points so we can ditch them all
       -- and return NULL
       --------------------------------------------------------------------------
       IF sdo_oldinter.get_gtype() = 5
       THEN
          RETURN NULL;
          
       ELSIF sdo_oldinter.get_gtype() IN (2,4,6)
       THEN
          NULL; -- OK
          
       ELSE
          RAISE_APPLICATION_ERROR(
             -20001,
             'LRS_INTERSECTION failed with error -13331 - cannot recover. ' || CHR(13) ||
             'sdo_intersection component returned gtype ' || TO_CHAR(sdo_oldinter.get_gtype())
          );
          
       END IF;
    
       --------------------------------------------------------------------------
       -- Step 60
       -- So now we could have:
       -- 1) A single line string
       -- 2) Several linestrings
       -- 3) A mix of linestrings and points
       -- So pick out the linestrings into a new "pure" gtype 2 or 6
       -- At the same time reorient each found linestring against the original line
       --------------------------------------------------------------------------
       FOR i IN 1 .. SDO_UTIL.GETNUMELEM(sdo_oldinter)
       LOOP
          sdo_initial := SDO_UTIL.EXTRACT(sdo_oldinter,i);
    
          IF sdo_initial.get_gtype() = 2
          THEN
             sdo_initial := overlay_lrs_measures(
                p_input_geometry => sdo_initial,
                p_lrs_geometry   => p_geometry_1
             );
    
             IF sdo_newinter IS NULL
             THEN
                sdo_newinter := sdo_initial;
                   
             ELSE
                sdo_newinter := SDO_LRS.CONCATENATE_GEOM_SEGMENTS(
                   geom_segment_1 => sdo_newinter,
                   geom_segment_2 => sdo_initial
                );
                   
             END IF;
    
          END IF;
    
       END LOOP;
    
       --------------------------------------------------------------------------
       -- Step 70
       -- Final check and then return the results
       --------------------------------------------------------------------------
       IF sdo_newinter.get_gtype() NOT IN (2,6)
       THEN
          RAISE_APPLICATION_ERROR(-20001,'ERROR, unable to process geometry!');
          
       END IF;
    
       RETURN sdo_newinter;
    
    END safe_lrs_intersection;

Legend

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