Hello everyone,
I have met a problem when using sdo_nn function to find nearest neighbor. Below is my scenario:
_ I have 2 tables client and store.
_ Client table has client_ID and a sdo_geom of 2D point
_ Store table has store_ID and a sdo_geom of 2D polygon.
Initially, I have this query to find nearest store to each client as below:
select s.STORE_ID, c.CLIENT_ID
from store s, client c
where sdo_nn(s.MYPOLYGON, c.MYPOINT, 'sdo_num_res=1', 1) = 'TRUE';
_It works as expected where it returns a table showing the nearest store to each client.
_Now I want to count how many clients who have the same nearest store:
select /*+ INDEX (store store_spatial_idx, client client_spatial_idx)*/ count(nearest_store.CLIENT_ID)
from (select s.STORE_ID, c.CLIENT_ID
from store s, client c
where sdo_nn(s.MYPOLYGON, c.MYPOINT, 'sdo_num_res=1', 1) = 'TRUE') nearest_store
group by nearest_store.STORE_ID;
Executing this query produces the following error:
Error report -
SQL Error: ORA-13249: SDO_NN cannot be evaluated without using index
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 17
ORA-06512: at "MDSYS.PRVT_IDX", line 9
13249. 00000 - "%s"
I'm pretty new to spatial database and hope to get some help to go further. Thank you in advance!