From: | Stefan Keller <sfkeller(at)gmail(dot)com> |
---|---|
To: | pgsql-general List <pgsql-general(at)postgresql(dot)org> |
Subject: | Query sought with windowing function to weed out dense points |
Date: | 2011-02-16 23:14:28 |
Message-ID: | AANLkTim3FSA_S8qoY5BbjrJtXc5DDg8kjJ1pJxoOt_TQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 :-<)
From | Date | Subject | |
---|---|---|---|
Next Message | Gianni Ciolli | 2011-02-17 00:16:55 | Re: Query sought with windowing function to weed out dense points |
Previous Message | Tom Lane | 2011-02-16 23:07:39 | Re: Hide db name and user name in process list arguments |