4 Replies Latest reply: Apr 18, 2013 6:09 AM by Stefan Jager RSS

    spatial function

    don123
      hi experts

      i have used following spatial function to select the features with touch=TRUE, it is fine.

      SQL> select a.idnumber from poly1 a, poly2 b where sdo_relate(a.geometry, b.geometry, 'mask=TOUCH')='TRUE';

      please help me in how to select the features that DO NOT touch ??

      regards
        • 1. Re: spatial function
          John O'Toole
          This should do it:
          select idnumber from poly1
          minus
          select a.idnumber from poly1 a, poly2 b where sdo_relate(a.geometry, b.geometry, 'mask=TOUCH')='TRUE';
          • 2. Re: spatial function
            Stefan Jager
            or
             select a.idnumber from poly1 a, poly2 b where NOT sdo_relate(a.geometry, b.geometry, 'mask=TOUCH')='TRUE';
            or this:
             select a.idnumber from poly1 a, poly2 b where sdo_relate(a.geometry, b.geometry, 'mask=TOUCH')<>'TRUE';
            Take your pick :-)

            HTH,
            Stefan
            • 3. Re: spatial function
              don123
              Thanks John and Stefan
              • 4. Re: spatial function
                Stefan Jager
                Oops! Did it wrong...

                Only realized just now that I gave those examples the wrongway. They should be, according to the [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_operat.htm#i78531] documentation  :
                select /*+ ORDERED */ a.idnumber from poly2 b, poly1 a where NOT sdo_relate(a.geometry, b.geometry, 'mask=TOUCH')='TRUE';
                Documentation says:If two or more geometries from geometry2 are passed to the operator, the ORDERED optimizer hint must be specified, and the table in geometry2 must be specified first in the FROM clause.
                This should improve your performance. Just make sure both SDO_GEOMETRY columns have proper Spatial Indexes.

                Cheers,