0

I want to do aggregation based not on group but on geometry, is that possible in PostGIS?

For example to calculate max, min, average price for all listings that's nearby a geometry point, how can I do something like:

select max(price), min(price), avg(price) 
from price_table 
where (ST_DWITHIN(geom_point, ST_SETSRID(ST_MAKEPOINT(-119.697321, 34.40592), 4326), 0.72)) 

If PostGIS doesn't have this feature, what other system should I look into?

1

Yes, that should work (have you tried it?)

PostgreSQL (the database software behind PostGIS) supports many aggregate functions, which can also be modified to window functions (if you need that).

PostGIS has other aggregate functions, such as ST_Collect, ST_Extent and more, if you need them.

| improve this answer | |
  • Ah I'm dumb. In my actual query I had some other field to select which cause the query doesn't work. Yes the sample query works. Thank you! – Suanmeiguo Jan 28 '15 at 22:04
2

As Mike noted, your query should work fine. are you having issue with it or is your question if you have a table of locations how to get the min, max, avg for the table points of interest (POI).

Assuming that is your question your query would look something like

SELECT  poi.gid,  poi.geom, max(price), min(price), avg(price) 
 FROM price_table INNER JOIN poi ON ST_DWithin(price_table.geom, poi.geom, 0.72)
  GROUP BY poi.gid, poi.geom;
| improve this answer | |
  • Thank you for providing an example. Yeah the query works, I was having other field in my actual query.. – Suanmeiguo Jan 28 '15 at 22:11

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.