[postgis-users] Calculating the area of overlapping polygons

[Brent Wood]

Hopefully someone can check my thinking on this as a spatial overlay analysis
using PostGIS.....


Data:

All in a Polar Stereographic projection which is close enough to an equal area
projection, units are meters.


table 1: 20,000,000 points with a depth attribute

table 2: grid of 50,000 cells (polygons) covering the region of interest, with 
         attrs of the min, max & avg depth of all the depth points in table1 
         which lie within each cell.

table 3: polygons describing sea-ice densities for required week within the
         area.


Problem: 

To determine the total area within this region for a given week, where the 
depth is within a specified range and the sea-ice density below a specified
percentage.


Proposed solution:

Select the total area of intersecting cells where the avg depth is inside the
range & they intersect with the appropriate ice density polygons:


SQL1="select sum(area2d($GRID_TAB.the_geom))/1000000
     from $GRID_TAB,
          $ICE_TAB
     where avg_depth <= -$MIN_DEPTH
       and avg_depth >  -$MAX_DEPTH
       and substring(icecode from 3 for 2) < $ICECODE
       and $GRID_TAB.the_geom && $ICE_TAB.the_geom
       and intersects($GRID_TAB.the_geom, $ICE_TAB.the_geom);"


Issue: this expands the area estimate by including all the area from cells
which are partially outside the ice density polygons. So:


Select half the total area of the cells lying on the ice polygon boundaries:

SQL2="select sum(area2d($GRID_TAB.the_geom))/2000000
     from $GRID_TAB,
          $ICE_TAB
     where avg_depth <= -$MIN_DEPTH 
       and avg_depth >  -$MAX_DEPTH 
       and substring(icecode from 3 for 2) < $ICECODE 
       and $GRID_TAB.the_geom && $ICE_TAB.the_geom 
       and overlaps($GRID_TAB.the_geom, $ICE_TAB.the_geom);"


Subtract the last from the first to get a reasonable answer, assuming that
about 1/2 the area of cells on the boundary is inside the polygons. 

Given the inaccuracies of both the ice cover & depth data, I don't expect an
accurate answer, just a reasonable estimate.



If I've got this horribly wrong somewhere, can someone point me in the right
direction? 

Do the intersect & overlap functions do what I want in this context? 
Any tips on optimising the queries?


Thanks

  Brent Wood