Raspberry Pi online logging thermometer

RPi_DHT11
Source: http://www.uugear.com/wordpress/wp-content/uploads/2014/01/RPi_DHT11.jpg

My office never has a steady temperature; sometimes it is too hot, too cold, or just flip-flops all day long. To prove that I wasn’t crazy, I decided to try a DIY thermometer project with a Raspberry Pi that currently was not doing anything else. The best part for me is that this was a project that nicely combines hardware with software in a very practical manner; from wiring together circuits, collecting data in a database, to the end result of a fairly nice looking, online interactive chart.

I have a Model B, but for the most part, these instructions should be generic enough for any of the raspberry pi’s. I think at some point the GPIO board was expanded, but that only added higher-order pins, not affecting the lower numbers. Also, I did not want to use a breadboard, nor any soldering – in the end, I twisted the wires together and taped them down for a layman’s compact design.

Goal

Make a device that records temperature every 15 minutes with as few wires as possible, able to save data on a database and display a public, online interactive chart to summarize data

My supplies:

Setting up the Pi

Sometimes the Raspberry Pi SD cards come pre-loaded with NOOBS. I had an older project still on my card, and I knew I wanted a headless version (no GUI, no pretty windows, pure command-line). I found a few online articles that helped guide me with this (like https://www.raspberrypi.org/forums/viewtopic.php?f=91&t=74176), and I also removed a number of things like Wolfram Mathematica, CUPS, and anything else I did not want. I then set up the wireless adapter so I could login with no wires (since that was part of my project goal), and then hardened the OS (again, google was my friend). I could still do more to harden it, but for now I am satisfied.

Wiring the temperature sensor

temp_sensor_GPIO_layout
Source: http://i.stack.imgur.com/5EKzW.png

I found and used a few different online articles to help with this part. I cannot find the one that I used for the DS18b20 Temperature probe, but the basics are very similar to this one: http://www.circuitbasics.com/raspberry-pi-ds18b20-temperature-sensor-tutorial/

Since I did not want to use a breadboard, nor did I want to solder, I used the female-to-female jumper wires. In retrospect, I could have used something like a female-to-bare-wire jumper. I took one end of the jumper I had, stripped off the female jumper, and twisted that to each end of the DS18b20 probe bare wires. I also twisted in the 4.7k resistor per the instructions (between yellow and red). Some kind of in-line wire clamps would have been nice, but I did not have any at the moment (and did not plan ahead for them), so I twisted the wires and used electrical tape to secure and wrap them up nicely in a low-profile bundle.

Next I plugged the female jumpers into the GPIO board as instructed; red (power) to pin 1, black (ground) to pin 6, and yellow (data) to pin 7.

The first couple times I tried to use modprobe and see the raw data from the Pi, I was foiled because of bad connections (yeah, twisting wires together is not a great way to make sure they stay together). But after redoing my wiring, I finally got some good data from w1_slave. Note also that each 18b20 will (should) have its own id, so if using more than one probe you will have multiple device directories, each with a w1_slave file.

Raspberry-Pi-DS18B20-Temperature-Sensor-Tutorial-DS18B20-Raw-Output
Source:
http://www.circuitbasics.com/raspberry-pi-ds18b20-temperature-sensor-tutorial/

Data Acquisition

At first, I thought about running a sqlite database and webserver locally on the pi itself. But then I got to thinking, if the IP address changes (I don’t have a reserved name), then trying to publish the new URL would be tricky. Since I have access to public-facing servers, for me it was easier to simply collect the raw data on the RPi and send it to another server to publish web pages.

After a few trials and errors, I finally came up with a script that does the following:

  1. scrapes data from every w1_slave file (I only have one right now)
  2. parse out the sensor id and the raw temperature reading (will be Celsius * 1000)
  3. ssh to webserver
  4. insert data directly into sqlite database on webserver
$ cat /home/pi/getTemp.sh
#!/bin/sh

# set -x

host="some_host"
login="some_login"
creds="$login@$host"
out="/home/pi/ssh.out"

if [ ! -e /sys/bus/w1/devices/*/w1_slave ]
then
 exit 1
fi

set `ls -1 /sys/bus/w1/devices/*/w1_slave`
num=$#
while [ $# -gt 0 ]
do
 thisDate=`date +%s`
 id=`echo $1|cut -f6 -d"/"`
 temp=`tail -1 $1|sed 's/.*t=//'`
 echo "thisDate:$thisDate id:$id temp:$temp"

 if [ $temp -gt 0 ]
 then
  query="insert into temps values ($thisDate, '$id', $temp*1.0/1000);"
  ssh $creds "echo \"$query\"|sqlite3 piTemps.db" > $out 2>&1
 fi

 shift
done

And then schedule it in cron:

00,15,30,45 * * * * /home/pi/getTemp.sh > /home/pi/getTemp.out 2>&1

The database

I build a query that inserts the data into a sqlite database called piTemps.db. To create the database and table:

sqlite3 piTemps.db 'CREATE TABLE temps(unix_time bigint primary key, celsius real);'

The unix_time is stored as a large integer (the number of seconds since January 1, 1970, aka epoch time). My insert statement multiples the temperature reading by 1.0 to convert it to decimal, then divide by 1000 to get the order of magnitude correct.

Graphing the data

This turned out to be the most time-consuming part. I had a pretty clear idea of what I wanted my end product to look like, but getting there was not easy (at least, for me). I have prior experience with a js graphing library called Highcharts and I wanted to use that for my presentation. (I have also used d3js, which is awesome, but a steeper learning curve.)

I started with the PiThermServer as my prototype (shout out to Tom Holderness for putting this on github). I like the idea of a simple NodeJS webserver that reads data from a sqlite database and displays data via Highcarts. Making small changes to his base code, I was able to get a simple line chart. I don’t know if you have ever seen a line chart of inside temperature data, especially one that is updated every 15 minutes – it is pretty boring. 🙂 I knew I wanted a radial infographic that resembles those big outdoor thermometers; I finally landed on a dual-guage speedometer example from Highcharts, which I adapted for temperature (fahrenheit and celsius).

To get the most recent temperature for my thermometer, I use this query:

SELECT unix_time*1000 unix_time, round(celsius*9/5 + 32,1) fahrenheit
FROM temps WHERE unix_time =(select max(unix_time) from temps) limit 1;

The subquery gets the maximum unit_time from the database. The outer query then gets the temperature that is associated with that maximum time. When I was testing, I was doing a number of other things as well. Now that the query is pretty much set, a faster way to get the same data might be:

SELECT unix_time*1000 unix_time, round(celsius*9/5 + 32,1) fahrenheit
FROM temps ORDER BY unix_time desc limit 1;

Note the classic conversion of Celsius to Fahrenheit. Since I have a dual-guage chart, I don’t need to convert it here anymore, but I left in for posterity as well.

I wrap that in a js call from my nodeJS server.js:

   var current_temp = db.all("SELECT unix_time*1000 unix_time, 
           round(celsius*9/5 + 32,1) fahrenheit 
           FROM temps WHERE unix_time =(select max(unix_time) from temps) 
           limit 1;",
      function(err, rows){
         if (err){blah blah blah}
         data = {temps:[rows]}
         callback(data);
    });

temperature_guage.png

The hardest part was tweaking the JSON configurations for Highcharts just the way I wanted. You can see my final result here (as opposed to copying it all into this blog post):
http://onyx.csit.parkland.edu/~cschultz/PiThermServer/temp_angular.txt

Next, I really like the Highstock viewer of showing a day, week, month or year of data, along with the overall summary of everything. So I spent a few hours adapting and perfecting that as well:
http://onyx.csit.parkland.edu/~cschultz/PiThermServer/temp_all.txt

The call to the database was slightly different, since I wanted all the data:

SELECT id, (unix_time-21600)*1000 unix_time, round(celsius*9/5 + 32,1) fahrenheit
FROM temps ORDER BY id, unix_time ASC;

And finally, here is my end-product:
http://onyx.csit.parkland.edu:35008/

temperature_end_product.PNG