Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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)?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
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
1  
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
1  
Thank you, this looks like what I need!! –  ESRIHelp Nov 24 '11 at 9:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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