Consolidation using SQLite - weewx/weewx GitHub Wiki
This code produces interim tables that can be easily inspected later to confirm calculations. Using temporary tables does not seem to improve the speed.
-- create a temporary copy fo the archive to manipulate
-- if want to check the operation then you might like to extract
-- a short time, like a month or two worth of data. (to do that,
-- uncomment the two lines with a date restriction
CREATE TABLE asmall AS SELECT * FROM
archive
WHERE
`interval` < 5
-- AND dateTime > STRFTIME('%s', '2017-01-01 00:00:00')
-- AND dateTime <= STRFTIME('%s', '2018-01-01 00:00:00')
;
ALTER TABLE asmall ADD COLUMN dt5m INT;
UPDATE asmall SET dt5m = FLOOR((dateTime-30) / 300);
CREATE UNIQUE INDEX ix_a_dt on asmall (dateTime );
CREATE INDEX ix_a_dt5 ON asmall (dt5m );
CREATE INDEX ix_a_speed ON asmall (windGust );
-- create first temporary table for wind vector averaging
CREATE TABLE wind (
dateTime INT PRIMARY KEY,
windDir DOUBLE,
xvec DOUBLE,
yvec DOUBLE
);
INSERT INTO wind
SELECT MAX(dateTime) AS dateTime,
NULL AS windDir, -- default if speed is zero
AVG(windSpeed * COS(RADIANS(windDir))) AS xvec,
AVG(windSpeed * SIN(RADIANS(windDir))) AS yvec
FROM asmall
GROUP BY dt5m ;
-- 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 TABLE c5 AS
SELECT MAX(dateTime) AS dateTime,
dt5m,
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,
NULL AS windDir,
MAX(windGust) AS windGust,
NULL 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 asmall
GROUP BY dt5m;
CREATE UNIQUE INDEX ix_c5_dt on c5 (dateTime );
CREATE INDEX ix_ac5dt5 ON c5 (dt5m );
CREATE INDEX ix_c5_speed ON c5 (windGust );
-- now do the vector averaging
UPDATE wind
SET windDir = DEGREES(ATAN2(yvec, xvec) )
WHERE xvec IS NOT NULL;
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 during the consolidation period. This code
-- extracts the direction at the time at which the maximum was observed.
CREATE TABLE gusts (
dateTime INT PRIMARY KEY,
dt5m INT,
windGustDir DOUBLE,
windGust DOUBLE
);
-- these indexes are not really needed but seem to speed up
-- operations on large datsets.
CREATE INDEX ix_gs_dt5 on gusts (dt5m );
CREATE INDEX ix_gs_gspeed on gusts (windGust);
INSERT OR IGNORE INTO gusts
SELECT
c5.dateTime AS dateTime,
a.dt5m AS dt5m,
a.windGustDir AS windGustDir,
a.windGust AS windGust -- just for checking
FROM asmall AS a JOIN c5
ON
c5.windGust = a.windGust AND
c5.dt5m = a.dt5m ;
UPDATE c5
SET windGustDir = (
SELECT g.windGustDir
FROM gusts AS g
WHERE c5.dateTime = g.dateTime
);