using Programming;

A Blog about some of the intrinsics related to programming and how one can get the best out of various languages.

Building a Rashberry Pi Graphing Machine

Building the software for a Rasberry Pi Weather Instrument System

Per the usual I recently tweeted about a thing, which was a Raspberry Pi Weather Measurement System, in particular I tweeted a picture of the graphs (and asked an expert in statistics / data-science for an assist on how to best model the data), and said expert actually responded with some helpful information.

Of course, shortly afterward someone asked me how to create said thing, so that's what this blog-post is about: how did I create the graphing system there?

Now, because you might not have a Raspberry Pi of your own to play with, I'm going to demonstrate with a blank Debian install (because my Raspberry Pi OS is Rasbian), and we'll go through the whole thing pretty quick as far as setup.

For me, I use Oracle (is it Oracle now? It was Sun when I started using it) VirtualBox, which is a freaking awesome piece of software. I then setup a Debian VM, get Debian installed with a minimal graphical config (XFCE), and then we can get started.

Step 1: Install MariaDB and Apache with Python

The first step to getting our setup going is to install MariaDB / MySQL. For this, a simple command is necessary (if using Debian 9.3 you'll want to su - then use the command instead of sudo with the command, as it seems like sudo isn't always included by default):

sudo apt-get install mariadb-server

(If it gives you crap about inserting a CD ROM, you can sudo nano /etc/apt/sources.list, then put a # as the first character on the first line without it, then CTRL + O, press [Return], then CTRL + X.) My MariaDB did not prompt for a password (it installed with a root user with no password), but if yours prompts you for a password just pick something you can remember (we'll need it later).

Next, we want to install Apache / httpd, and set it up for Python hosting. This is, once again, a pretty simple command:

sudo apt-get install apache2

Then set it up for Python, I won't detail the commands but they're pretty simple:

sudo a2dismod mpm_event
sudo a2enmod mpm_prefork cgi

Modify the /etc/apache2/sites-enabled/000-default.conf:

sudo nano /etc/apache2/sites-enabled/000-default.conf

In that config file, you want to add an Options line with +ExecCGI as the argument inside the Directory, which you should create if it doesn't exist (added mine right after the <VirtualHost *:80> line. You also want to set the index.

<Directory /var/www/html>
    Options + ExecCGI
    DirectoryIndex index.py
</Directory>

Next we want to AddHandler cgi-script .py after the directory lines:

AddHandler cgi-script .py

Finally:

sudo service apache2 restart

Step 2: Create a database

Assuming you setup MySQL / MariaDB, you should be able to login:

mysql -u ... -p

Where ... is your username (probably root, I'm insecure). If you don't have a password set, omit the -p bit. We create a database:

CREATE DATABASE WhateverNameYouReallyLike;
USE WhateverNameYouReallyLike;

Then we'll create a table (you can make as many as you need, I always make one per sensor):

CREATE TABLE TemperatureLog
(
    Logged datetime NOT NULL,
    Sample int(2) NOT NULL,
    Value float(11, 3) NOT NULL,
    PRIMARY KEY (Logged, Sample)
);

You can make as many as you need, I have 6, because I have that many sensor values.

Finally, make a user:

CREATE USER 'SomeUsername' IDENTIFIED BY 'SomePassword';
GRANT ALL ON WhateverNameYouReallyLike.* TO 'SomeUsername';

Step 3: Log to the Database

This part I won't detail much, we'll build a sample cron job that adds random data, not ideal but it'll get our graphing to work like we want.

We won't use truly random data, we'll actually use a formula with a little bit of a random skew, so that we see the graph adjust slightly but also to allow us to see how our tick settings and such work.

First we'll install pymysql, which isn't available by default:

sudo apt-get install python-pymysql

Then, create a test.py or something, with the following contents:

#!/usr/bin/python
from datetime import datetime
from time import sleep
from random import randint

import sys
import pymysql

SAMPLES = 5
DELAY = 2
DATE_FORMAT = "%Y-%m-%d %H:%M:%S"

def build_query(table, date, sample, value):
    template = """INSERT INTO {table} (Logged, Sample, Value) VALUES ('{logged}', {sample}. {value})"""
    return template.format(table=table, logged=date, sample=sample, value=value)

if __name__ == '__main__':
    date_val = datetime.utcnow()
    date = date_val.strftime(DATE_FORMAT)
    conn = pymysql.connect (
        db='WhateverNameYouLike',
        user='SomeUsername',
        passwd='SomePassword',
        host='localhost')
    cursor = conn.cursor()

    for sample in range(SAMPLES):
        value = date_val.hour * 60 + date_val.minute
        value = abs(1800 - value) / 100.0
        temp = value + (randint(0, 9) / 100.0)
        cursor.execute(build_query("TemperatureLog", date, sample, temp))
        print "Temp: {0}".format(temp)
    conn.commit()

Now the values themselves don't really matter, but they should range from 0 to 18, then back down. (0 at noon, 18 at midnight), that way we see a nice curve.

Next step is to setup a cronjob, usually cron is installed by default but if it's not there are articles on the internet about it.

We'll make a /etc/cron.d/test for ours, and fill it with:

SHELL=/bin/sh
* * * * * pi /home/pi/test.py

Chances are you are on a Raspberry Pi, so pi will be after the five asterisks, then /home/py/test.py is the path to your python file.

This will run every minute, so we'll let it run while we work on the next bit.

Step 4: Setup graphing (this is the fun part)

We want to get our Apache server setup to host some python, HTML and Javascript which allows us to view live-graphs.

We'll test Apache quick with an index.py:

#!/usr/bin/python
print "Content-Type: text/html"
print ""
print "Test"

If well-and-good you should see "Test" printed to your screen.

Now things get fun.

For graphing, I always use [d3.js], which is a really powerful and robust bit of JavaScript, it does a lot of stuff, far more than I know, so I just trust it for the most part (probably bad but oh well). You'll want to download the .zip, and pull all the files into a d3 folder in your /var/www/html.

You also probably want jQuery, I'm using 1.12.4 min, and whatever design stuff you want (bootstrap, etc.).

We want to make our index.py pretty simple:

#!/usr/bin/python
# Header must always be printed first
print "Content-type: text/html"
print ""

if __name__ == "__main__":
    with open("index.html") as template:
        print template.read()

That's it, we'll use index.html to hold all our stuff. (Realistically, we could have left the document root of Apache as index.html and just used it, but I had it setup this way because I did a couple weird things in the index.py.) The nice thing here is this still proves your python works.

Next, we want to define a time.py, which will be a sort of API:

#!/usr/bin/python
from datetime import datetime, timedelta
import cgi

DEFAULT_DATE_FORMAT = "%Y-%m-%d %H:%M:%S"

# Header must always be printed first
print "Content-type: text/html"
print ""

if __name__ == "__main__":
    args = cgi.FieldStorage()
    date_format = DEFAULT_DATE_FORMAT
    if "DateFormat" in args:
        date_format = args["DateFormat"].value

    if "Next" in args or "All" in args:
        utctime = datetime.utcnow()
        print (utctime + timedelta(seconds = 60 - utctime.second)).strftime(date_format)
   if "NextIn" in args or "All" in args:
        utctime = datetime.utcnow()
        print str(60 - utctime.second) + " seconds"
    if (not "Next" in args and not "NextIn" in args) or "All" in args:
        print datetime.utcnow().strftime(date_format)

Alright, so we're going to use this to display the system time and the countdown to the next thing. You can test it directly, it should print your current system time (in UTC).

Next I'll give you my stylesheet, it's pretty basic:

body { 
    background: #202020;
    color: #efefef;
    width: 100%;
    height: 100%;
    overflow: hidden;
}
html { height: 100%; }

.graphBlock {
    max-height: 100%:
    max-width: 100%;
    height: 100%;
    width: 100%;
    text-align: center;
}

svg { display: inline-block; }
.graphBlock svg { width: 100%; }
.graphBlock.split svg { width: 49%; }
.right { float: right; }
.axis line { stroke: #303030; }
.axis.y path, .axis.y .tick:nth-child(2) line { stroke: #efefef; }
.axis path { stroke: #303030; }
svg text { fill: #efefef; }

.header, .current {
    background: #07507F;
    padding: 5px;
}

.current { border-bottom: 1px solid #30303; }
select, input { color: #202020; }

Alright, so the stylesheet is done. Things only gett more interesting from here. We have a graph.js file to define:

function buildGraph(svgTitle, table, title, unit, params, cb) {
    var svg = d3.select(svgTitle);
    var margin = {top: 20, right: 20, bottom: 30, left: 40};
    var vb = svg.attr("viewBox").split(" ");
    var width = +vb[2] - margin.left - margin.right;
    var height = +vb[3] - margin.top - margin.bottom;
    var g = svg
        .append("g")
        .attr("transform", "translate(" + margin.left + "," + margin.top + ")")
        .attr("width", width);

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

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

    var line = d3.line()
        .curve(d3.curveMonotoneX)
        .x(function(d) { return x(d.Logged); })
        .y(function(d) { return y(d.Value); });

    var area = d3.area()
        .curve(d3.curveMonotoneX)
        .x(function(d) { return x(d.Logged); })
        .y1(function(d) { return y(d.Value); });

    d3.tsv("history.py?LogType=" + table + "&" + params, function(d) {
        d.Logged = parseTime(d.Logged);
        d.Value = +d.Value;
        return d;
    }, function(error, data) {
        if (error) throw error;

        x.domain(d3.extent(data, function(d) { return d.Logged; }));
        var yS = y.domain(d3.extent(data, function(d) { return d.Value; })).nice();

        area.y0(y(yS.domain()[0]));

        g.append("g")
            .attr("class", "x axis")
            .attr("transform", "translate(0," + height + ")")
            .call(d3.axisBottom(x).tickSize(-height))
            .select(".domain");

        g.append("g")
            .attr("class", "y axis")
            .call(d3.axisLeft(y).tickSize(-width))
            .append("text")
            .attr("fill", "#000")
            .attr("transform", "rotate(-90)")
            .attr("y", 6)
            .attr("x", -4)
            .attr("dy", "0.71em")
            .attr("text-anchor", "em")
            .text(title);

        g.select(".y")
            .select(".domain")
            .attr("transform", "translate(-1,0)");

        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);

        g.append("path")
            .datum(data)
            .attr("fill", "steelblue")
            .attr("fill-opacity", "0.25")
            .attr("stroke", "none")
            .attr("d", area);

        g.append("text")
            .attr("fill", "#000")
            .attr("y", "4")
            .attr("x", width - 4)
            .attr("dy", "0.71em")
            .attr("font-size", "11")
            .attr("text-anchor", "end")
            .text(parseFloat(Math.round(data[data.length - 1].Value * 100) / 100).toFixed(2) + unit);
        cb();
    });
}

function updateGraph(svgTitle, table, title, unit, params, cb) {
    var svg = d3.select(svgTitle);
    var margin = {top: 20, right: 20, bottom: 30, left: 40};
    var vb = svg.attr("viewBox").split(" ");
    var width = +vb[2] - margin.left - margin.right;
    var height = +vb[3] - margin.top - margin.bottom;
    var g = svg.select("g");

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

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

    var line = d3.line()
        .curve(d3.curveMonotoneX)
        .x(function(d) { return x(d.Logged); })
        .y(function(d) { return y(d.Value); });

    var area = d3.area()
        .curve(d3.curveMonotoneX)
        .x(function(d) { return x(d.Logged); })
        .y1(function(d) { return y(d.Value); });

    d3.tsv("history.py?LogType=" + table + "&" + params, function(d) {
        d.Logged = parseTime(d.Logged);
        d.Value = +d.Value;
        return d;
    }, function(error, data) {
        if (error) throw error;

        x.domain(d3.extent(data, function(d) { return d.Logged; }));
        var yS = y.domain(d3.extent(data, function(d) { return d.Value; })).nice();

        area.y0(y(yS.domain()[0]));

        g.selectAll("path").remove();
        g.selectAll("g").remove();
        g.selectAll("text").remove();

        g.append("g")
            .attr("class", "x axis")
            .attr("transform", "translate(0," + height + ")")
            .call(d3.axisBottom(x).tickSize(-height))
            .select(".domain");

        g.append("g")
            .attr("class", "y axis")
            .call(d3.axisLeft(y).tickSize(-width))
            .append("text")
            .attr("fill", "#000")
            .attr("transform", "rotate(-90)")
            .attr("y", 6)
            .attr("x", -4)
            .attr("dy", "0.71em")
            .attr("text-anchor", "em")
            .text(title);

        g.select(".y")
            .select(".domain")
            .attr("transform", "translate(-1,0)");

        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);

        g.append("path")
            .datum(data)
            .attr("fill", "steelblue")
            .attr("fill-opacity", "0.25")
            .attr("stroke", "none")
            .attr("d", area);

        g.append("text")
            .attr("fill", "#000")
            .attr("y", "4")
            .attr("x", width - 4)
            .attr("dy", "0.71em")
            .attr("font-size", "11")
            .attr("text-anchor", "end")
            .text(parseFloat(Math.round(data[data.length - 1].Value * 100) / 100).toFixed(2) + unit);
        cb();
    });
}

This is the D3 bit, it basically build an individual graph from the provided parameters.

The last two python files are for the history (which was in the blue field in my screenshot) and for the latest, which gathers the data-points for D3:

#!/usr/bin/python
from datetime import datetime, timedelta

import cgi
import pymysql

DATE_FORMAT = "%Y-%m-%d %H:%M:%S"
DEFAULT_DAY_LIMIT = 7
DEFAULT_MINUTE_GROUPING = 3

if __name__ == '__main__':
    print "Content-type: text/html"
    print

    args = cgi.FieldStorage()
    day_limit = DEFAULT_DAY_LIMIT

    if "Days" in args:
        day_limit = float(args["Days"].value)

    start_date = (datetime.utcnow() - timedelta(days = day_limit)).strftime(DATE_FORMAT)
    end_date = datetime.utcnow().strftime(DATE_FORMAT)
    minute_group = DEFAULT_MINUTE_GROUPING

    if "MinuteGroup" in args:
        minute_group = float(args["MinuteGroup"].value)

    table = "TemperatureLog"

    formatter = lambda c: c * 1.8 + 32

    if "LogType" in args:
        log_type = args["LogType"].value
        if log_type == "Humidity":
            table = "HumidityLog"
            formatter = lambda x: x
        elif log_type == "BmpTemp":
            table = "BmpTempLog"
            formatter = lambda c: c * 1.8 + 32
        elif log_type == "Pressure":
            table = "PressureLog"
            formatter = lambda p: p * 0.0002953
        elif log_type == "Altitude":
            table = "AltitudeLog"
            formatter = lambda m: m * 3.28084
        elif log_type == "SeaPressure":
            table = "SeaPressureLog"
            formatter = lambda p: p * 0.0002953

    if "Start" in args:
        start_date = args["Start"].value
    if "End" in args:
        end_date = args["End"].value

    conn = pymysql.connect(
        db='WhateverNameYouReallyLike',
        user='SomeUsername',
        passwd='SomePassword',
        host='localhost')
    cursor = conn.cursor()

    cursor.execute("""
        SELECT
            Logged, AVG(Value)
        FROM
            {0}
        WHERE
            Logged >= '{1}' AND Logged <= '{2}'
        GROUP BY
            ROUND(UNIX_TIMESTAMP(Logged)/({3} * 60));""".format(table, start_date, end_date, minute_group))

    didfirst = 0
    print "Logged\tValue"
    for row in cursor.fetchall():
        didfirst = 1
        print "{0}\t{1}".format(row[0], formatter(row[1]))

We're going to call this file history.py. The last python file is latest.py:

#!/usr/bin/python
from datetime import datetime, timedelta

import cgi
import pymysql

DATE_FORMAT = "%Y-%m-%d %H:%M:%S"
DEFAULT_DAY_LIMIT = 7
DEFAULT_MINUTE_GROUPING = 1

if __name__ == '__main__':
    print "Content-type: text/html"
    print

    args = cgi.FieldStorage()
    start_date = (datetime.utcnow() - timedelta(days = DEFAULT_DAY_LIMIT)).strftime(DATE_FORMAT)
    end_date = datetime.utcnow().strftime(DATE_FORMAT)
    minute_group = DEFAULT_MINUTE_GROUPING
    gather = []

    gather.append(("TemperatureLog", lambda c: c * 1.8 + 32))

    if "Start" in args:
        start_date = args["Start"].value
    if "End" in args:
        end_date = args["End"].value

    conn = pymysql.connect(
        db='WhateverNameYouReallyLike',
        user='SomeUsername',
        passwd='SomePassword',
        host='localhost')
    cursor = conn.cursor()

    for table, formatter in gather:
        cursor.execute("""
            SELECT
                Logged, AVG(Value)
            FROM
                {0}
            WHERE
                Logged >= '{1}' AND Logged <= '{2}'
            GROUP BY
                ROUND(UNIX_TIMESTAMP(Logged)/({3} * 60))
            ORDER BY
                Logged DESC
            LIMIT
                1;""".format(table, start_date, end_date, minute_group))

        for row in cursor.fetchall():
            print "{2}\t{0}\t{1}".format(row[0], formatter(row[1]), table[:-3])

So now there's only one file left, which is our index.html. I guess let's lay it out:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
        <link href="style.css" rel="stylesheet" />
        <script src='d3/d3.min.js'></script>
        <script src='graphs.js'></script>
</head>
<body>
    <div class="header">
        Last refresh: <span class="lastRefresh"></span>;
                    Pi time: <span class="piTime"></span>;
        Next sensor pull: <span class="nextRefresh"></span> (<span class="nextRefreshSeconds"></span>)
        <div class="right">
            Graph Timeline:
            <select id="days">
                <option value="0.041666667">1 hour</option>
                <option value="0.125">3 hours</option>
                <option value="0.25">6 hours</option>
                <option value="0.5">12 hours</option>
                <option value="1">1 day</option>
                <option value="2">2 days</option>
                <option value="3">3 days</option>
                <option value="4">4 days</option>
                <option value="5">5 days</option>
                <option value="6">6 days</option>
                <option value="7" selected>1 week</option>
                <option value="14">2 weeks</option>
                <option value="28">4 weeks</option>
                <option value="30">1 month</option>
                <option value="60">2 months</option>
                <option value="90">3 months</option>
            </select>
        </div>
    </div>
    <div class="current">
        Current readings (<span id="logValue"></span>):
        <span class="">
            Temp 1:
            <span id="temperatureValue"></span>F
        </span>
        <div class="right">
            Ticks:
            <select id="minuteGroup">
                <option value="0.083333">5 seconds</option>
                <option value="0.166667">10 seconds</option>
                <option value="0.25">15 seconds</option>
                <option value="0.5">30 seconds</option>
                <option value="1" selected>1 minute</option>
                <option value="2">2 minutes</option>
                <option value="3">3 minutes</option>
                <option value="5">5 minutes</option>
                <option value="10">10 minutes</option>
                <option value="15">15 minutes</option>
                <option value="30">30 minutes</option>
                <option value="60">1 hour</option>
                <option value="120">2 hours</option>
                <option value="180">3 hours</option>
                <option value="240">4 hours</option>
                <option value="360">6 hours</option>
                <option value="720">12 hours</option>
                <option value="1440">1 day</option>
            </select>
        </div>
        <br />
    </div>
    <div class='graphBlock split'>
        <svg class='temp' viewBox='0 0 1 1'></svg>
    </div>
    <script src='jquery.1.12.4.min.js'></script>

    <script>
        var loading = 0;

        Number.prototype.padLeft = function(base, chr) {
        var len = (String(base || 10).length - String(this).length) + 1;
            return len > 0 ? new Array(len).join(chr || '0') + this : this;
        }

        function round(value, decimals) {
            return Number(Math.round(value + 'e' + decimals) + 'e-' + decimals);
        }

        function graphDone() {
            loading -= 1;
        }

        function updateGraphs(callback) {
            if (loading > 0) { return; }

            clearInterval(graphInt);
            if (callback == null) {
                callback = updateGraph;
            }

            var days = "Days=" + $("#days").val() + "&MinuteGroup=" + $("#minuteGroup").val();
            loading = 1;

            var subHeight = $(".header").outerHeight() + $(".current").outerHeight();
            var vb = "0 0 ";

            if ($(".graphBlock").hasClass("split")) {
                vb += $(".graphBlock").outerWidth() / 2;
                vb += " ";
                vb += ($(".graphBlock").outerHeight() - subHeight) / 3;
            } else {
                vb += $(".graphBlock").outerWidth() / 1;
                vb += " ";
                vb += ($(".graphBlock").outerHeight() - subHeight) / 6;
            }

            $('svg').each(function () { $(this)[0].setAttribute('viewBox', vb); });
            callback('.temp', 'Temperature', 'Temp (F)', "ºF", days, graphDone);

            var d = new Date;
            var dFormat = [d.getUTCFullYear(), (d.getUTCMonth() + 1).padLeft(), d.getUTCDate().padLeft()].join('-');
            dFormat += " ";
            dFormat += [d.getUTCHours().padLeft(), d.getUTCMinutes().padLeft(), d.getUTCSeconds().padLeft()].join(':');
            $('.lastRefresh').html(dFormat);
            graphInt = setInterval(updateGraphs, 5000);
        }

        function updateTimes() {
            clearInterval(timeInt);
            var xmlHttp = new XMLHttpRequest();
            xmlHttp.onreadystatechange = function() {
                if (xmlHttp.readyState == 4 && xmlHttp.status == 200) {
                    var text = xmlHttp.responseText.trim().split("\n");
                    $(".nextRefresh").html(text[0]);
                    $(".nextRefreshSeconds").html(text[1]);
                    $(".piTime").html(text[2]);
                    timeInt = setInterval(updateTimes, 500);
                }
            }
            xmlHttp.open("GET", "time.py?All=true", true);
            xmlHttp.send(null);
        }

        function updateCurrents() {
            clearInterval(currentInt);
            var xmlHttp = new XMLHttpRequest();
            xmlHttp.onreadystatechange = function() {
                if (xmlHttp.readyState == 4 && xmlHttp.status == 200) {
                    var text = xmlHttp.responseText.trim().split("\n");
                    text.forEach(function(line) {
                        var items = line.split("\t");
                        switch (items[0]) {
                            case "Temperature":
                                $("#temperatureValue").html(round(items[2], 2));
                                break;
                        }
                        $("#logValue").html(items[1]);
                    });
                    currentInt = setInterval(updateCurrents, 1000);
                }
            }
            xmlHttp.open("GET", "latest.py", true);
            xmlHttp.send(null);
        }

        var graphInt = setInterval(updateGraphs, 5000);
        var timeInt = setInterval(updateTimes, 500);
        var currentInt = setInterval(updateCurrents, 1000);
        updateGraphs(buildGraph);
        updateTimes();
        updateCurrents();
    </script>
</body>
</html>

Yeah it's long, yeah it's ugly, but it works. :)

Alright, so if you put all this together you should have a graphing utility, that automatically refreshes (the setInterval lines do that, the 5000, 1000 and 500 are milliseconds), and the cron-job will keep data moving in.

The graphs update every 5 seconds or so, so you should be able to change the "Ticks" or "Graph Timeline" setting and see a very quick reaction. You will also see that all of this is setup for adding new tables (with the same structure) for new graphs / sensors.

Loading