[postgis-users] help with spatial query

[pcreso at pcreso.com]

Hi,

Assuming your cities are point features and you have some column to aggregate on (like name?) try something like this:

select setsrid(makepoint(avg(x(geom)),avg(y(geom))),4326) as geom, 
       sum(pop_1990) as pop_1990,
       name
from cities
group by name
ORDER BY pop_1990 DESC LIMIT 100;

Or create a new table with these aggregated data & query from that directly.

HTH,

  Brent Wood
--- On Tue, 6/14/11, Josh Jordan <joshjordan at robotjosh.com> wrote:

From: Josh Jordan <joshjordan at robotjosh.com>
Subject: [postgis-users] help with spatial query
To: postgis-users at postgis.refractions.net
Date: Tuesday, June 14, 2011, 2:03 AM

I am trying to return a map of the USA with the top 100 cities (point data) labeled.  
SELECT * FROM cities ORDER BY pop_1990 DESC LIMIT 100;
The problem is it returns clusters of cities, for instance 7 dots on Chicago and 8 dots on Manhattan.  Is there a way to return the top 100 cities omitting ones that are too close to each other?

-Josh Jordan

-----Inline Attachment Follows-----

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110613/d76ad442/attachment.html>