3 Replies Latest reply: Oct 29, 2012 5:45 AM by _jum RSS

    confusion sdo_relate method

    922439
      I run the following querys in oracle 11g :

      1) select a1.apid from access_point a1 where sdo_relate(a1.shape,a1.shape,'mask=equal') = 'TRUE' and a1.apid = 'a5vhe';
      2) select a1.apid from access_point a1 where sdo_relate(a1.shape,a1.shape,'mask=anyintersect') = 'TRUE' and a1.apid = 'a5vhe';

      now oracle 11g documentation defines mask 'anyintersect' as "ANYINTERACT: Returns TRUE if the objects are not disjoint." Then why oracle returns record a1 for my Query 1 and returns none for QUERY2 ??
        • 1. Re: confusion sdo_relate method
          _jum
          Can't find in the documentation
          now oracle 11g documentation defines mask 'anyintersect' as "ANYINTERACT
          So the query
          select a1.apid from access_point a1 where sdo_relate(a1.shape,a1.shape,'mask=anyintersect') = 'TRUE' and a1.apid = 'a5vhe';
          can't return anything.
          BTW your query compares only each point with itself, "normally" one uses a self join like:
          select a1.apid from access_point a1, access_point a2 where sdo_relate(a1.shape,a2.shape,'mask=anyintersect') = 'TRUE' and a1.apid = 'a5vhe';
          • 2. Re: confusion sdo_relate method
            922439
            Check this link http://docs.oracle.com/cd/B28359_01/appdev.111/b28400/sdo_objgeom.htm#BGHCDIDG You will find definition for anyintersect there. Ya I know that's what people usually do. But I am learning things and so trying things and so wanted to see how does anyintersect reacts. And it doesn't react the way it should. Moreover wouldn't if we had some s2 exactly same as s1 do the same thing ??
            • 3. Re: confusion sdo_relate method
              _jum
              Hi 919436,
                   
              1. In my book (I followed the link), there is neither a RELATION, nor the word "ANYINTERSECT" at all ?
              I only can find:
              If you pass the ANYINTERACT keyword in mask, the function returns TRUE if the two geometries are not disjoint.
              2. If you want to check relations between geometries in a table with SQL, you have to to use the table twice (check each geom with each other).
              Please check the following simple example, that DETERMINES the spatial relationship between the geometriess:
              DROP TABLE sdo_test;
               
              CREATE TABLE sdo_test (
                id      NUMBER, 
                GEOM    MDSYS.SDO_GEOMETRY);
               
               
              INSERT INTO sdo_test VALUES (1, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
                  SDO_ORDINATE_ARRAY( 0,0,  0,5, 5,5, 5,0, 0,0)));
                                     
              INSERT INTO sdo_test VALUES (2, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
                  SDO_ORDINATE_ARRAY( 4,5,  6,5, 6,6, 4,6, 4,5)));
               
              INSERT INTO sdo_test VALUES (3, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
                  SDO_ORDINATE_ARRAY( 5,6,  6,6, 6,7, 5,7, 5,6)));
               
              COMMIT;
              
              SELECT p1.id id1,p2.id id2, MDSYS.SDO_GEOM.relate(p1.geom,'DETERMINE',p2.geom,1) rela
                FROM sdo_test p1, sdo_test p2
               WHERE p1.id<p2.id;
              
              id1  id2    rela
              -----------------------   
              1     2     TOUCH
              1     3     DISJOINT
              2     3     TOUCH