<HTML><BODY style="word-wrap: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space; ">I will give this a try Robert. Thanks. The daily query is working and I think you are visualizing the math correctly. I probably do not need the -999 replacement (actually, as I think about it, the -999 replacement would quite mess up the monthly averages since the replacement algorithm finds the average monthly value for all years... some years are missing all data from some months). You suggestion to group on month and year should take care of that. <DIV><BR class="khtml-block-placeholder"></DIV><DIV>the obs_id column is simply a concatenated unique value to insure that there is no data duplication. If memory serves it is a combination of station_id, year, month, day.<BR><DIV><DIV>On Aug 14, 2007, at 3:12 PM, Burgholzer,Robert wrote:</DIV><BR class="Apple-interchange-newline"><BLOCKQUOTE type="cite"> <DIV class="Section1"><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">Kirk,</SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">I am not sure what exactly is NOT working here, but I will take a stab. If your daily is WORKING already, then simply encase it in a sub-query, i.e.:</SPAN></FONT></P><DIV><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy"> </SPAN></FONT><BR class="khtml-block-placeholder"></DIV><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">Select a.month, a.year, avg(a.tmax) as tmax, avg(a.tmin) as tmin, avg(a.precip) as precip) from </SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy"> ( your daily patching clause that already works) </SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">as a</SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">group by a.month, a.year;</SPAN></FONT></P><DIV><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy"> </SPAN></FONT><BR class="khtml-block-placeholder"></DIV><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">However, I am not sure that you even need to use your “patching” query at all, if all you are doing during the replacement of -999 values is to replace them with the average of days that don’t have -999s. If I am visualizing the math right, these additional days will not change the monthly daily average at all since they are equivalent to it, therefore, you could just multiply the monthly daily average (for days without -999) by the number of days in the month.</SPAN></FONT></P><DIV><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy"> </SPAN></FONT><BR class="khtml-block-placeholder"></DIV><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">I am entirely unsure of what you are doing with the w.obs_id and s.obs_id columns, and why that is in the condition, perhaps an explanation could shed some light.</SPAN></FONT></P><DIV><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy"> </SPAN></FONT><BR class="khtml-block-placeholder"></DIV><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">Best of luck,</SPAN></FONT></P><DIV><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy"> </SPAN></FONT><BR class="khtml-block-placeholder"></DIV> <DIV><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">Robert W. Burgholzer</SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">Surface Water Modeler</SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">Office of Water Supply and Planning</SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">Virginia Department of Environmental Quality</SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy"><A href="mailto:rwburgholzer@deq.virginia.gov">rwburgholzer@deq.virginia.gov</A></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">804-698-4405</SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">Open Source Modeling Tools:</SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy"><A href="http://sourceforge.net/projects/npsource/">http://sourceforge.net/projects/npsource/</A></SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy">Web-Based Water Supply Planning Demo:</SPAN></FONT></P><P class="MsoNormal"><FONT size="2" color="navy" face="Arial"><SPAN style="font-size: 10.0pt;font-family:Arial;color:navy"><A href="http://soulswimmer.dynalias.net/models/wsdemo/demo_hsi.php">http://soulswimmer.dynalias.net/models/wsdemo/demo_hsi.php</A></SPAN></FONT></P> </DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="2" face="Tahoma"><SPAN style="font-size:10.0pt;font-family:Tahoma">-----Original Message-----<BR> <B><SPAN style="font-weight:bold">From:</SPAN></B> postgis-users-bounces@postgis.refractions.net [<A href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] <B><SPAN style="font-weight:bold">On Behalf Of </SPAN></B>Kirk Wythers<BR> <B><SPAN style="font-weight:bold">Sent:</SPAN></B> Tuesday, August 14, 2007 12:15 PM<BR> <B><SPAN style="font-weight:bold">To:</SPAN></B> Discussion PostGIS Users<BR> <B><SPAN style="font-weight:bold">Subject:</SPAN></B> [postgis-users] monthly climate query</SPAN></FONT></P><DIV style="margin-left: 0.5in; "><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> </SPAN></FONT><BR class="khtml-block-placeholder"></DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><SPAN class="apple-style-span"><FONT size="1" face="Lucida Grande"><SPAN style="font-size:8.5pt;font-family:" lucida="" grande""="">I need some help with rewriting a query. I have a query that dumps daily climate data, filling in missing data with monthly averages (one line per day). </SPAN></FONT></SPAN></P> </DIV> <DIV><DIV style="margin-left: 0.5in; "><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> </SPAN></FONT><BR class="khtml-block-placeholder"></DIV> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><SPAN class="apple-style-span"><FONT size="1" face="Lucida Grande"><SPAN style="font-size:8.5pt;font-family:" lucida="" grande""="">I want to output monthly averages (one line per month). I am having a hard time wrapping my head around this. Particularly how to deal with the doy column (day of year). I have tried several approaches and my forehead is starting to get my keyboard bloody. </SPAN></FONT></SPAN></P> </DIV> <DIV><DIV style="margin-left: 0.5in; "><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> </SPAN></FONT><BR class="khtml-block-placeholder"></DIV> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><SPAN class="apple-style-span"><FONT size="1" face="Lucida Grande"><SPAN style="font-size:8.5pt;font-family:" lucida="" grande""="">Here is the daily query:</SPAN></FONT></SPAN></P> </DIV> <DIV><DIV style="margin-left: 0.5in; "><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> </SPAN></FONT><BR class="khtml-block-placeholder"></DIV> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt">SELECT CASE</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><SPAN class="apple-tab-span"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> </SPAN></FONT></SPAN>WHEN w.station_id = site_near.station_id THEN w.obs_id</P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><SPAN class="apple-tab-span"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> </SPAN></FONT></SPAN>ELSE s.obs_id</P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><SPAN class="apple-tab-span"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> </SPAN></FONT></SPAN>END AS obs_id,</P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><SPAN class="apple-tab-span"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> </SPAN></FONT></SPAN>site_near.station_id,</P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><SPAN class="apple-tab-span"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> </SPAN></FONT></SPAN>site_near.longname,</P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><SPAN class="apple-tab-span"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> </SPAN></FONT></SPAN>w.year,</P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><SPAN class="apple-tab-span"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> </SPAN></FONT></SPAN>w.doy,</P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt">--replace missing values (-999) with the monthly average</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> CASE w.tmax</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> WHEN -999 THEN avgtmax.avg</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> ELSE w.tmax</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> END,</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt">CASE w.tmin</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> WHEN -999 THEN avgtmin.avg</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> ELSE w.tmin</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> END,</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt">CASE s.par</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> WHEN -999 THEN avgpar.avg</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> ELSE s.par</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> END,</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt">CASE w.precip</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> WHEN -999 THEN avgprecip.avg</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> ELSE w.precip</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> END</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt">FROM site_near</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> INNER JOIN solar s</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> ON (site_near.ref_solar_station_id = s.station_id</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> AND site_near.obs_year = s.year)</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> INNER JOIN weather w</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> ON (site_near.ref_weather_station_id = w.station_id</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> AND site_near.obs_year = w.year</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> AND s.date = w.date)</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> INNER JOIN (SELECT MONTH,</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> round(avg(tmax)::numeric, 2) AS avg</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> FROM weather</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> WHERE tmax != -999</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> GROUP BY MONTH) AS avgtmax</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> ON (w.month = avgtmax.month)</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><SPAN class="apple-tab-span"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> </SPAN></FONT></SPAN>INNER JOIN (SELECT MONTH,</P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> round(avg(tmin)::numeric, 2) AS avg</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> FROM weather</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> WHERE tmin != -999</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> GROUP BY MONTH) AS avgtmin</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> ON (w.month = avgtmin.month)</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> INNER JOIN (SELECT MONTH,</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> round(avg(par)::numeric, 2) AS avg</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> FROM solar</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> WHERE par != -999</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> GROUP BY MONTH) AS avgpar</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> ON (s.month = avgpar.month)</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><SPAN class="apple-tab-span"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> </SPAN></FONT></SPAN>INNER JOIN (SELECT MONTH,</P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> round(avg(precip)::numeric, 2) AS avg</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> FROM weather</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> WHERE precip != -999</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> GROUP BY MONTH) AS avgprecip</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt"> ON (w.month = avgprecip.month)</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt">--select station to output climate data by id number</SPAN></FONT></P> </DIV> <DIV><P class="MsoNormal" style="margin-left:.5in"><FONT size="3" face="Times New Roman"><SPAN style="font-size:12.0pt">WHERE w.station_id = 219101</SPAN></FONT></P> </DIV> </DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">_______________________________________________</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">postgis-users mailing list</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><A href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A></DIV> </BLOCKQUOTE></DIV><BR></DIV></BODY></HTML>