I have two tables both of them having polygon geometry. Lets consider Table 1 has bigger polygons which overlap over the smaller polygons in table 2.Now I know how to find how many polygons from table 2 come under a single polygon from table 1. What I want to do is to find the proportional area for those polygons from table 2 which get intersected with the polygon from table 1.When I say proportional area I mean how much
(overlap polyon area/total area of that intersected polygon from table 2)*100.
I want find this because it would help me in calculating another value with the right proportions.
I did the below using 1 polygon from table 1. I wanted to know how could I do this for all the polygons from table 1 using cursor.
SELECT a.bg_id, (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'))"Proportional Area" FROM table 1 a, table 2 b where b.STORE_number= '351029' AND SDO_Relate(a.geometry,b.geometry,'mask=anyinteract') = 'TRUE';
Here is the one which I tried with cursor which doesnt work apparently.
Declare Cursor c1 is select store_number,geometry from stores 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 BULK COLLECT INTO c1_list from ( SELECT bg_id, (SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_INTERSECTION(geometry,r1.geometry, 0.005), 0.005, 'unit=sq_mile') /SDO_GEOM.SDO_AREA((geometry), 0.005, 'unit=sq_mile'))"Proportional Area" FROM DATA where r1.store_number != r1.store_number AND SDO_Relate(geometry,r1.geometry,'mask=anyinteract') = 'TRUE'); End loop; End;