firsts charts..

Post by **Snelvuur** » Sat Jul 14, 2007 1:11 am

http://www.binkey.nl/sensors.php

Takes some time to load, since its creating all the images on the fly. (So i still have to figure something out to speed that up)
Some gadgets are still updating, i just received the oregon sensors today (3x)
But i can easilly put everyting in 1 chart, or a different one with a few minutes. (thanks to xpl-perl)

Let me know what you think (although its pretty straightforward)

Post by **Digit** » Sat Jul 14, 2007 2:36 am

[funny-mode on]
What were you doing over there in the living room that made the temp rising so fast around 23:00 ??? [:D]
[funny-mode off]

Automatic scaling is nice, but i like the toilet temp/humidity graphs more than the rest; the other graphs show 'huge' changes, untill you look at the scale...

Personally, i like to keep graphs rather simple. Avoid people saying "wow! that's a beautiful graph!" and by that being distracted from really looking at what is shown.

Nevertheless, congratulations!; it took me some weeks/months till i went from browsing through query results (i am used to that in my daily work) to thinking charts would be more pleasant to look at...

Oh, and 1 tip, from my own experience: speed in creating charts is all about efficiency in the queries you use (MySQL?)

Robert.

Post by **Snelvuur** » Sat Jul 14, 2007 10:54 am

robert, take a look now, the others have more scale too. What i ment with update is that its ont populated for the last 24 hours.

I cant get more speed out of the mysql, most of it is allready optimized, and i also have much more data. I'll just make pictures every 5 minutes or so. Thats just as easy. (but much faster for the viewer) and its running on a 1ghz 320mb machine. (which could use some improvement)

But as you can see for the living room, one reason for moving. Extreme temperatures

Post by **Bwired** » Sat Jul 14, 2007 6:15 pm

Hi Rapidfire,

Im in Italie now, good to see your new home online webpage.
Looks good! I review it better when I get back [:)]
Ciao Pieter

Post by **Snelvuur** » Sat Jul 14, 2007 8:07 pm

Robert, i see what you mean.. for some reason the "toilet" does from 0 to the current degrees. All the other do not, but its exactly the same script. Needs some more work, if i want to do that

Post by **Digit** » Sat Jul 14, 2007 11:35 pm

Check the query results for the toilet. Perhaps the first or last row contains a 0 in the 'data' column. That could cause the toilet behaving different.

One small tip on speed: get your data by executing only 1 query (if you can...).

For example, this is the only query that is executed to retrieve the data for my chart named gas usage over the last 52 weeks (where there is 1 record stored in the data table for every hour):


select datepart(year,[time]),datepart(week,[time]),sum(endvalue-startvalue) from data where LGDEVICEID = 'GC1' group by datepart(year,[time]),datepart(week,[time]) order by datepart(year,[time]) desc,datepart(week,[time]) desc


And make sure the table has the right indexes to cut down the amount of data the SQL service has to inspect.

Post by **Snelvuur** » Sun Jul 15, 2007 12:23 am

Hmm, now the toilet does the same.. i think this is not part of the failure of my query, but i think its more an option in chart director (perl)

SELECT HOUR(FROM_UNIXTIME(msg.time)) as time,
AVG(SUBSTRING(SUBSTRING(body.body FROM 8+POSITION('current=' IN body.body)) FROM 1 FOR -1+POSITION("\n" IN CONCAT
(SUBSTRING(body.body FROM 8+POSITION('current=' IN body.body)),"\n")))) AS value
FROM msg,body WHERE msg.class = 'sensor.basic' AND msg.type = 'xpl-trig' AND
msg.body = body.id AND
msg.time > ? AND
body.body LIKE CONCAT('%%device=',?,'\ntype=',?,'\n%%')
GROUP BY HOUR(FROM_UNIXTIME(msg.time)) ORDER BY msg.time
});

This is the query that the xpl-perl package holds.. also because of the way it stores it from the xpl-sql-logger.

I'am trying to get something like this http://www.binkey.nl/perl/example.pl so you can then click on any of the bars for the normal 24 hour reading. But there is a ton of things you can want/do..

Post by **Bwired** » Sun Jul 15, 2007 8:05 pm

Hi guys,
looks great all the charts, chartdirector in combination with mysql does a good job. I agree with Robert, the sql querie needs to be perfect also don't forget the indexes on tables.
All the charts on Bwired are generated realtime as the user open the page. On the Bwired home page there are 50 queries and some charts generated directly and still a good performance.
Regards Pieter

Post by **Snelvuur** » Sun Jul 15, 2007 11:34 pm

I need a new pc for these kinds of jobs, you have a pretty thick pc for it. At least 4 times faster.. and more memory also helps.

Almost 28 degrees in the living room. Thats with a airco unit (which is full of water every few hours)

Post by **Niknik** » Tue Jul 17, 2007 1:01 pm

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Digit

For example, this is the only query that is executed to retrieve the data for my chart named gas usage over the last 52 weeks (where there is 1 record stored in the data table for every hour):


select datepart(year,[time]),datepart(week,[time]),sum(endvalue-startvalue) from data where LGDEVICEID = 'GC1' group by datepart(year,[time]),datepart(week,[time]) order by datepart(year,[time]) desc,datepart(week,[time]) desc

<hr height="1" noshade id="quote"></blockquote id="quote">I use the same approach in MySql:
SELECT YEAR(pwrDate),week(pwrDate), max(pwrValue)-min(pwrValue) FROM powerlog GROUP BY YEAR(pwrDate),week(pwrDate) ORDER BY YEAR(pwrDate) desc, WEEK(pwrDate) desc

Works very quickly.

Post by **Snelvuur** » Tue Jul 17, 2007 1:38 pm

Pieter has Dual P4 XEON 2.8 MHZ, 2GB mem. That helps too.. what do you guys have?

Post by **Niknik** » Tue Jul 17, 2007 2:33 pm

I run the "home automation" server on much lower spec PC.
It's a 1Ghz Intel PC with just 256Mb Ram and 80Gb HD - but it's a small form factor (book PC), makes no noise and uses relatively low power.

My main PC, although it's a couple of years old, is a different affair:
AMD 3500+ using Cool'n'Quiet, 2Gb Ram, and 6xHard disks in dual raid configuration.
http://www.oxiz.net/lj/mypc2005/mypc.html

I'll consider building a new machine when quad cores become more affordable.
At the moment my next "upgrade" will be a 24" TFT monitor.

Post by **Digit** » Tue Jul 17, 2007 5:41 pm

My HA PC is a AMD Athlon XP 2400+ with 256 MB RAM. It handles the 'everything' regarding Home Automation. I had it standing here with no purpose, so it wasn't because i thought i'd need this much "power". Could do with much less actually.

Post by **Niknik** » Tue Jul 17, 2007 6:02 pm

we're taking this "off-topic" for this thread

I have created a new thread to discuss this, and also what sort of "backup" plans you have in place, in case your "Home PC" fails.

http://www.domoticaforum.eu/topic.asp?TOPIC_ID=405

Post by **Snelvuur** » Wed Aug 01, 2007 12:05 am

Well the old layout was a bit "easy" so went with a cms instead, www.binkey.nl looks better now in my opinion but i still need more hardware to show