Below is the sql statement which I have explained below.

    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;

          For r1 in c1 

           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
                      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;


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.

Your Answer

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

Browse other questions tagged or ask your own question.