Обсуждение: 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