Average calculations should remove '-999' values and others that indicate sensor malfunction about openaq-api-v2 HOT 5 OPEN

@bitner I believe this is done at the rollup table level. Is there documentation on that, that can be shared?

from openaq-api-v2.

I was trying to help out and see if I could figure this one out but I wanted to make sure that I was looking at the right code for the rollups. Do all the averages come from the rollup table that is managed with the code in the following file?
https://github.com/openaq/openaq-db/blob/main/openaqdb/idempotent/update_rollups.sql

from openaq-api-v2.

@caparker yes, that's correct. Thanks for looking into it! Feel free to join slack to get community help

from openaq-api-v2.

Looked into it a bit more. And all we need to do is add another WHERE statement on line 273 of update_rollups.sql

from openaq-api-v2.

One thing that you might run into is that -999 is not the only error code in your measurements, different instruments will have different ways to write their error codes. And its also possible (unlikely though) that -999 is a real value on one instrument type and and error code in another.

So what if you tallied and documented what each instrument reports as its error code, ideally include that in the meta data for the instrument. Then write the where condition to remove the error code from the measurements during rollup, and that where statement may need to be conditional on the instrument type. And then it would be nice if you include a count of errors in the rollup data so you could track it better.

I would also think about the validity of an average that has a lot or errors in it. For example, if one day has 100% valid values and the next day only has 10% valid values, are those two average comparable? I would say no BUT you could pass that decision on to the user so long as they have all the information needed, hence adding the error count to the rollup table.

from openaq-api-v2.