You were right Regina. I did some testing and the subselects were
indeed returning averages for the entire weather table.
I also pulled all the -999 values out and replaced them with NULLs.
The way it should have been in the first place.
Here is my latest crack at your suggestions (including getting rid of
the double subselect), However I'm getting syntax error at the FROM
weather line at the end that I just can't find. Do you see it?
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
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, station_id,
round(avg(precip)::numeric, 2) AS avgprecip,
round(avg(tmin)::numeric, 2) AS avgtmin,
round(avg(tmax)::numeric, 2) AS avgtmax,
round(avg(par)::numeric, 2) AS avgpar,
FROM weather
GROUP BY month, station_id) AS avgclim
WHERE w.station_id = 219101;
On Aug 14, 2007, at 3:44 PM, Obe, Regina wrote:
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.
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
(SELECT MONTH, station_id,
round(avg(CASE WHEN precip != -999 THEN
precip ELSE NULL END)::numeric, 2) AS avgprecip,
round(avg(CASE WHEN tmax != -999 THEN tmax
ELSE NULL END)::numeric, 2) AS avgtmax,
FROM weather
GROUP BY MONTH, station_id) As wmonthsummer
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.
And then you could blissfully do
(SELECT MONTH, station_id,
round(avg(precip)::numeric, 2) AS
avgprecip,
round(avg(tmax)::numeric, 2) AS avgtmax,
FROM weather
GROUP BY MONTH, station_id) As wmonthsummer
Hope that helps,
Regina
From: [EMAIL PROTECTED] [mailto:postgis-
[EMAIL PROTECTED] On Behalf Of Kirk Wythers
Sent: Tuesday, August 14, 2007 4:25 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] monthly climate query
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.
Did I miss something?
On Aug 14, 2007, at 3:13 PM, Obe, Regina wrote:
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.
Hope that helps,
Regina
From: [EMAIL PROTECTED]
[mailto:[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
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.
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
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.
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
