I've got two layers: counties and forest type area. The county layer is what you'd expect: a bunch of simple polygons representing counties in Texas. The forest type area layer looks like Tetris shapes, and consists of "geom" and "type" attributes. See the attached screenshot for an example of what it looks like.
What I'm trying to do is get the total area of each "type" from the forest type layer that falls into each county. I've already intersected the forest type and county layers so that forest type is split where it intersects county lines.
The results should look something like this:
|---------------------|------------------|----------------|
|County Name |Type 1 Area |Type 2 Area |
|---------------------|------------------|----------------|
| Tarrant | 565.27 | 2737.28 |
|---------------------|------------------|----------------|
I've taken a stab at this before, before I had to put this project on hold for about a year. Now I'm revisiting it and cannot figure it out. Here's my previous attempt:
/*
Take forest fragmentation table and calculate the sum area of each patch type as it intersects with counties. Save the results to a new PostGIS table
*/
CREATE TABLE countyFragStats AS SELECT * FROM texascounties;
UPDATE countyFragStats
SELECT texascounties.geom FROM texascounties, coalesce(sum(area_1992_w3_decid.area),0) AS areaSum
FROM area_1992_w3_decid
LEFT OUTER JOIN texascounties
ON ST_Intersects(area_1992_w3_decid.geom, texascounties.geom)
GROUP area_w3_1992_decid.id
LIMIT 1000;
It's a non-starter, giving me
LINE 2: SELECT texascounties.geom FROM texascounties, coalesce(sum...
Any ideas?