<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16481" name=GENERATOR></HEAD>
<BODY
style="WORD-WRAP: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space">
<DIV dir=ltr align=left><SPAN class=072472720-14082007><FONT face=Arial
color=#0000ff size=2>Yes I think you did or I missed something. Your
subselects are going to return the average for the whole weather and solar
tables (not for just that reference site) which doesn't quite seem like what you
want if you are trying to put realistic placeholders for missing data in a
site. </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=072472720-14082007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=072472720-14082007><FONT face=Arial
color=#0000ff size=2>Also I don't think you need to do 2 subselects for weather
- I think you can combine into a single one with something like
below</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=072472720-14082007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=072472720-14082007><FONT face=Arial
color=#0000ff size=2><FONT face="Times New Roman" color=#000000
size=3>(SELECT MONTH, station_id,</FONT>
<DIV>
round(avg(<SPAN class=072472720-14082007>CASE WHEN
</SPAN>precip<SPAN class=072472720-14082007> != -999 THEN precip ELSE NULL
END</SPAN>)::numeric, 2) AS avg<SPAN class=072472720-14082007>precip,
</SPAN></DIV>
<DIV><SPAN
class=072472720-14082007>
round(avg(CASE WHEN tmax != -999 THEN tmax ELSE NULL END)::numeric, 2) AS
avgtmax,</SPAN></DIV>
<DIV>
FROM weather</DIV>
<DIV> GROUP
BY MONTH<SPAN class=072472720-14082007>, station_id) As
wmonthsummer</SPAN></DIV>
<DIV><SPAN class=072472720-14082007></SPAN> </DIV>
<DIV><SPAN class=072472720-14082007>Actually if you had put in NULLS for precip
amd tmax instead of -999, you would be better off since those fall out of the
equation quite nicely since aggregates completely ignore null.</SPAN></DIV>
<DIV><SPAN class=072472720-14082007></SPAN> </DIV>
<DIV><SPAN class=072472720-14082007>And then you could blissfully
do</SPAN></DIV>
<DIV><SPAN class=072472720-14082007></SPAN> </DIV>
<DIV><SPAN class=072472720-14082007><SPAN class=072472720-14082007><FONT
face=Arial color=#0000ff size=2><FONT face="Times New Roman" color=#000000
size=3>(SELECT MONTH, station_id,</FONT>
<DIV>
round(avg(precip)::numeric, 2) AS avg<SPAN
class=072472720-14082007>precip, </SPAN></DIV>
<DIV><SPAN
class=072472720-14082007>
round(avg(tmax)::numeric, 2) AS avgtmax,</SPAN></DIV>
<DIV>
FROM weather</DIV>
<DIV> GROUP
BY MONTH<SPAN class=072472720-14082007>, station_id) As
wmonthsummer</SPAN></DIV>
<DIV><SPAN class=072472720-14082007></SPAN> </DIV>
<DIV><SPAN class=072472720-14082007>Hope that helps,</SPAN></DIV>
<DIV><SPAN
class=072472720-14082007>Regina</SPAN></DIV></FONT></SPAN></SPAN></DIV></FONT></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Kirk
Wythers<BR><B>Sent:</B> Tuesday, August 14, 2007 4:25 PM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Subject:</B> Re: [postgis-users] monthly climate
query<BR></FONT><BR></DIV>
<DIV></DIV>If I follow your point Regina (as far as the working daily query
goes), I am returning data for a particular site based on the last line... WHERE
w.station_id = some_valid_number.
<DIV><BR class=khtml-block-placeholder></DIV>
<DIV>Did I miss something?
<DIV><BR>
<DIV>
<DIV>On Aug 14, 2007, at 3:13 PM, Obe, Regina wrote:</DIV><BR
class=Apple-interchange-newline>
<BLOCKQUOTE type="cite">
<DIV dir=ltr align=left><SPAN class=198020920-14082007><FONT face=Arial
color=#0000ff size=2>Wouldn't you want to return the average for month for
that particular site? None of your month subselects seem to group by
station id. Would seem to me logically you should add station_id to
each of your subselects, group by station_id as well as month and then
your ON clause would be month and station_id.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=198020920-14082007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=198020920-14082007><FONT face=Arial
color=#0000ff size=2>Hope that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=198020920-14082007><FONT face=Arial
color=#0000ff size=2>Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><BR></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</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>On Behalf Of </B>Kirk Wythers<BR><B>Sent:</B> Tuesday, August 14, 2007
12:15 PM<BR><B>To:</B> Discussion PostGIS Users<BR><B>Subject:</B>
[postgis-users] monthly climate query<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV style="MARGIN: 0px"><FONT class=Apple-style-span face="Lucida Grande"
size=3><SPAN class=Apple-style-span style="FONT-SIZE: 11px">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><FONT class=Apple-style-span
face="Lucida Grande" size=3><SPAN class=Apple-style-span
style="FONT-SIZE: 11px"></SPAN></FONT></DIV>
<DIV style="MARGIN: 0px"><FONT class=Apple-style-span face="Lucida Grande"
size=3><SPAN class=Apple-style-span style="FONT-SIZE: 11px"><BR
class=khtml-block-placeholder></SPAN></FONT></DIV>
<DIV style="MARGIN: 0px"><FONT class=Apple-style-span face="Lucida Grande"
size=3><SPAN class=Apple-style-span style="FONT-SIZE: 11px">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></DIV>
<DIV style="MARGIN: 0px"><FONT class=Apple-style-span face="Lucida Grande"
size=3><SPAN class=Apple-style-span style="FONT-SIZE: 11px"><BR
class=khtml-block-placeholder></SPAN></FONT></DIV>
<DIV style="MARGIN: 0px"><FONT class=Apple-style-span face="Lucida Grande"
size=3><SPAN class=Apple-style-span style="FONT-SIZE: 11px">Here is the daily
query:</SPAN></FONT></DIV>
<DIV style="MARGIN: 0px"><FONT class=Apple-style-span face="Lucida Grande"
size=3><SPAN class=Apple-style-span style="FONT-SIZE: 11px"><BR
class=khtml-block-placeholder></SPAN></FONT></DIV>
<DIV>SELECT CASE</DIV>
<DIV><SPAN class=Apple-tab-span style="WHITE-SPACE: pre"></SPAN>WHEN
w.station_id = site_near.station_id THEN w.obs_id</DIV>
<DIV><SPAN class=Apple-tab-span style="WHITE-SPACE: pre"></SPAN>ELSE
s.obs_id</DIV>
<DIV><SPAN class=Apple-tab-span style="WHITE-SPACE: pre"></SPAN>END AS
obs_id,</DIV>
<DIV><SPAN class=Apple-tab-span
style="WHITE-SPACE: pre"></SPAN>site_near.station_id,</DIV>
<DIV><SPAN class=Apple-tab-span
style="WHITE-SPACE: pre"></SPAN>site_near.longname,</DIV>
<DIV><SPAN class=Apple-tab-span style="WHITE-SPACE: pre"></SPAN>w.year,</DIV>
<DIV><SPAN class=Apple-tab-span style="WHITE-SPACE: pre"></SPAN>w.doy,</DIV>
<DIV>--replace missing values (-999) with the monthly average</DIV>
<DIV> CASE w.tmax</DIV>
<DIV> WHEN -999 THEN avgtmax.avg</DIV>
<DIV> ELSE w.tmax</DIV>
<DIV> END,</DIV>
<DIV>CASE w.tmin</DIV>
<DIV> WHEN -999 THEN avgtmin.avg</DIV>
<DIV> ELSE w.tmin</DIV>
<DIV> END,</DIV>
<DIV>CASE s.par</DIV>
<DIV> WHEN -999 THEN avgpar.avg</DIV>
<DIV> ELSE s.par</DIV>
<DIV> END,</DIV>
<DIV>CASE w.precip</DIV>
<DIV> WHEN -999 THEN avgprecip.avg</DIV>
<DIV> ELSE w.precip</DIV>
<DIV> END</DIV>
<DIV>FROM site_near</DIV>
<DIV> INNER JOIN solar s</DIV>
<DIV> ON (site_near.ref_solar_station_id =
s.station_id</DIV>
<DIV> AND site_near.obs_year =
s.year)</DIV>
<DIV> INNER JOIN weather w</DIV>
<DIV> ON (site_near.ref_weather_station_id =
w.station_id</DIV>
<DIV> AND site_near.obs_year =
w.year</DIV>
<DIV> AND s.date =
w.date)</DIV>
<DIV> INNER JOIN (SELECT MONTH,</DIV>
<DIV>
round(avg(tmax)::numeric, 2) AS avg</DIV>
<DIV>
FROM weather</DIV>
<DIV>
WHERE tmax != -999</DIV>
<DIV>
GROUP BY MONTH) AS avgtmax</DIV>
<DIV> ON (w.month = avgtmax.month)</DIV>
<DIV><SPAN class=Apple-tab-span style="WHITE-SPACE: pre"></SPAN>INNER JOIN
(SELECT MONTH,</DIV>
<DIV>
round(avg(tmin)::numeric, 2) AS avg</DIV>
<DIV>
FROM weather</DIV>
<DIV>
WHERE tmin != -999</DIV>
<DIV>
GROUP BY MONTH) AS avgtmin</DIV>
<DIV> ON (w.month = avgtmin.month)</DIV>
<DIV> INNER JOIN (SELECT MONTH,</DIV>
<DIV>
round(avg(par)::numeric, 2) AS avg</DIV>
<DIV>
FROM solar</DIV>
<DIV>
WHERE par != -999</DIV>
<DIV>
GROUP BY MONTH) AS avgpar</DIV>
<DIV> ON (s.month = avgpar.month)</DIV>
<DIV><SPAN class=Apple-tab-span style="WHITE-SPACE: pre"></SPAN>INNER JOIN
(SELECT MONTH,</DIV>
<DIV>
round(avg(precip)::numeric, 2) AS avg</DIV>
<DIV>
FROM weather</DIV>
<DIV>
WHERE precip != -999</DIV>
<DIV>
GROUP BY MONTH) AS avgprecip</DIV>
<DIV> ON (w.month = avgprecip.month)</DIV>
<DIV>--select station to output climate data by id number</DIV>
<DIV>WHERE w.station_id = 219101</DIV>
<DIV><BR class=khtml-block-placeholder></DIV>
<HR SIZE=1>
<DIV><BR class=khtml-block-placeholder></DIV>
<P><STRONG>The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure pursuant to
Massachusetts law. It is intended solely for the addressee. If you received
this in error, please contact the sender and delete the material from any
computer. </STRONG></P>
<DIV style="MARGIN: 0px">_______________________________________________</DIV>
<DIV style="MARGIN: 0px">postgis-users mailing list</DIV>
<DIV style="MARGIN: 0px"><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A></DIV>
<DIV style="MARGIN: 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></DIV></BODY></HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
</STRONG></P></BODY></HTML>