[postgis-users] Intersection - three tables

[Sufficool, Stanley]

The sum of the intersecting area of all 3 polygons.

Sum(area2d(ST_Intersection(ST_Intersection(n.the_geom, f.the_geom),
c.the_geom)))*0.000247105381 


> 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
> _______________________________________________
> postgis-users mailing list postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>