[postgis-users] Intersection - three tables

[Marcus C. England]

Hello all,

Well, I'm learning fast but I have come across a problem that perplexes 
me. I have three polygon tables. "new_vegetation" represents vegetation 
on a site. "grading_phases" represents the grading area for a project, 
and this is entirely within the limits of "new_vegetation". The third 
layer is "basin_grading", which represents five proposed basins (as well 
as polygons for 100' and 200' buffers around those basins), some of 
which are within "grading_phases" and some of which are outside of 
"grading_phases". Some are even split between being and out of 
grading_phases.

What I am trying to get is the acreage of vegetation communities 
(new_vegetation) within the each polygon type of basin_grading, but only 
those portions that are within grading_phases. The query below is my 
attempt at this, but it returns the acreage of new_vegetation within 
each entire basin, not just the portion that is also within 
grading_phases. I thought the ST_Intersects part would take care of 
that, but apparently not.


SELECT
   f.use,
   n.cat_2008,
   Sum(area2d(ST_Intersection(n.the_geom, f.the_geom)))*0.000247105381 
AS inside_project
 FROM
   basin_grading f,
   grading_phases c,
   new_vegetation n
 WHERE
   ST_Intersects(c.the_geom, f.the_geom)
 GROUP BY f.use, n.cat_2008
 ORDER BY f.use, n.cat_2008

thanks in advance,

Marcus