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.

I need help with a PostgreSql query. I need to find all parcels which have bigger area then average. I'm using this query:

SELECT OBJECTID 
FROM parcela
WHERE ST_Area(geom)<(SELECT Sum(ST_Area(geom)/count(*)))
GROUP BY OBJECTID;

but it gives me error:

aggregate function calls cannot be nested

I tried few different ways to solve this (using 'having' instead 'where' etc.) but always some new error occurs. Can anybody please tell me how to write right query to get this information. Thanks

share|improve this question

2 Answers 2

up vote 3 down vote accepted

One approach, which is a little more basic is to split this into two queries:

SELECT SUM(ST_Area(geom))/count(*) as avg_area
FROM parcela;

Then with this returned value (say it is 500), use this in your next query:

SELECT OBJECTID 
FROM parcela
WHERE ST_Area(geom) > 500; -- this returns parcels greater than average

Another approach, performing this in one step is the following:

SELECT 
    OBJECTID,
    x.avg_area 
FROM 
    (SELECT SUM(ST_Area(geom))/count(*) as avg_area 
     FROM parcela) as x,
    parcela
WHERE
    ST_Area(geom) > x.avg_area
share|improve this answer
    
Yes, I thought for a moment that I could try it like that but I haven't tried it. I've tried it now and it works. Thank you very much. But if somebody still knows how to do it in one query I would appreciate it. Because I'm still not quite sure why my query is wrong? :) –  user14026 Jan 10 '13 at 22:33
    
Just added a way of doing it in one step. –  djq Jan 10 '13 at 22:35
    
It works perfectly now! Thank you very much! You helped me alot. –  user14026 Jan 10 '13 at 22:38

Here is a window function alternative:

SELECT OBJECTID
FROM (
  SELECT OBJECTID, ST_Area(geom) > avg(ST_Area(geom)) OVER () AS filter
  FROM parcela
) AS ss
WHERE filter;

Note: I've replaced Sum(ST_Area(geom))/count(*) with a more readable avg(ST_Area(geom)).

With a window function, you have more power to your query, such as find all the parcels greater than the average area for each (e.g.) land_use:

SELECT OBJECTID
FROM (
  SELECT OBJECTID, ST_Area(geom) > avg(ST_Area(geom)) OVER (PARTITION BY land_use) AS filter
  FROM parcela
) AS ss
WHERE filter;
share|improve this answer
    
Thanks. This is very useful too. I appreciate it. –  user14026 Jan 11 '13 at 8:15

Your Answer

 
discard

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