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;
![Elevation legend for E/C Africa map]](https://i.stack.imgur.com/ZKl2U.jpg)
