I've used the Google graphs to find problems with my power usage and distribution for a couple of years now. Nice system. There are other graphing systems out there, but each of them is different from the others and one has to settle on something to work with. I just chose Google, not because it's the best, but because I think it might continue to exist for a while and has reasonable documentation. Oh, it's also free.
At any rate, most of the graphing software takes a table as input. That presents a particular problem for us inexperienced web programmers. What the heck is a table and how do we make one? Once we understand that to some degree, how do we get the data from Xively and put it into a table? This little bit of knowledge is hard to come by. I went through several iterations of trying to get data, format it into something to give to the Google API and see what was displayed. I came up with this:
The Web Page
<html>
<title>Desert Home Day Graph</title>
<head>
</head>
<body>
<!--somehow, SUN thought this was better than the c include statement....sigh -->
<!--These are the google api files that you need for their tables and graphs -->
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
<script type='text/javascript' src='https://www.google.com/jsapi'></script>
<script type='text/javascript'>
google.load('visualization', '1', {'packages':['annotatedtimeline']});
</script>
<script type="text/javascript">
//I stole this function almost verbatim from the Pachube web site.
//It formats the date into a form I like
function adjustdateformat(incoming){
if (typeof(incoming) != "function") { //no scripts allowed in data
var ts_parts = incoming.split('T');
var d_parts = ts_parts[0].split('-');
var t_parts = ts_parts[1].split(':');
var d = new Date(Date.UTC(parseInt(d_parts[0],10), parseInt(d_parts[1],10)-1 ,parseInt(d_parts[2],10), parseInt(t_parts[0],10), parseInt(t_parts[1],10), parseInt(t_parts[2],10) ) );
return(d);
} else {
alert("There was a script in the data !!");
return(0); //for when there is a script
}
}
// this creates the table that will be stuffed with data returned
var data = new google.visualization.DataTable();
// The google data api doesn't have a call to return a column id by name
// so I had to invent this one
function getColumnIdByName(name){
var idx;
var thisname;
var lastOne = data.getNumberOfColumns();
for(idx = 0; idx < lastOne; idx++){
if(name.toString() == data.getColumnLabel(idx)){
return(idx);
}
}
// I really don't like magic values like this, but it'll do for now.
return(99);
}
// This parses into the json data to grab values.
// there's documentation all over the web on the json format
// but it has to be parsed to use it
function climbtree(archivedData){
// this will create the table for the graph
if (typeof(archivedData.datastreams[0].datapoints) == 'undefined'){
alert("No archived data returned");
return;
}
$.each(archivedData.datastreams, function(key, value){
// key 0 has the first datastream and the times for the table.
// you'll always get this if there is data to deliver
if(key == 0){ // only add a date column for the first datastream
//alert("I got key 0 ");
if (data.getNumberOfColumns() == 0){
data.addColumn('datetime', 'Date');
data.addColumn('number', value.tags);
}
$.each(value.datapoints, function(key,value){
//alert("got an at value of" + this.at);
var d = adjustdateformat(value.at);
var level = value.value;
var thisrow = data.addRow();
data.setValue(thisrow, 0, d);
data.setValue(thisrow, 1, parseFloat(level));
})
} else {
//second, third, etc datastreams
//alert("got another key = " + value.tags);
var columnIdx;
if((columnIdx = getColumnIdByName(value.tags)) == 99){
columnIdx = data.addColumn('number', value.tags); //new datastream came in
}
$.each(value.datapoints, function(key,value){
var d = adjustdateformat(value.at); //format its datestamp the same way
var rowIdx = data.getFilteredRows([{column:0,value:d}]); //and find it in the table
var level = parseFloat(value.value);
data.setValue(rowIdx[0], columnIdx, level);
})
}
})
}
</script>
<!-- this actually goes and gets the data. What I'm doing is grabbing the data
for two items: Real Power and Outside Temperature. Since Cosm has a limit on
the number of items they will return, I do it in six hour intervals. So, a day
will loop four times and two days 8, etc. There's also a limit on other stuff,
so the most you can get is around a week. If you want more, you'll have to do
some more work to handle it. Remember though, there's a lot of data here and
it will take some time to get it back from the site. That's why I prefer server
generated charts. The server has the data right there and can access it quickly
when we go get it, it takes time for the chunks to come to us.
-->
<script type="text/javascript">
$(document).ready(function(){
var url='http://api.pachube.com/v2/feeds/9511.json?&key=GtGuoMKJSqv2tzqGvWaITLhlEDUxrYXSixqPSmlyj-s&per_page=1000&datastreams=0,7&duration=6hours&interval=60';
var databack;
var startpoint = '';
var loopcount;
if(queryString["day"] == undefined)
loopcount = 4;
else
loopcount = (queryString["day"] * 4) + 1;
//alert('loopcount = ' + loopcount);
for ( var i=0; i<loopcount; i++ ){
//alert('going for ' + url + startpoint);
$.ajax({
type: 'GET',
url: url + startpoint,
dataType: 'json',
data: {},
async: false, // you have to have this so the data will arrive before display
success: function(dataarchive) {
//alert("the url should have returned");
if (databack = (typeof(dataarchive.datastreams[0].datapoints) != 'undefined')){
var len = dataarchive.datastreams[0].datapoints.length;
//alert("got back " + len + " items");
//alert("from " + dataarchive.datastreams[0].datapoints[0].at +
// "to " + dataarchive.datastreams[0].datapoints[len - 1].at);
startpoint = "&end=" + dataarchive.datastreams[0].datapoints[0].at;
climbtree(dataarchive);
};
}
});
}
//alert('after the get loop');
var chart = new google.visualization.AnnotatedTimeLine(document.getElementById('chartholder'));
// OK, now I've got a chart, but need to rearrange the columns
// this is a matter of taste, so adjust as needed.
var chartView = new google.visualization.DataView(data);
// this sets the order of the columns
chartView.setColumns([0,2,1]);
chart.draw(chartView, {displayAnnotations: true, 'scaleType':'allfixed','scaleColumns': 'allmaximized', 'scaleColumns': [0,1]});
});
</script>
<!--
This little thing allows you to put a parameter in the URL to get
multiple days. Use a ? then the number of days i.e. ?day=2 will get
you two days worth of data.
-->
<script type="text/javascript">
var queryString = new Array();
var parameters = window.location.search.substring(1).split('&');
for (var i=0; i<parameters.length; i++) {
var pos = parameters[i].indexOf('=');
// If there is an equal sign, separate the parameter into the name and value,
// and store it into the queryString array.
if (pos > 0) {
var paramname = parameters[i].substring(0,pos);
var paramval = parameters[i].substring(pos+1);
queryString[paramname] = unescape(paramval.replace(/\+/g,' '));
}
else {
//special value when there is a querystring parameter with no value
queryString[parameters[i]]="[nil]"
}
}
</script>
<!-- and the actual chart declaration; change this for size and stuff -->
<div id='chartholder' style='width:100%; height:310px;'> </div>
</body>
</html>
<title>Desert Home Day Graph</title>
<head>
</head>
<body>
<!--somehow, SUN thought this was better than the c include statement....sigh -->
<!--These are the google api files that you need for their tables and graphs -->
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
<script type='text/javascript' src='https://www.google.com/jsapi'></script>
<script type='text/javascript'>
google.load('visualization', '1', {'packages':['annotatedtimeline']});
</script>
<script type="text/javascript">
//I stole this function almost verbatim from the Pachube web site.
//It formats the date into a form I like
function adjustdateformat(incoming){
if (typeof(incoming) != "function") { //no scripts allowed in data
var ts_parts = incoming.split('T');
var d_parts = ts_parts[0].split('-');
var t_parts = ts_parts[1].split(':');
var d = new Date(Date.UTC(parseInt(d_parts[0],10), parseInt(d_parts[1],10)-1 ,parseInt(d_parts[2],10), parseInt(t_parts[0],10), parseInt(t_parts[1],10), parseInt(t_parts[2],10) ) );
return(d);
} else {
alert("There was a script in the data !!");
return(0); //for when there is a script
}
}
// this creates the table that will be stuffed with data returned
var data = new google.visualization.DataTable();
// The google data api doesn't have a call to return a column id by name
// so I had to invent this one
function getColumnIdByName(name){
var idx;
var thisname;
var lastOne = data.getNumberOfColumns();
for(idx = 0; idx < lastOne; idx++){
if(name.toString() == data.getColumnLabel(idx)){
return(idx);
}
}
// I really don't like magic values like this, but it'll do for now.
return(99);
}
// This parses into the json data to grab values.
// there's documentation all over the web on the json format
// but it has to be parsed to use it
function climbtree(archivedData){
// this will create the table for the graph
if (typeof(archivedData.datastreams[0].datapoints) == 'undefined'){
alert("No archived data returned");
return;
}
$.each(archivedData.datastreams, function(key, value){
// key 0 has the first datastream and the times for the table.
// you'll always get this if there is data to deliver
if(key == 0){ // only add a date column for the first datastream
//alert("I got key 0 ");
if (data.getNumberOfColumns() == 0){
data.addColumn('datetime', 'Date');
data.addColumn('number', value.tags);
}
$.each(value.datapoints, function(key,value){
//alert("got an at value of" + this.at);
var d = adjustdateformat(value.at);
var level = value.value;
var thisrow = data.addRow();
data.setValue(thisrow, 0, d);
data.setValue(thisrow, 1, parseFloat(level));
})
} else {
//second, third, etc datastreams
//alert("got another key = " + value.tags);
var columnIdx;
if((columnIdx = getColumnIdByName(value.tags)) == 99){
columnIdx = data.addColumn('number', value.tags); //new datastream came in
}
$.each(value.datapoints, function(key,value){
var d = adjustdateformat(value.at); //format its datestamp the same way
var rowIdx = data.getFilteredRows([{column:0,value:d}]); //and find it in the table
var level = parseFloat(value.value);
data.setValue(rowIdx[0], columnIdx, level);
})
}
})
}
</script>
<!-- this actually goes and gets the data. What I'm doing is grabbing the data
for two items: Real Power and Outside Temperature. Since Cosm has a limit on
the number of items they will return, I do it in six hour intervals. So, a day
will loop four times and two days 8, etc. There's also a limit on other stuff,
so the most you can get is around a week. If you want more, you'll have to do
some more work to handle it. Remember though, there's a lot of data here and
it will take some time to get it back from the site. That's why I prefer server
generated charts. The server has the data right there and can access it quickly
when we go get it, it takes time for the chunks to come to us.
-->
<script type="text/javascript">
$(document).ready(function(){
var url='http://api.pachube.com/v2/feeds/9511.json?&key=GtGuoMKJSqv2tzqGvWaITLhlEDUxrYXSixqPSmlyj-s&per_page=1000&datastreams=0,7&duration=6hours&interval=60';
var databack;
var startpoint = '';
var loopcount;
if(queryString["day"] == undefined)
loopcount = 4;
else
loopcount = (queryString["day"] * 4) + 1;
//alert('loopcount = ' + loopcount);
for ( var i=0; i<loopcount; i++ ){
//alert('going for ' + url + startpoint);
$.ajax({
type: 'GET',
url: url + startpoint,
dataType: 'json',
data: {},
async: false, // you have to have this so the data will arrive before display
success: function(dataarchive) {
//alert("the url should have returned");
if (databack = (typeof(dataarchive.datastreams[0].datapoints) != 'undefined')){
var len = dataarchive.datastreams[0].datapoints.length;
//alert("got back " + len + " items");
//alert("from " + dataarchive.datastreams[0].datapoints[0].at +
// "to " + dataarchive.datastreams[0].datapoints[len - 1].at);
startpoint = "&end=" + dataarchive.datastreams[0].datapoints[0].at;
climbtree(dataarchive);
};
}
});
}
//alert('after the get loop');
var chart = new google.visualization.AnnotatedTimeLine(document.getElementById('chartholder'));
// OK, now I've got a chart, but need to rearrange the columns
// this is a matter of taste, so adjust as needed.
var chartView = new google.visualization.DataView(data);
// this sets the order of the columns
chartView.setColumns([0,2,1]);
chart.draw(chartView, {displayAnnotations: true, 'scaleType':'allfixed','scaleColumns': 'allmaximized', 'scaleColumns': [0,1]});
});
</script>
<!--
This little thing allows you to put a parameter in the URL to get
multiple days. Use a ? then the number of days i.e. ?day=2 will get
you two days worth of data.
-->
<script type="text/javascript">
var queryString = new Array();
var parameters = window.location.search.substring(1).split('&');
for (var i=0; i<parameters.length; i++) {
var pos = parameters[i].indexOf('=');
// If there is an equal sign, separate the parameter into the name and value,
// and store it into the queryString array.
if (pos > 0) {
var paramname = parameters[i].substring(0,pos);
var paramval = parameters[i].substring(pos+1);
queryString[paramname] = unescape(paramval.replace(/\+/g,' '));
}
else {
//special value when there is a querystring parameter with no value
queryString[parameters[i]]="[nil]"
}
}
</script>
<!-- and the actual chart declaration; change this for size and stuff -->
<div id='chartholder' style='width:100%; height:310px;'> </div>
</body>
</html>
This is the actual web page I created to display a days worth of data on power usage and outside temperature taken from my sensors. To use it, just copy the code into a file, save it as html (htm, whatever) and then click on it. It'll load my data and give you a graph you can play with. Then, modify it to use your feed id, key, and other stuff to display your own data. It can serve as a template for your own graph as well.
If you want to use it as part of a web page with other information, pictures, etc, just put it into an iframe and include the page. I keep this page on Dropbox so I can use it from anywhere, even my tablet. Some browsers have a little trouble with iframes (chrome) others have trouble with Google charts (Opera, Silk), so your mileage may vary, but it can certainly serve as an example.
This is what you will get with a line like:
<iframe width="100%" height="325" scrolling="No" seamless frameBorder="0"
src="http://dl.dropboxusercontent.com/u/128855213/CosmGraphPower.html"> </iframe>
And yes, the src attribute up there points to the page I have on Dropbox to show this page. Feel free to grab the source directly from there as well.
As I said before, my web programming experience is slim, so there may well be better, more elegant ways to do this. If so, leave a comment for other folks (and me!) to help make our lives simpler. For example, you have no idea what a pain in the butt it was to get this page to work using the tools that Blogger provides. Their editors kept changing things and messing up the display. I finally got it to work, but I haven't tried it on multiple browsers so, it may be messed up. Look at it in FireFox, it seems to be the best with this display.
Will this work with more descriptive channel names or just numeric identifiers? I've played with it a bit and haven't had much luck...
ReplyDeleteActually, I don't think it will without mucking around a bit. It's based on the way Xively worked back in the days when it was Pachube and their api was easier to understand. So, it goes after field 1, field 2, etc.
DeleteYou can try playing with the actual URL sent to Xively a bit and see what comes back though; at least that part's pretty easy to try out.
Late to the party, but named datastreams do work. ...&datastreams=Power_Usage,Back_Porch&... no problem. Interestingly, when I add a third datastream the chart has "gaps" of ~ 20 minutes where the line spans point-to-point even though Xively graphs show the "missing" data. Adding a fourth datastream increases the "gap" to ~120 minutes.
DeleteThat sounds like a problem I had once where my conversion of the date coming back was messed up. Take a look at the JSON you get back and then at the method used to reformat it for the chart. I severly messed that up a couple of times.
DeleteI'm having trouble getting this to work for my xively account. When I change the feed and key on the "var url" declaration to my device values, no graph appears.. I am very new to HTML, are there any other variables I need to change to make this code work for me?
ReplyDeleteThat should be all you need to get it going. Try using the Chrome brower, and when it fails, right click on the screen and 'examine element'. When you get the element window open, click on console and it should show you an error message. See what that is and it will give you a clue.
DeleteThe code above needs at least 24 hours of data accumulated on Xively before it will work though. So, if you just started putting data up there for the feeds you're trying to read, just wait a day and try it again. I didn't realize I had that problem until recently because I've always had a years worth of data on Xively and never encountered the problem.
Hi Dave,
ReplyDeletehave two problems with the html. I have tried to just change the feed ID and the API key. Then ask me to give user and password. (why? :) maybe should change the feed to public access? )
When HTML run under IE it says datastreams[0] is null or not an object...(without changing anything, your code run fine)
Can you help me with this?
Thanks!
Gabor
Sorry for seeing this late, you've probably fixed it already. Yes, you need to make a public read only key for it. If you prowl through the Xively documentation long enough you'll run across this.
Delete