PMS5003 Air Quality Monitor Part 3 – Data Presentation

After getting a week or so worth of data it was time to decide how I wanted to view it. I knew I wanted to see the output on a web page. The software for this project is going to be installed on the same Raspberry Pi I use to monitor my weather station. That already supports Apache so it will be easy enough to just create a new web page for Air Quality.

In the back of my head, I kind of knew how I wanted to see data – a series of graphs: one for today, one for the week, etc.

I started by extracting data from the SQLITE db in CSV and using LibreOffice’s Calc program to create Charts of what I thought I wanted to see. This gave me a good opportunity to see what queries I would need and how I would need to summarize the data.

For example, while I could produce a nice daily report using every data point in the database, that was too much data for the 30 day report. That data needed to be averaged. But when I averaged the data I lost the max values. After some experimenting I decided most of the reports needed the data points to be averaged, but then I would also graph the max for each each averaged sample.

Writing a script to extract data in CSV format using SQLITE3 was easy enough. But how to produce a graph for a web page?

Many years ago I had to do the same thing for a massive network monitoring project. Network performance data was summarized from a MySQL database and then gnuplot used to produce JPG files of graphs. I don’t know if that is still the best way to handle generating graphs of SQL data, but I know it works so that is how I proceeded.

Some Program Changes

While experimenting with the existing Pascal program, I found a couple of changes needed to be made from what I posted prior. The Pascal program needed to flush console output because now it’s output is to a log file that I monitor with the linux tail -f command.

The database was modified to correct a fieldname and an index was added to the data field since we will always be searching data based on date.

Further, a new script, was created to allow operation from cron. This script will verify the aqmonitor is not already running and will fixup the LCK..ttyUSB0 problem if it exists.

The new source code is here:

Building a Script to Graph 24 Hours of AQ Data

After messing with Calc, my goal was to have a graph roughly like this, except I would report only PM2.5:

I first created a CSV with pm2.5 and pm10 columns (though I will only chart the pm2.5):

# create and execute query that will output last 24 hours of data
# into aq24h.csv file

cat <<EOF | sqlite3 aqmonitor.db
.headers on
.mode csv
.output aq24h.csv
  strftime('%Y-%m-%d %H:%M', date) as date, pm25Env,
  (pm100Env-pm25Env) as pm100EOnly
from observations
where date > datetime('now','localtime','-24 hours')
order by date;

cat <<EOF | sqlite3 aqmonitor.db sends all of the commands until EOF is found to sqlite. Of these commands: .headers/.mode/.output create the proper CSV output file. Then the actual query occurs.

One thing about SQLITE that is a bit disconcerting. When you use the date ‘now’ it is always GMT. The data is the db is all store in local time. Therefore I must always use the ‘localtime’ modifier.

This creates the proper CSV file:

"2021-06-08 10:41",0,0
"2021-06-08 10:46",0,0
"2021-06-08 10:51",0,0
"2021-06-08 10:56",0,0
"2021-06-08 11:01",0,0
"2021-06-08 11:06",0,0

I created another query to will save the Maximum PM value in the bash variable ${maxPM} during this time period because I want to show that on the graph as well:

# This query determines the max Y value that will be plotted so we can 
# handle labels cleanly in the plot

maxPM=$(cat <<EOF | sqlite3 aqmonitor.db
  max(max(pm25Env), max(pm100Env-pm25Env)) as maxPM
from observations
where date > datetime('now','localtime','-24 hours')

I used gnuplot to generate the graph as a .PNG file. There is a lot going on in gnuplot so I will try to break it down. Overall, I feed commands to gnuplot the same way I fed them to sqlite3:

cat <<EOF | gnuplot 

Here is an explanation of the commands I sent to gnuplot. Note that I haven’t used gnuplot in over a decade. I found examples of what I needed online and changed them for my purposes. There may well be better ways and my understanding of what I’m doing here is limited.

I started by setting the gnuplot variable maxPM to bash’s ${maxPM} variable. I then created label 99 using that data. Next the CSV separator was specified and the graph’s Title defined:

    # display Max obs above and right of the graph
    set label 99 sprintf("Maximum Observation: %d", maxPM) at graph 1, graph 1 right offset 0, char 1
    set datafile separator ','							#CSV field separator
    set title "Particulate Matter for Last 24 Hours" font ",20"			#header

The X axis was formatted for using dates:

    set xdata time 								#tells gnuplot the x axis is time data
    set timefmt "%Y-%m-%d %H:%M" 						#specify our time string format
    set format x "%H:%M" 							#otherwise it will show only MM:SS

Here, the Y axis was labeled and the legend box defined:

    set ylabel "PM (Β΅grams / meter^{3})" 					#label for the Y axis    
    set ytics nomirror								#no Y ticks at top

    # setup legend box
    set style line 100 lt 1 lc rgb "dark-grey" lw 0.5                           # linestyle for the grid
    set grid ls 100 front                                                       # enable grid with specific linestyle

Here’s where it get’s a bit tricky. There are a series of ranges defined for PM2.5. For example, Good is 0 – 12 micro-grams/cubic meter:

In my graph, I want colored bars in the background for these ranges. Here, I defined each bar as a rectangle and gave it a color:

    # create colored background of bars 
    set object 50 rect from graph 0, graph 0  to graph 1, first 12        fc rgb "green"  lw 0 
    set object 51 rect from graph 0, first 12 to graph 1, first 35.4      fc rgb "yellow" lw 0
    set object 52 rect from graph 0, first 35.4  to graph 1, first 55.4   fc rgb "orange" lw 0
    set object 53 rect from graph 0, first 55.4  to graph 1, first 150.4  fc rgb "red"    lw 0
    set object 54 rect from graph 0, first 150.4 to graph 1, first 250.4  fc rgb "purple" lw 0
    set object 55 rect from graph 0, first 250.4 to graph 1, graph 1      fc rgb "brown"  lw 0

Further, I want to put text into each colored rectangle: Good, Moderate, etc. When I did this I had a lot of trouble because the text is a fixed font size and each colored bar would grow/shrink depending on the maximum values being graphed.

To deal with the problem, I decided to only display the colored bars that have data for them. To do that, I formatted the yrange and text based on the value of the maxPM variable:

    # Labels for the colored bars a defined based on which bars are actually displayed
    if (maxPM  12 && maxPM  35 && maxPM  55 && maxPM  150 && maxPM  250) {
      set yrange[0:500<*]
      set label 53 "Unhealthy"			    at graph 0.5, first 100  center tc rgb "black"
      set label 54 "Very Unhealthy"                 at graph 0.5, first 200  center tc rgb "black"
      if (maxPM <= 500) {
        set label 55 "Hazardous"                    at graph 0.5, first (500-250)/2+250    center tc rgb "black"
      else {
        set label 55 "Hazardous"	            at graph 0.5, first (maxPM-250)/2+250  center tc rgb "black" #Y position calculated to be centered

Finally, I setup the line styles for the plot, characteristics of the output graph (a PNG file), and do the actual plot:

    set style line 101               lw 1 lt rgb "black"                        #line color for PM2.5
    set style line 102 dashtype "-." lw 1 lt rgb "blue"				#line color for PM10
    # output graphic definitions
    set terminal pngcairo size 800,600 
    set output "aq24h.png"

    plot 'aq24h.csv' using 1:2 with lines ls 101 title 'PM2.5'#,\
         #''         using 1:3 with lines ls 102 title 'PM10'

When I run this for my current 24 hours, which has a maximum of 6 micro-grams/cubic meter I see:

Here, I tweak the data to create a single PM2.5 observation of 400. You can see how all of the appropriate color bars are added and the labels are removed from the skinny color bars:

Using this script as an example, I then proceeded to create scripts to produce plots for 7 days, 30 days, and 1 year.

Creating the Web Page

Now I have 4 graphs to display, plus a file that contains just text of the current time and the last observation recorded. I need an HTML file that ties this all together into a single web page.

Here is that page. The only thing even remotely tricky about this HTML file is using javascript to include the file aqCurrent.htm which contains the current observation.

Because the examples of the page I show are not yet on a web server, you won’t see this included file. But it will show up once I have the code running on a web server.

<!DOCTYPE html>
    BigDanz Air Quality Monitor
    BigDanz Air Quality Monitor
    <table border=0>
        <td align=center colspan="2">
          <div id="includedContent"></div>
          <img src="aq24h.png" style="max-width: 100%; height: auto">
          <img src="aq7d.png"  style="max-width: 100%; height: auto">
          <img src="aq30d.png" style="max-width: 100%; height: auto">
          <img src="aq1y.png"  style="max-width: 100%; height: auto">

Here is what the web page looks like. Note that the Last Year graph is a little funky because the average is for a full day, but there are only a few days of data.

The HTML file and the scripts to extract data and generate graphs are here:

In this project, I ended up graphing only PM2.5. I tried showing both on the graph, but the graph was just too sloppy. From what I’ve read, excessive PM2.5 is more critical than PM10, because it can embed itself further in the lungs. So I’ve focused only on PM2.5.

This entry was posted in c-electronics, c-lazarus and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.