Creating impressive graphs using D3.js

postgres
charts
visualisation
Tags: #<Tag:0x00007f0e8e056b28> #<Tag:0x00007f0e8e056998> #<Tag:0x00007f0e8e056858>

(DaHome Solutions) #1

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!


[solved] HABpanel with D3.js and SQL
(Yannick Schaus) #2

Impressive work @Pirx, thanks for sharing! :+1:

I have changed the category of this topic to the more appropriate “HABPanel Examples” :slight_smile:


(Christian) #3

Hi Pirx

Thank you very much for the manual!

I tried to replicate your examples using a local InfluxDB database. Do you have any experience adapting get_series.php for InfluxDB?

Could you also provide item definition of items “E_Logger_Item”, “E_Logger_Period”, …?

Thanks!


(DaHome Solutions) #4

Hi tailor,

no, I don’t have experience using InfluxDB in that way. Interestingly you will not find a PHP extension for InfluxDB on the official PHP website (see here). But there is a version on Github. You should try it out. The problem will be, that I’m using a SQL function called date_part() with PostgreSQL. You will find a similar function in most of the relational databases but not in InfluxDB. In my view it’s ridiculous for a “time-Series DB” to not provide some basic time and date functions. (This is one of the reasons why I moved to a different DB…)

Here are the two items.
String E_Logger_Item
Number E_Logger_Period

BTW: I’m further developing my graph module and can provide a new (better) version soon.


(Christian) #5

Thanks for your inputs. I’m switching now to PostgreSQL DB… items get already persisted :wink:

Where should the php file be saved in? conf/html folder? In “barchart1.js” you are referencing

var baseUrl = "https://192.168.178.18:443/get_series.php";

You have everything on the same machine, right? So we could also use localhost instead of the IP? I miss here the “static” subfolder… I’m confused


(DaHome Solutions) #6

Don’t be confused, you will manage it…
Yes, I’m running everything on the same machine, but I use for PHP a separate Apache2 web server. If it is not there (I already had it on my Raspi) then you need to install it. Then you need to install PHP alongside with the PHP Extension for Postgre SQL. Look here for it.
The get_series.php is stored within the home folder of Apache2. BTW: localhost will not work, because the call is being executed in the browser, which can be a tablet as well…
I’ll be off tomorrow , but I can continue to help you on Monday or Tuesday.


(shane kevin) #7

Thank you sir for the tutorials, may I ask you how well this d3js perform in OH ios/android client?Does the chart can shown in both local and remote view for ios/android client? I have trouble using grafana to show chart when remote viewing on the ios client, so if I using d3js that means I can finally kill both the influx and grafana instance to save some ram/disk usage?


(DaHome Solutions) #8

Hi Kevin,
one is for sure: d3.js is much faster then Grafana, espacially if you use Habpanel on a mobile client. When you use Grafana the your browser on the client has to call Grafana server, which calls the DB to get data, then it produces the graph and send it back to your client which at the end draw it on the display. D3.js sits on already on the client (let say within the browser) . So using my way, Habpanel calls a script on the server to get data only, then D3 is drawing the graph directly on client using javascript and SVG which is in my opinion the fastest method anyway to draw anything.
And yes, you can then drop Grafana. Instead of InflufDB you can take Postgres or MariaDB or MySQL or any other supported JDBC database. For latency resons I highly suggest to install the DB and openhab on the same server!


(shane kevin) #9

Really awesome! Thanks! Few more questions:

  • Could you please kindly provide more chart theme example? (like a simple line chart displaying Temp/Humidity by hourly/daily etc…)
  • Could you please also show how to config the jdbc.cfg? or no need to?
  • Does the Postgres need to install seperately? or only installing the jdbc-postgresql will be enough?
  • Could you please provide a step by step how to do it correctly for a noob like me?
  • How to config the xxx.persist file for Postgres like mapdb.persist did?

Sorry for the noob questions, thanks!


(DaHome Solutions) #10

Hi Kevin,

I apologize to not have enough time right now to fully fullfill your desire for a line chart with Temp/Humidity. Below is a simple linechart in my Habpanel for daily energy production using D3.js (the x-Axis must be of cource better formatted) .
I’d like to point out one important thing: D3 is nothing for non-programmers. If you want to use it you should get aquianted with JavaScript and D3.js. I’m providing ideas and general methods but not a final solutions for end users. This might change over time and I might share something which is ready to use, but not by now.

I regards to the other questions:
Yes, you need a jdbc.cfg. This is my version:

url=jdbc:postgresql://localhost:5432/openhab_db
user=openhab
password=your-pass
tableUseRealItemNames=true
tableIdDigitCount=0

Yes, you need to install Postgres separately (same for any other SQL database) .
In order to provide a step by step installation … Which HW and OS are you using ?

To the .persist files. I use Mapdb for every item and Postgres in addition for some selected items (for those I’d like to have a history). This is my mapdb.perist file

Strategies {
  default = everyUpdate
}

Items {
  * : strategy = everyChange, restoreOnStartup
}

And this is my jdbc.perisist file

Strategies {
	everyMinute : "0 * * * * ?"
	every5Minute: "0 */5 * * * ?"
   	everyHour   : "0 0 * * * ?"
   	everyDay    : "0 0 0 * * ?"
   	default = everyChange
}

Items {
        gP_ED* : strategy = everyDay
        gP_EH* : strategy = everyHour
        gP_E5M* : strategy = every5Minute
        gP_EM* : strategy = everyMinute
        gP_EC* : strategy = everyChange
}

linechart_500