add aqi calculation

[vinceskahan]

enhancements to calculate and store the AQI based on US calculations and the pm2_5 sensor readings.

[bakerkj]

What is the database upgrade strategy for someone that has an existing database and wants to now have (and archive) AQI values?

[bakerkj]

lookUpColor(PMobs) fails to return values when PMobs is between PMmax and PMmin. e.g. 15.45

[vinceskahan]

Upgrade strategy is a little ugly. You can update the archive schema with one sqlite3 command and create the matching archive_day_aqi2_5 table with another. That would get you aqi starting from the current point in time, but it won't back-calculate the pre-existing values. What I did was select from archive to a /tmp/file to get all the existing records, then wrote a python program to read that file and generate sqlite 'update archive where dateTime = NNNN' commands to a second tmpfile. I then did 'cat tmpfile | sqlite3 purpleair.sdb' to update the pre-existing records in the archive. I then did a wee_database --binding=purpleair_binding --rebuild-daily --from YYYYMMDD --to YYYYMMDD to catch up the archive table. Worked fine and was reasonably quick, but I only had 3 months of purpleair data. I can provide more details via email if you'd like.

On Sat, Jul 6, 2019 at 6:05 PM Ken Baker notifications@github.com wrote:

What is the database upgrade strategy for someone that has an existing database and wants to now have (and archive) AQI values?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/bakerkj/weewx-purpleair/pull/10?email_source=notifications&email_token=AAETMKVZJQI76LWJMHBCJJTP6E6M5A5CNFSM4H4ODPU2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODZLCXEQ#issuecomment-508963730, or mute the thread https://github.com/notifications/unsubscribe-auth/AAETMKWVO3LQCZV54GIZXXLP6E6M5ANCNFSM4H4ODPUQ .

-- ----- vinceskahan@gmail.com ----

[vinceskahan]

Agree. The table shouldn't have any holes in it. If green PMmax is 15.5 then the next color's PMmin should be 15.5 to match. It was a little unclear from the referenced URL how much this matters and whether we should be working off PMobs to only one decimal point (which would be an alternate solution). Suggest using your judgement. Either tweak the mapping table to leave no holes, or take your PMobs average to only one decimal point.

On Sat, Jul 6, 2019 at 6:07 PM Ken Baker notifications@github.com wrote:

lookUpColor(PMobs) fails to return values when PMobs is between PMmax and PMmin. e.g. 15.45

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/bakerkj/weewx-purpleair/pull/10?email_source=notifications&email_token=AAETMKSSINFOUTUBV2EJCQLP6E6VRA5CNFSM4H4ODPU2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODZLCX4I#issuecomment-508963825, or mute the thread https://github.com/notifications/unsubscribe-auth/AAETMKQSL2RQEPZZYY3F3ATP6E6VRANCNFSM4H4ODPUQ .

-- ----- vinceskahan@gmail.com ----

[bakerkj]

OH wait! Look at this. The latest json output from my purple-air contains pm2.5_aqi and pm2.5_aqi_b!! So we don't need to calculate the values at all!

[bakerkj]

As far as updates go. Ideally we'd write up instructions to add to the readme.

[vinceskahan]

me too - excellent ! even has the colors in it, although I'm not sure why green isn't 0,255,0 - maybe it's green(ish) or something.

$ curl http://purpleair/json 2>/dev/null |jq . | egrep "2.5|2_5" "pm2.5_aqi_color_b": "rgb(6,229,0)", "pm2.5_aqi_b": 14, "pm2_5_atm_b": 3.38, "p_2_5_um_b": 1.77, "pm2_5_cf_1_b": 3.38, "pm2.5_aqi_color": "rgb(4,228,0)", "pm2.5_aqi": 13, "pm2_5_atm": 3.08, "p_2_5_um": 0.77, "pm2_5_cf_1": 3.08,

Note the discrepancy in naming, sometimes they use pm2.5 and sometimes pm2_5 - uncertain if they have a stable interface or not.

For adding the sqlite table parameters....assuming your element is named aqi2_5 in the archive table:

CREATE TABLE archive_day_aqi2_5 (dateTime INTEGER NOT NULL UNIQUE PRIMARY KEY, min REAL, mintime INTEGER, max REAL, maxtime INTEGER, sum REAL, count INTEGER, wsum REAL, sumtime INTEGER);

ALTER TABLE archive ADD aqi2_5 INTEGER;

You have a schema check in your extension (nice) so be sure to add the aqi2_5 element to the definition there or weewx won't start up.

And of course be consistent re: whatever you name the element....

On Sat, Jul 6, 2019 at 6:34 PM Ken Baker notifications@github.com wrote:

OH wait! Look at this. The latest json output from my purple-air contains pm2.5_aqi and pm2.5_aqi_b!! So we don't need to calculate the values at all!

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/bakerkj/weewx-purpleair/pull/10?email_source=notifications&email_token=AAETMKUIJDLYZX4RBYXMC5TP6FBYPA5CNFSM4H4ODPU2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODZLC7LI#issuecomment-508964781, or mute the thread https://github.com/notifications/unsubscribe-auth/AAETMKVXPR5Y4CC2RDSDEDDP6FBYPANCNFSM4H4ODPUQ .

-- ----- vinceskahan@gmail.com ----

[trdischat]

To avoid holes in the aqi calculation, the official EPA method for calculating AQI requires you to truncate (round down) the raw pm2_5 value to the nearest 0.1 before using it to calculate AQI. See https://www3.epa.gov/airnow/aqi-technical-assistance-document-sept2018.pdf. This formula seems to work: PMobs=record['pm2_5_atm'] // 0.1 / 10

[vinceskahan]

Sure, but if we simply pick the vendor calculated number out of their json then they get to do all the heavy lifting!

On Sun, Aug 30, 2020, 2:57 PM trdischat notifications@github.com wrote:

To avoid holes in the aqi calculation, the official EPA method for calculating AQI requires you to truncate (round down) the raw pm2_5 value to the nearest 0.1 before using it to calculate AQI. See https://www3.epa.gov/airnow/aqi-technical-assistance-document-sept2018.pdf. This formula seems to work: PMobs=record['pm2_5_atm'] // 0.1 / 10

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/bakerkj/weewx-purpleair/pull/10#issuecomment-683475609, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAETMKUZDA5AJH2ROFOIEBLSDLDMLANCNFSM4H4ODPUQ .

[trdischat]

Unfortunately, if you pull the data directly from purpleair.com, the json only includes the raw pm readings. There are no aqi values. E.g., see https://www.purpleair.com/json?show=25849. So I am using your aqi code to compute realtime aqi values from the raw pm2_5_atm value in the json.

[vinceskahan]

I think we can probably close this one as not needed, but I thought I'd mention that there seems to be different breakpoints for the calculations than in the PR above.

https://www.airnow.gov/sites/default/files/2020-05/aqi-technical-assistance-document-sept2018.pdf has the details....

FWIW, I modified the PR code to calculate AQI for the Davis Airlink sensor, which does 'not' have AQI in its returned JSON, so the code itself is ok after updating the breakpoints to be:

# 2018 breakpoints from https://www.airnow.gov/sites/default/files/2020-05/aqi-technical-assistance-document-sept2018.pdf
# actual min start at 0.1 above the previous max but we set them the same to not miss anything fractionally in between
# rather than round either way (lazy me)
AQImapping = {
    "green" : { 'AQImin':   0, 'AQImax':  50, 'PMmin':     0, 'PMmax':  12.0, 'Category': "Good"                           },
    "yellow": { 'AQImin':  51, 'AQImax': 100, 'PMmin':  12.1, 'PMmax':  35.4, 'Category': "Moderate"                       },
    "orange": { 'AQImin': 101, 'AQImax': 150, 'PMmin':  35.4, 'PMmax':  55.4, 'Category': "Unhealthy for Sensitive Groups" },
    "red"   : { 'AQImin': 151, 'AQImax': 200, 'PMmin':  55.4, 'PMmax': 150.4, 'Category': "Unhealthy"                      },
    "purple": { 'AQImin': 201, 'AQImax': 300, 'PMmin': 150.4, 'PMmax': 250.4, 'Category': "Very Unhealthy"                 },
    "maroon": { 'AQImin': 301, 'AQImax': 500, 'PMmin': 250.4, 'PMmax': 500.4, 'Category': "Hazardous"                      },
 }