# Calculate Area proportional

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;
``````
• What do you mean by "proportional area"? intersect(A, B)/A? union(A, B)/A? intersect(A, B)/union(A, B)? Maybe if you write out the math, maybe the rest will follow... Dec 21, 2013 at 19:49
• Hey man, you said your original code worked, but not the cursor. I 'm not going to try to work with the code, but how about posting this on stackoverflow as a general coding question? Get the cursor working, then stick your original code in there. Jan 9, 2014 at 14:49
• I didnt get any response regarding the same earlier on stack overflow.
– SNT
Jan 9, 2014 at 15:41

Once again, your question is not clear, so I need to guess and assume that you want to find out the proportion of each geometry from TABLE_1 (A) that overlaps the single geometry you pick from TABLE_2 (B). Do this by computing the ratio between the area of A and the area of B as a percentage:

``````with t as (
SELECT a.bg_id,
SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_INTERSECTION(a.geometry,b.geometry, 0.005), 0.005, 'unit=sq_mile') overlap_area,
SDO_GEOM.SDO_AREA(a.geometry, 0.005, 'unit=sq_mile') total_area
FROM table_1 a, table_2 b
where b.STORE_number= '351029'
and sdo_anyinteract(a.geometry,b.geometry) = 'TRUE'
)
select bg_id, overlap_area/total_area*100 pct_overlap
from t;
``````
• I just edited the question . Hope this one makes more sense now.
– SNT
Dec 27, 2013 at 1:29

You can do it with one query into a new table. In this example I copy everything into a new table (tmp_table_c) and store the area sizes as well. Storage is cheap and this is way faster than dynamically calculate everything.

``````INSERT INTO tmp_table_c (ID, area, data_area, geometry)
SELECT tmp_table_c_seq.nextval,
SDO_GEOM.SDO_AREA(a.geometry, 0.0005) AS area,
SDO_GEOM.SDO_AREA(SDO_CS.MAKE_2D(sdo_geom.sdo_intersection(a.geometry, b.geometry, 0.0005)), 0.0005) AS data_area,
SDO_CS.MAKE_2D(sdo_geom.sdo_intersection ( a.geometry, b.geometry, 0.0005)) as geometry
FROM table_a, table_b
WHERE sdo_relate(a.geometry, b.geometry, 'mask=OVERLAPBDYINTERSECT+INSIDE+EQUAL') = 'TRUE';
``````
• Why do I need a 'to 2d' functionality ? Also does this calculate it for each polygon from table which overlaps over polygons on table A
– SNT
Jan 8, 2014 at 20:32
• Did you try this proposd solution, @Senthil? Jan 9, 2014 at 1:35
• @martinf yes I tried and it worked fine.I had to modify it . First thing being I didnt want to use the "to 2D" functionality.
– SNT
Jan 9, 2014 at 15:40

Contextualizing the general approach related to your problem...

1. What kind of "proportion" you need?
2. What kind of error you have?
3. Summarize totals by pairs (two overlapped polygons) or by "what is viewed" (union)?

For other readers may be important this kind of checklist (was for me in similar problems).

## Check#1

Mathematically there are two good ways to check the proportion of overlapped areas. Let `A1` the bigger area, `A2` the other, and `AI` the intersection area,

• proportion of the least of them: `100*AI/A2`, your case;
• average proportion: `200*AI/(A1+A2)`, used when need measure "percentual of overlaping" in a broad sense.

If it you frequently check intersection areas, is interesting express a general library function that use the two geometries and a boolean (type of proportion) as inputs. You can express by SQL something like,

`````` SELECT CASE WHEN A1=0 or A2=0 THEN NULL
WHEN p_use_average THEN 200*AI/(A1+A2)
ELSE 100*AI/LEAST(A1,A2) END
FROM (
SELECT SDO_GEOM.SDO_AREA(p_g1,p_tol) as A1, SDO_GEOM.SDO_AREA(p_g2,p_tol) as A2
) as t;
``````

## Check#2

What the "grid" that you used to snap polygons? Or earlier, what the error used to obtain measures of position (ex. GPS error)?

Big areas and little areas have the same accuracy?

Use the traditional engineering error analysis for calculations of error propagation, or for estimatives in typical cases.

You use the 0.005 as tolerance in area measures, but intersection have the error of the biggest area (A1 in your problem), and the `100*AI/LEAST(A1,A2)` have the error of `E2=ERROR(A2)` in your problem... So, your propagated error is something like `EP = SQRT((100*E1/E2)^2+(100*A1*E2/A2^2)^2)`...

If a proportion `P`, calculated by step#1, is larger than error `EP`, you can not use it (!), must considerate zero. In "big areas vs little areas" this is an usual situation.

## Check#3

You need the what?

• `TV` "total overlapped area" as it is viewed (one of a "double coverage" is not counted): a union of geometries do this.
• or `TP`, "total of pairs", as it occurs really in each pair of polygons.

`TV<=TP`

When using `TV`, pay attention: you also need to check (by check#2) the error at each pair, to select when a pair can be used or not in the union.