Additional Rules

##Unhealthy Humidity##

Ever since we moved into our new apartment we noticed that the relative humidity is very low, sometimes even as low as 20%. This has caused some sparkling moments between my wife and myself…

Depending where you look for information, different ranges are being mentioned as being healthy relative humidity. For now I am regarding the range between 30 and 60 percent as being OK. between 30 and 60 percent seems to be considered a ‘healthy’ relative humidity.

Query

select d.value
from   sensor_data d
,      (select max(id) as id from sensor_data where sensor_id = 3) c
where  d.id = c.id
and    ( d.value < 30 or d.value > 60 );

Message

Humidity is outside the healthy range (30-60%%). Humidity is %.2f%%

Pre-process

None

Post-process

None

##Remove Outlier Records##

This one is not so much a rule that would actually require information to be reported but the Rule module provides a convenient method to periodically wipe incorrect and/or suspect data. The main reason for this rule, that from time to time data collected from the DHT22 sensor is not correct as can been seen from these charts. The spikes in the charts are definitely the result of bad data read from the sensors. To make the process easier an new column (and index) has been added to the sensor_data table, named valid which will be populated with the value Y.

alter table sensor_data add column valid char(1) not null default 'Y' after value;
create index sd_valid_idx on sensor_data(valid);

Query

select x.ct
from   (select count(*) as ct
        from   sensor_data
        where  valid = 'N'
       ) x
where x.ct > 0;

Message

%d records with suspect data have been removed.

Pre-process

call mark_invalid()

Post-process

call delete_invalid()

MySQL Procedures

Definition of the mark_invalid procedure 1

create procedure mark_invalid()
begin
    update sensor_data as a
    inner join (select id
                from (select id
                     ,       (@prev := case when @sens = sensor_id then @prev else null end) as prev
                     ,       (@sens := sensor_id) sensor
                     ,       ( value - @prev ) / @prev * 100 as perc
                     ,       (@prev := value) as curr
                     from    sensor_data d
                     ,       (select @prev := null, @sens := null) a
                     order by sensor_id, id) x
                where abs(x.perc) > 25) as b
    on a.id = b.id
    set a.valid = 'N';

    commit;
end;

Definition of the delete_invalid procedure 1

create procedure delete_invalid()
begin
    delete from sensor_data where valid = 'N';

    commit;
end;