# How to sum area of polygons by values occuring in multiple fields?

My data has 2 fields holding landuse types, which both describe what is within the polygon. For example, the polygon may have Type1=Retail, Type2=Leisure Area= 1 but another polygon has Type1=Leisure, Type2=Swimming Pool Area=3

I want to discover the total area of polygons with a Retail attribute, Leisure attribute etc.. to build a table like:

``````Retail 1
Leisure 4
Swimming Pool  3
``````

etc. Of course some polygons will be counted in multiple categories, but this is ok for what I am working on.

In some cases the polygon might have Type1= Retail, Type2= Retail. In this case the area should only be counted once.

Can I ask PostGIS to sum the areas for all of the values it finds in the fields, without me having to write/run a query for each of the types (there are a lot)?

-

I'm no SQL expert, but this works for me.

My table definition looks like this:

``````CREATE TABLE test(id integer PRIMARY KEY, type1 varchar(32), type2 varchar(32), area double precision);
``````

And I create these entries:

`````` id |   type1   |     type2     | area
----+-----------+---------------+------
0 | Retail    | Leisure       |    1
1 | Leisure   | Swimming Pool |    3
2 | Retail    | Computers     |    5
3 | Municipal | Municipal     |   50
``````

What I've done is to do a `UNION` on the two type columns in the same table, then sum the resulting areas:

``````SELECT f.type, SUM(f.area) FROM (
SELECT type1 AS type, area
FROM test
UNION
SELECT type2 AS type, area
FROM test
) AS f
GROUP BY f.type;
``````

Which gives me the result:

``````     type      | sum
---------------+-----
Swimming Pool |   3
Leisure       |   4
Municipal     |  50
Retail        |   6
Computers     |   5
``````

Note how Municipal has an area of 50, rather than 100. If I had done a `UNION ALL` it would have counted both columns and summed them together.

-
using an ORM like geoalchemy, how would you build the same query? I know it's a follow up question, but I think it's somewhat related to the OP's – dassouki Nov 23 '11 at 16:19
Well the question isn't about spatial queries, so SQLAlchemy would work. I've not used it much, but the principle is to first build the two inner queries, then union them with `q3 = q1.union(q2)` Then use `session.query(q3.type, func.sum(q3.area)).group_by(q3.type).all()` My syntax might be a bit duff, but you get the general principle: break it down into the logical blocks of the original query, define objects for them, then glue them all together. Of course you could use raw SQL, but that partially defeats the point of an ORM. – MerseyViking Nov 23 '11 at 16:52
Thank you, this looks like what I need!! – ESRIHelp Nov 24 '11 at 9:22