Обсуждение: Query sought with windowing function to weed out dense points

Hi,

Given a table 'peaks' with the fields id, name, elevation and geometry
I'd like to get a query which returns only peaks which dont overlap -
and from those which would do, I'd like to get the topmost one (given
a certain 'density parameter').

This problem is motivated by a visualization task where label names
should'nt overlap because of lack of visual space - as well as because
of limited network capacity between db and client!

Here is a solution I fiddled about which is based on a grid (ST_SnapToGrid):

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
  )

...where 5 is the 'density parameter': a higher number means larger
grid which returns less peaks. This parameter could be parametrised in
a stored procedure and set according to the map units.

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).

=> Any windowing function gurus around?

Yours, S.

P.S. I had difficulties finding (OLTP) examples for windowing
functions (and unfortunately the slides from PGDay.EU last year aren't
available :-<)

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@2ndquadrant.it | www.2ndquadrant.it