<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.18812"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=961142319-24092009><FONT color=#0000ff
size=2 face=Arial>George,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=961142319-24092009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=961142319-24092009><FONT color=#0000ff
size=2 face=Arial>Not sure. Haven't really though much on windows and
raster since we haven't played with it yet.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=961142319-24092009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=961142319-24092009><FONT color=#0000ff
size=2 face=Arial>I would think so, though you may need to throw an iterator
array in there.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=961142319-24092009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=961142319-24092009><FONT color=#0000ff
size=2 face=Arial>I'm curious -- what particular use case are you thinking of
for a 3x3 window.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=961142319-24092009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=961142319-24092009><FONT color=#0000ff
size=2 face=Arial>Thanks,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=961142319-24092009><FONT color=#0000ff
size=2 face=Arial>Regina</FONT></SPAN></DIV><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>georger.silva<BR><B>Sent:</B> Thursday, September 24, 2009 10:24
AM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users]
SQL moving window averages/statistics<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV>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?</DIV>
<DIV> </DIV>
<DIV>Att.</DIV>
<DIV> </DIV>
<DIV>George<BR><BR></DIV>
<DIV class=gmail_quote>On Thu, Sep 24, 2009 at 7:35 AM, Paragon Corporation
<SPAN dir=ltr><<A href="mailto:lr@pcorp.us">lr@pcorp.us</A>></SPAN>
wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>Kai,<BR><BR>On second thought it appears you are trying to
do a 3 day moving average<BR>like thing.<BR><BR>A self-join would probably
work best given that the window plumbing in<BR>PostgreSQL doesn't support
bounded ranges<BR><BR>SELECT d.date, MAX(d2.tmax) As max_tmax, MIN(d2.tmax) As
min_tmax<BR>FROM daily_climate As d INNER JOIN daily_climate As
d2<BR> ON( d2.date BETWEEN (d.date - INTERVAL '1 day') AND (d.date +
INTERVAL '1<BR>day') )<BR>GROUP BY d.date<BR>
<DIV class=im><BR>-----Original Message-----<BR>From: <A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A><BR></DIV>
<DIV>
<DIV></DIV>
<DIV class=h5>[mailto:<A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A>]
On Behalf Of Paragon<BR>Corporation<BR>Sent: Thursday, September 24, 2009 1:34
AM<BR>To: 'PostGIS Users Discussion'<BR>Subject: Re: [postgis-users] SQL
moving window averages/statistics<BR><BR>Kai,<BR><BR>Still not quite sure what
you are doing here, but if you are using<BR>PostgreSQL 8.4 you can use the New
windowing functions lead(), lag() will<BR>give you forward and backward
values. I put in 1 and 1 for example, but if<BR>you wanted to count back
say 5 records from your current you would replace<BR>with 5<BR><BR><A
href="http://www.postgresql.org/docs/8.4/interactive/functions-window.html"
target=_blank>http://www.postgresql.org/docs/8.4/interactive/functions-window.html</A><BR><BR>So
something like<BR><BR>SELECT date, tmax As curr_tmax, lag(tmax,1) OVER(ORDER
BY date) As<BR>previous_day_tmax, lead(tmax,+1) OVER(ORDER BY date) As
next_day_tmax
FROM<BR>daily_climate;<BR><BR><BR>Leo<BR><BR><BR><BR>-----Original
Message-----<BR>From: <A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A><BR>[mailto:<A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A>]
On Behalf Of Kai<BR>Carter<BR>Sent: Wednesday, September 23, 2009 6:36
PM<BR>To: <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>Subject:
[postgis-users] SQL moving window averages/statistics<BR><BR>Hi
there,<BR><BR>I'm currently have an sql statement that selects a week of
descriptive<BR>statistics for various historical weather variables, sorted by
date.<BR><BR>SELECT to_char(date, 'MM')||to_char(date, 'DD') as date,
max(tmax) as<BR>max_tmax, min(tmax) as min_tmax, avg(tmax) as mean_tmax,
stddev(tmax) as<BR>std_tmax, count(tmax) as count_tmax, sum(tmax) as sum_tmax,
variance<BR>(tmax) as var_tmax FROM daily_climate where icao='KSFO' and
(EXTRACT (MONTH<BR>from date) =9 and EXTRACT(DAY from date) = 23) or
(EXTRACT (MONTH from<BR>date) = 9 and EXTRACT(DAY from date) = 24) or
(EXTRACT (MONTH from date) =<BR>9 and EXTRACT(DAY from date) = 25) or
(EXTRACT (MONTH from date) = 9 and<BR>EXTRACT(DAY from date) = 26) or
(EXTRACT (MONTH from date) =9 and<BR>EXTRACT(DAY from date) = 27) or
(EXTRACT (MONTH from date) = 9 and<BR>EXTRACT(DAY from date) = 28) or
(EXTRACT (MONTH from date) = and<BR>EXTRACT(DAY from date) = 29)
group by date order by date;<BR><BR>The problem is that I only have 36 years
of data to work with, and I would<BR>prefer to have a sample of ~100 rather
than 30. So the idea would be to<BR>have a sample statistics for each
day made up of 3 days:<BR>the current day, the day previous and the day
after.<BR><BR>Is it possible to get this sort of a result with one select
statement?<BR><BR>Thanks in advance for your responses,<BR><BR>Kai
Carter<BR>_______________________________________________<BR>postgis-users
mailing list<BR><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR><BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></DIV></DIV></BLOCKQUOTE></DIV><BR><BR
clear=all>
<DIV></DIV><BR>-- <BR>George R. C. Silva<BR><BR>Desenvolvimento em GIS<BR><A
href="http://www.sextantegeo2.blogspot.com">www.sextantegeo2.blogspot.com</A><BR></BODY></HTML>