Wednesday, February 6, 2008

Oracle Spatial query windowing technique

This again is one of the problems i faced while coding in oracle 10g.
I will post both the problem and the solution for it..

Dear friends,
The database i'm using (mvdemo - mapviewer demo DB that comes along with Oracle 10g), has only data about united states. Say if i have my query window like this, -180, 0, 180 , 90 (which is the upper half of the earth), i am getting the desired result. ie.,Here is that query,

SELECT count(s.geom) FROM states s WHERE SDO_FILTER(s.geom,SDO_GEOMETRY(2003,8265,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(-180, 0 , 180, 90))) = 'TRUE';
--Returns 55
I'm currently having a problem with my rectangular window query. When i query with a window value of -180, -90, 180, 90 (which covers the whole earth), the number of spatial objects (SDO_GEOM) that is returned is 0. It is not throwing any ORA error. But the expected result is as usual 55.

SELECT count(s.geom) FROM states s WHERE SDO_FILTER(s.geom,SDO_GEOMETRY(2003,8265,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(-180, 0 , 180, 90))) = 'TRUE';
--Returns 0
Can anyone please help me out with this issue??

Solution
I found the solution to my problem. actually the query given here works just fine.wat i was doing was using the sdo_intersection(, ). The query window am using here is the whole earth. But according to spatial rule, there cant be any geometry greater than half the surface of the earth.
Yes.. its pretty true. Imagine yourself trying to calculate the distance from Pearl harbor to japan. For a small kid, if you give the map and ask him to measure, he'l tell the longest route in the conventional map. But it is not so. We have to calculate the shortest distance which ofcourse will only be possible when you see the earth as a sphere. Oracle 10g is no exception. The design is logically correct. So any query window given exceeds half the size of the earth, oracle will throw an error.

No comments: