Temperature Graph Update

Today I added weekly, monthly and yearly graphs, as well as breaking the cron job into 3 different ones. One that runes every minute. An hourly and a monthly one.

I also learned that if you have a large dataset and you are using rrdtool to pull data from MySQL it can take a long time. This is completely understandable as rrdtool is optimized to use rrd (round robin data) files.

The last thing is that I had a few days where the temperature sensor could not reach the main server to insert data into the DB. This pointed out the need to cache the data locally then attempt to upload it, allowing for connection outages. This will be my next upgrade to the system.

Temperature

I added another page today, or I should say the beginnings of one.

I have a home temperature monitoring project and this page is the beginnings of the documentation for it. In the future I hope to have more then just the temperature graph. Plans include schematics, pictures and code. Stay tuned for… something…

Temperature… The beginnings…

Pieces used:

  • Raspberry Pi.
  • Dallas 18B20 temperature sensor.
  • Prototyping board and random bits of wire.
  • Remote Debian server with a LAMP style setup

    The basic process flow:

    A cron job on the PI collects the current temperature from the 18B20 chip attached to the GPIO. It then inserts the temperature into a remote MySQL database on the big server. A cron job on the big server creates a PNG from the data using rrdtool. There is a link in the web directory to the PNG, which you see above.


    Here is the table creation commands.


    CREATE DATABASE temp_monitor;

    USE temp_monitor;

    CREATE TABLE `temp_data` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `unixtimestamp` int(11) NOT NULL,
    `temp_f` float NOT NULL,
    `temp_c` float NOT NULL,
    `sensor` varchar(25) NOT NULL,
    `adjusted` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
    );


    Here is the cron job on the rPI that reads the temp and sends it to the big server in the sky.

    #!/usr/bin/python

    import time;
    import httplib

    # Exampe temperature file output:
    # —————————————–
    # 77 01 4b 46 7f ff 09 10 6f : crc=6f YES
    # 77 01 4b 46 7f ff 09 10 6f t=23437
    # Config options:
    # Temperature “file”
    pin=0
    device=”28-00000441aeff”
    tempfile = “/sys/bus/w1/devices/”+device+”/w1_slave”
    outputfile = “/tmp/newtemps”

    # Get the temperature
    tfile = open(tempfile)
    text = tfile.read()
    tfile.close()

    # Split the text with new lines (\n)
    # and select the second line.
    secondline = text.split(“\n”)[1]

    # Split the line into words, referring to the spaces,
    # and select the 10th word (counting from 0).
    temperaturedata = secondline.split(” “)[9]

    # The first two characters are “t=”, so get rid of those
    # and convert the temperature from a string to a number.
    temperature = float(temperaturedata[2:])

    # Put the decimal point in the right place and display it.
    temperature = temperature / 1000

    # Convert to fahrenheit
    ctemp = temperature;
    temperature = 32 + ( temperature * 9 ) / 5

    # Get time
    ut = time.time()
    localtime = time.localtime(time.time())

    #Output
    outline = ‘{} {} {} {} {} {} {}\n’ . format(localtime[0], localtime[1], localtime[2], localtime[3], localtime[4], localtime[5], temperature)

    ofile = open(outputfile, “a”)
    ofile.write( outline )
    ofile.close()

    # send the temp to the server
    conn = httplib.HTTPConnection( “database.myserver.org”)
    conn.request(“GET”, “/sensor_input.php?c=” + str(ctemp) + “&s=28-00000441c8b3&t=” + str(ut))
    r1 = conn.getresponse()


    Here is the PHP code that accepts the data from the PI and stuffs it into the DB.


    // inputs:
    // Required:
    // c = temp in celcius
    // s = id of sensor providing the data
    //
    // Optional:
    // t = unix time stamp. If this does not exist use NOW()
    //

    function error_out() {
    print “Something is missing.&th;br>&th;pre>\n”;
    print_r($_REQUEST);
    exit();

    }

    function clean($in) {
    return preg_replace(“/[^A-Za-z0-9\.]/”, ”, $in);
    }

    if( isset( $_REQUEST[‘c’] ) ) {
    $c = clean( $_REQUEST[‘c’] );
    $f = ( ( $c * 9 ) / 5 ) + 32;
    } else
    error_out();

    if( isset( $_REQUEST[‘s’] ) )
    $s = clean( $_REQUEST[‘s’] );
    else
    error_out();

    if( isset( $_REQUEST[‘t’] ) )
    $t = clean( $_REQUEST[‘t’] );
    else
    $t = ‘NOW()’;

    // For some reason we periodically get two known bogus numbers
    // 185’f and 32.1116’f. So we are going to toss them out
    // even if on the off chance they were valid.

    if( $f == 185 or $f == 32.1116 )
    exit;

    $query = “INSERT INTO temp_data ( unixtimestamp, temp_c, temp_f, sensor ) ” .
    ” VALUES ( $t, $c, $f, ‘$s’ )”;

    $link = mysql_connect(‘localhost’, ‘fakeUser’, ‘fakePassword’)
    or die(‘Could not connect: ‘ . mysql_error());

    mysql_select_db(‘temp_monitor’) or die(‘Could not select database’);

    // Performing SQL query

    $result = mysql_query($query) or die(‘Query failed: ‘ . mysql_error());


    And here are the cron jobs on the big server that creates the image.


    Runs every minute.

    #!/bin/bash

    cd /var/www

    F_BASE=”sql//mysql/host=127.0.0.1/dbname=temp_monitor/username=fakeUser/password=fakePassword//temp_data/unixtimestamp/temp_f/sensor=’2800000441c8b3′”

    C_BASE=”sql//mysql/host=127.0.0.1/dbname=temp_monitor/username=fakeUser/password=fakePassword//temp_data/unixtimestamp/temp_f/sensor=’28dfca4140090′”

    rrdtool graph daily.png –imgformat=PNG –start=-1day –end=now –width=400 –height=200 \
    “DEF:temp_f=$F_BASE:min:AVERAGE” \
    “LINE1:temp_f#FF0000:Office” \
    “GPRINT:temp_f:MIN:Min\: %6.2lf” \
    “GPRINT:temp_f:AVERAGE:Avg\: %6.2lf” \
    “GPRINT:temp_f:MAX:Max\: %6.2lf” \
    “GPRINT:temp_f:LAST:Last\: %6.2lf” \
    “DEF:temp_c=$C_BASE:min:AVERAGE” \
    “LINE1:temp_c#00FF00:Remote” \
    “GPRINT:temp_c:MIN:Min\: %6.2lf” \
    “GPRINT:temp_c:AVERAGE:Avg\: %6.2lf” \
    “GPRINT:temp_c:MAX:Max\: %6.2lf” \
    “GPRINT:temp_c:LAST:Last\: %6.2lf”

    rrdtool graph weekly.png –imgformat=PNG –start=-1day –end=now –width=400 –height=200 \
    “DEF:temp_f=$F_BASE:min:AVERAGE” \
    “LINE1:temp_f#FF0000:Office” \
    “GPRINT:temp_f:MIN:Min\: %6.2lf” \
    “GPRINT:temp_f:AVERAGE:Avg\: %6.2lf” \
    “GPRINT:temp_f:MAX:Max\: %6.2lf” \
    “GPRINT:temp_f:LAST:Last\: %6.2lf” \
    “DEF:temp_c=$C_BASE:min:AVERAGE” \
    “LINE1:temp_c#00FF00:Remote” \
    “GPRINT:temp_c:MIN:Min\: %6.2lf” \
    “GPRINT:temp_c:AVERAGE:Avg\: %6.2lf” \
    “GPRINT:temp_c:MAX:Max\: %6.2lf” \
    “GPRINT:temp_c:LAST:Last\: %6.2lf”


    Runs every hour.

    #!/bin/bash

    cd /var/www

    F_BASE=”sql//mysql/host=127.0.0.1/dbname=temp_monitor/username=fakeUser/password=fakePassword//temp_data/unixtimestamp/temp_f/sensor=’2800000441c8b3′”

    C_BASE=”sql//mysql/host=127.0.0.1/dbname=temp_monitor/username=fakeUser/password=fakePassword//temp_data/unixtimestamp/temp_f/sensor=’28dfca4140090′”

    rrdtool graph monthly.png –imgformat=PNG –start=-1day –end=now –width=400 –height=200 \
    “DEF:temp_f=$F_BASE:min:AVERAGE” \
    “LINE1:temp_f#FF0000:Office” \
    “GPRINT:temp_f:MIN:Min\: %6.2lf” \
    “GPRINT:temp_f:AVERAGE:Avg\: %6.2lf” \
    “GPRINT:temp_f:MAX:Max\: %6.2lf” \
    “GPRINT:temp_f:LAST:Last\: %6.2lf” \
    “DEF:temp_c=$C_BASE:min:AVERAGE” \
    “LINE1:temp_c#00FF00:Remote” \
    “GPRINT:temp_c:MIN:Min\: %6.2lf” \
    “GPRINT:temp_c:AVERAGE:Avg\: %6.2lf” \
    “GPRINT:temp_c:MAX:Max\: %6.2lf” \
    “GPRINT:temp_c:LAST:Last\: %6.2lf”


    Runs every day.

    #!/bin/bash

    cd /var/www

    F_BASE=”sql//mysql/host=127.0.0.1/dbname=temp_monitor/username=fakeUser/password=fakePassword//temp_data/unixtimestamp/temp_f/sensor=’2800000441c8b3′”

    C_BASE=”sql//mysql/host=127.0.0.1/dbname=temp_monitor/username=fakeUser/password=fakePassword//temp_data/unixtimestamp/temp_f/sensor=’28dfca4140090′”

    rrdtool graph yearly.png –imgformat=PNG –start=-1day –end=now –width=400 –height=200 \
    “DEF:temp_f=$F_BASE:min:AVERAGE” \
    “LINE1:temp_f#FF0000:Office” \
    “GPRINT:temp_f:MIN:Min\: %6.2lf” \
    “GPRINT:temp_f:AVERAGE:Avg\: %6.2lf” \
    “GPRINT:temp_f:MAX:Max\: %6.2lf” \
    “GPRINT:temp_f:LAST:Last\: %6.2lf” \
    “DEF:temp_c=$C_BASE:min:AVERAGE” \
    “LINE1:temp_c#00FF00:Remote” \
    “GPRINT:temp_c:MIN:Min\: %6.2lf” \
    “GPRINT:temp_c:AVERAGE:Avg\: %6.2lf” \
    “GPRINT:temp_c:MAX:Max\: %6.2lf” \
    “GPRINT:temp_c:LAST:Last\: %6.2lf”


Link Collection

So I’ve started adding links to the link collection. Yep, you heard that right. You see one of the main reasons for this site is to consolidate my ever growing bookmark list spread across multiple browsers on multiple devices. It has been growing for many years, and being the pack rat that I am I bookmark everything that might be useful. Now all I have to do is remember to clean out my bookmarks on a regular basis.

Anyone know of a firefox, chrome, safari, konquerer plugin that will do that?

FYI: If you are setting up a new wordpress installation and want a links/blogroll section you will need to install the WordPress Link Manager plugin.

Many thanks to Lorelle at Lorelle on WordPress for posting this.