Getting Area of Polygon Intersections Stratified by Categorical Variable in PostGIS

Question

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?

Answer 1

The easiest way would be:

SELECT texascounties.county_name, trees.type, st_area(st_union(trees.geom))
    FROM texascounties
    LEFT OUTER JOIN area_w3_1992_decid trees
    ON ST_Intersects(trees.geom, texascounties.geom)
    GROUP by texascounties.county_name, trees.type 
    LIMIT 1000;

However the result will be:

|---------------------|------------------|----------------|
|County Name          |Tree Type         |Area            |
|---------------------|------------------|----------------|
|          Tarrant    |         Birch    |     2737.28    |
|---------------------|------------------|----------------|

Repeated for each county and tree type.