[postgis-users] monthly climate query

[Kirk Wythers]

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

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.

Here is the daily query:

SELECT CASE
	WHEN w.station_id = site_near.station_id THEN w.obs_id
	ELSE s.obs_id
	END AS obs_id,
	site_near.station_id,
	site_near.longname,
	w.year,
	w.doy,
--replace missing values (-999) with the monthly average
        CASE w.tmax
          WHEN -999 THEN avgtmax.avg
          ELSE w.tmax
        END,
CASE w.tmin
          WHEN -999 THEN avgtmin.avg
          ELSE w.tmin
        END,
CASE s.par
          WHEN -999 THEN avgpar.avg
          ELSE s.par
        END,
CASE w.precip
          WHEN -999 THEN avgprecip.avg
          ELSE w.precip
        END
FROM  site_near
        INNER JOIN solar s
          ON (site_near.ref_solar_station_id = s.station_id
              AND site_near.obs_year = s.year)
        INNER JOIN weather w
          ON (site_near.ref_weather_station_id = w.station_id
              AND site_near.obs_year = w.year
              AND s.date = w.date)
        INNER JOIN (SELECT   MONTH,
                             round(avg(tmax)::numeric, 2) AS avg
                    FROM     weather
                    WHERE    tmax != -999
                    GROUP BY MONTH) AS avgtmax
          ON (w.month = avgtmax.month)
	INNER JOIN (SELECT   MONTH,
                             round(avg(tmin)::numeric, 2) AS avg
                    FROM     weather
                    WHERE    tmin != -999
                    GROUP BY MONTH) AS avgtmin
          ON (w.month = avgtmin.month)
        INNER JOIN (SELECT   MONTH,
                             round(avg(par)::numeric, 2) AS avg
                    FROM     solar
                    WHERE    par != -999
                    GROUP BY MONTH) AS avgpar
          ON (s.month = avgpar.month)
	INNER JOIN (SELECT   MONTH,
                             round(avg(precip)::numeric, 2) AS avg
                    FROM     weather
                    WHERE    precip != -999
                    GROUP BY MONTH) AS avgprecip
          ON (w.month = avgprecip.month)
--select station to output climate data by id number
WHERE  w.station_id = 219101
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070814/3888f70e/attachment.html>