I am trying to find out why for some cases SDO_FILTER and SDO_RELATE does not returns bigger geometries.
Here is my table with two geometries:
DROP table foo;
CREATE table foo(
state\_code VARCHAR2(2 Char)
,shape MDSYS.SDO_GEOMETRY
,PRIMARY KEY(state_code)
);
INSERT INTO foo
SELECT 'T1',MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(-170,40,-120,20,-120,40,-170,40)) FROM dual UNION ALL
SELECT 'B1',MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(-163,37,-162,37,-162,37.5,-163,37.5,-163,37)) FROM dual;
COMMIT;
DELETE FROM user_sdo_geom_metadata WHERE table_name = 'FOO';
INSERT INTO user_sdo_geom_metadata VALUES(
'FOO'
,'SHAPE'
,MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180,180,0.05),MDSYS.SDO_DIM_ELEMENT('Y',-90,90,0.05))
,4326
);
COMMIT;
CREATE INDEX foo_spx ON foo(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
this is how it looks like on visualization:
Now when I run following queries:
SELECT STATE_CODE, SHAPE FROM FOO
WHERE SDO_RELATE(SHAPE, MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-163.00,37.00,-162.00,37.5)), 'mask=ANYINTERACT querytype=WINDOW') = 'TRUE' ;
SELECT STATE_CODE, SHAPE FROM FOO
WHERE SDO_FILTER(SHAPE, MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-163.00,37.00,-162.00,37.5))) = 'TRUE' ;
Both queries does not finds bigger geometry T1 only smaller box B1.
But query window is equal to B1 so it should return both.
What is interesting when I ask with bigger box like:
SELECT STATE_CODE, SHAPE FROM FOO
WHERE SDO_RELATE(SHAPE, MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-163.00,37.00,-162.00,38.00)), 'mask=ANYINTERACT querytype=WINDOW') = 'TRUE' ;
SELECT STATE_CODE, SHAPE FROM FOO
WHERE SDO_FILTER(SHAPE, MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-163.00,37.00,-162.00,38.00))) = 'TRUE' ;
then SDO_RELATE finds only B1,
but SDO_FILTER find B1 and T1.
I think in all above cases bot B1 and T1 should be returned.
Can anybody have similar problems?
I am on 'Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production'