Building the 411 for air quality in the United States: a texting platform accessible to all, that provides actionable local information to protect your and your community.
Users should be able to set a preference to choose which AQI conversion (none, LRAPA, USEPA) they want applied to data.
To support the USEPA conversion, we'll need to start tracking humidity and something called pm_cf_1 as well. pm_cf_1 is not yet available in the PurpleAir API, but it's coming soon per the person I talked to.
In terms of how to track this additional data, I'm starting to feel like it's redundant to store all of this on both client and zipcode. Instead, I think we should add a new table as follows:
CREATE TABLE metrics (
id INTEGER NOT NULL,
pm25 double precision NOT NULL,
pm_cf_1 double precision NOT NULL,
humidity INTEGER NOT NULL
)
Then zipcodes and clients can grow a new metrics_id column joining them to the metrics table.
As part of the sync process, we will insert into the metrics table instead of updating the zipcodes table. We will, however, keep track of which zipcode rows map to which metrics. We will then point these rows to the new metrics entries and delete the old metrics if they are not referenced by any other rows (more on this later).
Then, when we send an alert to a client, we will update the clients metrics_id to point to its zipcode's metrics row. We will not delete a metrics row as long as any client still points to it.
In the future, metrics could grow a unique hash column and we could use that to avoid duplicating rows. However, as metrics range over real numbers, I don't foresee this having as much space savings as just doing automatic GC when a row becomes unreferenced.
ianhoffman
commented
3 years ago
Users should be able to set a preference to choose which AQI conversion (none, LRAPA, USEPA) they want applied to data.
To support the USEPA conversion, we'll need to start tracking humidity and something called
pm_cf_1as well.pm_cf_1is not yet available in the PurpleAir API, but it's coming soon per the person I talked to.In terms of how to track this additional data, I'm starting to feel like it's redundant to store all of this on both
clientandzipcode. Instead, I think we should add a new table as follows:Then zipcodes and clients can grow a new
metrics_idcolumn joining them to the metrics table.As part of the sync process, we will insert into the metrics table instead of updating the zipcodes table. We will, however, keep track of which zipcode rows map to which metrics. We will then point these rows to the new metrics entries and delete the old metrics if they are not referenced by any other rows (more on this later).
Then, when we send an alert to a client, we will update the clients
metrics_idto point to its zipcode's metrics row. We will not delete a metrics row as long as any client still points to it.In the future,
metricscould grow a uniquehashcolumn and we could use that to avoid duplicating rows. However, as metrics range over real numbers, I don't foresee this having as much space savings as just doing automatic GC when a row becomes unreferenced.