Very sweet, Cameron! Somebody capture this in the Wiki!
-tk On Sat, Oct 19, 2019 at 5:28 AM Cameron D <[email protected]> wrote: > 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 > <https://groups.google.com/d/msgid/weewx-development/6983dd6e-2f18-4f5d-a08c-8bf163f9f10e%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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/CAPq0zECh%2BbGgS2f18YDd41NWPmK_NWSP5cRXo5b4TfJb2xsokw%40mail.gmail.com.
