2

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.

2
+50

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;
  • Here is the description: I have two tables . (table)One just has the store id's and their geometries and (table two)other has the store id's and their polygon geometries. So I want to pick a few stores from the table one as of now say just one and put it in a temptable 1.Now in temp table1 I just have one record. So I want to do a SDO_relate to figure out for that store id are there any other polygons in table 2 which overlap and if yes push those records in temp table 2. This is just one part.Next if they overlap I want to clip the polygon for the store id record in table 1. – SNT Nov 21 '13 at 17:49
  • I am still confused. Why do you have two tables with stores ? Are the same stores in the two tables ? Or do the two tables contain different set of stores ? And what are the geometries of the stores ? Are they actually polygons then ? Also I don't understand the need for all those temporary tables. You can just run the operation directly on the base tables, including the clipping. – Albert Godfrind Nov 21 '13 at 17:58
  • One table has stores as points and other has stores as polygons – SNT Nov 21 '13 at 18:11
  • Well I use table 1 to just select stores which are in points. Now using the one store which I have selected from table 1 I go to table 2 which has all polygon geometries for all the stores. Now I want to see for that selected point store number from the polygon store table if it overlaps with polygons for other stores.If yes I want the one store polygon to clip with the overlapping polygons and then push this new geometry to a new table. The reason I am using temporary tables are because I want to see the result at each step and also the data tables are pretty big and dont want to mess – SNT Nov 21 '13 at 22:43
  • 2
    See my edit to the answer.Is that what you want ? If not, give us more info: the actual data model (table structure and relationships) you use. Otherwise, this will turn into a guessing game. – Albert Godfrind Nov 25 '13 at 10:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.