This discussion is archived
5 Replies Latest reply: Mar 6, 2013 7:49 AM by B Hall RSS

SDO_RELATE issue with mask=OVERLAPBDYDISJOINT+OVERLAPBDYINTERSECT

SeshuNaresh Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    _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

Legend

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