2 Replies Latest reply: Jun 29, 2012 5:57 AM by Paul Dziemiela RSS

    SDO_FILTER giving odd results

    Paul Dziemiela
      Hi folks,

      Not sure if this is a bug or perhaps I am just not seeing something simple. On 11.2.0.4. I have an area of interest box and a nearby line representing a water feature, both geometries validate at 5 centimeter tolerance.
      http://www.dziemiela.com/june27.PNG
      The box is over a mile away from the stream. The stream is relatively small in size and its MBR is no where near touching the box. However according to SDO_FILTER the two geometries are TRUE. I am perplexed. So I wrote out the whole mess for anyone willing to give it a shot. I get back

      Streamy is valid? TRUE
      Boxy is valid? TRUE
      Basic SDO_GEOM.RELATE? DISJOINT
      Distance Between 1795.19857225394M
      MBRs SDO_GEOM.RELATE? DISJOINT
      Distance Between 1794.97512096382M
      SDO_RELATE Count 0
      SDO_FILTER Count 1

      So the two geometries are 1.7 km apart and their MBRs are 1.7 km apart. I would say pretty clearly these things should not interact. Right? Yet filter gives back TRUE. Am I being a dullard about something?

      Thanks,
      Paul
      DECLARE
         sdo_streamy SDO_GEOMETRY := SDO_GEOMETRY(2002,8265,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-77.6495001234516,38.9423350727726,-77.6482987901201,38.9418092727734,-77.6468049234557,38.9413522727742,-77.6461311901235,38.9413982061074,-77.6438177901271,38.942107872773,-77.6427925901286,38.9423368727726,-77.6423533234627,38.9422912727727,-77.6410643234647,38.9417884061068,-77.6406835234653,38.9416968727736,-77.6403321234658,38.9417886061068,-77.6394825901338,38.9417886727735,-77.63760792347,38.9414232061074,-77.6370221234709,38.9410114727747,-77.6357033901397,38.9392504727774,-77.6352933234736,38.9389760727778,-77.6347071901412,38.9380154727793,-77.6313675901464,38.9348822727842,-77.6301375234816,38.9342876061185,-77.6295517234825,38.9336930061194,-77.6294929901493,38.9335098727863));
         sdo_boxy SDO_GEOMETRY := SDO_GEOMETRY(2003,8265,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-77.6087382736728,38.9295533233688,-77.4229814409148,38.9295533233688,-77.4229814409148,39.0744969598833,-77.6087382736728,39.0744969598833,-77.6087382736728,38.9295533233688));
         num_result NUMBER;
      BEGIN
         dbms_output.put_line('Streamy is valid? ' || SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(sdo_streamy,0.05));
         dbms_output.put_line('Boxy is valid? ' || SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(sdo_boxy,0.05));
         dbms_output.put_line('Basic SDO_GEOM.RELATE? ' || SDO_GEOM.RELATE(sdo_streamy,'DETERMINE',sdo_boxy,0.05));
         dbms_output.put_line('Distance Between ' || SDO_GEOM.SDO_DISTANCE(sdo_streamy,sdo_boxy,0.05) || 'M');
         dbms_output.put_line('MBRs SDO_GEOM.RELATE? ' || SDO_GEOM.RELATE(SDO_GEOM.SDO_MBR(sdo_streamy),'DETERMINE',SDO_GEOM.SDO_MBR(sdo_boxy),0.05));
         dbms_output.put_line('Distance Between ' || SDO_GEOM.SDO_DISTANCE(SDO_GEOM.SDO_MBR(sdo_streamy),SDO_GEOM.SDO_MBR(sdo_boxy),0.05) || 'M');
         EXECUTE IMMEDIATE 'CREATE TABLE streamy (shape SDO_GEOMETRY)';
         EXECUTE IMMEDIATE 'INSERT INTO streamy (shape) VALUES (:p01)' USING sdo_streamy; COMMIT;
         INSERT INTO user_sdo_geom_metadata(table_name,column_name,diminfo,srid) VALUES 
         ('STREAMY','SHAPE',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180,180,.05),MDSYS.SDO_DIM_ELEMENT('Y',-90,90,.05)),8265); COMMIT;
         EXECUTE IMMEDIATE 'CREATE INDEX streamy_spx ON streamy(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX';
         EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM streamy a WHERE SDO_RELATE(a.shape,:p01,''MASK=ANYINTERACT'')=''TRUE''' INTO num_result USING sdo_boxy;
         dbms_output.put_line('SDO_RELATE Count ' || TO_CHAR(num_result));
         EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM streamy a WHERE SDO_FILTER(a.shape,:p01)=''TRUE''' INTO num_result USING sdo_boxy;
         dbms_output.put_line('SDO_FILTER Count ' || TO_CHAR(num_result));
      END;
      /
        • 1. Re: SDO_FILTER giving odd results
          Ying Hu-Oracle
          Hi Paul,

          For geodetic geometries, R-tree indexes are 3D. For example, you can see:
          SQL> select sdo_index_name, SDO_ROOT_MBR from user_sdo_index_metadata;
          
          SDO_INDEX_NAME
          --------------------------------
          SDO_ROOT_MBR(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINAT
          --------------------------------------------------------------------------------
          STREAMY_SPX
          SDO_GEOMETRY(3008, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1007, 3), SDO_ORDINATE_ARR
          AY(.16636, -.759816, .628418, .166646, -.759754, .628538))
          sdo_boxy is converted to:
          SDO_GEOMETRY(3008, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1007, 3), SDO_ORDINATE_ARR
          AY(.166589, -.759798, .628364, .169393, -.757698, .630331))

          So the two MBRs or Minimal Bounding Boxes (MBBs) intersect.

          Thanks,
          Ying
          • 2. Re: SDO_FILTER giving odd results
            Paul Dziemiela
            thanks Yi.