2

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;
  • 2
    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... – Martin F Dec 21 '13 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. – Barrett Jan 9 '14 at 14:49
  • I didnt get any response regarding the same earlier on stack overflow. – SNT Jan 9 '14 at 15:41
2
+100

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 '13 at 1:29
0

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 '14 at 20:32
  • Did you try this proposd solution, @Senthil? – Martin F Jan 9 '14 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 '14 at 15:40
0

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.

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.