Sensor Dashboard Using Raspberry Pi, MYSQL, and Highcharts

This tutorial will walk you through the process of getting data from the BME280 Temperature, Pressure, and Humidity sensor to be displayed on a local webserver.  We will log the data with a Raspberry Pi, keep it in a MYSQL database, and feed to Highcharts using PHP.  This project is the culmination of a *ton* of work and a *ton* of mistakes that I would like to help you avoid.

In the end, you’ll get a marvelous graph embedded in a webpage like this:

What You’ll Need

Connect the Sensor

The BME280 sensor is available from Adafruit for $19.95 and it measures temperature, pressure, and humidity.  There is another sensor (the BMP280) that measures temperature, pressure, and altitude.  They both work the same way, and get connected the same way.

I have another tutorial that thoroughly describes the soldering and connections for the sensor.  You can check it out here.

There is a little bit of work you need to do to make the sensor run, and I will revisit it here.

apt-get install build-essential python-pip python-dev python-smbus git
git clone https://github.com/adafruit/Adafruit_Python_GPIO.git
sudo python setup.py install

In the folder that was just created

git clone https://github.com/adafruit/Adafruit_Python_BME280.git

Everything is loaded now.  From the Adafruit_Python_BME280 folder run the example file.

 python ./Adafruit_BME280_Example.py

You should see the temperature, pressure, and humidity.  NOTE:  This works with either the BME280 or the BMP280.  The main difference is the humidity and altitude reading respectively.

Now that you can see that you can get sensor readings, we want to get them every 10 minutes, and store them somewhere.  You have a lot of options in storing this information, and in this tutorial, we are going to store them in a MYSQL database.

Make a MYSQL Database

I avoided MYSQL for a long time, and now I can’t figure out why.  I found this very easy to work with.  First thing you will need to do load the software onto the Raspberry Pi.

sudo apt-get install mysql-server mysql-client php5-mysql

This will install server and client versions of mysql that will interact with PHP.  When it does, it will ask you to create a password.  Make sure remember what you select.  The username will be root.

You will also need another library that will allow you to interact with MYSQL via python, so you will need to install this.

sudo apt-get install python-mysqldb

At this point MYSQL is installed, you will just need to start it.  In the command line, type:

sudo mysql -u root -p

It will ask you for your password.  Once entered you will get another command line interface.

We are going to create a database, and in that database we are going to create a table.  We will call the database sensor, and we will call the table bmesensor.

CREATE DATABASE sensor;

This makes a database called sensor.  You only need to do this once.  All of the commands in MYSQL end with a ‘;’.

USE sensor;

This takes you in to the sensor database.  In this database we will create a table called bmesensor.

CREATE TABLE bmesensor(datetime BIGINT NOT NULL, temperature FLOAT (6,3) NOT NULL, pressure FLOAT (8,3) NOT NULL, humidity FLOAT (6,3) NOT NULL);

This is a long expression, so lets pick it apart.  Everything in the parentheses after bmesensor are the headings in the bmesensor table we are creating.  We are making four columns.

  1. datetime – This will be an integer that is meant to have a lot of digits.  We could have used INTEGER but that is limited to 11 digits, so we used BIGINT instead.  This column will hold a UNIX timestamp in milliseconds.
  2. temperature – This will be a floating point decimal with a total of 6 digits, three of which could be after the decimal point.  You can change either of those numbers if you need a different range.
  3. pressure – Again, a floating point decimal.  The sensor gives it to us in pascals, so I increased the total number of digits.  (101325 pascals is 1.0 atm, and that is about the pressure we would be measuring.)
  4. humidity – Our final floating point decimal.

NOT NULL appears after each one. That means that these columns must each contain a value.  We will populate this table by running a python program, which will either give us a value, or give us a traceback.  It seemed like a good idea to have it there.

Now you can view your (empty) table.

DESCRIBE bmesensor;

mysql describe

This is empty, and ready for some data.

To leave MYSQL hit “Control Z”.

A couple other commands that I found useful:

  • TRUNCATE TABLE – that will delete the data from your table, but keep the rows.
  • DROP TABLE – that will delete the table and its data.

Now we we need to make a program that will get the data we need with our sensor.  Change directories so you are in the Adafruit sensor folder

cd
cd Adafruit_Python_GPIO/Adafruit_Python_BME280/

In this folder you will make a program called sensor.

nano sensor.py

This is the program we are going to use:

#!/usr/bin/env python

import os
import time
import datetime
import glob
import MySQLdb
from time import strftime
from Adafruit_BME280 import *

sensor = BME280(t_mode=BME280_OSAMPLE_8, p_mode=BME280_OSAMPLE_8, h_mode=BME280_OSAMPLE_8)

#Variables for MySQL
db = MySQLdb.connect(host="localhost", user="root", passwd="password", db="sensor") # replace password with your password
cur = db.cursor()

def dateTime(): #get UNIX time
		secs = float(time.time())
		secs = secs*1000
		return secs

def tempRead(): #read temperature, return float with 3 decimal places
		degrees = float('{0:.3f}'.format(sensor.read_temperature()))
		return degrees

def pressRead():#read pressure, return float with 3 decimal places
		pascals = float('{0:.3f}'.format(sensor.read_pressure()/100))
		return pascals

def humidityRead(): #read humidity, return float with 3 decimal places
		humidity = float('{0:.3f}'.format(sensor.read_humidity()))
		return humidity

secs = dateTime()
temperature = tempRead()
pressure = pressRead()
humidity = humidityRead()

sql = ("""INSERT INTO bmesensor (datetime,temperature,pressure,humidity) VALUES (%s,%s,%s,%s)""", (secs, temperature, pressure, humidity))

try:
	print "Writing to the database..."
	cur.execute(*sql)
	db.commit()
	print "Write complete"

except:
	db.rollback()
	print "We have a problem"

cur.close()
db.close()

print secs
print temperature
print pressure
print humidity

There is a lot in this program, but we can unpack it pretty easily.

  • The very top has all of the libraries that need to be imported for the program to run.
  • The variables db and cur are there to open your MYSQL database.
  • Then we have some methods to define:
    • datetime(): This will get the UNIX timestamp and convert it into milliseconds.  This is needed because that is what Highcharts uses for timestamps.
    • tempRead():  This gives us a floating point decimal with three decimal places indicating temperature in Celsius.
    • pressRead(): This gives us a floating point decimal with three decimal places indicating pressure in pascals.
    • humidityRead(): This gives us a floating point decimal with three decimal places indicating relative humidity in percent.
  • After that we call each method and store the result in a variable.
  • The sql=”” line is a MYSQL command that will insert each of those four variables into your bmesensor table.
  • We use try and except to do the actual writing, and I included some print statements just to make sure things were working.
  • The we close the connection to the database.
  • Finally, I print out the variables.

This is just a variation on the example sensor python program from the previous section.  You may notice that it doesn’t loop.  That is because we will run the program as a cron job, so it will repeat in regular intervals.  I think that is less problematic than running an infinite loop in one program.

To make the program executable

sudo chmod +x sensor.py

Test it from the same folder, and you should see the variables print out.

./sensor.py

Lastly, we will make it a scheduled job.

sudo crontab -e

At the bottom of this file you will add

*/10 * * * * /home/pi/Adafruit_Python_GPIO/Adafruit_Python_BME280/sensor.py

This means that every 10 minutes the program will run.  If you change the 10 to a 5, it will run every five minutes.

Take a break, and grab a Diet Coke.  You’ve earned it.

Make a Web Server

Now we will make the Raspberry Pi a webserver so we report the sensor results to a web page.  Luckily, this is really easy.

Use the following command

sudo apt-get install apache2 php5 libapache2-mod-php5

This installs the software that will let your Raspberry Pi serve up a web page.  From the RPi you can go to localhost.com or its IP address to see your default web page (192.168.0.3 was mine.)

The code for this page can be found in your /var/www/html/ folder.  So lets go there and make a new file called index.php.


cd /var/www/html

nano index.php

At some point, you will need to delete the index.html file that is there. If you don’t do that, you won’t end up seeing the .php page that we are making. You can do this now by removing it.

rm index.html

Put Your Data in the Web Page

In the index.php file you just created, you will need to have at least three different parts.

  1. A php section that gets information from your MYSQL data table.
  2. An html section that a browser can read
  3. A javascript portion that uses Highcharts

I will go through these individually.

PHP section

In the index.php file, copy the following code.

<?php

$username="root";
$password="password";//use your password
$database="sensor";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die ( "Unable to make it happen Cap'n");

$query = "SELECT datetime, temperature FROM bmesensor";
$result = mysql_query($query);
$dateTemp = array();
$index = 0;
while ($row = mysql_fetch_array($result, MYSQL_NUM))
{
$dateTemp[$index]=$row;
$index++;
}

//echo json_encode($dateTemp, JSON_NUMERIC_CHECK);

mysql_close();

?>

The magic is in the query portion (lines 10-18). Here we set up a while loop. It goes through the bmesensor table, and selects the datetime, and temperature values. Those get put into an array called dateTemp.  I went through several iterations before I finally got this right.  Line 20 has something that is commented out, but useful.  If you uncomment it those [x,y] pairs will be printed so you know you are on the right track.  The json_encode portion removes quotes, making it easier to put things into Highcharts.

The html section
<html>
<head>
<title>HighChart</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.js"></script>
<script src="https://code.highcharts.com/highcharts.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment-timezone/0.5.13/moment-timezone-with-data-2012-2022.min.js"></script>

</head>
<body>

<script type="text/javascript">

</script>
<script src="charts/js/highcharts.js"></script>
<script src="charts/js/modules/exporting.js"></script>

<div class="container">
<br/>
<h2 class="text-center">Living Room Sensor - Temp vs. Time</h2>
<div class="row">
<div class="col-md-10 col-md-offset-1">
<div class="panel panel-default">
<div class="panel-heading">Dashboard</div>
<div class="panel-body">
<div id="container"></div>
</div>
</div>
</div>
</div>
</div>

</body>
</html>

There is more here than there needs to be.  I ended pulling from a number of sources, and I should probably clean it up a bit.  There are some things to pay attention to in lines 7-10.  These are various script sources that are needed to run Highcharts.  Lines 9 and 10 give the x-axis the right time zone, since our time is measured in milliseconds from the epoch.  Line 22 creates a container for the Highcharts chart.

Highcharts

This is the final section.  It is some code that is used to generate an interactive chart.  This could be its own tutorial/lesson/class/graduate degree.  It is incredibly powerful, and equally challenging for a novice to pick up.  The code I used is below and it goes between lines 15 and 17 in the code above:

$(function () {

$('#container').highcharts({
chart: {
type: 'line'
},
time: {
timezone: 'America/New_York'
},
title: {
text: 'Temperature vs Time'
},
xAxis: {
title: {
text: 'Time'
},
type: 'datetime',
},
yAxis: {
title: {
text: 'Temperature'
}
},
series: [{
name: 'Celcius',
data: <?php echo json_encode($dateTemp, JSON_NUMERIC_CHECK);?>
}]
});
});

Line 26 is the most important, so I will show it here again.

data: <?php echo json_encode($dateTemp, JSON_NUMERIC_CHECK);?>

We are telling Highcharts that the [x,y] pairs should come from our $dateTemp array that we made previously.  The beauty here is that it updates dynamically.  Every time you refresh the page it reads the chart again, and adds any new points.

Here is the code all together:

<?php

$username="root";
$password="password";//use your password
$database="sensor";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die ( "Unable to make it happen Cap'n");

$query = "SELECT datetime, temperature FROM bmesensor";
$result = mysql_query($query);
$dateTemp = array();
$index = 0;
while ($row = mysql_fetch_array($result, MYSQL_NUM))
{
$dateTemp[$index]=$row;
$index++;
}

//echo json_encode($dateTemp, JSON_NUMERIC_CHECK);

mysql_close();

?>

<!DOCTYPE html>
<html>
<head>
<title>HighChart</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.js"></script>
<script src="https://code.highcharts.com/highcharts.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment-timezone/0.5.13/moment-timezone-with-data-2012-2022.min.js"></script>

</head>
<body>

<script type="text/javascript">
$(function () {

$('#container').highcharts({
chart: {
type: 'line'
},
time: {
timezone: 'America/New_York'
},
title: {
text: 'Temperature vs Time'
},
xAxis: {
title: {
text: 'Time'
},
type: 'datetime',
},
yAxis: {
title: {
text: 'Temperature'
}
},
series: [{
name: 'Celcius',
data: <?php echo json_encode($dateTemp, JSON_NUMERIC_CHECK);?>
}]
});
});

</script>
<script src="charts/js/highcharts.js"></script>
<script src="charts/js/modules/exporting.js"></script>

<div class="container">
<br/>
<h2 class="text-center">Living Room Sensor - Temp vs. Time</h2>
<div class="row">
<div class="col-md-10 col-md-offset-1">
<div class="panel panel-default">
<div class="panel-heading">Dashboard</div>
<div class="panel-body">
<div id="container"></div>
</div>
</div>
</div>
</div>
</div>

</body>
</html>

So that is it.  I hope it works for you.  If you have any questions or suggestions, please feel free to email me.