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 <[email protected]> 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: [email protected] > [mailto:[email protected]] 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: [email protected] > [mailto:[email protected]] On Behalf Of Kai > Carter > Sent: Wednesday, September 23, 2009 6:36 PM > To: [email protected] > 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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- George R. C. Silva Desenvolvimento em GIS www.sextantegeo2.blogspot.com
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
