Store and visualize sensor data in SQLite

Store sensor data with SQLite

Very often as part of an Orange Pi or Raspberry Pi project involving sensors, it is useful to store the sensor data in some kind of way or another. A good way to store the data is in a database that you can manage and query. SQLite is a popular embedded database management system. SQLite comes in the form of a C library. Due to it’s low footprint on resources, it fits well with a device such as a Raspberry Pi or the Orange Pi.

In the first part of the post I will show you how to read and store the data in a SQLite database and in the second part we will look at plotting the data in a chart using Python and JavaScript. In this example, I will use the output of a DHT22 temperature an humidity sensor, but any sensor will do. I showed how to connect the temperature and humidity sensor and the C code for reading measurements in a previous post.

First, we need to install SQLlite on our Orange Pi, if you don’t already have it:

sudo apt-get install sqlite3

Then we need to create a database:

sqlite3 dht22.db

Finally, in this database we need to create a table to store the data sent from the sensor:

CREATE TABLE temps (mdate DATETIME,
 humidity NUMERIC, temperature NUMERIC);

Now, we need to make the Orange Pi read the sensor and store the readings in the table we’ve just created. Most of the code below is recycled from one of my previous posts. Have a look there for the basic DHT22 set-up. Regarding sending data from C to the database, there is a nice introduction on how to work with SQLlite on the official SQLite website. So this is the code with the new database writing functionality:

#include <wiringPi.h>
#include <stdio.h>
#include <stdlib.h>
#include <stdint.h>
#include <sqlite3.h>

#define MAX_TIMINGS    85
#define DHT_PIN        7    /* GPIO-22 */
  
int data[5] = { 0, 0, 0, 0, 0 };
sqlite3 *db;
int rc;  
char *zErrMsg=0;
char qry[80];
  static int callback(void *NotUsed, int argc, char **argv, char **azColName){
    int i;
    for(i=0; i<argc; i++){
      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
 }

void read_dht_data()
{
    uint8_t laststate    = HIGH;
    uint8_t counter        = 0;
    uint8_t j            = 0, i;
  
    data[0] = data[1] = data[2] = data[3] = data[4] = 0;
  
    /* pull pin down for 18 milliseconds */
    pinMode( DHT_PIN, OUTPUT );
    digitalWrite( DHT_PIN, LOW );
    delay( 18 );
  
    /* prepare to read the pin */
    pinMode( DHT_PIN, INPUT );
  
    /* detect change and read data */
    for ( i = 0; i < MAX_TIMINGS; i++ )
    {
        counter = 0;
        while ( digitalRead( DHT_PIN ) == laststate )
        {
            counter++;
            delayMicroseconds( 1 );
            if ( counter == 255 )
            {
                break;
            }
        }
        laststate = digitalRead( DHT_PIN );
  
        if ( counter == 255 )
            break;
        /* ignore first 3 transitions */
        if ( (i >= 4) && (i % 2 == 0) )
        {
            /* shove each bit into the storage bytes */
            data[j / 8] <<= 1;
            if ( counter > 16 )
                data[j / 8] |= 1;
            j++;
        }
    }
  
    /*
     * check we read 40 bits (8bit x 5 ) + verify checksum in the last byte
     * print it out if data is good
     */
    rc = sqlite3_open("/home/ionut/dht22.db", &db);
    if (rc){
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if ( (j >= 40) &&
         (data[4] == ( (data[0] + data[1] + data[2] + data[3]) & 0xFF) ) )
    {
        float h = (float)((data[0] << 8) + data[1]) / 10;
        if ( h > 100 )
        {
            h = data[0];    // for DHT11
        }
        float c = (float)(((data[2] & 0x7F) << 8) + data[3]) / 10;
        if ( c > 125 )
        {
            c = data[2];    // for DHT11
        }
        if ( data[2] & 0x80 )
        {
            c = -c;
        }
        sprintf(qry,"INSERT INTO temps values(datetime('now','localtime'), %.1f, %.1f);",h,c);
        rc = sqlite3_exec(db,qry, callback, 0, &zErrMsg);    
        } else {
        rc = sqlite3_exec(db,"INSERT INTO temps values(datetime('now','localtime'), NULL, NULL);", callback, 0, &zErrMsg);

    }
    sqlite3_close(db);
}
  
int main( void )
{
    printf( "Raspberry Pi DHT11/DHT22 temperature/humidity test\n" );
  
    if ( wiringPiSetup() == -1 )
        exit( 1 );
  
    while ( 1 )
    {
        read_dht_data();
        delay( 2000 ); /* wait 2 seconds before next read */
    }
  
    return(0);
}

As before the code above is based on this code from UUgear. To compile you need the SQLite C library:

sudo apt-get install libsqlite3-dev

Compile and start the program:

sudo cc -Wall -o dht dht.c -lwiringPi -lsqlite3
sudo ./dht

Then let it run for a while so you get some readings. If all went well, we should have some new records in our database. To check that, start SQLlite and do a simple select from our table:

SELECT * from temps;

The output should look like this:

temperature-sensor-data-logger
Next, let’s have a look at how to visualize the data with Python and D3.js.

Visualize sensor data with D3.js

The way I am going to do this is by using Flask, a Python web micro framework, to read data from the SQLite database, store it in a pandas DataFrame. Next we will pass this data to a HTML template that Flask will use to render the chart dynamically, updating as new data comes in. For a intro on Flask, please see my previous post. As before, I will use the SQLAlchemy module to read data in Python from the SQLite database:

from flask import Flask, render_template
import pandas as pd
import numpy as np
from sqlalchemy import create_engine
app=Flask(__name__)
 
@app.route('/')
def index():
    #create the connection string
    disk_engine = create_engine('sqlite:///dht22.db')
    #get data
    df=pd.read_sql_query("SELECT * FROM temps", disk_engine)
    #filter out missing values
    df=df[~np.isnan(df['temperature'])]
    #average values by 10 min intevals
    df['mdate']=df['mdate'].str[0:15]+'0'
    df=df.groupby(['mdate'])['temperature'].mean().reset_index()
    #assign the dataframe to a variable "table"
    return render_template('chart.html',table=df)
if __name__=="__main__":
    app.run(debug=True)

Save it as ‘plotSensor.py’. Based on the line chart example from the D3 website, I wrote the following HTML template:

<!DOCTYPE html>
<html lang="en">
<head>
  <title>Sensor data dashboard</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <!-- Bootstrap CSS -->
  <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <!-- jQuery -->
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
  <!-- Bootstrap JavaScript -->
  <script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  <script src="//d3js.org/d3.v4.min.js"></script>
<style>

.axis--x path {
  display: none;
}

.line {
  fill: none;
  stroke: steelblue;
  stroke-width: 1.5px;
}

</style>
</head>
<body>
 
<div class="container">
  <h2>Temperature sensor data</h2>
  <svg width="960" height="500"></svg>
</div>

The JavaScript code:

var svg = d3.select("svg"),
    margin = {top: 20, right: 80, bottom: 30, left: 50},
    width = svg.attr("width") - margin.left - margin.right,
    height = svg.attr("height") - margin.top - margin.bottom,
    g = svg.append("g").attr("transform", "translate(" + margin.left + "," + margin.top + ")");


var parseTime = d3.timeParse("%Y-%m-%d %H:%M");

var x = d3.scaleTime()
    .rangeRound([0, width]);

var y = d3.scaleLinear()
    .rangeRound([height, 0]);

var data={{table.to_json(orient ='records') |safe}}

var line = d3.line()
    .curve(d3.curveBasis)
    .x(function(d) { return x(parseTime(d.mdate))})
    .y(function(d) { return y(d.temperature) });

  x.domain(d3.extent(data, function(d) { return parseTime(d.mdate)}));
  y.domain([25,28]);
console.log(data)
  g.append("g")
      .attr("transform", "translate(0," + height + ")")
      .call(d3.axisBottom(x).tickFormat(d3.timeFormat("%H:%M")));

  g.append("g")
      .call(d3.axisLeft(y))
    .append("text")
      .attr("fill", "#000")
      .attr("transform", "rotate(-90)")
      .attr("y", -45)
      .attr("dy", "0.71em")
      .attr("text-anchor", "end")
      .text("Temperature");

  g.append("path")
      .datum(data)
      .attr("fill", "none")
      .attr("stroke", "steelblue")
      .attr("stroke-linejoin", "round")
      .attr("stroke-linecap", "round")
      .attr("stroke-width", 1.5)
      .attr("d", line);

Now run the Python script and type in ‘http://127.0.0.1:5000/’ on the browser of your Orange Pi, you should see a plot similar to this one:

temperatur-data-line-chart

Ultrasonic distance sensor
Temperature sensor on Orange Pi with Python code

Comments

  1. Thanks for one marvelous posting! I enjoyed reading it; you are a great author. I will make sure to bookmark your blog and may come back someday. I want to encourage that you continue your great posts
    Hadoop Training in Bangalore

Leave a Reply

Your email address will not be published / Required fields are marked *