5 Replies Latest reply: Mar 6, 2013 9:49 AM by B Hall RSS

    SDO_RELATE issue with mask=OVERLAPBDYDISJOINT+OVERLAPBDYINTERSECT

    SeshuNaresh
      I have a table geodata with a column named shape containing 1 record given below
      MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(0,5,5,10)).

      The SRID of the table is 4326.

      Query:

      select shape from geodata where
      SDO_RELATE( GEODATA.SHAPE, SDO_GEOMETRY(2003,4326,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),
      SDO_ORDINATE_ARRAY(-5,-5,-5,5,5,5,5,-5,-5,-5)) ,'mask=OVERLAPBDYDISJOINT+OVERLAPBDYINTERSECT') = 'TRUE'

      When I execute the above query, the given record is returned which is not expected since the filter geometry touches the bounding box but does not overlap. When I replace the mask value to 'TOUCH', no record is returned even though it is expected.
        • 1. Re: SDO_RELATE issue with mask=OVERLAPBDYDISJOINT+OVERLAPBDYINTERSECT
          _jum
          This is because SRID=4326 "WGS 84" is a geodetic (spheric) coordinate system, the "line" -5,5 -> 5,5 is an arc and doesn't touch the "line" 0,5 -> 5,10.
          --ARC doesn't touch
          SELECT
            SDO_GEOM.RELATE(MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(0,5,5,10)),
            'DETERMINE',
            SDO_GEOMETRY(2003,4326,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-5,-5,-5,5,5,5,5,-5,-5,-5))
            ,0.01) 
          FROM dual;
          ->OVERLAPBDYINTERSECT
          
          --include POINT 0,5
          SELECT
            SDO_GEOM.RELATE(MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(0,5,5,10)),
            'DETERMINE',
            SDO_GEOMETRY(2003,4326,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-5,-5, -5,5 ,0,5, 5,-5, -5,-5))
            ,0.01) 
          FROM dual;
          ->TOUCH
          
          --PROJECTED UTM CS, line touches
          SELECT
            SDO_GEOM.RELATE(MDSYS.SDO_GEOMETRY(2003,2030,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(0,5,5,10)),
            'DETERMINE',
            SDO_GEOMETRY(2003,2030,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-5,-5,-5,5,5,5,5,-5,-5,-5))
            ,0.01) 
          FROM dual;
           -> TOUCH
          • 2. Re: SDO_RELATE issue with mask=OVERLAPBDYDISJOINT+OVERLAPBDYINTERSECT
            B Hall
            Seshu,

            We are missing some information in regards to your question. What db version are you using, and what are your metadata parameters?

            First off, your ordinate array has the polygon constructed backwards - it should be counter-clockwise, and may affect your results depending on version.

            With 11.2.0.3.5:
            SQL> CREATE TABLE geodata (pk number, shape SDO_GEOMETRY);
            
            Table created.
            
            SQL>
            SQL> INSERT INTO user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
              2       VALUES (
              3                 'GEODATA',
              4                 'SHAPE',
              5                 sdo_dim_array (sdo_dim_element ('LON', -180, 180, 1),
              6                                sdo_dim_element ('LAT', -90, 90, 1)),
              7                 4326);
            
            1 row created.
            
            SQL>
            SQL> CREATE INDEX geodata_sndx
              2     ON geodata (shape)
              3     INDEXTYPE IS mdsys.spatial_index;
            
            Index created.
            
            SQL>
            SQL> INSERT INTO geodata
              2       VALUES (1,sdo_geometry (2003,
              3                             4326,
              4                             NULL,
              5                             mdsys.sdo_elem_info_array (1, 1003, 3),
              6                             mdsys.sdo_ordinate_array (0, 5, 5, 10)));
            
            1 row created.
            
            SQL>
            SQL> INSERT INTO geodata
              2       VALUES (2,sdo_geometry (2003,
              3                             4326,
              4                             NULL,
              5                             mdsys.sdo_elem_info_array (1, 1003, 1),
              6                             mdsys.sdo_ordinate_array (0,5, 5,5, 5,10, 0,10, 0,5)));
            
            1 row created.
            
            SQL>
            SQL> INSERT INTO geodata
              2       VALUES (3,sdo_geometry (2003,
              3                             4326,
              4                             NULL,
              5                             mdsys.sdo_elem_info_array (1, 1003, 1),
              6                             mdsys.sdo_ordinate_array (0,5.01, 5,5.01, 5,10, 0,10, 0,5.01)));
            
            1 row created.
            
            SQL>
            SQL> INSERT INTO geodata
              2       VALUES (4,sdo_geometry (2003,
              3                             4326,
              4                             NULL,
              5                             mdsys.sdo_elem_info_array (1, 1003, 1),
              6                             mdsys.sdo_ordinate_array (0,5.1, 5,5.1, 5,10, 0,10, 0,5.1)));
            
            1 row created.
            
            SQL>
            SQL> COMMIT;
            
            Commit complete.
            
            SQL>
            SQL> -- wrong orientation
            SQL> select pk,shape from geodata where
              2  SDO_RELATE( GEODATA.SHAPE, SDO_GEOMETRY(2003,4326,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),
              3  SDO_ORDINATE_ARRAY(-5,-5,-5,5,5,5,5,-5,-5,-5)) ,'mask=OVERLAPBDYDISJOINT+OVERLAPBDYINTERSECT') = 'TRUE';
            
                    PK
            ----------
            SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
            --------------------------------------------------------------------------------
                     2
            SDO_GEOMETRY(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
            AY(0, 5, 5, 5, 5, 10, 0, 10, 0, 5))
            
                     1
            SDO_GEOMETRY(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR
            AY(0, 5, 5, 10))
            
                     3
            
                    PK
            ----------
            SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
            --------------------------------------------------------------------------------
            SDO_GEOMETRY(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
            AY(0, 5.01, 5, 5.01, 5, 10, 0, 10, 0, 5.01))
            
            
            SQL>
            SQL> -- correct orientation
            SQL> select pk,shape from geodata where
              2  SDO_RELATE( GEODATA.SHAPE, SDO_GEOMETRY(2003,4326,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),
              3  SDO_ORDINATE_ARRAY(-5,-5, 5,-5, 5,5, -5,5, -5,-5)) ,'mask=OVERLAPBDYDISJOINT+OVERLAPBDYINTERSECT') = 'TRUE';
            
                    PK
            ----------
            SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
            --------------------------------------------------------------------------------
                     2
            SDO_GEOMETRY(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
            AY(0, 5, 5, 5, 5, 10, 0, 10, 0, 5))
            
                     1
            SDO_GEOMETRY(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR
            AY(0, 5, 5, 10))
            
                     3
            
                    PK
            ----------
            SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
            --------------------------------------------------------------------------------
            SDO_GEOMETRY(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
            AY(0, 5.01, 5, 5.01, 5, 10, 0, 10, 0, 5.01))
            
            
            SQL>
            SQL> -- Why?
            SQL> select pk,SDO_RELATE( GEODATA.SHAPE, SDO_GEOMETRY(2003,4326,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),
              2  SDO_ORDINATE_ARRAY(-5,-5, 5,-5, 5,5, -5,5, -5,-5)) ,'DETERMINE')
              3  from geodata;
            
                    PK
            ----------
            SDO_RELATE(GEODATA.SHAPE,SDO_GEOMETRY(2003,4326,NULL,SDO_ELEM_INFO_ARRAY(1,1003,
            --------------------------------------------------------------------------------
                     1
            OVERLAPBDYINTERSECT
            
                     2
            OVERLAPBDYINTERSECT
            
                     3
            OVERLAPBDYINTERSECT
            
            
                    PK
            ----------
            SDO_RELATE(GEODATA.SHAPE,SDO_GEOMETRY(2003,4326,NULL,SDO_ELEM_INFO_ARRAY(1,1003,
            --------------------------------------------------------------------------------
                     4
            DISJOINT
            Here, I added a second row using standard polygon points, and a third and fourth slightly offsetting it. Using MBRs many times will result in arc densification by itself. But that is not the problem here, although it is related. Since this is a geodetic coordinate system, there are no straight lines. The chord from -5 to 5 and the entry from 0 to 5 are quite long, and are converted to "arcs" (using multiple straight lines) by inserting nodes. These line arcs just so happen to overlap at some point. For a polygon of this size, it appears you need around 1/10 of a degree separation so that they won't overlap.

            So - is the answer wrong? Yes it is - but then it is a very complex problem for that large of an area of the earth, realizing that this is an easy case since the touch is on a line of latitude. But if it was at an angle other than 0 or 90, the curvature really makes the calculations expensive since - where do you decided to check polygon on polygon relations? Line or point is easy - they have a limited number of interaction points. But polygon on polygon is infinite.

            In a nutshell, if you only care about simple relationships and not area or distance, just use a planer coordinate system (NULL SRID). The math is easier and thus faster, and here you would have got the right answer of TOUCH even with the MBR.

            Bryan
            • 3. Re: SDO_RELATE issue with mask=OVERLAPBDYDISJOINT+OVERLAPBDYINTERSECT
              SeshuNaresh
              The DB version I'm using is 11.2.0.3.0. My metadata parameters are given below:


              SQL> select * from user_sdo_geom_metadata;

              TABLE_NAME
              --------------------------------
              COLUMN_NAME
              --------------------------------------------------------------------------------

              DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)
              --------------------------------------------------------------------------------

              SRID
              ----------
              GEODATA
              SHAPE
              SDO_DIM_ARRAY(SDO_DIM_ELEMENT('Longitude', -180, 180, .5), SDO_DIM_ELEMENT('Lati

              tude', -90, 90, .5))
              4326

              I also observed that I receive inaccurate results as the size of the query polygon increases. For SDO_ORDINATE_ARRAY(0,0 , 1,0 , 1,1 , 0,1 , 0,0) <-----> QUERY with an MBR (0,1 , 1,2) <-----> TABLE DATA, I got the TOUCH relationship as expected but when I increased the values to (0,0 , 5,0 , 5,5 , 0,5 , 0,0) with an MBR (0,5 , 5,10), I started getting OVERLAPBDYINTERSECT as the relationship.

              Is there anything I can do overcome the offset created as the size of the data increases?
              • 4. Re: SDO_RELATE issue with mask=OVERLAPBDYDISJOINT+OVERLAPBDYINTERSECT
                _jum
                Dear Seshu,

                did You read the explanation we gave ? The relationship is as expected for geodetic problems with ARCs on the surface of the earth.

                Also keep in mind, that your SDO_DIM_ELEMENT
                SDO_DIM_ARRAY(SDO_DIM_ELEMENT('Longitude', -180, 180, .5), SDO_DIM_ELEMENT('Latitude', -90, 90, .5))
                has a big SDO_TOLERANCE of 0.5, that IMO means 0.5 degree or 55 kilometers. respectively two objects INTERACT, if they are "nearer" than 55 kilometers.
                • 5. Re: SDO_RELATE issue with mask=OVERLAPBDYDISJOINT+OVERLAPBDYINTERSECT
                  B Hall
                  _jum wrote:
                  Also keep in mind, that your SDO_DIM_ELEMENT
                  SDO_DIM_ARRAY(SDO_DIM_ELEMENT('Longitude', -180, 180, .5), SDO_DIM_ELEMENT('Latitude', -90, 90, .5))
                  has a big SDO_TOLERANCE of 0.5, that IMO means 0.5 degree or 55 kilometers. respectively two objects INTERACT, if they are "nearer" than 55 kilometers.
                  Sorry, but that would be in meters, not degrees for 4326.

                  Bryan