4 Replies Latest reply: Dec 27, 2012 7:24 AM by Paul Dziemiela RSS

    Strange zero distance results for DISJOINT geometries

    Paul Dziemiela
      Hi folks,

      Not sure if I am missing something here but the query below seems to be problematic. At the supported 0.05 meter tolerance these geodetic geometries are DISJOINT and are valid so why the zero distance?

      The query on my 11.2.0.3.2 machine returns DISJOINT, 0, TRUE, TRUE.

      If its disjoint at 0.05 meters shouldn't there be a measurable distance at 0.05 meters? Changing the tolerance up or down seems to make no difference.

      Is this Bug 13402612? That seems to be about LRS measures and I've none in this example. Has anyone installed patch 13402612 and could run the query?

      Thanks so much,

      Paul
      WITH foo AS (
         SELECT
         SDO_GEOMETRY(
            2002,
            8265,
            NULL,
            SDO_ELEM_INFO_ARRAY(
               1,
               2,
               1
            ),
            SDO_ORDINATE_ARRAY(
               -80.4909413277175,
               36.1143890437704,
               -80.4909189996158,
               36.1143720699674
            )
         ) AS geom1,
         SDO_GEOMETRY(
            2002,
            8265,
            NULL,
            SDO_ELEM_INFO_ARRAY(
               1,
               2,
               1
            ),
            SDO_ORDINATE_ARRAY(
               -80.490922570435,
               36.114372538981,
               -80.4909415698575,
               36.1143879189855
            )
         ) AS geom2
         FROM dual
      )
      SELECT
      SDO_GEOM.RELATE(
         geom1,
         'DETERMINE',
         geom2,
         0.05
      ) AS relate,
      SDO_GEOM.SDO_DISTANCE(
         geom1,
         geom2,
         0.05,
         'UNIT=M'
      ) AS distance,
      SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(geom1,0.05) AS geom1_valid,
      SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(geom2,0.05) AS geom2_valid
      FROM 
      foo;
        • 1. Re: Strange zero distance results for DISJOINT geometries
          Stefan Jager
          Hi Paul,

          Don't know if it is that bug you mentioned, but it is Oracle Version related. I am running
          SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 30 13:15:37 2012
          Copyright (c) 1982, 2010, Oracle.  All rights reserved.
          
          Connected to:
          Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
          With the Partitioning, OLAP, Data Mining and Real Application Testing options
          And this is what I get:
          SQL> WITH foo AS (
            2     SELECT
            3     SDO_GEOMETRY(
            4        2002,
            5        8265,
            6        NULL,
            7        SDO_ELEM_INFO_ARRAY(
            8           1,
            9           2,
           10           1
           11        ),
           12        SDO_ORDINATE_ARRAY(
           13           -80.4909413277175,
           14           36.1143890437704,
           15           -80.4909189996158,
           16           36.1143720699674
           17        )
           18     ) AS geom1,
           19     SDO_GEOMETRY(
           20        2002,
           21        8265,
           22        NULL,
           23        SDO_ELEM_INFO_ARRAY(
           24           1,
           25           2,
           26           1
           27        ),
           28        SDO_ORDINATE_ARRAY(
           29           -80.490922570435,
           30           36.114372538981,
           31           -80.4909415698575,
           32           36.1143879189855
           33        )
           34     ) AS geom2
           35     FROM dual
           36  )
           37  SELECT
           38  SDO_GEOM.RELATE(
           39     geom1,
           40     'DETERMINE',
           41     geom2,
           42     0.05
           43  ) AS relate,
           44  SDO_GEOM.SDO_DISTANCE(
           45     geom1,
           46     geom2,
           47     0.05,
           48     'UNIT=M'
           49  ) AS distance,
           50  SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(geom1,0.05) AS geom1_valid,
           51  SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(geom2,0.05) AS geom2_valid
           52  FROM
           53  foo;
          
          RELATE
          --------------------------------------------------------------------------------
          
            DISTANCE
          ----------
          GEOM1_VALID
          --------------------------------------------------------------------------------
          
          GEOM2_VALID
          --------------------------------------------------------------------------------
          
          DISJOINT
          .106053481
          TRUE
          TRUE
          Distance is not zero.

          Regards,
          Stefan
          • 2. Re: Strange zero distance results for DISJOINT geometries
            Luc Van Linden
            Hi Paul

            Same result as you on:

            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - *64bit* Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options.

            Luc
            • 3. Re: Strange zero distance results for DISJOINT geometries
              Paul Dziemiela
              Thanks guys for the help.

              I'll submit the issue to support.

              Cheers,

              Paul
              • 4. Re: Strange zero distance results for DISJOINT geometries
                Paul Dziemiela
                Hi folks,

                Just to close out this thread, support says the issue is indeed bug 13402612 first reported in 2011 and as mentioned is a regression in 11.2.0.3. Though the bug report says it affects all operating systems I can state that its not a problem on AIX 5 so for me seems related to 64 bit Linux. Patch 13402612 should fix it they say though I have not verified this. The issue is slated to be corrected in 11.2.0.4.

                Cheers and Happy Holidays,

                Paul