7 Replies Latest reply: Dec 1, 2013 10:50 PM by _jum

Explanation of behavior SDO_GEOM.RELATE for two polylines

Currently Being Moderated

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

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

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

Maybe contact oracle support to get a backport.

• 4. Re: Explanation of behavior SDO_GEOM.RELATE for two polylines
Currently Being Moderated

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

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

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.9

COVERS

TRUE

TRUE

 1

COVERS

TRUE

TRUE

 1.01

OVERLAPBDYDISJOINT

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

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

 9

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

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

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

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

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

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.