[postgis-users] SQL moving window averages/statistics

[georger.silva]

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/8f270053/attachment.html>