[postgis-users] SQL moving window averages/statistics

[Paragon Corporation]

George,
 
Not sure.  Haven't really though much on windows and raster since we haven't
played with it yet.
 
I would think so, though you may need to throw an iterator array in there.
 
I'm curious -- what particular use case are you thinking of for a 3x3
window.
 
Thanks,
Regina

  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
georger.silva
Sent: Thursday, September 24, 2009 10:24 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] SQL moving window averages/statistics


Just a thought. Moving window queries will give PostGIS an excellent
capability of analysing WKTRasters. Is it possible to do a query extracting
the median/avg value in a raster using a 3x3 window?
 
Att.
 
George


On Thu, Sep 24, 2009 at 7:35 AM, Paragon Corporation <lr at pcorp.us> wrote:


Kai,

On second thought it appears you are trying to do a 3 day moving average
like thing.

A self-join would probably work best given that the window plumbing in
PostgreSQL doesn't support bounded ranges

SELECT d.date, MAX(d2.tmax) As max_tmax, MIN(d2.tmax) As min_tmax
FROM   daily_climate As d INNER JOIN daily_climate As d2
  ON( d2.date BETWEEN (d.date - INTERVAL '1 day') AND (d.date + INTERVAL '1
day') )
GROUP BY d.date


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net

[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paragon
Corporation
Sent: Thursday, September 24, 2009 1:34 AM
To: 'PostGIS Users Discussion'
Subject: Re: [postgis-users] SQL moving window averages/statistics

Kai,

Still not quite sure what you are doing here, but if you are using
PostgreSQL 8.4 you can use the New windowing functions lead(), lag() will
give you forward and backward values.  I put in 1 and 1 for example, but if
you wanted to count back say 5 records from your current you would replace
with 5

http://www.postgresql.org/docs/8.4/interactive/functions-window.html

So something like

SELECT date, tmax As curr_tmax, lag(tmax,1) OVER(ORDER BY date) As
previous_day_tmax, lead(tmax,+1) OVER(ORDER BY date) As next_day_tmax FROM
daily_climate;


Leo



-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Kai
Carter
Sent: Wednesday, September 23, 2009 6:36 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] SQL moving window averages/statistics

Hi there,

I'm currently have an sql statement that selects a week of descriptive
statistics for various historical weather variables, sorted by date.

SELECT to_char(date, 'MM')||to_char(date, 'DD') as date, max(tmax) as
max_tmax, min(tmax) as min_tmax, avg(tmax) as mean_tmax, stddev(tmax) as
std_tmax, count(tmax) as count_tmax, sum(tmax) as sum_tmax, variance
(tmax) as var_tmax  FROM daily_climate where icao='KSFO' and (EXTRACT (MONTH
from date) =9 and EXTRACT(DAY from date) = 23) or  (EXTRACT (MONTH from
date) = 9 and EXTRACT(DAY from date) = 24) or  (EXTRACT (MONTH from date) =
9 and EXTRACT(DAY from date) = 25) or  (EXTRACT (MONTH from date) = 9 and
EXTRACT(DAY from date) = 26) or  (EXTRACT (MONTH from date) =9 and
EXTRACT(DAY from date) = 27) or  (EXTRACT (MONTH from date) = 9 and
EXTRACT(DAY from date) = 28) or  (EXTRACT (MONTH from date) =  and
EXTRACT(DAY from date) = 29) group by date order by date;

The problem is that I only have 36 years of data to work with, and I would
prefer to have a sample of ~100 rather than 30.  So the idea would be to
have a sample statistics for each day made up of 3 days:
the current day, the day previous and the day after.

Is it possible to get this sort of a result with one select statement?

Thanks in advance for your responses,

Kai Carter
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users





-- 
George R. C. Silva

Desenvolvimento em GIS
www.sextantegeo2.blogspot.com

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090924/62465985/attachment.html>