Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SDO_FILTER giving unexpected results

3056435Nov 11 2015 — edited Nov 16 2015

Hello,

I am using Oracle 11g (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production).

I have the following records inside my table:

select acqreqid, shape from t_shared_ar;

ACQREQID             SHAPE         
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1                    MDSYS.SDO_GEOMETRY(2003,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(24.8,1.058333333,24.883333333,1.058333333,24.883333333,1.183333333,24.8,1.183333333,24.8,1.058333333))                                                                             
2                    MDSYS.SDO_GEOMETRY(2003,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(24.883333333,1.016666667,24.966666667,1.016666667,24.966666667,1.266666667,24.883333333,1.266666667,24.883333333,1.016666667))                                                      
3                    MDSYS.SDO_GEOMETRY(2003,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(24.966666667,1,25.05,1,25.05,1.3,24.966666667,1.3,24.966666667,1))                                                                                                                 
4                    MDSYS.SDO_GEOMETRY(2003,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(25.05,1.016666667,25.133333333,1.016666667,25.133333333,1.266666667,25.05,1.266666667,25.05,1.016666667))                                                                           
5                    MDSYS.SDO_GEOMETRY(2003,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(25.133333333,1.044444444,25.216666667,1.044444444,25.216666667,1.211111111,25.133333333,1.211111111,25.133333333,1.044444444))                                                      
6                    MDSYS.SDO_GEOMETRY(2003,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(25.216666667,1.072222222,25.3,1.072222222,25.3,1.155555556,25.216666667,1.155555556,25.216666667,1.072222222))                                                                     
7001                 MDSYS.SDO_GEOMETRY(2003,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(112.001637488342,-43.0100678837062,-120.029612511658,-42.4937680792239,-118.271800011658,69.0659147873703,102.861012488342,69.3157018772838,112.001637488342,-43.0100678837062)) 
7005                 MDSYS.SDO_GEOMETRY(2003,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(24.966666667,1,25.05,1,25.05,1.3,24.966666667,1.3,24.966666667,1))                                                                                                                
7002                 MDSYS.SDO_GEOMETRY(2003,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(100.001637488342,-40.0100678837062,-100.029612511658,-40.4937680792239,-100.271800011658,60.0659147873703,100.861012488342,60.3157018772838,100.001637488342,-40.0100678837062))

select ACQREQID, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(shape,mdsys.SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', -180, 180, 1),SDO_DIM_ELEMENT('Y', -90, 90, 1))) from t_shared_ar;

ACQREQID SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(SHAPE,MDSYS.SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X',-180,180,1),SDO_DIM_ELEMENT('Y',-90,90,1)))

-------------------- ----------------------------------------------------------------------------------------------------------------------------------

1 TRUE

2 TRUE

3 TRUE

4 TRUE

5 TRUE

6 TRUE

7001 TRUE

7005 TRUE

7002 TRUE

select acqreqid, SDO_RELATE(shape, (MDSYS.SDO_GEOMETRY(2003,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(112.001637488342,-43.0100678837062,-120.029612511658,-42.4937680792239,-118.271800011658,69.0659147873703,102.861012488342,69.3157018772838,112.001637488342,-43.0100678837062))), 'mask=anyinteract') from t_shared_ar;

ACQREQID SDO_RELATE(SHAPE,(MDSYS.SDO_GEOMETRY(2003,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(112.001637488342,-43.0100678837062,-120.029612511658,-42.4937680792239,-118.271800011658,69.0659147873703,102.861012488342,69.315701877283

This post has been answered by Paul Dziemiela on Nov 14 2015

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Dec 14 2015

Added on Nov 11 2015

#database-key-features, #spatial

23 comments

4,977 views