[postgis-users] running median

[Lee Hachadoorian]

On 07/17/2013 08:18 AM, Florian Lengyel wrote:
> Is there a running median algorithm in PostGIS?

Hi Florian,

Unless I'm missing a spatial aspect to this question, this seems more 
like a straight PostgreSQL question. The first issue is providing a 
median() function. One common solution is to use PL/R [1]. Then, a 
running median could be implemented with a window function [2]. The 
general form (assuming the median aggregate function was already created 
as in [1]) would be

SELECT median(column1) OVER (ORDER BY column2) FROM table1;

This would produce a running median, i.e., the median for the current 
row and all previous rows given the sort order of the resultset. See in 
particular the explanation of window frames and the use of the ORDER BY 
clause in [2].

Best,
--Lee


[1] http://www.joeconway.com/plr/doc/plr-aggregate-funcs.html
[2] http://www.postgresql.org/docs/9.1/static/tutorial-window.html

-- 
Lee Hachadoorian
Assistant Professor in Geography, Dartmouth College
http://freecity.commons.gc.cuny.edu