[postgis-users] SQL moving window averages/statistics

[George Silva]

There are many analysis techniques that uses 3x3, 5x5, 7x7, 9x9
windows. In particular, in block statistics and focal statistics.
There are functions that are used to determine the flow of a raster
(higher cell flows to lower cell), euclidean distance, best path, and
many others!

Also, this may be widely used in simulations and in the construction
of environmental models and viability surfaces, such as environmental
fragility, and best place to install a new center for storing garbage
(for example).

Let me know if i can help in any way.

On 9/24/09, Paragon Corporation <lr at pcorp.us> wrote:
> 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
>
>


-- 
George R. C. Silva

Desenvolvimento em GIS
www.sextantegeo2.blogspot.com