Below is the sql statement which I have explained below.
Declare Cursor c1 is select store_number ,0 as total_area,0 as BG_ID ,0 as store_geom from table1 where CLIENT_ID= 1 and ORG_ID = 1 ; Type C1_TAB_TYPE is table of c1%ROWTYPE; c1_list c1_TAB_TYPE; Begin For r1 in c1 Loop select BG_ID,store_number,total_area BULK COLLECT INTO c1_list from ( SELECT BG_ID,store_number,b.geometry store_geom, ( (SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_INTERSECTION(a.geometry,b.geometry, 0.005), 0.005, 'unit=sq_mile') /SDO_GEOM.SDO_AREA((a.geometry), 0.005, 'unit=sq_mile'))*100) Total_area FROM table2 a, table1 b Where store_number != r1.store_number AND sdo_relate(a.geometry, b.geometry, 'mask=anyinteract') = 'TRUE'); if total_area=100 then For i in 1..c1_list.count loop insert into temp_Prop_area_100 values c1_list(i); End Loop; else if For i in 1..c1_list.count loop insert into temp_Prop_area_block values c1_list(i); End Loop; End if End Loop; End;
So I select a record(the biggest geometry of all three tables) from table 1 and overlay over smaller polygons from table 2. I get the list of the overlapping geometries from table 2. This may have a few of them intersecting and a few of them completely under the polygon from table 1.
Then I want to put them in to two different tables . The ones that are completely under in temp_prop_area and the ones which intersect in temp_prop_area_block.
Now the issue I am having is, when I get the id's for all intersecting polygons(temp_prop_area_block) I want to overlay each of this polygon over the polygons from table 3 which area smaller polygons.And similarly find out which polygons do they intesect and what is the area of intersection.
The polygon size are in this order polygons from table 1 as the biggest, the from table 2 or temp_prop_area_block and then the polygons from table 3.