Using gnuPlot to Graph House Temperatures
I have been playing around with building and logging house temperatures for a while now. Previously I had manually created Excel based graphs, but I knew I wanted to generate some gnuPlot graphs and to possibly have a go at sending the data out onto the internet for graphing as well. I have used gnuPlot previously to plot other data and found it extremely fast, generating 1600 pixel wide graphs of 160K points of data in way under a second. Finally I have got around to writing some gnuPlot scripts to graph my house temperatures:
Inside:
Outside:
The Python script extracts the temperature data from the networked MySQL DB, dumps it into a text file, then calls a gnuPlot script to build the png based graphs. The MySQL DB has a view that acts as a temporary table of the last 24 hours worth of temperature readings. This table is used to extract the min/max temperatures for the period.
Here is the Python script:
#!/usr/local/bin/python
#
# this will extract the relevant temperature data from the DB and plot the data via gnuPlot into a png graph# This has been updated to suit the new MySQL table that hosts one record for all the sensors
#
# Parkview 2013-04-05
#import os
import time
import MySQLdb as mdb
import sys
import subprocess
import datetime, time
import ephem # install from pyephem`Data_Limit=360
MYSQL_HOST = '<mysql_svr_ip>'
MYSQL_DB = "temperature"
MYSQL_USER = "<mysql_user>"
MYSQL_PASSWD = "<mysql_pswd>"
MYSQL_TBL_SENSOR = "sensors_tbl"
MYSQL_TBL_TEMP = "temp_tb"
file="/tmp/gnuplot-data.dat"
max_file="/tmp/gnuplot-data-max.dat"
min_file="/tmp/gnuplot-data-min.dat"
srise_file="/tmp/gnuplot-data-srise.dat"
sset_file="/tmp/gnuplot-data-sset.dat"# lets work out the previous Sun Set and Sun Rise:
o = ephem.Observer()
o.lat, o.long, o.date = '-33:65', '115:39', datetime.datetime.utcnow()
sun = ephem.Sun(o)
SRISE=ephem.localtime(o.previous_rising(sun))
SSET=ephem.localtime(o.previous_setting(sun))# First need to query the DB for a list of sensor names and compare with what we actually have on the network
try:
con = mdb.connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWD, MYSQL_DB);
cur = con.cursor()
# go get around days worth of temperature records
cur.execute("""SELECT datetime,Sensor1,Sensor2,Sensor3,Sensor4,Sensor5,Sensor6,Sensor7,Sensor8,Sensor9,Sensor10,11,Sensor12,Sensor13,Sensor14,Sensor15,Sensor16,Sensor17,Sensor18,Sensor19,Sensor20,Sensor21,Sensor22,Sensor23 FROM temp_5min_tbl order by datetime desc limit %s""", Data_Limit)
f=open(file, 'w')
for row in cur.fetchall():
# now lets add this into a list variable for use
DATA1=row[0].strftime('%Y-%m-%d %H:%M:%S'),str(row[8]),str(row[9]),str(row[10]),str(row[12]),str(row[16]),str(row[17])
DATA=','.join(DATA1)+'\n'
f.write(DATA)
print DATA # could add a strip RH to clear the line feed
print " "
f.close()
# now lets get the min data so it can be plotted
cur.execute("""SELECT datetime,temp FROM temperature.newlastDayTemperature order by temp asc limit 1;""")
f=open(min_file, 'w')
for row in cur.fetchall():
# now lets add this into a list variable for use
DATA1=row[0].strftime('%Y-%m-%d %H:%M:%S'),str(row[1])
DATA=','.join(DATA1)+'\n'
f.write(DATA)
# now lets get the max data so it can be plotted
cur.execute("""SELECT datetime,temp FROM temperature.newlastDayTemperature order by temp desc limit 1;""")
f=open(max_file, 'w')
for row in cur.fetchall():
# now lets add this into a list variable for use
DATA1=row[0].strftime('%Y-%m-%d %H:%M:%S'),str(row[1])
DATA=','.join(DATA1)+'\n'
f.write(DATA)
print DATA # could add a strip RH to clear the line feed
print " "
# now lets get the Sun Rise data so it can be plotted
cur.execute("""SELECT datetime,Sensor17 FROM temperature.temp_5min_tbl where datetime > %s order by datetime asc limit 1;""", SRISE)
f=open(srise_file, 'w')
for row in cur.fetchall():
# now lets add this into a list variable for use
DATA1=row[0].strftime('%Y-%m-%d %H:%M:%S'),str(row[1])
DATA=','.join(DATA1)+'\n'
f.write(DATA)
print DATA # could add a strip RH to clear the line feed
print " "
# now lets get the Sun Set data so it can be plotted
cur.execute("""SELECT datetime,temp FROM temperature.newlastDayTemperature where datetime > %s order by datetime asc limit 1;""", SSET)
f=open(sset_file, 'w')
for row in cur.fetchall():
# now lets add this into a list variable for use
DATA1=row[0].strftime('%Y-%m-%d %H:%M:%S'),str(row[1])
DATA=','.join(DATA1)+'\n'
f.write(DATA)
print DATA # could add a strip RH to clear the line feed
print " "
# print "Data count: ", cur.count
f.close()
except mdb.Error, e:
print "Error %d: %s" % (e.args[0],e.args[1])
sys.exit(1)
finally:
if con:
con.close()# call the various gnuPlot scripts from here:
try:
subprocess.Popen('/usr/local/bin/RPi-Temperature/new_gnuplot_temperature_inside.sh',shell = True)
except OSError, e:
print 'OS Error: '
except subprocess.CalledProcessError, e:
print cmd_output," - "
try:
subprocess.Popen('/usr/local/bin/RPi-Temperature/new_gnuplot_temperature_outside.sh',shell = True)
except OSError, e:
print 'OS Error: '
except subprocess.CalledProcessError, e:
print cmd_output," - "#print " Sun Rise: ", SRISE
#print " Sun Set: ", SSET
and here is the Outside gnuPlot script:
#!/bin/sh
# this is a GNUPLOT script to take the website download data and create a
# graphical overview png file
#
# Parkview August 2009# Local Variables
BASEDIR="/usr/local/apache2/htdocs/"
DATAFILE_IN="/tmp/gnuplot-data.dat"
MINDATAFILE_IN="/tmp/gnuplot-data-min.dat"
MAXDATAFILE_IN="/tmp/gnuplot-data-max.dat"
SRISEDATAFILE_IN="/tmp/gnuplot-data-srise.dat"
SSETDATAFILE_IN="/tmp/gnuplot-data-sset.dat"
PNG="$BASEDIR/house_temperature-outside.png"
MINY=13
MAXY=41
MYTICS=2
COLOR1="#CD2222"
COLOR2="#CD9F22"
COLOR3="#2225CD"
COLOR5="#f24D97"
COLOR4="#000066"
COLOR6="#22fDd7"
COLOR7="#22fD57"
COLOR8="#225D57"
COLOR9="#72aDa7"
COLOR10="#000000"
COLOR11="#666666"# extract the required data from the csv file
EDATE=`tail -1 $DATAFILE_IN | cut -d"," -f1`
SDATE=`head -2 $DATAFILE_IN | tail -1 | cut -d"," -f1`# Now go and plot the graph
/usr/local/bin/gnuplot << EOF
set datafile separator ","
set style fill solid border -1
set style line 1 lt 2 lw 1
set pointsize .5
set sample 45
set terminal png size 1024,400
set terminal png
set output "$PNG"
set xlabel "Date - Time"
set ylabel "Degree C"
set title "House Temperatures - Outside"
set xdata time
set timefmt "%Y-%m-%d %H:%M:%S"
set format x "%d %h\n%H:%M"
#set xrange [ "$SDATE":"$EDATE" ]
set xrange [ "$EDATE":"$SDATE" ]
set timefmt "%Y-%m-%d %H:%M:%S"
set yrange [ * : * ]
#set yrange [ $MINY : $MAXY ]
set xtics border out scale 2
set ytics out mirror $MYTICS
set mxtics 4
set mytics 2
set grid x y
plot "$DATAFILE_IN" using 1:7 title "Outside-average" smooth bezier, "" using 1:7 title "Outside" with lines lc rgb "$COLOR3", "" using 1:6 title "Attic" with lines lc rgb "$COLOR8","" using 1:3 title "Attic Switch" with lines lc rgb "$COLOR9", "$MAXDATAFILE_IN" using 1:2 title "Max Temp" with points pointtype 6 pointsize 2 lc rgb "$COLOR1", "$MINDATAFILE_IN" using 1:2 title "Min Temp" with points pointtype 6 pointsize 2 lc rgb "$COLOR4", "$SRISEDATAFILE_IN" using 1:2 title "Sunrise" with points pointtype 24 pointsize 1 lc rgb "$COLOR11", "$SSETDATAFILE_IN" using 1:2 title "Sunset" with points pointtype 24 pointsize 1 lc rgb "$COLOR10"
EOF
Just for clarification, here are some sample data for the above data files...
cat /tmp/gnuplot-data.dat
2014-02-23 07:50:08,27.312,25.625,28.312,25.062,23.875,21.375,1015.36
2014-02-23 07:45:08,27.25,25.187,28.375,24.937,23.312,21.125,1015.42
2014-02-23 07:40:08,27.062,24.687,28.375,24.937,22.812,20.875,1015.39
2014-02-23 07:35:07,26.937,24.187,28.437,25.0,22.25,20.562,1015.37
2014-02-23 07:30:07,26.75,23.625,28.5,25.0,21.5,20.187,1015.42
cat /tmp/gnuplot-data-max.dat
2014-02-22 15:20:07,33.812
cat /tmp/gnuplot-data-min.dat
2014-02-23 06:15:08,16.0
cat /tmp/gnuplot-data-srise.dat
2014-02-23 06:00:07,16.125
cat /tmp/gnuplot-data-sset.dat
2014-02-22 19:05:08,27.0
Note: these scripts are run on a FreeBSD based server, so will need to be adapted for your OS. They are being posted as an example for other people to use.