Analyzing Wood Burning Stoves with FLaNK Stack Part 2 - Analytics
Tags: apacheflink, apachekafka, apachenifi110, breakoutgarden
Published: 2020-01-27 16:08:49

---
title: Analyzing Wood Burning Stoves with FLaNK Stack Part 2 - Analytics
published: true
date: 2020-01-23 18:03:00 UTC
tags: apache-flink,apache-kafka,apache-nifi-1.10,breakout-garden
canonical_url: 
---

## Analyzing Wood Burning Stoves with FLaNK Stack Part 2 - Analytics - Part 2
**Part 1: **  [https://www.datainmotion.dev/2020/01/analyzing-wood-burning-stoves-with.html](https://dev.to/tspannhw/analyzing-wood-burning-stoves-with-flank-stack-minifi-flink-nifi-kafka-kudu-36on)  
  
**See** :   [https://shop.pimoroni.com/products/sgp30-air-quality-sensor-breakout](https://shop.pimoroni.com/products/sgp30-air-quality-sensor-breakout)  

- Sensiron SGP30 TVOC and eCO2 sensor
- TVOC sensing from 0-60,000 ppb (parts per billion)
- CO2 sensing from 400 to 60,000 ppm (parts per million)

Running the fire I can see I am getting higher CO2 production than normal.

  

Since I stored my data in Kudu tables, it's easy to analyze with Impala and Hue.

  

  

**select equivalentco2ppm, totalvocppb, systemtime**

**from gassensors**

**order by equivalentco2ppm desc**

  

  

[![](https://1.bp.blogspot.com/-QvvAvx4XOSU/XijWKqpfEbI/AAAAAAAAZTA/TSyaTZ1g3m0gfVPN05WfGtsA3EBBLVMPQCLcBGAsYHQ/s640/maxco2.png)](https://1.bp.blogspot.com/-QvvAvx4XOSU/XijWKqpfEbI/AAAAAAAAZTA/TSyaTZ1g3m0gfVPN05WfGtsA3EBBLVMPQCLcBGAsYHQ/s1600/maxco2.png)

  

**select avg( cast(  equivalentco2ppm as double) ) CO2PPM**

**from gassensors**

  

The average was 493.

  

Now that we have some time series data, I can start feeding this to some standard machine learning algorithms and have CML and a Data Scientist if me some analytics and help me determine where I a may want an alert.

  

When should you worry:   [https://www.co2meter.com/blogs/news/56788737-co2-sensor-range-compared](https://www.co2meter.com/blogs/news/56788737-co2-sensor-range-compared) [https://www.co2meter.com/blogs/news/56788737-co2-sensor-range-compared](https://www.co2meter.com/blogs/news/56788737-co2-sensor-range-compared) 

  

Up to 400 is considered normal.

  

400 to 1,000 is typical of occupied locations with air exchange.

  

Once you get over 1,000 you start getting drowsy and noticeable effects.

  

Over 2,000 you get headaches, this is a concern.   Over 5,000 you should remove yourself from the situation.   

  

**select appx\_median(cast(equivalentco2ppm as double)) median, min(cast(equivalentco2ppm as double)) min, **

**       max(cast(equivalentco2ppm as double)) max, avg(cast(equivalentco2ppm as double)) avg, **

**stddev(cast(equivalentco2ppm as double)) standarddev,**

**stddev\_pop(cast(equivalentco2ppm as double)) standardpop**

**from gassensors**

  

[![](https://1.bp.blogspot.com/-H2cRgs-m-zg/XinFKwVl_yI/AAAAAAAAZTQ/feBwz5O2t4kcJd0H2IcLv5pJJoJz2YStwCLcBGAsYHQ/s640/co2max.png)](https://1.bp.blogspot.com/-H2cRgs-m-zg/XinFKwVl_yI/AAAAAAAAZTQ/feBwz5O2t4kcJd0H2IcLv5pJJoJz2YStwCLcBGAsYHQ/s1600/co2max.png)
  

Let's start setting alerts at various levels.

  

We can also look at the indoor air quality.

  

[https://iaqscience.lbl.gov/voc-intro](https://iaqscience.lbl.gov/voc-intro)

  

As a baseline for the sensor, in an empty ventilated room my numbers are:

  

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 18.0px Menlo; color: #ffffff; background-color: #224fbc} span.s1 {font-variant-ligatures: no-common-ligatures} </style>  

{"uuid": "sgp30\_uuid\_glv\_20200123132631", "ipaddress": "192.168.1.221", "runtime": "0", "host": "garden3", "host\_name": "garden3", "macaddress": "dc:a6:32:32:98:20", "end": "1579785991.7173052", "te": "0.0261075496673584", "systemtime": "01/23/2020 08:26:31", "cpu": 53.5, "diskusage": "109138.7 MB", "memory": 46.5, "equivalentco2ppm": "  412", "totalvocppb": "    6", "id": "20200123132631\_dec207f1-9234-4bee-ad38-a0256629c976"}

{"uuid": "sgp30\_uuid\_snt\_20200123132633", "ipaddress": "192.168.1.221", "runtime": "0", "host": "garden3", "host\_name": "garden3", "macaddress": "dc:a6:32:32:98:20", "end": "1579785993.7479923", "te": "0.02589273452758789", "systemtime": "01/23/2020 08:26:33", "cpu": 55.6, "diskusage": "109137.0 MB", "memory": 46.5, "equivalentco2ppm": "  403", "totalvocppb": "    5", "id": "20200123132633\_3bd5fb39-d6b2-4f23-8904-0ada862ede2b"}

{"uuid": "sgp30\_uuid\_uha\_20200123132635", "ipaddress": "192.168.1.221", "runtime": "0", "host": "garden3", "host\_name": "garden3", "macaddress": "dc:a6:32:32:98:20", "end": "1579785995.7779448", "te": "0.025917768478393555", "systemtime": "01/23/2020 08:26:35", "cpu": 51.1, "diskusage": "109135.3 MB", "memory": 46.5, "equivalentco2ppm": "  406", "totalvocppb": "    3", "id": "20200123132635\_0412f445-9b8c-43a8-b34a-a5466f914be7"}

{"uuid": "sgp30\_uuid\_wau\_20200123132637", "ipaddress": "192.168.1.221", "runtime": "0", "host": "garden3", "host\_name": "garden3", "macaddress": "dc:a6:32:32:98:20", "end": "1579785997.8079107", "te": "0.02591681480407715", "systemtime": "01/23/2020 08:26:37", "cpu": 58.7, "diskusage": "109133.5 MB", "memory": 47.1, "equivalentco2ppm": "  406", "totalvocppb": "   13", "id": "20200123132637\_73f069d9-0beb-4d06-a638-2bd92e50ece7"}

{"uuid": "sgp30\_uuid\_lse\_20200123132639", "ipaddress": "192.168.1.221", "runtime": "0", "host": "garden3", "host\_name": "garden3", "macaddress": "dc:a6:32:32:98:20", "end": "1579785999.83777", "te": "0.025897502899169922", "systemtime": "01/23/2020 08:26:39", "cpu": 53.1, "diskusage": "109131.6 MB", "memory": 46.5, "equivalentco2ppm": "  410", "totalvocppb": "    1", "id": "20200123132639\_1aa392fe-0eb7-4332-9631-83ac5838e153"}

  

Very low parts per billion between 1 and 13, with nothing changing in the static room seems like that's a 10 ppb margin of error, we can run some queries in Hue for better stats.

  

Let's look at some data over time for TVOC.

  

**select appx\_median(cast(totalvocppb as double)) median, min(cast(totalvocppb as double)) min, **

**       max(cast(totalvocppb as double)) max, avg(cast(totalvocppb as double)) avg, **

**stddev(cast(totalvocppb as double)) standarddev,**

**stddev\_pop(cast(totalvocppb as double)) standardpop**

**from gassensors**

  

[![](https://1.bp.blogspot.com/-zrHYX8-ryW4/XinF2cFfM4I/AAAAAAAAZTo/4orBArCjWoUYz6R8Fc93NlijOdZVjWaCQCLcBGAsYHQ/s640/maxvoc.png)](https://1.bp.blogspot.com/-zrHYX8-ryW4/XinF2cFfM4I/AAAAAAAAZTo/4orBArCjWoUYz6R8Fc93NlijOdZVjWaCQCLcBGAsYHQ/s1600/maxvoc.png)

  

  

So what's a good TVOC?   On average we are below the range of potential irritation of 120 - 1200 ppb.   We do have some variance for sensor capabilities and lack of professional calibration.      Median and Average numbers look good.   The maximum is a bit disturbing but can be sensor error, warm up time or other data quality issues.   We'll have to dive more into the numbers.

  

Next we can look at PM 2.5 values.

  

Need to crowd source some science here.

  

We had 3,500+ records of data over 120.

  

**select count(\*)**

**from gassensors**

**where  cast(totalvocppb as double) > 120**

  

I can see a number of records and the data climb as the fire burns and we add more cherry wood.

  

**select systemtime, equivalentco2ppm, totalvocppb**

**from gassensors**

**where  cast(totalvocppb as double) > 120**

**order by systemtime asc**

  

I should also note that the time series data is coming in every 2 seconds.

  

**select to\_timestamp(systemtime, 'MM/dd/yyyy HH:mm:ss'), EXTRACT(to\_timestamp(systemtime, 'MM/dd/yyyy HH:mm:ss'), **  
**    'MINUTE') as minute , **  
**cast(totalvocppb as double) as TVOC, cast(equivalentco2ppm as double) CO2PPM**  
  
**from gassensors**  
**order by systemtime desc**

  

**Resources**

  

- [https://joshefin.xyz/air-quality-with-raspberrypi-pms7003-and-java/](https://joshefin.xyz/air-quality-with-raspberrypi-pms7003-and-java/)
- [https://en.wikipedia.org/wiki/Indoor\_air\_quality](https://en.wikipedia.org/wiki/Indoor_air_quality)
- [https://www.hackster.io/bastiaan-slee/chiiiiiirp-indoor-air-quality-measurement-and-alarm-260622](https://www.hackster.io/bastiaan-slee/chiiiiiirp-indoor-air-quality-measurement-and-alarm-260622)
- [http://wiki.seeedstudio.com/Grove-VOC\_and\_eCO2\_Gas\_Sensor-SGP30/](http://wiki.seeedstudio.com/Grove-VOC_and_eCO2_Gas_Sensor-SGP30/)
- [https://learn.adafruit.com/adafruit-sgp30-gas-tvoc-eco2-mox-sensor?view=all](https://learn.adafruit.com/adafruit-sgp30-gas-tvoc-eco2-mox-sensor?view=all)
- [https://www.weather.gov/arx/why\_dewpoint\_vs\_humidity](https://www.weather.gov/arx/why_dewpoint_vs_humidity)
- [https://www.health.state.mn.us/communities/environment/air/toxins/voc.htm](https://www.health.state.mn.us/communities/environment/air/toxins/voc.htm)
- [https://www.mdpi.com/2071-1050/11/22/6225/htm](https://www.mdpi.com/2071-1050/11/22/6225/htm)
- [https://impala.apache.org/docs/build/html/topics/impala\_datetime\_functions.html#datetime\_functions\_\_from\_timestamp](https://impala.apache.org/docs/build/html/topics/impala_datetime_functions.html#datetime_functions__from_timestamp)