7 Replies Latest reply: Dec 2, 2013 12:50 AM by _jum RSS

    Explanation of behavior SDO_GEOM.RELATE for two polylines

    _jum

      Can somebody explain the "strange" behavior of sdo_geom.relate ? At some specific tolerance values the relation changes.

      This makes sense IMO at 0.9 as distance from the endpoint l1 to l2, but with the other tolerances ?

      (The coordinates where taken from a real problem and simplified as much as possible.)

       

      WITH tols AS
      (SELECT      0.01 tol FROM dual UNION ALL
        SELECT      0.89     FROM dual UNION ALL
        SELECT      0.90     FROM dual UNION ALL
        SELECT      1.00     FROM dual UNION ALL
        SELECT      1.01     FROM dual UNION ALL
        SELECT      9.00     FROM dual UNION ALL
        SELECT      9.01     FROM dual UNION ALL
        SELECT     10.00     FROM dual UNION ALL
        SELECT     10.01     FROM dual UNION ALL
        SELECT     90.00     FROM dual UNION ALL
        SELECT     90.01     FROM dual UNION ALL
        SELECT    100.00     FROM dual UNION ALL
        SELECT    100.01     FROM dual UNION ALL
        SELECT    900.00     FROM dual UNION ALL
        SELECT    900.01     FROM dual UNION ALL
        SELECT 100000.00     FROM dual UNION ALL
        SELECT 100000.10     FROM dual),
           g1 AS
      (SELECT SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY( -60,0, 0,0, 1,0, 10.1,0.1)) l FROM dual),
           g2 AS
      (SELECT SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY( -10,0, 0,0, 1,0, 11.0,0.0)) l FROM dual)
      SELECT to_char(tol,'999990.99') tolc, sdo_geom.relate(g1.l,'DETERMINE',g2.l,tol)  rela
      FROM g1, g2, tols
      ORDER BY tol;
      
      
      
      

       

       

      TOLC          RELA
      -----------------------------------------------------------

       

           0.01    OVERLAPBDYDISJOINT
           0.89    OVERLAPBDYDISJOINT
           0.90    COVERS
           1.00    COVERS
           1.01    OVERLAPBDYDISJOINT
           9.00    OVERLAPBDYDISJOINT
           9.01    COVERS
          10.00    COVERS
          10.01    OVERLAPBDYDISJOINT
          90.00    OVERLAPBDYDISJOINT
          90.01    COVERS
         100.00    COVERS
         100.01    OVERLAPBDYDISJOINT
         900.00    OVERLAPBDYDISJOINT
         900.01    COVERS
      100000.00    COVERS
      100000.10    EQUAL

        • 1. Re: Explanation of behavior SDO_GEOM.RELATE for two polylines
          Ying Hu-Oracle

          In 12c, I got:

            0.01  OVERLAPBDYDISJOINT
            0.89  OVERLAPBDYDISJOINT
            0.90  OVERLAPBDYDISJOINT
            1.00  COVERS
            1.01  COVERS
            9.00  COVERS
            9.01  COVERS
          10.00  CONTAINS
          10.01  CONTAINS
          90.00  EQUAL
          90.01  EQUAL
          100.00  EQUAL
          100.01  EQUAL
          900.00  EQUAL
          900.01  EQUAL

          100000.00  EQUAL

          100000.10  EQUAL

          • 2. Re: Explanation of behavior SDO_GEOM.RELATE for two polylines
            _jum

            yhu - thanks for testing this in 12c!

            I tried in 10.2.0.5 and 11.2.0.3. with the "strange" results above.

            Unfortunately this versions will still be used from us/our customers for some time, so I have to find a workaround...

            • 3. Re: Explanation of behavior SDO_GEOM.RELATE for two polylines
              Ying Hu-Oracle

              Maybe contact oracle support to get a backport.

              • 4. Re: Explanation of behavior SDO_GEOM.RELATE for two polylines
                B Hall

                FYI - I get the same result as you in 11.2.0.4 as well, in case they ask.

                • 5. Re: Explanation of behavior SDO_GEOM.RELATE for two polylines
                  _jum

                  Thanks - hoped a little that the 11.2.0.4 patch could help...

                  Opened a SR.

                  • 6. Re: Explanation of behavior SDO_GEOM.RELATE for two polylines
                    Siva Ravada

                    The input data is not valid at the higher tolerance values (tolerance more than 1.0) and once the data is invalid, the results from RELATE are not

                    going to be stable.

                    And tolerance is not intended to be used in this manner, so what are you trying to test with these high tolerance values ?

                     

                    WITH tols AS

                       (SELECT      0.01 tol FROM dual UNION ALL 

                         SELECT      0.89     FROM dual UNION ALL 

                         SELECT      0.90     FROM dual UNION ALL 

                         SELECT      1.00     FROM dual UNION ALL 

                         SELECT      1.01     FROM dual UNION ALL 

                         SELECT      9.00     FROM dual UNION ALL 

                         SELECT      9.01     FROM dual UNION ALL 

                         SELECT     10.00     FROM dual UNION ALL 

                         SELECT     10.01     FROM dual UNION ALL 

                         SELECT     90.00     FROM dual UNION ALL 

                         SELECT     90.01     FROM dual UNION ALL 

                         SELECT    100.00     FROM dual UNION ALL 

                         SELECT    100.01     FROM dual UNION ALL 

                         SELECT    900.00     FROM dual UNION ALL 

                         SELECT    900.01     FROM dual UNION ALL 

                         SELECT 100000.00     FROM dual UNION ALL 

                         SELECT 100000.10     FROM dual), 

                            g1 AS 

                       (SELECT SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY( -60,0, 0,0, 1,0, 10.1,0.1)) l FROM dual), 

                            g2 AS 

                       (SELECT SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY( -10,0, 0,0, 1,0, 11.0,0.0)) l FROM dual) 

                       SELECT to_char(tol,'999990.99') tolc,

                       sdo_geom.relate(g1.l,'DETERMINE',g2.l,tol)  rela,

                       sdo_geom.validate_geometry_with_context(g1.l,tol)  status1,     

                       sdo_geom.validate_geometry_with_context(g2.l,tol)  status2

                       FROM g1, g2, tols 

                       ORDER BY tol;

                     

                     

                      0.01

                    OVERLAPBDYDISJOINT

                    TRUE

                    TRUE

                     

                      0.89

                    OVERLAPBDYDISJOINT

                    TRUE

                    TRUE

                     

                      0.90

                    COVERS

                    TRUE

                    TRUE

                     

                      1.00

                    COVERS

                    TRUE

                    TRUE

                     

                      1.01

                    OVERLAPBDYDISJOINT

                    13356 [Element <1>] [Coordinate <2>]

                    13356 [Element <1>] [Coordinate <2>]

                     

                      9.00

                    OVERLAPBDYDISJOINT

                    13356 [Element <1>] [Coordinate <2>]

                    13356 [Element <1>] [Coordinate <2>]

                     

                      9.01

                    COVERS

                    13356 [Element <1>] [Coordinate <2>]

                    13356 [Element <1>] [Coordinate <2>]

                     

                    10.00

                    COVERS

                    13356 [Element <1>] [Coordinate <2>]

                    13356 [Element <1>] [Coordinate <2>]

                     

                    10.01

                    OVERLAPBDYDISJOINT

                    13356 [Element <1>] [Coordinate <2>]

                    13356 [Element <1>] [Coordinate <1>]

                     

                    90.00

                    OVERLAPBDYDISJOINT

                    13356 [Element <1>] [Coordinate <1>]

                    13356 [Element <1>] [Coordinate <1>]

                     

                    90.01

                    COVERS

                    13356 [Element <1>] [Coordinate <1>]

                    13356 [Element <1>] [Coordinate <1>]

                     

                    100.00

                    COVERS

                    13356 [Element <1>] [Coordinate <1>]

                    13356 [Element <1>] [Coordinate <1>]

                     

                    100.01

                    OVERLAPBDYDISJOINT

                    13356 [Element <1>] [Coordinate <1>]

                    13356 [Element <1>] [Coordinate <1>]

                     

                    900.00

                    OVERLAPBDYDISJOINT

                    13356 [Element <1>] [Coordinate <1>]

                    13356 [Element <1>] [Coordinate <1>]

                     

                    900.01

                    COVERS

                    13356 [Element <1>] [Coordinate <1>]

                    13356 [Element <1>] [Coordinate <1>]

                     

                    100000.00

                    COVERS

                    13356 [Element <1>] [Coordinate <1>]

                    13356 [Element <1>] [Coordinate <1>]

                     

                    100000.10

                    EQUAL

                    13356 [Element <1>] [Coordinate <1>]

                    13356 [Element <1>] [Coordinate <1>]

                    • 7. Re: Explanation of behavior SDO_GEOM.RELATE for two polylines
                      _jum

                      Got the answer to my SR:
                      Here the facts in short:
                      -this is improved in 12.1 and above (as yhu found)

                      -once the tolerance goes over 1.0, the input data is not valid anymore for the given tolerance

                      sdo_geom.relate expects valid geometries passed in

                      in this case, the large tolerances result in invalid geometries being passed to sdo_geom.relate (as SivaRavada stated)

                      As a workaround it is possible to use buffers.


                      Thanks for your helpful answers!