Need solution for Creating chart with X and Y values.

[karthhic Mukil]

Dear All,

I want to create a chart where i will be giving the values for both X axis and Y axis.

I am using contrib-ui to display a HMI.

Is there any way i can use any use the template from contrib-ui?

Thank you,


Best regards,
Karthhic

[karthhic Mukil]

THis is my flow.

I want to get information of weather, store it in a database, retrive it and create a chart from it.


my flow:

[{"id":"c40d8c91.ae6f3","type":"ui_tab","z":"7ed71e0a.377cd8","name":"HISTORY","icon":"dashboard","order":"2"},{"id":"f7c004a6.665e4","type":"ui_tab","z":"","name":"WEATHER","icon":"dashboard","order":"1"},{"id":"9cb4c385.634b4","type":"sqlitedb","db":"/home/karthhic/weather"},{"id":"2c320d48.59ff02","type":"openweathermap in","z":"7ed71e0a.377cd8","name":"","lon":"","lat":"","city":"Tampere","country":"Finland","x":84,"y":85,"wires":[["da4a1d3f.077c8"]]},{"id":"537e7081.111108","type":"debug","z":"7ed71e0a.377cd8","name":"","active":false,"console":"false","complete":"topic","x":777,"y":145,"wires":[]},{"id":"6e7cc218.88ea54","type":"template","z":"7ed71e0a.377cd8","name":"insert data","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO temperature (date,time,temperature,humidity,look,detail)\nVALUES ({{date}},\"{{time}}\",{{payload.tempc}},{{payload.humidity}},\"{{payload.weather}}\",\"{{payload.detail}}\");","x":491,"y":81,"wires":[["88876ba0.6da6f8"]]},{"id":"da4a1d3f.077c8","type":"moment","z":"7ed71e0a.377cd8","name":"date","topic":"","input":"payload","format":"DDMMYY","locale":"","output":"date","x":230,"y":85,"wires":[["e7992502.970478"]]},{"id":"88876ba0.6da6f8","type":"sqlite","z":"7ed71e0a.377cd8","mydb":"9cb4c385.634b4","name":"db","x":652,"y":160,"wires":[["537e7081.111108","31ad659b.e87c92","eaccf76b.92c0b8","4d1aa0b5.7a743","b3c03490.964338","f2427fd3.a428e"]]},{"id":"e7992502.970478","type":"moment","z":"7ed71e0a.377cd8","name":"time","topic":"","input":"","format":"HH:mm:ss","locale":"","output":"time","x":349,"y":85,"wires":[["6e7cc218.88ea54"]]},{"id":"31ad659b.e87c92","type":"debug","z":"7ed71e0a.377cd8","name":"","active":false,"console":"false","complete":"payload","x":788,"y":179,"wires":[]},{"id":"54b746e4.4d99f8","type":"ui_button","z":"7ed71e0a.377cd8","tab":"f7c004a6.665e4","name":"Delete","payload":"","topic":"","group":"CLEAR DATA","order":1,"x":106,"y":157,"wires":[["25a87100.e67ade"]]},{"id":"25a87100.e67ade","type":"template","z":"7ed71e0a.377cd8","name":"del data","field":"topic","format":"handlebars","template":"DELETE FROM temperature;","x":497,"y":158,"wires":[["88876ba0.6da6f8"]]},{"id":"601802d8.00c73c","type":"comment","z":"7ed71e0a.377cd8","name":"inserting data to the DB","info":"Whenever there is a change in the reading the data will be inserted into the database\n\nwait for changes.\nwhen data changes send the values.\nadd date in ddmmyy format\nadd time.\nsql injection inside the database.","x":265,"y":44,"wires":[]},{"id":"2746e421.9de08c","type":"comment","z":"7ed71e0a.377cd8","name":"Clear all data in the DB","info":"one code to clear all the data in the database.","x":264,"y":130,"wires":[]},{"id":"4504491a.162438","type":"ui_text_input","z":"7ed71e0a.377cd8","tab":"f7c004a6.665e4","mode":"text","delay":"2000","name":"","topic":"","group":"input","order":"3","x":79,"y":241.5,"wires":[["e5f08a46.fe9c88"]]},{"id":"e5f08a46.fe9c88","type":"template","z":"7ed71e0a.377cd8","name":"retrive data","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT time,temperature FROM temperature WHERE date like {{payload}};","x":481.5,"y":241.5,"wires":[["88876ba0.6da6f8"]]},{"id":"eaccf76b.92c0b8","type":"ui_text","z":"7ed71e0a.377cd8","tab":"f7c004a6.665e4","name":"Text","group":"output","order":1,"format":"{{msg.payload}}","x":804.7222290039062,"y":228.83334350585938,"wires":[]},{"id":"7d925d02.a8173c","type":"comment","z":"7ed71e0a.377cd8","name":"retriving data from the DB","info":"one code to clear all the data in the database.","x":271,"y":205,"wires":[]},{"id":"f2427fd3.a428e","type":"csv","z":"7ed71e0a.377cd8","name":"","sep":",","hdrin":"","hdrout":false,"multi":"one","ret":"\\n","temp":"time, temperature","x":901.9444580078125,"y":54.499969482421875,"wires":[["3d61e007.11ad"]]},{"id":"1df5efda.c7a3","type":"debug","z":"7ed71e0a.377cd8","name":"","active":false,"console":"false","complete":"temperature","x":1071.5,"y":404,"wires":[]},{"id":"4d1aa0b5.7a743","type":"csv","z":"7ed71e0a.377cd8","name":"","sep":",","hdrin":"","hdrout":"","multi":"one","ret":"\\n","temp":"time,","x":770.5556030273438,"y":292.4444274902344,"wires":[["46d6c16f.30e53"]]},{"id":"b3c03490.964338","type":"csv","z":"7ed71e0a.377cd8","name":"","sep":",","hdrin":"","hdrout":false,"multi":"one","ret":"\\n","temp":"temperature,","x":770.5554809570312,"y":339.4443664550781,"wires":[["af067caf.c0dd08"]]},{"id":"8a1028a4.11237","type":"debug","z":"7ed71e0a.377cd8","name":"","active":false,"console":"false","complete":"time","x":1046.666748046875,"y":238.33334350585938,"wires":[]},{"id":"1c9959d5.054a86","type":"ui_template","z":"7ed71e0a.377cd8","tab":"c40d8c91.ae6f3","name":"","group":"graph","order":1,"format":"<html xmlns=\"http://www.w3.org/1999/xhtml\" xml:lang=\"en\" lang=\"en\">\n\n<head>\n\t<title>chart java script</title>\n\t<meta http-equiv=\"content-type\" content=\"text/html;charset=utf-8\" />\n\t<meta name=\"generator\" content=\"Geany 1.23.1\" />\n    <script src=\"https://googledrive.com/host/0B8skak-tVRh0SEFpMHhSWXkzV0k\"></script>\n</head>\n\n<body>\n<canvas id=\"myChart\" width=\"400\" height=\"400\"></canvas>\n<script>\nvar ctx = document.getElementById(\"myChart\");\nvar tm = {{msg.time}};\nvar tem = {{msg.temperature}};\nvar myChart = new Chart(ctx, {\n    type: 'line',\n    data: {\n        labels: [tm],\n        datasets: [{\n            label: '# of Votes',\n            data: [tem]\n        }]\n    },\n    options: {\n        scales: {\n            yAxes: [{\n                ticks: {\n                    beginAtZero:true\n                }\n            }]\n        }\n    }\n});\n</script>\n\n</body>\n\n</html>","storeOutMessages":true,"fwdInMessages":true,"x":1085.4444580078125,"y":359.6666259765625,"wires":[[]]},{"id":"3d61e007.11ad","type":"file","z":"7ed71e0a.377cd8","name":"","filename":"/home/karthhic/Desktop/data.csv","appendNewline":true,"createDir":false,"overwriteFile":"true","x":1022.77783203125,"y":117.2221908569336,"wires":[]},{"id":"85f498d4.ae901","type":"template","z":"7ed71e0a.377cd8","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Strict//EN\"\n\t\"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd\">\n<html xmlns=\"http://www.w3.org/1999/xhtml\" xml:lang=\"en\" lang=\"en\">\n\n<head>\n\t<title>chart java script</title>\n\t<meta http-equiv=\"content-type\" content=\"text/html;charset=utf-8\" />\n\t<meta name=\"generator\" content=\"Geany 1.23.1\" />\n    <script src=\"Chart.js\"></script>\n</head>\n\n<body>\n<canvas id=\"myChart\" width=\"400\" height=\"400\"></canvas>\n<script>\nvar ctx = document.getElementById(\"myChart\");\nvar myChart = new Chart(ctx, {\n    type: 'line',\n    data: {\n        labels: [{{msg.payload}} ],\n        datasets: [{\n            label: '# of Votes',\n            data: [ ]\n        }]\n    },\n    options: {\n        scales: {\n            yAxes: [{\n                ticks: {\n                    beginAtZero:true\n                }\n            }]\n        }\n    }\n});\n</script>\n\n</body>\n\n</html>","x":1080.5,"y":317.5,"wires":[["32199384.9c33d4","2a3de9cd.81567e"]]},{"id":"46d6c16f.30e53","type":"template","z":"7ed71e0a.377cd8","name":"","field":"time","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"{{payload}}","x":895.5,"y":292.5,"wires":[["8a1028a4.11237","85f498d4.ae901","1c9959d5.054a86"]]},{"id":"af067caf.c0dd08","type":"template","z":"7ed71e0a.377cd8","name":"","field":"temperature","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"{{payload}}","x":889,"y":341,"wires":[["1df5efda.c7a3","85f498d4.ae901","1c9959d5.054a86"]]},{"id":"32199384.9c33d4","type":"debug","z":"7ed71e0a.377cd8","name":"","active":true,"console":"false","complete":"false","x":1248.5,"y":317,"wires":[]},{"id":"2a3de9cd.81567e","type":"file","z":"7ed71e0a.377cd8","name":"","filename":"/home/karthhic/Desktop/html/index2.html","appendNewline":true,"createDir":false,"overwriteFile":"true","x":1307,"y":247,"wires":[]}]



Thank you,


BR
Karthhic

[Mark Setrem]

The contrib-ui node has its own Google group. You might want to ask your question there...

https://groups.google.com/forum/m/#!forum/node-red-contrib-ui

[Adam S]

I have a python script that graphs data from in a MySQL database using matplotlib.  Would that be useful?

[Dave C-J]

the default graph in contrib-ui is just time based so not x-y - so currently you would need to look at other options (like Adam's maybe)

[Julian Knight]

You could simply call one of the Google charts or load the client library for Hicharts or any of the other JS based chart libraries. These would be much better than calling out to Python code. X-Y plots are common in those libraries.

[karthhic Mukil]

Dear Adam,

It would be helpful to have your python script.

thank you,

Best Regards,
Karthhic

[Geoffrey Arnold]

Two more options, one using Collectd and the other using Librato:

https://www.npmjs.com/~geoffreyarnold

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.
For more options, visit https://groups.google.com/d/optout.

[Julian Knight]

If you are going to follow a route like that, I'd recommend InfluxDB and Grafana. The first collects the data and works fine with NR. The second is used to produce charts, tables, etc as dashboards from InfluxDB. That's what I'm now using to dashboard both my home environment and the performance of my Pi, NAS, Router and the Internet.

[karthhic Mukil]

Dear all,

I managed to download a html template for the issue, but still i cant able to access the local file from the template.
if the csv is in cloud it is working fine, if it is in local is no working.
this is the code:

<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<script type="text/javascript" src="http://code.jquery.com/jquery-2.1.1.js"></script>
<script type="text/javascript">
$(document).ready(function () {

    $.ajax({
        type: "GET",
        url:"file:///home/karthhic/Desktop/data.csv",
        dataType: "text",
        success: function(data) {processData(data);}
    });

    function processData( allText ) {
        var allLinesArray = allText.split("\n");
        if( allLinesArray.length > 0 ){
            var dataPoints = [];
            for (var i = 0; i <= allLinesArray.length-1; i++) {
                var rowData = allLinesArray[i].split(",");
                dataPoints.push({ label:rowData[0], y:parseInt(rowData[1]) });
            }
            drawChart(dataPoints);
        }
    }

    function drawChart( dataPoints) {
        var chart = new CanvasJS.Chart("chartContainer", {
            title:{
                text: "ID"
            },
             axisY:{
            valueFormatString:"#######.00",
            },
            axisX:{
                labelAngle: 0,
                labelWrap:true,
                labelAutoFit: true,
                labelFontSize: 15,
                labelMaxWidth: 200,
                labelFontColor: "black"
            },
            data: [
            {
                indexLabelPlacement: "outside",
                indexLabelFontWeight: "bold",
                indexLabelFontColor: "black",
                type: "line",
                dataPoints: dataPoints
            }
            ]
        });
        chart.render();
    }
});
</script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/canvasjs/1.7.0/canvasjs.min.js"></script>
</head>
<body style="background-color: #ADB68B; background-image:url(../Images/bg_body_new.png); background-repeat: repeat-x;text-align:center">
<div id="chartContainer" style="height: 800px; width: 100%; background-image:url("fonto1.png"); background-repeat:no-repeat; background-position:center; background-size:100% 100%"></div>
<div style="text-align: center; color:red; font-size:25px;"><b>Chart for the selected time period</b></div>
</body>
</html>

[Julian Knight]

"url:"file:///home/karthhic/Desktop/data.csv","

You can't do that as you are mixing local and remote domains which isn't allowed because it is pretty deadly.

You need to put the CSV file in a folder that is accessible to the Express web server that node-red creates.

If you are using a default setup, the "httpStatic" entry in settings.js tells you where - in relation to the NR user directory - the "public" folder will be, the CSV file needs to go in there. So if the static folder is in "~/.nodered/static/" and you put the csv file there, the URL would be "/data.csv". (assuming you haven't changed the other settings for NR).

The alternative is to send the CSV data to your page by reading it into NR and passing the data via a template node. This has the advantage of not exposing your raw CSV file to the web interface but adds to complexity and memory overheads. If you are simply charting the whole file there is probably no point. But it would be a useful approach if you needed to hide some details or otherwise process the file before sending it for charting.

[Adam S]

Sorry, I just saw your reply.  I'll post my matplotlib code tonight if I remember.

[Adam S]

This is for plotting temps from my smoker, but it should give you a pretty good idea how to modify it for your own use.

import sys
import matplotlib
matplotlib.use('Agg')
from pylab import *
import MySQLdb

cooking_session = ""
meat_temp_goal = 165
temp1_goal = 290

db = MySQLdb.connect("ip.addr.x.x",
                    "user",
                    "pass",
                    "database")
cursor = db.cursor()

query = "SELECT * FROM table"
cursor.execute(query)
result = cursor.fetchall()

time_meat = []
temp_meat = []
temp_goal_meat = []

for record in result:
    time_meat.append(record[0])
    temp_meat.append(record[1])
    temp_goal_meat.append(meat_temp_goal)

plt.plot(time_meat, temp_meat, marker = '.', linestyle='-', color='r', label='Meat Temp')
plt.plot(time_meat, temp_goal_meat, linestyle='--', color='r', label='Meat Temp Goal')

query = "SELECT * FROM table"
cursor.execute(query)
result = cursor.fetchall()

time_temp1 = []
temp_temp1 = []
temp_goal_temp1 = []

for record in result:
    time_temp1.append(record[0])
    temp_temp1.append(record[1])
    temp_goal_temp1.append(temp1_goal)
 
plt.plot(time_temp1, temp_temp1, marker = '.', linestyle='-', color='b', label='Temp 1')
plt.plot(time_temp1, temp_goal_temp1, linestyle='--', color='b', label='Temp 1 Goal')

axes = plt.gca()
axes.set_ylim([60,375])
axes.xaxis.set_major_formatter(DateFormatter('%I:%M %p'))
plt.xlabel('Time')
plt.xticks(rotation=17)
plt.ylabel('Temp')
plt.title("Live Data (Last 5 Minutes)")
plt.legend()
plt.grid(True)

fig = gcf()
DPI = fig.get_dpi()
fig.set_size_inches(12,6.5)
fig.savefig('livegraph.png',dpi = (80))

[karthhic Mukil]

Dear Adam

Thank you for your code. I will learn it and use it.

Best regards,
Karthhic

[karthhic Mukil]

Dear All,

I finally found a solution to get data from db and make a chart.
i exported the data to CSV and stored it in contrib UI Static folder and created a template by using canvasjs.
now it is showing up in contrib-UI. but due to the css settings it only shows a narrow chart which is compatible with mobile.

I have attached my Flow here..

And, Adam once again Thank you for the python code i will use it in another project.

Best Regards,
Karthhic

On Thursday, April 28, 2016 at 7:01:04 AM UTC+3, karthhic Mukil wrote:

[Julian Knight]

I have similar problems with UI. Thankfully the guys working on the next generation that replaces UI have that in hand and there will be a method to control the width.

You can test already if you are happy installing things manually from GitHub. There is another recent thread in this group with some details.