Taking the DataMine logs further

Plugins & Plugin Development Unmaintained
21

FYI, I have setup my development environment and I am getting my feet wet on lua development. Coming from a strongly typed language (C), it makes me feel insecure to dynamically define structures and arrays and trust the LUA interpreter to clean up after me, but I think I can get used to that.

So far, debugging has been fun and I have only scratched the surface with the following code
print(_G.dmDB_handler (“”, {id=“lr_dmDB”,device=“43”,name=“Watts”,format=“csv”,dt=“week”}, “”))

I changed the way csv works for me since I want to be able to import that directly into excel and have the dates formatted appropriately.

Next on my list is to understand how the graph is generated and possibly improve it. When I try to generate a certain graph, onle one scale is used (where dataMine defines 2). See how the graph looks like:
http://i.imgur.com/6a44Psj.png

By using a visualisation code like this:

var w = new google.visualization.ChartWrapper(
{
chartType:'LineChart',
options:{
	height:600,
	title:'Energie VS Temperature',
	legend:'bottom',
	vAxes :{
			0: {format: '#'},
			1 : {format: '#,###'}
		
	},
	series: {
				0:{ type: "lineChart", targetAxisIndex: 0 },
				1: { type: "lineChart", targetAxisIndex: 1}
			}									
	
},
containerId:'gVizDiv'
}
);

I can get a graph like this
http://i.imgur.com/7mFJK62.png

Also, I’ll try to to pull the raw data from defined graphics and export them in CSV… e.g. support a line similar to this:
http://192.168.10.199:3480/data_request?id=lr_dmDB&graph=2&interval=week&format=csv

This is to allow plotting multiple values in Excel with multiple Y axis scales (for example Watts in 1000s and outside temperature (10s of C)) and have a moving average of power over temperature.

For example the graph below is a starting point without temperature for now:
http://i.imgur.com/3SyPPyt.png (this is a URL, as I didn’t know how to embed the picture)

On a side note, I just found another plugin visualization that can be used with gViz and that natively does moving average to remove spikes…
http://dygraphs.com/tutorial.html

22

Oh, this is really excellent work on the graphics!

I have to say that my attentions are now on the next-generation dataMine, with the prototype code I have posted called DataYours, but the plotting is the same so that’s good. It interfaces to both dataMine and a Graphite / Whisper round-robin database. See [url=http://forum.micasaverde.com/index.php/topic,23109.0.html]http://forum.micasaverde.com/index.php/topic,23109.0.html[/url]

The multi-channel data retrieval (in csv, json, and svg formats) is also to be included there, but I haven’t started working on the graphics, so delighted to share.

The gviz package was slightly obfuscated - I can post a clean version if it would help you.

Welcome aboard!

23

Here is a new test I did with dygraph visualization package and moving average.

The Raw data:

with a roll period of 100

with a roll period of 150

Here is the code

[code]

var chart1 = new Dygraph.GVizChart(
document.getElementById(‘dygraphs’)).draw(dt,
{
rollPeriod: 100,
height:400,
showRoller: true,
labels: [ ‘Date’, ‘Temperature’, ‘Energy’],
‘Temperature’: {
axis: {
}
},
‘Energy’: {
axis: ‘Energy’
},
xAxisLabelWidth: 100,
yAxisLabelWidth: 100,
ylabel: ‘Energy’,
y2label: ‘Temperature’
});

}[/code]

24

Yes, that’s a nice picture, but I think the rolling average shifts the features in the data? (unless it’s just the axes not aligning with the gviz plot.)

You may need something more sophisticated than a graphics package for data processing if you really want to squeeze the most out these types of data - Kalman filtering or “Holt-Winters double exponential smoothing” would perhaps be appropriate here, because they estimate the current value of the data without the time shift inherent in a simple window (unless, of course, the window is non-causal and uses points in the future to estimate the value now.)

But I do like the graphics package.

25

I understand how data is skewed by the moving average (the energy usage peak on the 17th occurred between 7:00 and 7:30 and the graph package shows it at 10:00 AM). What it did allow was to at least get a “feeling” of how big of an impact outside temperature has on the energy consumption.

Now I think I am ready to start looking at your other work. :slight_smile:

26

You can correct the moving average by shifting the data back in time by 1/2 the length of the averaging window width.

27

True enough - but you can’t do that in real time!

28

Try telling that to the Doctor ;D

Some useful sound files:
http://www.maddmansrealm.com/drwho/tardis/sounds/sounds.htm

29

that’s “Wibbly Wobbly Timey Wimey” time, not real time. :smiley:

30

Gentlemen:

Thanks for the continued great work on the plugins. I apologize in advance for the newbie questions, but I have made some good progress installing datamine, logging data to my NAS, and now installing dmDB. I’m a decent embedded programmer, but my work has all be in C and C++. I haven’t dove in to learn the vera language and tools yet.

I’m confused about the dmDB web interface. I have been able to successfully use the “report=channels” and the graph interface. From reading the logs it looks to me like I should be able to also bring up the raw data from the individual nodes I am recording. I can’t seem to interpret the postings to see if this is supported and my attempts to craft a query string to the http interface have failed.

I installed the files included in this posting (dmDB, dmDBserver, dmDBserver2) and included the LUA startup line require “dmDBserver”.

Can I craft a query string that will allow me to dump the data from one of my datamine channels in (time,value) pairs and log it off into a csv file? Or would my approach be to write vera code to do that dump for me?

Ideally what I am looking to do is craft some additional graphs beyond those supported in datamine and merge datamine data with another data source. I thought about trying to push data from outside of vera into the datamine database and fake out the database to think it was native vera channels. In the end I thought it might be better to pull the values out of datamine and intergrate my other data that way. Thus my dmDB investigation.

My solar panel array isn’t logging data into vera. I’m in the process of getting an RS485 link running to the panels to get that data via my PC. Hoping to show an overall energy picture between consumption (logged in datamine) and generation (logged in a csv file on my PC).

Thanks in advance for any help!

31

You should be aware that, in fact, dataMine is not now under further development. You are in danger, therefore, of steering yourself into a dead end if you go down this route.

I'm confused about the dmDB web interface. I have been able to successfully use the "report=channels" and the graph interface. From reading the logs it looks to me like I should be able to also bring up the raw data from the individual nodes I am recording. I can't seem to interpret the postings to see if this is supported and my attempts to craft a query string to the http interface have failed.
Hmmm... this should work. See the more explicit example here: [url=http://forum.micasaverde.com/index.php/topic,23109.msg157683.html#msg157683]http://forum.micasaverde.com/index.php/topic,23109.msg157683.html#msg157683[/url]
I installed the files included in this posting (dmDB, dmDBserver, dmDBserver2) and included the LUA startup line require "dmDBserver".
Not clear from this which version you've actually installed.
Can I craft a query string that will allow me to dump the data from one of my datamine channels in (time,value) pairs and log it off into a csv file? Or would my approach be to write vera code to do that dump for me?
Answer to first question, as per above, is "yes". You could also do the latter - the API is straight-forward (being a subset of the Berkeley DB API) and Lua is a small (but sophisticated) language, infinitely better than C or C++.
Ideally what I am looking to do is craft some additional graphs beyond those supported in datamine and merge datamine data with another data source. I thought about trying to push data from outside of vera into the datamine database and fake out the database to think it was native vera channels. In the end I thought it might be better to pull the values out of datamine and intergrate my other data that way. Thus my dmDB investigation.
A wise choice not to try and push data into dataMine. I've written a read-only interface. The DB structure is spread over many files (one per week for each data channel, and two index configuration files). Could I suggest you take a look at DataYours, which would make it very easy to write new data into (it's a one-liner), to extract CSV data, or whatever else.
My solar panel array isn't logging data into vera. I'm in the process of getting an RS485 link running to the panels to get that data via my PC. Hoping to show an overall energy picture between consumption (logged in datamine) and generation (logged in a csv file on my PC).

This is one thing I am doing too - recently installed solar PV and have just crossed the 500 kWh mark on generated energy. (See attached plot from DataYours.)