This discussion is archived
2 Replies Latest reply: Jun 29, 2012 3:57 AM by Paul Dziemiela RSS

SDO_FILTER giving odd results

Paul Dziemiela Journeyer
Currently Being Moderated
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
    yhu Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    thanks Yi.

Legend

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