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.
Best 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:
This should do what you want:
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".
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:
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.
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:
and the table to hold the results of our calculations
And the process I propose is this:
1) Compute the union of the polygons of the overlapping stores, like this:
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
3) Save the results in the new table
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.
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
to the query: