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

# SDO_FILTER giving odd results

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;
('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
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
thanks Yi.