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.
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.
On Aug 14, 2007, at 3:12 PM, Burgholzer,Robert wrote:
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
[EMAIL PROTECTED]
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: [EMAIL PROTECTED] [mailto:postgis-
[EMAIL PROTECTED] 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
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
