<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>