A few weeks ago I started to investigate how I can leverage D3.js within HABpanel to create dynamic graphs like bar, line charts and others, and how can I access my persistence layer of OpenHAB.
D3.js is one of the best known Frameworks to create data driven documents. For people who don’t know it I recommend to take a longer look at the examples on this site: https://github.com/d3/d3/wiki/Gallery
If you spot a nice graph on the web the probability is high that it has been made using D3.js and the good news is that even HABpanel is internally using D3.js (Version 3)
I’m opening this thread to share the status of my work, to get your opinion and of course to get suggestions how to optimize my code.
While I gain a good understanding of D3.js, I still struggle with AngularJS. It is quite new to me and I’d love to get some help
Motivation:
The reason to deal with D3.js came with the dissatisfaction with other options to create graphs. HABpanel already has some graphical capabilities such as bar and line charts. However it is not the purpose of HABpanel to focus on data visualization. Hence, to do more some users turn to Grafana. I have done it for a while using a well-known combination of Grafana+InfluxDB. The generated charts can then be integrated into HABpanel via iFrames.
Relatively quickly, I came to the conclusion that even simple desires can not be done with Grafana+InfluxDB
Things I couldn’t do: labeling of x axis with month names, group by month or by year, compare values using group charts, drilling down when clicking on a bar chart, a.s.o. I experienced as well some performance issues unexpected delays a.s.o. Then I tried Grafana+PostgreSQL and recognized that it is not the right fit for OpenHAB anyway (conflict with the column name “time”). Grafana is the very good tool, so no question about it. But the objective is clearly to create portals to monitor Linux systems and not to act as general purpose graph engine.
Then I done a bigger deep dive into D3.js+PostgreSQL and I was totally impressed. Here is a short video of my first dynamic bar chart.
Advantages of D3.js:
- infinite design possibilities
- many options for animations and interactions
- tons of D3 examples in WWW
- very fast rendering
- it scales perfectly when resizing the browser window (incl. fonts)
- no more unexpected wait times
Disadvantage:
- you have to program… or take and adjust templates other have produced
My example is just a beginning: I will soon add more intervals (day, hour…), realize the drill down function, add line charts…
And here is the code.
First of all the code for the HABpanel template. As you can see I have created a AngularJS directive to communicate with my “barchar” code. The drop down menues are a bit wired and I’d like to do it differently (using Array of objects and ng-repeat), but failed so far. This where I’d like to have some help.
<div oc-lazy-load="'/static/mylib.js'"></div>
<div oc-lazy-load="'/static/barchart1.js'" >
<link rel="stylesheet" type="text/css" href="/static/barchart1.css" media="screen" />
<x4711></x4711> <!-- the position of the bar chart -->
<div my-barchart1
graph-width="600"
graph-height="340"
graph-position="x4711"
bar-labels="true"
periods="1"
interval='Y'
legend="true"
item-name="PV_Ertrag_kWh"
title="PV Ertrag pro Monat"
yaxis-title="Kilowattstunden (kWh)" />
<!------- navigation items -------->
<style type="text/css">
#mytable31 {
width:100%;
padding: 0px;
margin-top: 10px;
margin-bottom: 10px;
border-spacing:0px;
border: 0px solid gray;
border-collapse:collapse;
table-layout:fixed;
}
</style>
<table id="mytable31" >
<tr>
<!------- Drop-down for ITEMS; Save last setting in the item "E_Logger_Item" -------->
<td class="list-col-auto links-mitte">
<div class="btn-group" uib-dropdown >
<button id="single-button" type="button" class="btn mybtn" uib-dropdown-toggle>
<div ng-if="itemValue('E_Logger_Item')=='PV_Ertrag_kWh'"> PV Ertrag (kWh) <span class="caret"></span></div>
<div ng-if="itemValue('E_Logger_Item')=='Hzg_Aussen_Temperatur'"> Aussen-Temperatur <span class="caret"></span></div>
<div ng-if="itemValue('E_Logger_Item')=='KNX_Regenwasserpumpe_Stromwert'"> Regenwasserpumpe (Wh) <span class="caret"></span></div>
</button>
<ul class="dropdown-menu" uib-dropdown-menu role="menu" aria-labelledby="single-button">
<li role="menuitem">
<a ng-click="sendCmd('E_Logger_Item', 'PV_Ertrag_kWh'); set_source('PV_Ertrag_kWh','PV Ertrag pro Monat','SUM','Kilowattstunden (kWh)');">PV Ertrag (kWh)</a>
</li>
<li class="divider"></li>
<li role="menuitem">
<a ng-click="sendCmd('E_Logger_Item', 'Hzg_Aussen_Temperatur'); set_source('Hzg_Aussen_Temperatur','Aussen-Temperatur','AVG','Tagesdurchschnitt in °C');">Aussen Temperatur</a>
</li>
<li role="menuitem">
<a ng-click="sendCmd('E_Logger_Item', 'KNX_Regenwasserpumpe_Stromwert'); set_source('KNX_Regenwasserpumpe_Stromwert','Regenwasserpumpe Stromverbrauch','DIF','Wattstunden (Wh)');">Regenwasserpumpe (Wh)</a>
</li>
</ul>
</div>
</td>
<!------- Drop-down for PERIODs. Save last setting in the item "E_Logger_Period" -------->
<td class="list-col-auto links-mitte">
<span class="dh4"> Perioden:</span>
<div class="btn-group" uib-dropdown>
<button id="single-button" type="button" class="btn mybtn" uib-dropdown-toggle>
<div ng-if="itemValue('E_Logger_Period')=='1'"> 1 <span class="caret"></span></div>
<div ng-if="itemValue('E_Logger_Period')=='2'"> 2 <span class="caret"></span></div>
<div ng-if="itemValue('E_Logger_Period')=='3'"> 3 <span class="caret"></span></div>
</button>
<ul class="dropdown-menu" uib-dropdown-menu role="menu" aria-labelledby="single-button">
<li role="menuitem"><a ng-click="sendCmd('E_Logger_Period', '1');set_periods('1');"> 1 </a></li>
<li role="menuitem"><a ng-click="sendCmd('E_Logger_Period', '2');set_periods('2');"> 2 </a></li>
<li role="menuitem"><a ng-click="sendCmd('E_Logger_Period', '3');set_periods('3');"> 3 </a></li>
</ul>
</div>
</td>
<!------- START OF: navigation buttons -------->
<td class="list-col-round-button">
<button class="my-round-btn" ng-click="btn_decrease()">
<img src="/static/myicons/arrow-left-g20.svg" class="list-img-round-button" /></button>
</td>
<td class="list-col-round-button">
<button class="my-round-btn" ng-click="btn_now()">
<img src="/static/myicons/treffpunkt2-g20.svg" class="list-img-round-button" /></button>
</td>
<td class="list-col-round-button">
<button class="my-round-btn" ng-click="btn_increase()">
<img src="/static/myicons/arrow-right-g20.svg" class="list-img-round-button" /></button>
</td>
<!------- END OF: navigation buttons -------->
</tr>
</table>
</div>
This is the CSS code (barchart1.css).
.axis path,
.axis line {
fill: none;
stroke: rgb(230, 230, 230); /* Zwischenlinien */
stroke-width: 1px;
stroke-opacity: 20%;
shape-rendering: crispEdges;
}
.axis text {
font-size: 12px;
font-family: Verdana, Geneva, Tahoma, sans-serif;
fill: gray;
}
.x.axis path {
stroke:gray;
}
.y.axis path {
stroke:gray;
}
.axis-title {
font-size: 8px;
font-family: Verdana, Geneva, Tahoma, sans-serif;
text-anchor: middle;
fill: gray;
}
.bar {
stroke-width: 1px;
stroke-opacity: 50%;
}
.bar:hover {
fill: orangered ;
}
.title {
font-size: 14px;
font-family: Verdana, Geneva, Tahoma, sans-serif;
text-anchor: middle;
fill: orange;
color: orange;
}
.barlabel {
font-size: 8px;
font-family: Verdana, Geneva, Tahoma, sans-serif;
font-style: italic;
text-anchor: middle;
fill: green;
}
.legend {
stroke-width: 1px;
stroke-opacity: 50%;
}
.legendtext {
font-size: 8px;
font-family: Verdana, Geneva, Tahoma, sans-serif;
text-anchor: left;
fill: orange;
}
And this is my barchart1.js code.
//
// barchart1.js - Create a Bar Chart using D3.js
//
// V 0.5 - it works!
//
(function() {
'use strict';
var app = angular.module('app', []);
// app.controller('myBarchart1Ctrl', function($scope) {});
app.directive('myBarchart1', function() {
return {
restrict : 'AE',
scope : true,
controller: function($scope, $element, $attrs) {
// take over initial settings OR take a default
$scope.graph_position = $attrs.graphPosition || 'body';
$scope.graph_width = $attrs.graphWidth || 400;
$scope.graph_height = $attrs.graphHeight || 200;
$scope.bar_labels = ($attrs.barLabels === 'true') || false;
$scope.legend = ($attrs.legend === 'true') || false;
$scope.item_name = $attrs.itemName || 'PV_Ertrag_kWh';
$scope.table_name = $attrs.tableName || 'pv_ertrag_kwh_17';
$scope.title = $attrs.title || 'No title';
$scope.interval = $attrs.interval || 'Y';
$scope.periods = $attrs.periods || 1;
$scope.func = $attrs.func || "SUM";
$scope.yaxis_title = $attrs.yaxisTitle || "Value";
var fromDateString = (new Date()).getFullYear() + '-01-01T00:00:00';
var fromDate = new Date(fromDateString);
var fromYear = fromDate.getFullYear();
var toDateString = fromYear + '-12-31T23:59:59';
var toYear = 0;
function calculateDates() {
fromDateString = fromYear + '-01-01T00:00:00';
toYear = fromYear + parseInt($scope.periods) - 1;
toDateString = toYear + '-12-31T23:59:59';
}
calculateDates();
$scope.btn_decrease = function() {
--fromYear;
calculateDates();
render_barchart1();
};
$scope.btn_increase = function() {
++fromYear;
calculateDates();
render_barchart1();
};
$scope.btn_now = function() {
fromYear = (new Date()).getFullYear()
calculateDates();
render_barchart1();
};
$scope.set_source = function(arg1,arg2,arg3,arg4) {
$scope.item_name = arg1;
$scope.title = arg2;
$scope.func = arg3;
$scope.yaxis_title = arg4;
render_barchart1();
};
$scope.set_periods = function(cmd1) {
$scope.periods = +cmd1;
calculateDates();
render_barchart1();
};
var mycolor = ["#0080ff", "#66b2ff", "#cce5ff"];
var baseUrl = "https://192.168.178.18:443/get_series.php";
var monatsnamen = ["Jan","Feb","Mar","Apr","Mai","Jun","Jul","Aug","Sep","Okt","Nov","Dec"];
var duration = 800;
var margin_bottom = ($scope.legend) ? 50 : 30;
var margin = {top:50, right:0, bottom:margin_bottom, left:50},
width = $scope.graph_width,
height = $scope.graph_height;
var svg = d3.select($scope.graph_position)
.append("svg")
.attr("width", "100%")
.attr("height", "100%")
.attr("viewBox", "0 0 " + width + " " + height);
var formatNumber = d3.format(".f");
var yScale = d3.scale.linear()
.range([height - margin.top - margin.bottom, 0]);
var xScale = d3.scale.ordinal()
.rangeRoundBands([0, width - margin.right - margin.left], .2);
var xAxis = d3.svg.axis()
.scale(xScale)
.orient("bottom");
var yAxis = d3.svg.axis()
.scale(yScale)
.orient("left")
.ticks(4)
.tickSize(-5) // do "0-width" for continues lines
.tickFormat(function(d) {
var s = formatNumber(d); // or i.e. ... d / 1000
return this.parentNode.nextSibling
? s
: "kWh " + s;
});
//adding y axis to the left of the chart
svg.append("g")
.attr("transform", "translate(" + margin.left + "," + margin.top + ")")
.attr("class", "y axis");
//adding x axis to the bottom of chart
svg.append("g")
.attr("transform", "translate(" + margin.left + "," + (height - margin.bottom) + ")")
.attr("class", "x axis");
// this is the render function, which will be used for UPDATES as well
function render_barchart1() {
// create PHP/JSON call
var phpCall = baseUrl +
"?item=" + $scope.item_name +
"&from_date=%27" + fromDateString + "%27" +
"&to_date=%27" + toDateString + "%27" +
"&func=" + $scope.func;
d3.json(phpCall, function(error, data) {
prepare_json_data(data, fromYear, $scope.interval);
var jahre = get_years(data);
var spalten = jahre.length;
var svg = d3.select("svg");
// yscale's domain is from zero to the maximum "Wert" in your data
yScale.domain([0, d3.max(data, function(d){ return d["wert"]; })]);
// fixed scale: yScale.domain([0, 1500]);
// xscale is unique values in your data (Monatsname, since they are all different)
xScale.domain(data.map(function(d){ return d["monat"]; }));
//update axis
svg.select('.y.axis')
.transition()
.duration(duration)
.call(yAxis);
svg.select('.x.axis')
.transition()
.duration(duration)
.call(xAxis);
// Add the title for the Y axis
svg.select(".axis-title").remove();
svg.append("text")
.attr("transform", "rotate(-90)")
.attr("y", 0)
.attr("x", 0 - (height / 2))
.attr("dy", "1em")
.attr("class", "axis-title")
.text($scope.yaxis_title);
//----- BARS ---------------
var bars = svg.selectAll(".bar")
.data(data);
// Enter
bars.enter().append("rect")
.attr("transform", "translate(" + margin.left + "," + margin.top + ")")
.attr("class", "bar");
// Update
bars.attr("fill", function(d){ return d3.rgb(mycolor[d["jahr"]-fromYear]); })
.transition()
.duration(duration)
.attr("x", function(d){ return xScale(d["monat"]) + ((d["jahr"]-fromYear)*(xScale.rangeBand()/spalten)); })
.attr("y", function(d){ return yScale(d["wert"]); })
.attr("height", function(d){ return height - margin.top - margin.bottom - yScale(d["wert"]); })
.attr("width", function(d){ return (xScale.rangeBand()/spalten); });
// Exit
bars.exit()
.transition()
.duration(duration)
.ease("exp")
.attr("height", 0)
.remove();
//----- LEGEND and LEGENDTEXT ---------------
if ($scope.legend) {
var legend = svg.selectAll(".legend")
.data(jahre);
// Enter
legend.enter().append("rect")
.attr("transform", "translate(" + margin.left + "," + (height - margin.bottom) + ")")
.attr("class", "legend");
// Update
legend.attr("fill", function(d,i){ return d3.rgb(mycolor[i]); })
.attr("x", function(d,i){ return 50*i; })
.attr("y", 35)
.attr("height", 10)
.attr("width", 20);
// Exit
legend.exit().remove();
var legendtext = svg.selectAll(".legendtext")
.data(jahre);
// Enter
legendtext.enter().append("text")
.attr("transform", "translate(" + margin.left + "," + (height - margin.bottom) + ")")
.attr("class", "legendtext");
// Update
legendtext
.attr("x", function(d,i){ return (50*i +23); })
.attr("y", 43)
.text(function(d,i){ return (jahre[i]) });
// Exit
legendtext.exit().remove();
}
//----- LABELS at the top of each bar
if ($scope.bar_labels) {
var barlabels = svg.selectAll(".barlabel")
.data(data);
// Enter
barlabels.enter().append("text")
.attr("transform", "translate(" + margin.left + "," + margin.top + ")");
// Update
barlabels
.transition()
.duration(duration)
.attr("x", function(d){ return xScale(d["monat"]) + ((d["jahr"]-fromYear+0.5)*(xScale.rangeBand()/spalten)); })
.attr("y", function(d){ return yScale(d["wert"]) - 3; })
.attr("class", "barlabel")
.text(function(d){ return (d["wert"]) ? d3.format("^.0f")(d["wert"]) : " "; });
// Exit
barlabels.exit().remove();
}
//----- TITEL at the top of the chart
var show_years = ($scope.periods == 1) ? fromYear : fromYear+'-'+toYear;
// Remove previous title
svg.select(".title").remove();
// Enter new titel
svg.append("text")
.attr("transform", "translate(" + (width/2) + ", 15)")
.attr("class", "title")
.text($scope.title + " ("+show_years+")");
}); // end of d3.json
} // end of render_barchart1()
render_barchart1();
} // end of controller
} // the return
}); // end of app.directive
})();
The tricky part is the access to the OH persistence layer. My persistence strategy is to use MAPDB for all items (to enable the restoring of all items on restart) and then use PostgreSQL for selected items to store temperatures, energy consumption a.s.o.
I’m accessing the PostgreSQL database through a PHP-script which is running on a separate Apache2 web server, located on the same server where the OH instance is running. This PHP-script (get_series.php) is delivering the data to D3.js in JSON format. If someone find a better way, please shout out! You can of course change the script to access a MySQL, Maria DB and other databases.
This is the PHP-script “get_series.php”:
<?php
// get_series.php - Gets a time series of data from a table managed by OpenHAB and return it as JSON data.
// In fact this script is accessing the persistance layer of OpenHAB, (here) stored in a PostgreSQL database.
// Currently the data is grouped by month only! Upcoming version will include day and hour.
//
// Attributes:
// item - the OpenHAB item name
// table - table name (... will be skiped if item has been provided)
// func - function on a group. It could be currently "SUM", "AVG" or "DIF"
// from_date - start time for the series (>=) - timestamp in ISO 8601 format
// to_date - end time for the series (<) - timestamp in ISO 8601 format
//
// Example: http://localhost/get_series.php?item=PV_Ertrag_kWh&from_date=%272018-01-01T00:00:00%27&from_to=%272018-12-31T23:59:59%27
//
//
// V1.2 - add function attribute
// V1.1 - add item attribute
// V1.0 - first release
//
header('Access-Control-Allow-Origin: *');
// Connecting and selecting a database (.... original: "password=openhab!")
$dbconn = pg_connect("host=localhost dbname=openhab_db user=openhab password=openhab!")
or die('Could not connect: ' . pg_last_error());
//
// Tablename - if item name is provided pick up the table name from the OH 'items' table
//
if (isset($_GET['item'])) {
$query = "SELECT itemid FROM items WHERE itemname = '" . $_GET['item'] . "'";
$result = pg_query($query) or die('Error: ' . pg_last_error());
$val = pg_fetch_result($result, 0, 0);
$tablename = $_GET['item'] . "_" . $val;
pg_free_result($result);
} else {
if (isset($_GET['table']))
$tablename = $_GET['table'];
else
die('Error: no table or item provided');
}
//
// Function to be used on Group Level
//
$func = "sum(value)"; // default
if (isset($_GET['func'])) {
if ($_GET['func'] == "SUM") $func = "sum(value)";
else if ($_GET['func'] == "AVG") $func = "avg(value)";
else if ($_GET['func'] == "DIF") $func = "(max(value)-min(value))";
}
//
// Create SQL query
//
$query = "SELECT date_part('year',time) jahr, date_part('month',time) monat, " . $func . " wert";
$query = $query . " FROM " . $tablename;
//
// WHERE clause
//
$where_clause = FALSE;
if (isset($_GET['from_date'])) {
if ( $where_clause == FALSE) {$query = $query . " WHERE "; $where_clause = TRUE;}
else $query = $query . " AND ";
$query = $query . "(time >= " . $_GET['from_date'] . ")";
}
if (isset($_GET['to_date'])) {
if ( $where_clause == FALSE) {$query = $query . " WHERE "; $where_clause = TRUE;}
else $query = $query . " AND ";
$query = $query . "(time < " . $_GET['to_date'] . ")";
}
// GROUP BY and ORDER BY
$query = $query . " GROUP BY jahr, monat ORDER BY jahr, monat";
// Execute and get results
$result = pg_query($query) or die('Query failed: ' . pg_last_error());
$resultArray = array();
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC)) {
$resultArray[] = $row;
}
// Convert to JSON format and send it out
echo json_encode($resultArray);
// Free resultset
pg_free_result($result);
// Close connection
pg_close($dbconn);
?>
Your comments are well welcome!
