wviewweather.com Forum Index wviewweather.com
wview and Weather Topics
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

What are people using their MySQL/Posgres data for?

 
Post new topic   Reply to topic    wviewweather.com Forum Index -> Everything Else
View previous topic :: View next topic  
Author Message
bhnb



Joined: 28 Nov 2005
Posts: 127

PostPosted: Sun Feb 19, 2006 4:48 pm    Post subject: What are people using their MySQL/Posgres data for? Reply with quote

Hi All

I don't know how many people are gathering data with MySQL, but I was just curious to know what use it's being put to.

Personally I was interested in seeing graphs for non-standard periods so I've glued together my limited knowledge of perl, MySQL and GD to come up with some code to do the job. It's not ready for primetime and is in no way meant to or able to replace the excellent output from wview and on top of that there's at least one serious flaw (it doesn't check for missing data).

There's some sample output graphing 9 days worth of data here: http://www.scorpiocomputing.com/weather/imperial/wvsql.html

If anybody wants to grab a copy of the script it's at
http://www.scorpiocomputing.com/weather/imperial/wvsql.txt - download it and rename it to wvsql.pl

It has hard-coded paths to /root/wvsql and will fail if this directory does not exist or is not accessible. I did it this way to keep out of wview's space.

It grabs your database parameters from /etc/wview/wview.conf so as long as you are gathering the data it should work without modification as long as the path mentioned above exists.

It expects to find /root/wvsql/wvsql.conf - you can grab a sample from (you've guessed it) http://www.scorpiocomputing.com/weather/imperial/wvsql.conf

The script expects the number of days to graph as an argument. e.g.
wvsql.pl 10
would graph 10 days of whatever is specified in wvsql.conf

Not all of the parameters are graphable yet.

Any questions/comments/criticisms gratefully received.

Cheers

Jon
Back to top
View user's profile Send private message
mteel



Joined: 30 Jun 2005
Posts: 435
Location: Collinsville, TX

PostPosted: Sun Feb 19, 2006 6:59 pm    Post subject: Reply with quote

Quote:
I don't know how many people are gathering data with MySQL, but I was just curious to know what use it's being put to.

No use at all Wink - it just makes me feel good that it is there. But I have always assumed there would be some type of data mining tool which could do graphing and complex analysis. I have yet to research this.

Your graphs are pretty cool - I especially like the different sensor combinations you use in your multi-line graphs.

You are a man of many gifts!

Mark
Back to top
View user's profile Send private message Send e-mail Visit poster's website
mhweather



Joined: 07 Aug 2005
Posts: 54

PostPosted: Tue Feb 21, 2006 11:34 am    Post subject: Reply with quote

Nice graphs. I haven't turned on MySQL support yet, but thanks to mteel's station simulator I now feel confident to do it. A nice addition to Wview!

Not knowing anything about SQL coding or stuff, I was looking for ways to generate "top five" types of lists, say 5 highest/lowest temps on station record and the dates/times they occurred. Other fun things would be the greatest temp ranges (low-high) in a day, barometer drops, etc. I haven't looked really hard at it but I wish there were some kind of GUI tool that could maybe extract the data you want and save it in some kind of text or .CSV file.
Back to top
View user's profile Send private message
jensjk



Joined: 27 Feb 2006
Posts: 31
Location: Greenland

PostPosted: Sat Apr 01, 2006 10:02 am    Post subject: Reply with quote

Sounded good to me also - but when trying to perl wvsql.pl (after installing GD) I get:

Can't locate GD/Graph/lines.pm in @INC (@INC contains: /usr/lib/perl5/5.8.7/i386-linux /usr/lib/perl5/5.8.7 /usr/lib/perl5/site_perl/5.8.7/i386-linux /usr/lib/perl5/site_perl/5.8.7 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.7/i386-linux /usr/lib/perl5/vendor_perl/5.8.7 /usr/lib/perl5/vendor_perl/5.8.6 /usr/lib/perl5/vendor_perl/5.8.6/i386-linux /usr/lib/perl5/vendor_perl .) at wvsql.pl line 6.
BEGIN failed--compilation aborted at wvsql.pl line 6.
- must miss something - but what?
_________________
jensjk.dk, Greenland
Back to top
View user's profile Send private message Send e-mail Visit poster's website
bhnb



Joined: 28 Nov 2005
Posts: 127

PostPosted: Sat Apr 01, 2006 1:14 pm    Post subject: Reply with quote

More dependencies I'm afraid, this time it's the GD::Graph module - you can get it from http://search.cpan.org/~bwarfield/GDGraph-1.4307/Graph.pm
Back to top
View user's profile Send private message
jensjk



Joined: 27 Feb 2006
Posts: 31
Location: Greenland

PostPosted: Sat Apr 01, 2006 6:55 pm    Post subject: Reply with quote

Got that (and one more) - now I'm nearly there - but get:
[root@localhost ~]# perl wvsql.pl 7
archive interval: 5 minutes
Query 7 days, starting from 2006-03-25 21:50:36
wviewDB
DBI connect('wviewDB;host=localhost','jjk',...) failed: Access denied for user 'jjk'@'localhost' (using password: YES) at wvsql.pl line 338
Could not connect to database: :errstr at wvsql.pl line 338, <WVPLOT> line 9.

Strange - because my archive interval is 30 minutes (never been 5) and my password - as stated in wview.conf - is jjk, not YES (tried to change it to YES in wviewconfig, same result - no connection).

Really would like to fix this - last? - hurdle -
_________________
jensjk.dk, Greenland
Back to top
View user's profile Send private message Send e-mail Visit poster's website
bhnb



Joined: 28 Nov 2005
Posts: 127

PostPosted: Sun Apr 02, 2006 4:53 am    Post subject: Reply with quote

Ah! The archive interval is hard-coded in this version of the script. Have a look around line 80 where it says
Code:
my $arc=5;
and change the 5 to 30. If the script makes it past version 0.0.1 I'll have it get the correct value automatically. Let me know if this is a problem and I'll post an updated version for you.

In the 'using password: YES' message the YES is an affirmative (the only alternative is 'using password: NO' Wink ). The script picks up your database name, username and password from wview.conf.

To confirm that mysql is working as expected, are you seeing something like this in your messages file?
Quote:
Mar 30 22:00:27 (none) user.info wviewsqld[2116]: <1291127208> : radlib: wviewsqld started as a daemon ...
Mar 30 22:00:27 (none) user.info wviewsqld[2116]: <1291127249> : SQLDB: basic US archiving enabled -
Mar 30 22:00:27 (none) user.info wviewsqld[2116]: <1291127251> : SQLDB: to localhost table wviewDB.archive
Mar 30 22:00:28 (none) user.info wviewsqld[2116]: <1291127863> : SQLDB: beginning initial database sync (this may take some time)...
Mar 30 22:00:28 (none) user.info wviewsqld[2116]: <1291127864> : running...
Mar 30 22:03:12 (none) user.info wviewsqld[2116]: <1291291325> : SQLDB: initial database sync complete:
Mar 30 22:03:12 (none) user.info wviewsqld[2116]: <1291291325> : SQLDB: 1412 inserts, 29433 duplicates, 0 errors


If not, you need to make sure that the values in wview.conf are the same as those you entered when setting up the access to the database in this part of the process:
Quote:
11.1a.3 Create the user account and password for the new database:
GRANT ALL ON wviewDB.* TO wvwuser@localhost IDENTIFIED BY "wvwpasswd";

Note: wvwuser and wvwpasswd should be the same values specified in
wview.conf for SQLDB_USERNAME and SQLDB_PASSWORD respectively.

For example with the information you have given this would be
Code:
GRANT ALL ON wviewDB.* to jjk@localhost IDENTIFIED BY  "***"
(but replace the *** with your real password).
Back to top
View user's profile Send private message
jensjk



Joined: 27 Feb 2006
Posts: 31
Location: Greenland

PostPosted: Sun Apr 02, 2006 9:04 am    Post subject: Reply with quote

Well, tested all that (had done exactly as in the manual) - but it turned ou, that wviewDB was NOT there. Reason? Emitted the semicolon after CREATE DATABASE wviewDB;
- adding that and doing it all over - now wviewDB is there, also confirmed by webmin.

rebuilt radlib and wview 3.2.1 (after first make distclean, and of course sql support, wview.conf is correct also) - but then:

[root@localhost wview-3.2.1]# /etc/init.d/wview start
Starting wview daemons:
[root@localhost wview-3.2.1]# /usr/local/bin/wviewsqld: symbol lookup error: /usr/local/bin/wviewsqld: undefined symbol: raddatabaseOpen

- rebuilt once more, just in case - same problem. So...
- if your patience hangs on -
_________________
jensjk.dk, Greenland
Back to top
View user's profile Send private message Send e-mail Visit poster's website
bhnb



Joined: 28 Nov 2005
Posts: 127

PostPosted: Sun Apr 02, 2006 12:32 pm    Post subject: Reply with quote

Hmmmm.

Could you try this sequence:

root@x:radlib-2.6.2# ./configure --enable-mysql
...
root@x:radlib-2.6.2# make
...
root@x:radlib-2.6.2# make install
...

cd wview-3.2.1

root@x:wview-3.2.1# ./configure --enable-mysql
...
root@x:wview-3.2.1# make
...
root@x:wview-3.2.1# make install

Then start wview.

This should ensure that everything is in the right place as expected.


Good luck!

Jon
Back to top
View user's profile Send private message
jensjk



Joined: 27 Feb 2006
Posts: 31
Location: Greenland

PostPosted: Sun Apr 02, 2006 1:22 pm    Post subject: Reply with quote

How foolish can a man be?
When I saw your reply, I thougt: I have done that - even twice - but - from some distant part of memory instead from the manual, I built --with-mysql instead of --enable-mysql.

So, of course, now everythings works - and I am studying the output on wvsql.htm, with varying no of days (when using anything below ca 80 -including your 9, all graphs are empty - maybe something to do with archive interval 30><5?)

On my way here, I stumbled over a bug: when setting up vpinstall, one has to input lat and long as the actual figure *10 - ie, I live on 64.1, but must input 641 - if using 64.1, I end up at 6.4 -

Thank you for help and advice - I will probably soon be back -
_________________
jensjk.dk, Greenland
Back to top
View user's profile Send private message Send e-mail Visit poster's website
bhnb



Joined: 28 Nov 2005
Posts: 127

PostPosted: Sun Apr 02, 2006 1:30 pm    Post subject: Reply with quote

Well done on getting this going!

Please can you post the output from wvsql? It should give me a clue as to where I have gone wrong.

Thanks

Jon
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    wviewweather.com Forum Index -> Everything Else All times are GMT - 6 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group