Graphing Wonders

Tonight I’ve been building on my earlier work on graphing, if you read a few blogs back you’ll know I got some basic graphing running on the Raspberry Pi 2, taking MQTT data from a sensor and storing it in MYSQL.

Well that was fine apart from the graphs having no anti-aliasing and hence looking a bit naff. The big problem was I could not get my head around how to handle missing items- if you have a sensor sending MQTT messages on a regular basis, well, you might have any number of reasons why some might be missed. If relying on waking up for a solar installation or just you’ve been in there and been messing with the unit or upgrading it – you could end up instead of a nice set of regular readings, some chunks missing!

Well, as of this evening the whole graphics library is in the bin, saving me some room in the PI. I discovered – which for all except proper commercial use can be had for free. They use JQUERY and 2 highcharts libraries all of which are on CDNs and hence you only need 3 links to access them.

I suggest you go look at DEMOS, BASIC LINE, TIME DATA WITH IRREGULAR INTERVALS. It’s wonderful. ONE PHP/Javascript page does this.

Not only do you get a relatively simple page but it’s HTML5, the imagery is really nice etc. AND you’re looking at filling in with data including the time-date and your reading…. either one graph – or many lines superimposed and if the latter, colouring the different lines and matching them up is all done automatically, you don’t have to lift a finger. Took me less than an hour to take the Javascript code they supply, bang some PHP in there to populate the table with data from mysql and produce nice graphs on demand all done inside the Raspberry Pi. Pretty much all modern browsers will support the output…including the naff browser that comes with the Pi itself.

Here’s a sample – remembering that the data from the two lines were at completely different intervals and one had more samples than the other! All scaling, smoothing etc. is done completely automatically.


And if you don’t like the idea of relying on external libraries you can of course download the lot onto your Pi. Personally, I look at it this way, if the Internet isn’t working, I can’t access the graphs anyway! Hopefully now I’m using my un-interruptible power supply idea that will happen less and less (the bottom of the range Juicebar was involuntarily tested this evening – I forgot to plug it in – it powered the Pi for nearly 2 hours).

I ended up bringing everything into a PHP function with 2 arguments – the title of the line – and the SQL statement needed to get the results. I store all of my temperature readings with 3 fields – the value, a timedate stamp and the location – so I can easily select for example all front room readings from X date to Y  date. The titles and data above are just dummy data I entered into the database. I have 5 months worth of internal, external temperature and internal humidity data available to import from Grovestreams as soon as I figure out how to do it.

If you look at their examples here you’ll see the source code is simply not that hard and dropping in some PHP to replace the static figures takes no time. This has saved me so much time and effort.

After some messing about, automating commas in the right place and struggling with the ridiculous fact that PHP doesn’t by default recognise global variables in a function (EH!!!?!!?!?!) I finally reduced the effort to adding lines to the graph to this..

drawgraph("Autumn","select value,UNIX_TIMESTAMP(logged) as logging from readings where location='office'");
drawgraph("Spring","select value,UNIX_TIMESTAMP(logged) as logging from readings where location='kitchen'");

That’s it… as many lines as needed.  I’m sure once you look at this if it’s of use you’ll come up with your own scheme using your own preferred language.

I really do need to back up this Pi. There has to be a better way than turning it off for several hours to copy the SD…


23 thoughts on “Graphing Wonders

    1. Well, the problem I have with that great idea - and it is a good idea... is that Mosquitto on my Pi is not web-socket enabled and I haven't the foggiest how to change that?

      1. I think that only Mosquitto version 1.4 has websocket support. If using version 1.3 we are out of luck.

        Anyway if using node-red it's trivial to make a bridge between MQTT and WS protocol.

          1. In my case, my node-red instance has an web-socket output node.
            So just need to connect an instance of MQTT input node with an instance of a websocket output node.

            I'm running the plain vanilla version that I downloaded from node-red site, and have no specific nodejs websocket module installed, so I suppose that websocket support is out of the box on node-red. Might not be true for RPi.

            For an example, see this video:
            It is, I think, pretty simple. Watching this video is worth every minute.

          1. Only version 1.4, as far as I'm aware has websockets enabled. I did try to use version 1.3.5 with, that has websockets client by default, and it doesn't work because of no WS support on version 1.3.5. So I ended up using Node-Red also for bridging MQTT into WebSockets, so that could see my data.

            But this solution means that I have to run two different things, Mosquitto (in reality I'm using RSBM) and Node-Red.

            But Node-red is Node-js based and also there is an MQTT broker for Node-js with out of the box websocket support ! It's called Mosca: An example:

            Basically one ring (cof cof) server to rule them all.

            Probably still there isn't a version 1.4 of mosquitto available for the RPi with websockets enabled (it needs to be enabled at compile time), hence the problems...

          2. A simple sample. Sorry I'm not at home, and I was unable to test it, but it is more or less as this: (select the below code, and paste it on the Node-Red Import menu option)

            "port":"1883","clientid":""},{"id":"e686d7cd.578d18","type":"mqtt in","name":"Temperature","topic":"/data/s_temp","broker":"dd3f60a0.99d9e8",

            The Websocket interface will be available at the address http://nodered:1880/ws/data/s_temp , but this url must be consumed by a websocket client. A browser won't work.

            Just use this page, and access it. Change if needed the URL. It will need a HTML5 based browser. It works fine on FireFox.

            function WebSocketTest()
            if ("WebSocket" in window)
            alert("WebSocket is supported by your Browser!");
            // Let us open a web socket
            var ws = new WebSocket("ws://localhost:1880/ws/data/s_temp");
            ws.onopen = function()
            // Web Socket is connected, send data using send()
            ws.send("Message to send");
            alert("Message is sent...");
            ws.onmessage = function (evt)
            var received_msg =;
            var msg = "Message received: ";
            ws.onclose = function()
            // websocket is closed.
            alert("Connection is closed...");
            // The browser doesn't support WebSocket
            alert("WebSocket NOT supported by your Browser!");

            Run WebSocket

  1. Bitbucket has free private storage. Create a repository for you script path and add the following line to cron.

    cd /var/www/;/usr/local/bin/git commit -a -m "Autocommit from cron..";/usr/local/bin/git push -u origin

    If you would like to store the database as well, use mysqldump and pipe it to gzip, then store the file with all others in the www dir.

  2. Ok, so I have a websockets question.

    I have the socket input working - and from a web page I can, say press a button. This will send a message to the websocket input... and I can filter that if I want - and send to the websocket output - and the relevant callback in the browser page will respond.

    But what would be good - would be if something changes in Node-red - i.e. a status, I could send that out to the page. That doesn't work presumably because the websocket output connection no longer knows who to send it to,. So my question is... what information from the incoming message do I need to keep so that at any arbitrary time I can fire something back at that open web page.. OR do I need to set up a timer on the web page to have it poll constantly for anything new from node-red. I want for example to have a page showing temperature etc.

      1. What you've suggested here is a client, not a broker and I use MQTT-SPY which is superb. What I don't really understand is that if Mosquitto can be compiled to run with websockets, I don't understand why there isn't a precompiled version ready for Pi out there, you'd think someone would have done it.

        1. mosquitto 1.4 has just come out and that has websockets in it. However it hasn't hit the repo yet, not sure as to the reason but it should be out soon.

          Hivemq works out of the box free up to x (can't remember the exact number) free clients. It has websockets. This is the one I currently use.

  3. Personlly I prefer graphite as graphing system and it runs well on a PI .

    Great blogg some very interesting articles and I'm learning loads. Thanks 🙂

Comments are closed.