Re: Query sought with windowing function to weed out dense points

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(dot)ciolli(at)2ndquadrant(dot)it | www.2ndquadrant.it