[postgis-users] monthly climate query

[Burgholzer,Robert]

Kirk,

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

 

Select a.month, a.year, avg(a.tmax) as tmax, avg(a.tmin) as tmin,
avg(a.precip) as precip) from 

  ( your daily patching clause that already works) 

as a

group by a.month, a.year;

 

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.

 

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.

 

Best of luck,

 

Robert W. Burgholzer

Surface Water Modeler

Office of Water Supply and Planning

Virginia Department of Environmental Quality

rwburgholzer at deq.virginia.gov

804-698-4405

Open Source Modeling Tools:

http://sourceforge.net/projects/npsource/

Web-Based Water Supply Planning Demo:

http://soulswimmer.dynalias.net/models/wsdemo/demo_hsi.php

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Kirk
Wythers
Sent: Tuesday, August 14, 2007 12:15 PM
To: Discussion PostGIS Users
Subject: [postgis-users] monthly climate query

 

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/01f2395b/attachment.html>