Was a bit more painful than necessary, but...
There are now 3 temporary tables and intermediate calculations.
It might be possible to do it more concisely, but this way gives you
intermediate steps to check if something goes wrong.
I spent most time because I figured it should not need the full direction
rotation, reversal and then back again. Eventually swapping X and Y does
that.
I wasn't helped by excel swapping the order of arguments to atan2().
The sqlite maths extension also provides the degrees() and radians()
functions.
-- create first temporary table for wind vector averaging
-- It would be easier in table c5 except that sqlite3 cannot drop columns
later.
create temporary table wind as
select max(dateTime) as dateTime,
1 as windDir,
avg(windSpeed * cos(radians(windDir))) as yvec,
avg(windSpeed * sin(radians(windDir))) as xvec
FROM archive WHERE interval = 1
GROUP BY FLOOR((dateTime-30) / 300);
-- create the temp table containing the newly consolidated results
-- the column names here should match the original database archive table,
-- for ease of re-inserting into archive
create temporary table c5 as
select max(dateTime) as dateTime,
usUnits,
5 as interval,
avg(barometer) as barometer,
avg(pressure) as pressure,
avg(altimeter) as altimeter,
avg(inTemp) as inTemp,
avg(outTemp) as outTemp,
avg(inHumidity) as inHumidity,
avg(outHumidity) as outHumidity,
avg(windSpeed) as windSpeed,
1 as windDir,
max(windGust) as windGust,
1 as windGustDir,
avg(rainRate) as rainRate,
sum(rain) as rain,
avg(dewpoint) as dewpoint,
avg(windchill) as windchill,
avg(heatindex) as heatindex,
sum(ET) as ET,
avg(radiation) as radiation,
avg(UV) as UV,
avg(extraTemp1) as extraTemp1,
avg(extraTemp2) as extraTemp2,
avg(extraTemp3) as extraTemp3,
avg(soilTemp1) as soilTemp1,
avg(soilTemp2) as soilTemp2,
avg(soilTemp3) as soilTemp3,
avg(soilTemp4) as soilTemp4,
avg(leafTemp1) as leafTemp1,
avg(leafTemp2) as leafTemp2,
avg(extraHumid1) as extraHumid1,
avg(extraHumid2) as extraHumid2,
avg(soilMoist1) as soilMoist1,
avg(soilMoist2) as soilMoist2,
avg(soilMoist3) as soilMoist3,
avg(soilMoist4) as soilMoist4,
avg(leafWet1) as leafWet1,
avg(leafWet2) as leafWet2,
avg(rxCheckPercent) as rxCheckPercent,
avg(txBatteryStatus) as txBatteryStatus,
avg(consBatteryVoltage) as consBatteryVoltage,
sum(hail) as hail,
avg(hailRate) as hailRate,
avg(heatingTemp) as heatingTemp,
avg(heatingVoltage) as heatingVoltage,
avg(supplyVoltage) as supplyVoltage,
avg(referenceVoltage) as referenceVoltage,
avg(windBatteryStatus) as windBatteryStatus,
avg(rainBatteryStatus) as rainBatteryStatus,
avg(outTempBatteryStatus) as outTempBatteryStatus,
avg(inTempBatteryStatus) as inTempBatteryStatus
FROM archive WHERE interval = 1
GROUP BY FLOOR((dateTime-30) / 300);
-- now do the vector averaging
update wind
set windDir = degrees(atan2(xvec, yvec) );
update wind
set windDir = windDir + 360.0 where windDir < 0.0;
update c5
SET windDir = (
select wind.windDir
from wind
where c5.dateTime = wind.dateTime
);
-- wind gust averaging is not done, since the gust is
-- the maximum ovre the consolidation period. This
-- extracts the direction at which the maximum was observed.
create temporary table gusts as
select
c5.dateTime as dateTime,
archive.dateTime as arcdt, -- just for checking
archive.windGustDir as windGustDir,
archive.windGust as windGust -- just for checking
from archive join c5
on archive.windGust = c5.windGust and FLOOR((archive.dateTime-30) / 300)
= FLOOR((c5.dateTime-30)/300)
order by dateTime;
update c5
SET windGustDir = (
select gusts.windGustDir
from gusts
where c5.dateTime = gusts.dateTime
);
--
You received this message because you are subscribed to the Google Groups
"weewx-development" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/weewx-development/6983dd6e-2f18-4f5d-a08c-8bf163f9f10e%40googlegroups.com.
