Using a exists with a spatial query in oracle results in an error

Question

I try to query all items that are within a distance of 130 km to each other. If I use a exists in the where part I get an exception:

This works fine:

select *
from tbl_geometry g1, tbl_geometry g2
where sdo_within_distance(g1.GEOMETRY, g2.GEOMETRY, 'distance=130 unit=km')='TRUE'
and g1.id <> g2.id;

Here I get an exception:

select *
from tbl_geometry g1
where exists(
    select *
    from tbl_geometry g3
    where g1.id <> g3.id
    and sdo_within_distance(g1.GEOMETRY, g3.GEOMETRY, 'distance=130 unit=km') = 'TRUE'
);

Error: [72000][13226] ORA-13226: interface not supported without a spatial index ORA-06512: at "MDSYS.MD", line 1723 ORA-06512: at "MDSYS.MDERR", line 8 ORA-06512: at "MDSYS.SDO_3GL", line 1052

Adding another instance of tbl_geometry "solves" the problem:

select *
from tbl_geometry g1
where exists(
    select *
    from tbl_geometry g2, tbl_geometry g3
    where g2.id = g1.id
    and g2.id <> g3.id
    and sdo_within_distance(g2.GEOMETRY, g3.GEOMETRY, 'distance=130 unit=km') = 'TRUE'
    );

Any idea how I can use the exists without having 2 tbl_geometry in there for no reason.

Thanks.

Answer 1

Want to see some magic?

Your query:

SQL> SELECT COUNT(*)
  2    FROM tbl_geometry g1
  3   WHERE EXISTS
  4            (SELECT *
  5               FROM tbl_geometry g3
  6              WHERE     g1.id <> g3.id
  7                    AND sdo_within_distance (g1.geometry,
  8                                             g3.geometry,
  9                                             'distance=130 unit=km') = 'TRUE');
SELECT COUNT(*)
*
ERROR at line 1:
ORA-13226: interface not supported without a spatial index
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 8
ORA-06512: at "MDSYS.SDO_3GL", line 1082

My query:

SQL> SELECT COUNT(*)
  2    FROM tbl_geometry g1
  3   WHERE EXISTS
  4            (SELECT *
  5               FROM tbl_geometry g3
  6              WHERE     g1.id <> g3.id
  7                    AND sdo_within_distance (g3.geometry,
  8                                             g1.geometry,
  9                                             'distance=130 unit=km') = 'TRUE');

  COUNT(*)
----------
       815

SQL>

Can you spot the difference? No? Here it is:

  7                    AND sdo_within_distance (g1.geometry,    --> g1
  8                                             g3.geometry,    --> g3
  9                                             'distance=130 unit=km') = 'TRUE');

vs.

  7                    AND sdo_within_distance (g3.geometry,    --> g3
  8                                             g1.geometry,    --> g1
  9                                             'distance=130 unit=km') = 'TRUE');

Why? I have NO IDEA. But it works.