Hi,
On Thu, Feb 17, 2011 at 12:14:28AM +0100, Stefan Keller wrote:
> SELECT ST_AsText(geometry), name as label
> FROM
> peaks t1
> WHERE
> t1.id = (
> SELECT id
> FROM (
> SELECT ST_SnapToGrid(geometry, 5) as geometry, elevation, id
> FROM peaks
> ) t2
> WHERE ST_Equals(ST_SnapToGrid(t1.geometry, 5), t2.geometry)
> ORDER BY elevation DESC
> LIMIT 1
> )
> I think there could be perhaps an even more elegant solution with the
> new windowing functions! My idea is to partition peaks around a grid
> and chose the one with max(elevation).
You might eliminate the correlated subquery, like in:
SELECT *
FROM (
SELECT ST_AsText(geometry)
, name as label
, rank() OVER (
PARTITION BY ST_Equals(ST_SnapToGrid(geometry, 5)
ORDER BY elevation DESC)
FROM
peaks
) x
WHERE rank = 1;
-- query not tested as I don't have postgis available
which "feels" more elegant; but you still need a subquery, as window
functions are not allowed in the WHERE clause.
Cheers,
Dr. Gianni Ciolli - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gianni.ciolli@xxxxxxxxxxxxxx | www.2ndquadrant.it
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
