Search code examples
sqlpostgresqlcountarea

How to find the combined area of geometries of a certain type using ST_Area() and COUNT() postgresql

This table provides the unique combination of territory_type, territory_category and a count for each unique combination: (lets say 13)

SELECT iso_country_code, country_code, territory_type, territory_category, count(territory_type||territory_category)
FROM table
GROUP BY iso_country_code, country_code, territory_type, territory_category
HAVING COUNT(territory_type||territory_category)>0;

I would like the total area(km2) of each unique combination of territory_type, territory_category. I would expect 13 results.

Can anyone help?

New to postgresql ^-^

Tried:

SELECT ST_Area(geom::geography)/1000000  as Area_KM2 
FROM table;

It got me the areas of each indvidual feature. Not quite what I wanted. So I tried a hap-hazard combination of the two queries:

SELECT iso_country_code, country_code, territory_type, territory_category, geom, count(territory_type||territory_category),
       ST_Area(geom::geography)/1000000  as Area_KM2
FROM table
GROUP BY iso_country_code, country_code, territory_type, territory_category,geom
HAVING COUNT(territory_type||territory_category)>0;

It gives me areas of each individual feature still.

Solution

  • If you want the total area, you can sum the individual areas.

    SELECT iso_country_code, 
       country_code, 
       territory_type, 
       territory_category, 
       count(territory_type||territory_category),
       sum(ST_Area(geom::geography))/1000000 as Area_KM2
FROM table 
GROUP BY iso_country_code, country_code, territory_type, territory_category 
HAVING COUNT(territory_type||territory_category)>0;