Checking for overlapping polygons in Oracle Spatial?

I have a table 1 which has a fields like store number and geometry and it just has one record in it. Now I want to check for this store number in table 2 and identify if there are any polygons overlapping to this particular store number polygon and insert them into the table 3.

Below is sql block I am trying to use.

Insert into table 3

select a.store_id,a.store_number,a.client_id,a.geometry

from  table 2 a,table 1 b
where a.client_id=b.client_id
and SDO_RELATE(a.Geometry, (select Geometry from table 1  where store_id     
= 34746),'mask=anyinteract') = 'TRUE';

But it just returns 1 field in the new table for any store id I select.

Answer

Your problem description is not clear. Also one thing you did not say, but appears in your code is that both TABLE_1 and TABLE_2 also share a CLIENT_ID attribute.

Looks like you want to find geometries from table 2 that match the geometry of one particular store from table 1, having the same client id. If that is not what you expect, then explain your problem in a better way. Things are generally easier to understand if you use real table names (like STORES, CUSTOMERS, etc) rather than TABLE 1 and TABLE 2. Having some sample data is even better.

So I am assuming you have the following tables:

create table table_1 ( 
    store_id        number,
    store_number    number, 
    client_id       number,
    geometry        sdo_geometry 
);

create table table_2 ( 
    client_id       number,
    geometry        sdo_geometry

);

create table table_3 ( 
    store_id        number,
    store_number    number, 
    client_id       number,
    geometry        sdo_geometry 
);

This should do what you want:

insert into table_3 (store_id, store_number, client_id, geometry)
select t1.store_id, t1.store_number, t1.client_id, t1.geometry
from   table_1 t1, table_2 t2 
where  t1.store_id = 34746
and    t1.client_id = t2.client_id
and    sdo_anyinteract (t2.geometry, t1.geometry) = 'TRUE';

To complete the picture, assuming you actually want to get the clipped geometries (i.e. the matching geometries from "table 2" should be clipped by the window geometry picked from "table 1".

insert into table_3 
select t1.store_id, t1.store_number, t1.client_id, 

       sdo_geom.sdo_intersection (t2.geometry, t1.geometry, 0.005)
from   table_1 t1, table_2 t2 
where  t1.store_id = 34746
and    t1.client_id = t2.client_id
and    sdo_anyinteract (t2.geometry, t1.geometry) = 'TRUE';

---

OK, let me try one more time to understand what you say.

You have stores. They are identified by a STORE_ID. They are described in two tables each containing a geometric attribute. One has the location of each store as a point. The other has a polygon for each store.

Store polygons (I imagine they represent traction zones or similar) can overlap.

The problem you want to solve is this: given one particular store S, find all the stores whose polygons intersect the polygon of S, compute the intersection of each store polygon with that of S, and write the result into a new table.

Let's use meaningful names for your tables:

create table store_points ( 
    store_id                number,
    store_location          sdo_geometry 
);

create table store_areas ( 
    store_id                number,
    store_area              sdo_geometry

);

create table store_intersections ( 
    store_id                number,
    intersecting_store_id   number, 
    intersection_area       sdo_geometry 
);

The following will find all stores whose area intersects that of store with id 34746, compute the intersection between the area of store 34746 and the matching areas found, and write the results into the STORE_INTERSECTIONS table.

insert into store_intersections (

  store_id, 
  intersecting_store_id, 
  intersection_area
)
select s1.store_id, s2.store_id, 
       sdo_geom.sdo_intersection (s1.store_area, s2.store_area, 0.005)
from   store_areas s1, store_areas s2
where  s1.store_id = 34746
and    sdo_anyinteract (s2.store_area, s1.store_area) = 'TRUE';

Notice I am not using the STORE_LOCATIONS table: it is not needed since all we do is use the store polygons available in the STORE.

If this does not match what you want, then post the actual data model you use (table structure and relationships) together with some example content.

---

Good, so here is what I think you want: for each store polygon, clip out the polygons for other stores whose polygon overlaps the polygon of that store.

Again, we have the store polygon table:

create table store_areas ( 
    store_id                number,
    store_area              sdo_geometry
);

and the table to hold the results of our calculations

create table store_intersections ( 
    store_id                number,
    remaining_area          sdo_geometry 
);

And the process I propose is this:

1) Compute the union of the polygons of the overlapping stores, like this:

select s1.store_id,
       sdo_aggr_union (sdoaggrtype(s2.store_area, 0.005)) union_area

from   store_areas s1, store_areas s2
where  s1.store_id = 34746
and    sdo_anyinteract (s2.store_area, s1.store_area) = 'TRUE'
group by s1.store_id

The result will be one row for each store, with a geometry that is formed by aggregating the polygons of all the overlapping stores.

2) Compute the difference between the main store polygon and the union computed above

select s3.store_id,
       sdo_geom.sdo_difference (
         s3.store_area,

         o.union_area,
         0.005
       )
 from (
   select s1.store_id,
          sdo_aggr_union (sdoaggrtype(s2.store_area, 0.005)) union_area
  from   store_areas s1, store_areas s2
  where  s1.store_id = 34746
  and    sdo_anyinteract (s2.store_area, s1.store_area) = 'TRUE'
  group by s1.store_id

) o,
store_areas s3
where s3.store_id = o.store_id;

3) Save the results in the new table

insert into store_intersections (
  store_id,  
  remaining_area
)
select s3.store_id,

       sdo_geom.sdo_difference (
         s3.store_area,
         o.union_area,
         0.005
       )
 from (
   select s1.store_id,
          sdo_aggr_union (sdoaggrtype(s2.store_area, 0.005)) union_area
  from   store_areas s1, store_areas s2
  where  s1.store_id = 34746

  and    sdo_anyinteract (s2.store_area, s1.store_area) = 'TRUE'
  group by s1.store_id
) o,
store_areas s3
where s3.store_id = o.store_id;

You can apply the process to all stores (just remove the "s1.store_id = 34746" predicate). This will take time to compute however.

---

Here is an alternate possibility: instead of aggregating then taking the difference, do it the other way: take the difference between the base store polygon and each overlapping polygon, then aggregate the result.

insert into store_intersections (

  store_id,  
  remaining_area
)
select store_id,
       sdo_aggr_union (sdoaggrtype(diff_area, 0.005))
from (
  select s1.store_id,
         sdo_geom.sdo_difference (s1.store_area, s2.store_area, 0.005) diff_area
  from store_areas s1, store_areas s2
  where  s1.store_id = 34746

  and    sdo_anyinteract (s2.store_area, s1.store_area) = 'TRUE'
)
group by store_id;

BUT in both cases, there is one very important additional point to consider. Since we are relating store polygons between them, one of the store polygons that intersects the polygon of store 34746 is that of store 34746 itself. So the result of all operations will always be NULL since we will always take the difference between store 34746 and itself. So we need to make sure to exclude the base store from the match, i.e. add

  and    s2.store_id <> s2.store_id

to the query:

insert into store_intersections (
  store_id,  

  remaining_area
)
select store_id,
       sdo_aggr_union (sdoaggrtype(diff_area, 0.005))
from (
  select s1.store_id,
         sdo_geom.sdo_difference (s1.store_area, s2.store_area, 0.005) diff_area
  from store_areas s1, store_areas s2
  where  s1.store_id = 34746
  and    sdo_anyinteract (s2.store_area, s1.store_area) = 'TRUE'

  and    s2.store_id <> s2.store_id
)
group by store_id;