Examples (17)


Mqttcogs Now Supports Leaflet

We’ve already seen that it is possible to show a simple plot of longitude and latitudes from a JSON payload. See Visualize OwnTracks GPS Data.

If you like simple Google maps this works but isn’t particularly flexible. You can only display points on the Google map using the very limited Google Map Visualization. If you also want to track ‘assets’ you’ll be liable at some point to pay a fee per asset. This is not good!

I added the ability to visualize using the Leaflet mapping library. You can set this up to use open source maps and the library is flexible enough to allow fancy stuff like polylines to be drawn!

Lets draw a Leaflet map in a similar way to our Google map. This is really easy. Just use an mqttcogs_drawleaflet shortcode. This draws points in a similar way to a Google Map Visualization in the link above.

[ mqttcogs_drawleaflet 
	refresh_secs="15" 
	height="400px" 
	options="{center:{lat:52,lng:1},zoom:13}"]
    [ mqttcogs_data 
		topics="owntracks/another/ckandroid"
		order="DESC"
		limit="100"]
[/mqttcogs_drawleaflet ]

…and here is the result.




Grouping and Aggregating

Version 2.2 includes the ability to group and aggregrate data. This is really useful, it allows you slice data by day and apply operations like MIN, MAX and AVG.

We’re going to work through an example that will show the MINimum temperature and the MAXimum temperature for each day for one of my sensors.

The new attributes are applied to the mqttcogs_data shortcode. The resulting grouping and aggregation is done at the server*.

We’ll dive straight into the example to see how they work.

[ mqttcogs_data
topics="mysensors_out/92/1/1/0/0,mysensors_out/92/1/1/0/0"
from="-30"
group="DAY"
aggregations="MIN,MAX"]

Here’s the corresponding explanation of each of the attributes for this example.

topics: Extracts temperature data for the topic mysensors_out/92/1/1/0/0. It extracts data for the same topic twice as we want to compare max and min values.

from: Restricts data to the last 30 days

group: Groups the data for each topic based on the DAY. Note that grouping can be anything that the MySQL EXTRACT funtion supports.

aggregrations: Applies the aggregation to the grouped data for each topic. This attribute should contain a comma delimited list of aggregations. One for each topic. Note that the aggregation can be anything that the MySQL group by operation supports.

Heres my full shortcode and underneath my graph!

[ mqttcogs_drawgoogle options="{height:200}"]
	[ mqttcogs_data  
		topics="mysensors_out/92/1/1/0/0,mysensors_out/92/1/1/0/0" 
		aggregations="MIN,MAX" 
		from="-30" 
		group="DAY"]
[/mqttcogs_drawgoogle]

The graph shows the MINimum temperature and the MAXimum temperature for each day. Neat!

*Note: It is also possible to group and aggregrate data client-side using the ‘script’ attribute of the mqttcogs_drawgoogle shortcode




Visualizing Mqtt JSON Payload 2

This post describes how to manipulate the Mqtt payload when it contains JSON data. You must be using Version 2.2 or greater for this example to work!

For this example, we’ll use some owntracks data. See Visualizing Owntracks Data for an introduction. Firstly, lets remind ourselves of what the data looks like in our database.

and here is the expanded JSON data.

{	"_type":"location",
	"acc":8,
	"alt":56,
	"batt":75,
	"conn":"m",
	"lat":53.8165549,
	"lon":-1.6013796,
	"tid":"1",
	"tst":1557255979,
	"vac":8,
	"vel":0
}

My requirements are simple. I want to write a script that will plot my location data on a Google Visualization Map and include a flag that shows the Latitude, Longitude and fix time.

If we examine the documentation for the Map visualization we can see that we need to create data containing fields lat, lng and description. The google map visualization then takes care of the rest.

We are going to use the mqttcogs ‘script’ attribute. This attribute allows a user to apply a javascript function to the data client side. This allows a developer to make full use of the datatable and dataview javascript classes. You can manipulate, filter, group, create calculated columns and format the data before the chart is drawn!

Before we look at a function remember that the script attribute points to a wordpress custom field. The custom field contains the script. This is necessary as we can’t embed a javascript function directly into the shortcode.

The custom field section is located at the bottom of a post. You will need to make it visible via the Gutenberg editor options.

Now we are ready to examine a typical script. In fact, we’ll look at the default script that is applied if you don’t provide one yourself. Remember this is a javascript function. The parameters include

  • data – a google visualization datatable containing the mqtt data
  • chart – a google visualization chart, created using the charttype attribute you supplied
  • options – a javascript object created from the options attribute you supplied in the shortcode

function (data, chart, options) {
	if (this.charttype == 'Map') {				
		var view  = new google.visualization.DataView(data);
		
		view.setColumns([
			{
				calc:function (data, ridx) {
					var payload = data.getValue(ridx, 1);
					return payload.lat?payload.lat:payload.latitude;
				}, 
				type:'number', 
				label:'Lat'
			},
			{
				calc:function (data, ridx) {
					var payload = data.getValue(ridx, 1);
					return payload.lon?payload.lon:(payload.lng?payload.lng:payload.longitude);
				}, 
				type:'number', 
				label:'Long'
			},
			{
				calc:function (data, ridx) {
					return data.getColumnId(1) + ' @ ' + data.getValue(ridx,0);
				},
				type:'string', 
				label:'Description'
			}
		]);
		
		chart.draw(view, options);
	} 
	else {
		chart.draw(data, options);
	}
}

The above code is very simple. We check if the chart is a Map, if it isn’t we simply draw the chart using the datatable.

If the chart is a Map, then we create a DataView with three columns. Columns 1 and 2 are latitude and longitude (we do our best to find them as they could be named differently depending on the data source) and Column 3 is a string composed of the topic and the utc (datetime). The visualization is then drawn using the view rather than the datatable.

So here is our updated function.


function (data, chart, options) {
	if (this.charttype == 'Map') {				
		var view  = new google.visualization.DataView(data);
		
		view.setColumns([
			{
				calc:function (data, ridx) {
					var payload = data.getValue(ridx, 1);
					return payload.lat?payload.lat:payload.latitude;
				}, 
				type:'number', 
				label:'Lat'
			},
			{
				calc:function (data, ridx) {
					var payload = data.getValue(ridx, 1);
					return payload.lon?payload.lon:(payload.lng?payload.lng:payload.longitude);
				}, 
				type:'number', 
				label:'Long'
			},
			{
				calc:function (data, ridx) {
					var payload = data.getValue(ridx, 1);
					return 'Lat: ' + payload.lat + ' Lng: ' + payload.lon + ' @ ' + data.getValue(ridx,0);
				},
				type:'string', 
				label:'Description'
			}
		]);
		
		chart.draw(view, options);
	} 
	else {
		chart.draw(data, options);
	}
}

The ‘this’ object contains, amongst other things the shortcode attributes. We use this to determine the type of chart that we are drawing.




How to Output a Table

This post shows how to output a Google Visualization Table.

This is useful for a simple data dump of Mqtt data. I’ve tried to use this visualization in other projects but I found that the table is a little difficult to format and doesn’t include features that other table libraries do a lot better. See DataTables, for example!

Firstly we’ll have a little look at some Mqtt data from the database. You can dump the JSON data directly to the browser by using an mqttcogs_data shortcode on it’s own.

[ mqttcogs_data limit="5" order="DESC" topics="owntracks/another/ckandroid"]

and here is the output. As you can see, the data is JSON formatted ready to be used by the Google Visualizations

{"cols":[{"id":"utc","label":"utc","type":"datetime"},{"id":"Paul","type":"string"}],"rows":[{"c":[{"v":new Date(1564485214000)},{"v":{"_type":"location","acc":4,"alt":128,"batt":34,"conn":"w","lat":53.90525389999999816836862009949982166290283203125,"lon":-1.749858300000000088658680397202260792255401611328125,"tid":"id","tst":1564485214,"vac":8,"vel":0,"Desc":"Speed:0kph At:2019-07-30 11:13:34"}}]},{"c":[{"v":new Date(1564421498000)},{"v":{"_type":"location","acc":3,"alt":125,"batt":73,"conn":"w","lat":53.906377800000001343505573458969593048095703125,"lon":-1.74596280000000003695959094329737126827239990234375,"t":"p","tid":"id","tst":1564421498,"vac":4,"vel":34,"Desc":"Speed:34kph At:2019-07-29 17:31:38"}}]},{"c":[{"v":new Date(1564419309000)},{"v":{"_type":"location","acc":3,"alt":125,"batt":74,"conn":"w","lat":53.906377800000001343505573458969593048095703125,"lon":-1.74596280000000003695959094329737126827239990234375,"t":"p","tid":"id","tst":1564419309,"vac":4,"vel":34,"Desc":"Speed:34kph At:2019-07-29 16:55:09"}}]},{"c":[{"v":new Date(1564417461000)},{"v":{"_type":"location","acc":3,"alt":125,"batt":75,"conn":"w","lat":53.906377800000001343505573458969593048095703125,"lon":-1.74596280000000003695959094329737126827239990234375,"t":"p","tid":"id","tst":1564417461,"vac":4,"vel":34,"Desc":"Speed:34kph At:2019-07-29 16:24:21"}}]},{"c":[{"v":new Date(1564417461000)},{"v":{"_type":"location","acc":3,"alt":125,"batt":75,"conn":"w","lat":53.906377800000001343505573458969593048095703125,"lon":-1.74596280000000003695959094329737126827239990234375,"t":"p","tid":"id","tst":1564417461,"vac":4,"vel":34,"Desc":"Speed:34kph At:2019-07-29 16:24:21"}}]}]}

Creating a table is straightforward. Simply pass in the relevant charttype to the shortcode like this.

[ mqttcogs_drawgoogle 
ajax="true" 
charttype="Table"
options="{width: '100%', height: '100%'}]
       [ mqttcogs_data 
          limit="5"
          order="DESC"
          topics="owntracks/another/ckandroid"]
[/mqttcogs_drawgoogle]

The result will look something like this. Not particularly pretty, even with the width and height specifiers the table doesn’t fill the window.




A State Triggered Action!

In a Create a Time Triggered Action we were able to schedule an Mqtt Message (the action) to be sent at a given time or interval. This example shows how to take an action based on an incoming message. I’ve highlighted my criteria in green and my action in red

When I receive a moisture low message AND it is after 6pm AND watering is off I want to start watering my plants.

Incoming Message (from sensor)

Incoming messages from my sensor have a topic of mysensors_out/92/0/1/0/37.  The payload contains the moisture level, anything above 100 is ok, anything below 100 is ‘dry’.

Outgoing Message (to sensor)

In order to turn on my pump I send a message to the mysensors_in/10/6/1/0/2 topic. The payload contains the number of minutes that the pump should be on.

So lets have a first go at implementing our logic. This won’t include ‘debounce’ and detecting whether watering is off. We’ll get this one working first and then look at implementing the other bits. We’ll use our standard after_mqttcogs_msg_in Action Hook.

add_action('after_mqttcogs_msg_in', 'moisture_msg_in', 10, 3);

function moisture_msg_in($utc, $topic, $payload)
{
	//this is not the sensor you are looking for, move along
	if ($topic != 'mysensors_out/92/0/1/0/37') {
		return;
	}

	//it is not dry, move along
	$payload = (int) $payload;
	if ($payload>=100) {
		return;
	}

	//it is not after 6pm, move along
	$dtm = new DateTime();
	$dtm->setTimezone(new DateTimeZone('Europe/London'));
	if ((int)$dtm->format('H'))<18) {
		return;
	};

	//it is after 6pm, it's dry so turn the water on!
	
	$outtopic = 'mysensors_in/10/6/1/0/2';
	$outpayload = 1;

	$mqttcogs = new MqttCogs_Plugin();
	$mqttcogs->sendMqtt('moisture_msg_in', $outtopic, $outpayload, 1, 0, '');
}

All pretty easy so far. We are left with one final criteria to check. We need to decide whether the watering is currently ‘on’ or ‘off’.

MqttCogs provides some neat function calls so that you can easily query the Mqtt data stored in the database. For this case, we’ll simply find the last mysensors_out/10/6/1/0/2 message from the device and look at it’s payload. A payload set to ‘0’ means that watering is ‘off’. A positive payload means watering is on.

//watering is currently active, move along
	$rows = $this->getLastN('data', 'mysensors_out/10/6/1/0/2', 1, 'DESC');
	//check there is a last record and if it's payload is not
	//zero then we are watering
	if ((count($rows)==1) && ($rows['payload'] > 0)) {
		return;
	}

So here is the final version. This includes a check for all the criteria at the top of the post and some simple error handling to email me when watering is on/buffered or we fail for some reason.

add_action('after_mqttcogs_msg_in', 'moisture_msg_in', 10, 3);
function moisture_msg_in($utc, $topic, $payload)
{
    $debug = false;
    
	//this is not the sensor you are looking for, move along
	if ($topic != 'mysensors_out/92/0/1/0/37') {
		return;
	}

	//it is not dry, move along
	$payload = (int) $payload;
	if ((!$debug) && ($payload>=100)) {
		return;
	}

	//it is not after 6pm local time, move along
	$dtm = new DateTime();
	$dtm->setTimezone(new DateTimeZone('Europe/London'));
	if ((!$debug) && ((int)$dtm->format('H'))<18) {
		return;
	};

    //watering is currently active, move along
	$rows = $this->getLastN('data', 'mysensors_out/10/6/1/0/2', 1, 'DESC');
	if ((count($rows)==1) && ($rows['payload'] > 0)) {
		return;
	}
    
	//it is after 6pm, it's dry and watering is off - so turn the water on!
	$outtopic = 'mysensors_in/10/6/1/0/2';
	$outpayload = 1;

	$mqttcogs = new MqttCogs_Plugin();
	$error = new stdClass();

	//note that this function returns
	// true (message ok or buffered) or 
	// false for error
	// I just use the $error object though...
	$mqttcogs->sendMqtt('moisture_msg_in', $outtopic, $outpayload, 1, 0, $error);
    
	switch($error->status) {
		case 'ok':
			wp_mail( 'test@test.com', 'Watering on (sent)', 'Moisture is: '.$outpayload ); 
		break;
		case 'buffered':
			wp_mail( 'test@test.com', 'Watering on (buffered)', 'Moisture is: '.$outpayload ); 
		break;
		case 'error':
			wp_mail( 'test@test.com', 'Watering FAIL', $error->errors[0].reason); 	
		break;
	}
}



A Quick MqttCogs Dashboard

In this post i want to see how quickly I can put together a dashboard based on MqttCogs. I want it to display information from one of my sensors. My requirements are

  • To display graphs of my sensors that show temperature
  • To display my data sliced by date and time
  • To incorporate other types of graph rather than just a LineGraph
  • I want the graphs to fill the screen on my laptop but ‘flow’ when viewing on my phone
  • I don’t want to be messing around with CSS!

We’ll start by crossing off the nasty CSS and columns problem. I ended up using a plugin called SiteOrigin (see this link). I’ve used a few page builders but I quite like this one. It’s free and is an easy, clutterless way of organising content on your blog.

WordPress now provides a much better page builder interface. You can add columns using the standard wordpress editing features! 

You can set up columns and rows and then add the mqttcogs shortcodes really easily.

C @ UTC




A Time Triggered Action!

This example shows how to take an action based on a time or schedule. It uses the excellent WP Control plugin.

This plugin allows you to easily set the time window or repeating schedule that you want to use. Here is my logic that I would like to implement. I’ve highlighted my criteria in green and my action in red

If it is 6pm I want to start watering my plants for 3 minutes.

Firstly, I set up a new cron schedule using WP Control. This looks like the picture below. Choose a daily schedule and don’t forget when you first want it to run! I can now start writing my logic in the box.

WP Control – Cron Event

In order to turn on my pump I send a mysensors_in/10/6/1/0/2 the playload contains the number of minutes that the pump should be on.

So lets have a first go at implementing our logic. It is possible to send data to your Mqtt broker really easily using the MqttCogs_Plugin class. This is REALLY easy. The sendMqtt function will even automatically buffer messages for MySensors nodes that implement smartSleep.

I’ve expanded the call parameters into variables but this is not necessary.

$clientid = 'cron_w'; //can be any short text
$outtopic = 'mysensors_in/10/6/1/0/2'; //topic to write to
$outpayload = 3; //payload, on for 3 minutes
$qos = 1; //qos
$retain = 0; //retain
$error = new stdClass();

$mqttcogs = new MqttCogs_Plugin();
$mqttcogs->sendMqtt($clientid, $outtopic, $outpayload, $qos, $retain, $error);

Here’s a second go. This one picks up the return from the function and then emails the result. I haven’t expanded the variables this time to keep things concise.

$mqttcogs = new MqttCogs_Plugin();
$error = new stdClass();

//note that this function returns
// true (message ok or buffered) or 
// false for error
// I just use the $error object though...
$mqttcogs->sendMqtt('cron_w', 'mysensors_in/10/6/1/0/2', 3, 1,0, $error); 
	switch($error->status) {
		case 'ok':
			wp_mail( 'test@test.com', 'Watering on', 'Message sent' ); 
		break;
		case 'buffered':
			wp_mail( 'test@test.com', 'Watering on', 'Message buffered' ); 
		break;
		case 'error':
			wp_mail( 'test@test.com', 'Watering FAIL', $error->errors[0].reason); 	
		break;
	}




Visualize OwnTracks GPS Data

This post describes how to visualize Mqtt data from the OwnTracks App.

For this experiment I installed OwnTracks onto my phone and filled in my credentials for my Mqtt broker.

Problem 1: My Mqtt broker uses port 14027. Outgoing data to this port wasn’t allowed on the particular Wifi network I was using. This took me ages to work out and was solved simply by turning off Wifi and using the phone data connection.

When this happens OwnTracks won’t fall back and use a different data connection so you might get delayed tracking data when a user phone moves in and out of particular Wifi networks.

OwnTracks topic and mqtt message format

The OwnTracks format is well documented, see here. It is a little verbose but it does contain longitude, latitude and speed. Be aware that the data can change depending on your phone platform. The topic is in the format owntracks/user/device. Both user and device can be set in the App.

Here’s an example of the two types of packets that I received from my Android device :

{	"_type":"location",
	"acc":8,
	"alt":56,
	"batt":75,
	"conn":"m",
	"lat":53.8165549,
	"lon":-1.6013796,
	"tid":"1",
	"tst":1557255979,
	"vac":8,
	"vel":0
}

{     "_type":"lwt",
     "tst":1557286504
}

The lwt type is a special ‘last will and testament’ type. It is sent when the device disconnects from the Mqtt broker. This can happen often, particularly if you are travelling!

Problem 2: I need to combine the deviceid from the topic and the lng/lats and timestamp from the payload to get a meaningful piece of data to plot.

Problem 3: What do I do with the lwt (last will and testament) message? This tells me when the device disconnected, although I can’t find a corresponding ‘device has connected’ message.

I can change or shape the data by using a mqttcogs_msg_in_pre filter hook. This is called just before the data is persisted to the wordpress database. I’m going to do aim to do the following:

  • Ignore messages that aren’t of type ‘location’
  • Use the GPS datetime rather than the server time in the utc field
  • Add a new field ‘Desc’ that I actually never use. This is simply to demonstrate how the payload could be manipulated.

add_filter('mqttcogs_msg_in_pre', 'mqttcogs_msg_in_pre', 10, 3);
 function mqttcogs_msg_in_pre($publish_object, $datetime)
 {
     $topic = $publish_object->getTopic();
     //filter by topic
     if (!(substr( $topic, 0, 9 ) === "owntracks")) {
         return $publish_object;
     }
     
     $mqttmsg = $publish_object->getMessage();
     $payload = json_decode($mqttmsg);

     //only do this to correct type of messages
     if ($payload->_type != 'location') {
        return NULL;
     }
     
     //update utc time to GPS datetime
     if (property_exists($payload, 'tst')) {
        $datetime->setTimestamp((int) $payload->tst);
     }
     
     //Now manipulate the payload. This extra attribute isn't actually 
     //used!
     $speed = $payload->vel;
     $nicedatetime = $datetime->format('Y-m-d H:i:s');
     $payload->Desc  = 'Speed:'.$speed.'kph At:'.$nicedatetime;
     $publish_object->setMessage(json_encode($payload));
     return $publish_object;
 }

….and here is the result. I’ve included the shortcode below. Remember that as of version 2.2 the longitude and latitude will be automatically extracted for the Map Visualization. Map looks good but the Google Map Visualization isn’t set up to refresh very nicely. When you set the refresh_secs=”60″ and the data is refreshed, this particular visualization draws the Map tiles and resets the zoom level. Not very useful!

[ mqttcogs_drawgoogle 
	refresh_secs="120" 
	charttype="Map" 
	options="{height:300,zoomLevel:12,showTooltip: true,showInfoWindow:true,showLine:true}"]
	[ mqttcogs_data 
		limit="1"
		order="DESC"
		topics="owntracks/another/ckandroid"]
[ /mqttcogs_drawgoogle]




Playing with GPS Data using Filter Hooks

I wrote a really basic example here that showed how to plot some GPS points onto map. The example works well but you may have noticed that it relies on the data in the Mqtt message payload formatted in a particular way. Remember what it looked like?

If you’ve worked with GPS devices before you know that the GPS data that ends up at the Mqtt broker won’t look like that. That’s far too simple!

MqttCogs provides a Filter Hook to allow you change the incoming payload before it is persisted to the database. This allows you to reshape the JSON in the payload to a form that can be used by the MqttCogs shortcodes.

To use the hook you need to be able to write some php. I used My Custom Functions wordpress plugin. This allows you to quickly add some php to your site without messing around with functions.php or any other part of your site. I highly recommend this plugin.

My code looks something like this. It reads the payload, changes the format of the incoming payload data. The data is then persisted as normal.

add_filter('mqttcogs_msg_in_pre', 'mqttcogs_msg_in_pre', 10, 3);
 function mqttcogs_msg_in_pre($publish_object, $utc)
 {
     //do nothing if we are not interested in the topic
     if ($publish_object.getTopic() != 'sometopicthatcontainslnglat") {
           return;
     }
     
     //reshape the JSON data
     $payload = $publish_object.getMessage();
     $payload = reshapethedatafunction($payload);
     $publish_object.setMessage($payload);
 }

To extract and plot the values from my json payload I need to instruct MqttCogs where to get them from. Have a read of visualizing mqtt json payload this provides a worked example of how to show your lng lats on a map.

Check out Visualizing OwnTracks GPS Data this uses this principle to show owntracks GPS data on a map!




Making a Gauge Chart

This post explains how to create a Google Visualization Gauge chart.

Extracting Data for a Google Visualization Gauge

Google Visualization Gauges need a bit of thought. If you look at the documentation for the Gauge chart here, there is a sneaky little comment that reads:

At the moment there’s no way to specify the title of a gauge chart as you can with other Google Charts

That makes things difficult. We’ll do our best to create a gauge but it won’t have a title. I’ve instead used a plugin to create 2 columns in my WordPress page and added a title above the Gauge. It’s the best I can do!

Here’s the shortcode for one of the Gauges. I simply get the top 1 row for my one of my probes.

[ mqttcogs_drawgoogle options="{height:200}"
	charttype="Gauge" 
	ajax="true" ]
		[ mqttcogs_data limit="1" topics="mysensors_out/92/1/1/0/0" ]
[ /mqttcogs_drawgoogle ]

Soil Temperature

Air Temperature

There’s obviously a bit of work for me to do here. There is no way currently of getting the maximum/min/average value for today!