2

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?

My Data

Improve this question
2
  • You need a SET statement if you are doing an UPDATE. The error is because you have SELECT right after UPDATE. You are also missing the word BY after GROUP and why do you have LIMIT 1000? You can probably reduce your two steps to one: CREATE TABLE countyFragStats AS SELECT texascounties.county_name, trees.type, etc.
    – John Powell
    Dec 8, 2017 at 10:58
  • just do a fresh st_intersection from the 2 tables
    – ziggy
    Dec 8, 2017 at 20:32

1 Answer 1

Reset to default
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.

Improve this answer
3
  • The reason for the OP error is not due to the SELECT query but due to the lack of a SET, though your version is cleaner, albeit with an extra comma after trees.type (now edited).
    – John Powell
    Dec 8, 2017 at 11:02
  • Thanks! Is there any way to get it to a wide format table? I'm sure I can brute force it through libreoffice or something after the query, but it'd be nice to do it all through postgis.
    – Ross Wardrup
    Dec 8, 2017 at 11:04
  • To have it wide format it would be well more complex. @JohnPowellakaBarça thanks for the edit.
    – HeikkiVesanto
    Dec 8, 2017 at 11:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.