How to use SDO_JOIN to find if one object is inside another

Question

I have two table. One has a point SDO_GEOMETRY and the other has a circle SDO_GEOMETRY object. I want to check using SDO_JOIN, all the objects of table1 that are inside of table2. Can someone guide as to how to write this query?

My output is supposed to be as follows:

T1  --  T2
1   --  2
2   --  2
2   --  1

which mean 1 is inside 2 and 2 is inside both 1 and 2

Answer 1

The following shows how to correlate counties (polygons) and cities (points) using SDO_JOIN

SELECT ci.city, ci.state_abrv, co.county
  FROM us_cities ci,
       us_counties co,
       TABLE(SDO_JOIN(
             'US_COUNTIES', 'GEOM',
             'US_CITIES', 'LOCATION',
             'MASK=INSIDE') 
       ) j
WHERE j.rowid1 = ci.rowid
  AND j.rowid2 = co.rowid
ORDER BY ci.city, ci.state_abrv;