Author Topic: How to store MeteoBridge data in a MySQL Database  (Read 5064 times)

0 Members and 1 Guest are viewing this topic.

Offline SteveJ

  • Senior Member
  • **
  • Posts: 95
    • My Blog
How to store MeteoBridge data in a MySQL Database
« on: February 15, 2015, 05:46:04 PM »
For those who might be MySQL and/or MeteoBridge novices, I wrote this how-to on my blog that walks through connecting MeteoBridge to a MySQL database.

The article also includes an example meteobridge.sql dump file (stored as a Gist on GitHub) that can be easily imported into MySQL to create an example database, along with a matching INSERT query ready to paste into MeteoBridge with imperial data conversion and formatting (Fahrenheit, wind in MPH, rainfall in inches, etc.)

http://www.stevejenkins.com/blog/2015/02/storing-weather-station-data-mysql-meteobridge/

Please feel free to comment here and/or on the blog article itself.
My LinkedIn

Weather.LakeWebster.com

WU: KMAPLE47 | PWS: JNKNS | CWOP: EW6690 | WXBug: p22113


Offline jlmr

  • Member
  • *
  • Posts: 11
Re: How to store MeteoBridge data in a MySQL Database
« Reply #1 on: August 30, 2016, 11:51:17 PM »
Finally got this to work Thank you
No I had problems getting it to work using your sql, it came down to the end where you had
Code: [Select]
-- AUTO_INCREMENT for table `mystation`
--
ALTER TABLE `lakewebster`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;

Once i changed 
Code: [Select]
ALTER TABLE `lakewebster` to 'mystation'  it started working

Thanks again


Davis Vantage Pro 2 
CumulusMX
http://youngstownweather.com

Offline SteveJ

  • Senior Member
  • **
  • Posts: 95
    • My Blog
Re: How to store MeteoBridge data in a MySQL Database
« Reply #2 on: August 31, 2016, 12:45:50 AM »
Derp. That's my bad. That was an old table name, and I forgot to update it in the Gist.

It's fixed now for anyone else who stumbles upon it. Thanks for the catch!
My LinkedIn

Weather.LakeWebster.com

WU: KMAPLE47 | PWS: JNKNS | CWOP: EW6690 | WXBug: p22113


Offline jalmz

  • Senior Member
  • **
  • Posts: 59
    • BiliranIsland.com
Re: How to store MeteoBridge data in a MySQL Database
« Reply #3 on: September 18, 2016, 10:20:58 PM »
Hi Steve,

Good day, how i add the davis pro 2 Forecast? in the mysql. thanks

Offline Jáchym

  • Meteotemplate Developer
  • Forecaster
  • *****
  • Posts: 8572
    • Meteotemplate
Re: How to store MeteoBridge data in a MySQL Database
« Reply #4 on: September 19, 2016, 09:47:13 AM »
Do you want to save your station values to MySQL? Im not sure you can save the forecast, plus I dont see any point in doing that. It changes and Im not even sure Meteobridge can read this data. The MySQL is primarily for saving the actual station measured values.

Offline jalmz

  • Senior Member
  • **
  • Posts: 59
    • BiliranIsland.com
Re: How to store MeteoBridge data in a MySQL Database
« Reply #5 on: September 19, 2016, 10:32:48 AM »
Yeah i was able to fetch the forecast..

Code: [Select]
CREATE TABLE IF NOT EXISTS `stationname` (
  `ID` int(11) NOT NULL,
  `DateTime` datetime NOT NULL COMMENT 'Date and Time of Readings',
  `TempOutCur` decimal(4,1) NOT NULL COMMENT 'Current Outdoor Temperature',
  `HumOutCur` int(11) NOT NULL COMMENT 'Current Outdoor Humidity',
  `PressCur` decimal(4,2) NOT NULL COMMENT 'Current Barometric Pressure',
  `DewCur` decimal(4,1) NOT NULL COMMENT 'Current Dew Point',
  `HeatIdxCur` decimal(4,1) NOT NULL COMMENT 'Current Heat Index',
  `WindChillCur` decimal(4,1) NOT NULL COMMENT 'Current Wind Chill',
  `TempInCur` decimal(4,1) NOT NULL COMMENT 'Current Indoor Temperature',
  `HumInCur` int(11) NOT NULL COMMENT 'Current Indoor Humidity',
  `WindSpeedCur` decimal(4,1) NOT NULL COMMENT 'Current Wind Speed',
  `WindAvgSpeedCur` decimal(4,1) NOT NULL COMMENT 'Current Average Wind Speed',
  `WindDirCur` int(11) NOT NULL COMMENT 'Current Wind Direction (Degrees)',
  `WindDirCurEng` varchar(3) NOT NULL COMMENT 'Current Wind Direction (English)',
  `WindGust10` decimal(4,1) NOT NULL COMMENT 'Max Wind Gust for Past 10 Mins',
  `WindDirAvg10` int(11) NOT NULL COMMENT 'Average Wind Direction (Degrees) for Past 10 Mins',
  `WindDirAvg10Eng` varchar(3) NOT NULL COMMENT 'Average Wind Direction (English) for Past 10 Mins',
  `RainRateCur` decimal(5,2) NOT NULL COMMENT 'Current Rain Rate',
  `RainDay` decimal(4,2) NOT NULL COMMENT 'Total Rain for Today',
  `RainYest` decimal(4,2) NOT NULL COMMENT 'Total Rain for Yesterday',
  `RainMonth` decimal(5,2) NOT NULL COMMENT 'Total Rain this Month',
  `RainYear` decimal(5,2) NOT NULL COMMENT 'Total Rain this Year',
  `Forecast` VARCHAR(255) NOT NULL COMMENT 'Davis Forecast'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


I can successfully get the forecast while the code below

Im still struggling on Sunset, Sunrise and Barometer trend.. it will give me an error..

Code: [Select]
`Sunrise` datetime NOT NULL COMMENT 'Sunrise',
`Sunset` datetime NOT NULL COMMENT 'Sunset',
`BarometerTrend` char(15) COMMENT 'Barometer Trend'

Any suggestion?


« Last Edit: September 19, 2016, 10:35:00 AM by jalmz »

Offline Jáchym

  • Meteotemplate Developer
  • Forecaster
  • *****
  • Posts: 8572
    • Meteotemplate
Re: How to store MeteoBridge data in a MySQL Database
« Reply #6 on: September 19, 2016, 10:53:17 AM »
What error

Offline jalmz

  • Senior Member
  • **
  • Posts: 59
    • BiliranIsland.com
Re: How to store MeteoBridge data in a MySQL Database
« Reply #7 on: September 19, 2016, 10:58:53 AM »
Code: [Select]
2016-09-19 22:55:27 Error: MYSQL query 'INSERT INTO `mystation` (`ID`, `DateTime`, `TempOutCur`, `HumOutCur`, `PressCur`, `DewCur`, `HeatIdxCur`, `WindChillCur`, `TempInCur`, `HumInCur`, `WindSpeedCur`, `WindAvgSpeedCur`, `WindDirCur`, `WindDirCurEng`, `WindGust10`, `WindDirAvg10`, `WindDirAvg10Eng`, `RainRateCur`, `RainDay`, `RainYest`, `RainMonth`, `RainYear`, `Forecast`) VALUES (NULL, '2016-09-19 22:55:27', '27.0', '89.0', '29.71', '25.0', '30.6', '27.0', '31.6', '64.0', '0.0', '1.4', '94.0', 'E', '4.7', '94.0', 'E', '0.00', '0.15', '0.00', '0.15', '0.15','Mostly cloudy and cooler. Windy with possible wind shift to the W, NW, or N.','05:32')' failed: Column count doesn't match value count at row 1 (no more tries):
I change the Sunrise from datetime to time.. still error.. hmmp

I already fixec it..

Code: [Select]
INSERT INTO `biliranisland` (`ID`, `DateTime`, `TempOutCur`, `HumOutCur`, `PressCur`, `DewCur`, `HeatIdxCur`, `WindChillCur`, `TempInCur`, `HumInCur`, `WindSpeedCur`, `WindAvgSpeedCur`, `WindDirCur`, `WindDirCurEng`, `WindGust10`, `WindDirAvg10`, `WindDirAvg10Eng`, `RainRateCur`, `RainDay`, `RainYest`, `RainMonth`, `RainYear`, `Forecast`,`Sunrise`) VALUES (NULL, '[YYYY]-[MM]-[DD] [hh]:[mm]:[ss]', '[th0temp-act]', '[th0hum-act]', '[thb0seapress-act=inHg.2]', '[th0dew-act]', '[th0heatindex-act]', '[wind0chill-act]', '[thb0temp-act]', '[thb0hum-act]', '[wind0wind-act=kmh]', '[wind0avgwind-act=kmh]', '[wind0dir-act]', '[wind0dir-act=endir]', '[wind0wind-max10=kmh]', '[wind0dir-avg10]', '[wind0dir-avg10=endir]', '[rain0rate-act=in.2]', '[rain0total-daysum=in.2]', '[rain0total-ydaysum=in.2]', '[rain0total-monthsum=in.2]', '[rain0total-yearsum=in.2]','[forecast-text]','[mbsystem-sunrise]')
missing `Sunrise` in the query..
by the way.. Time or DayTime?
« Last Edit: September 19, 2016, 11:06:44 AM by jalmz »

Offline Jáchym

  • Meteotemplate Developer
  • Forecaster
  • *****
  • Posts: 8572
    • Meteotemplate
Re: How to store MeteoBridge data in a MySQL Database
« Reply #8 on: September 19, 2016, 11:26:04 AM »
DayTime? There is no such thing as far as I know.

Offline jalmz

  • Senior Member
  • **
  • Posts: 59
    • BiliranIsland.com
Re: How to store MeteoBridge data in a MySQL Database
« Reply #9 on: September 19, 2016, 11:28:49 AM »
i mean datetime...


Offline Jáchym

  • Meteotemplate Developer
  • Forecaster
  • *****
  • Posts: 8572
    • Meteotemplate
Re: How to store MeteoBridge data in a MySQL Database
« Reply #10 on: September 19, 2016, 11:47:33 AM »
that depends if you are going to be saving both date and time or just time

Offline jalmz

  • Senior Member
  • **
  • Posts: 59
    • BiliranIsland.com
Re: How to store MeteoBridge data in a MySQL Database
« Reply #11 on: September 19, 2016, 11:56:30 AM »
that depends if you are going to be saving both date and time or just time

ah okay.. i cant find the variable for moon phase..

Offline Jáchym

  • Meteotemplate Developer
  • Forecaster
  • *****
  • Posts: 8572
    • Meteotemplate
Re: How to store MeteoBridge data in a MySQL Database
« Reply #12 on: September 19, 2016, 12:00:14 PM »
Why do you want to store all this in the mySQL, it is going to be just one huge table. Remember that each extra column will slow it down. I dont understand why you want to be saving all this. The forecast, the moon phase... those in my opinion should not be there. The forecast for example is useless, it is forecast, it will change so it tells you nothing. Moon phase can be calculated, sun rise/ sun set too, you dont need to have it in the table.

Offline jalmz

  • Senior Member
  • **
  • Posts: 59
    • BiliranIsland.com
Re: How to store MeteoBridge data in a MySQL Database
« Reply #13 on: September 19, 2016, 12:14:50 PM »
Why do you want to store all this in the mySQL, it is going to be just one huge table. Remember that each extra column will slow it down. I dont understand why you want to be saving all this. The forecast, the moon phase... those in my opinion should not be there. The forecast for example is useless, it is forecast, it will change so it tells you nothing. Moon phase can be calculated, sun rise/ sun set too, you dont need to have it in the table.

ah okay.. how can i calculate or get (fetch) the sunrise/sunset?  and the moon phase

Offline jlmr

  • Member
  • *
  • Posts: 11
Re: How to store MeteoBridge data in a MySQL Database
« Reply #14 on: September 19, 2016, 10:47:48 PM »
Here is the site that gives all the variables of the data that can be pulled from meteobridge
http://www.meteobridge.com/wiki/index.php/Templates
Go down close to the bottom and you will see the System Variables for what you are looking for.

Why do you want to store all this in the mySQL, it is going to be just one huge table. Remember that each extra column will slow it down. I dont understand why you want to be saving all this. The forecast, the moon phase... those in my opinion should not be there. The forecast for example is useless, it is forecast, it will change so it tells you nothing. Moon phase can be calculated, sun rise/ sun set too, you dont need to have it in the table.
I see no reason why he can not store this, its his and maybe it will be for historical purpose. and it wont slow down unless he is querying the data constantly, writing the data will only eat up disk space faster and not necessarily slow down.

If you get this working let us know how it works for you 


Davis Vantage Pro 2 
CumulusMX
http://youngstownweather.com

Offline jalmz

  • Senior Member
  • **
  • Posts: 59
    • BiliranIsland.com
Re: How to store MeteoBridge data in a MySQL Database
« Reply #15 on: September 19, 2016, 10:52:21 PM »
ok im working on it.

do you have any idea how to achieve this one?

http://www.boracayweatherstation.com

the "Current Tab" i would like to embed wunderground on my website

Offline Bushman

  • Forecaster
  • *****
  • Posts: 7076
    • Eagle Bay Weather
Re: How to store MeteoBridge data in a MySQL Database
« Reply #16 on: September 20, 2016, 12:32:27 AM »
Copy that site's HTML.

Offline Jáchym

  • Meteotemplate Developer
  • Forecaster
  • *****
  • Posts: 8572
    • Meteotemplate
Re: How to store MeteoBridge data in a MySQL Database
« Reply #17 on: September 20, 2016, 05:06:12 AM »
Here is the site that gives all the variables of the data that can be pulled from meteobridge
http://www.meteobridge.com/wiki/index.php/Templates
Go down close to the bottom and you will see the System Variables for what you are looking for.

Why do you want to store all this in the mySQL, it is going to be just one huge table. Remember that each extra column will slow it down. I dont understand why you want to be saving all this. The forecast, the moon phase... those in my opinion should not be there. The forecast for example is useless, it is forecast, it will change so it tells you nothing. Moon phase can be calculated, sun rise/ sun set too, you dont need to have it in the table.
I see no reason why he can not store this, its his and maybe it will be for historical purpose. and it wont slow down unless he is querying the data constantly, writing the data will only eat up disk space faster and not necessarily slow down.

If you get this working let us know how it works for you

I never said he must not store it, I said it is useless and will slow down the database and that is correct. I have lot of experience with mySQL and the more columns you have the longer any query takes to execute, even if you select just some of the columns, not to mention that most of the time, people use the "*" wildcard to select all. It wont be a problem at the beginning, but once you have 150+ datasets each day, it very soon gets relatively large and it is not about the disk space, but about the queries you pass to it, calculating or extracting data based on it.

And the reason why I see sunrise/sunset as being useless to save is because it can easily be calculated with PHP, there is a built-in function for it. So why would you want to have it extra in the database, repeating every year. It is like as if for example you were saving the number of days in that particular month for each record in the database. It is the same thing because you can deduce it retrospectivelly and you dont need to have it there. It is a useful piece of information but it is redundant in the database.

Offline SpringHillWeather

  • Weather Hobbyist
  • Forecaster
  • *****
  • Posts: 533
  • Spring Hill, Florida
    • Spring Hill
Re: How to store MeteoBridge data in a MySQL Database
« Reply #18 on: January 15, 2017, 05:36:36 PM »
For those who might be MySQL and/or MeteoBridge novices, I wrote this how-to on my blog that walks through connecting MeteoBridge to a MySQL database.

The article also includes an example meteobridge.sql dump file (stored as a Gist on GitHub) that can be easily imported into MySQL to create an example database, along with a matching INSERT query ready to paste into MeteoBridge with imperial data conversion and formatting (Fahrenheit, wind in MPH, rainfall in inches, etc.)

http://www.stevejenkins.com/blog/2015/02/storing-weather-station-data-mysql-meteobridge/

Please feel free to comment here and/or on the blog article itself.

I know this is an old thread but wanted to thank you for the post and blog article.

I finally got chance to setup a local LAMP server on my Raspberry PI and was able to have Meteobridge insert the data into the mySql database on the PI.
I used this code to pull the data which is working great and seems to be a good starting point.


Code: [Select]
<!DOCTYPE html>
<html>
<body>

<?php
$servername 
"hostname";
$username "username";
$password "password";
$dbname "dbname";

// Create connection
$conn = new mysqli($servername$username$password$dbname);
// Check connection
if ($conn->connect_error) {
    die(
"Connection failed: " $conn->connect_error);


$sql "SELECT DateTime, TempInCur, HumInCur FROM mystation";
$result $conn->query($sql);

if (
$result->num_rows 0) {
    
// output data of each row
    
while($row $result->fetch_assoc()) {
        echo 
"DateTime" $row["DateTime"]. " - TempInCur: " $row["TempInCur"]. " - HumInCur " $row["HumInCur"]. "<br>";
    }
} else {
    echo 
"0 results";
}
$conn->close();
?>

DateTime2017-01-15 15:41:30 - TempInCur: 69.1 - HumInCur 30 - TempOutCur 40.6 - HumOutCur 44 - UV 1.0 - Solar 330.0
DateTime2017-01-15 15:42:31 - TempInCur: 69.1 - HumInCur 30 - TempOutCur 40.6 - HumOutCur 44 - UV 1.0 - Solar 325.0
DateTime2017-01-15 15:43:32 - TempInCur: 69.1 - HumInCur 30 - TempOutCur 40.6 - HumOutCur 44 - UV 1.0 - Solar 325.0
DateTime2017-01-15 15:44:33 - TempInCur: 69.1 - HumInCur 30 - TempOutCur 40.6 - HumOutCur 44 - UV 1.0 - Solar 288.

Thank you Steve
« Last Edit: January 15, 2017, 05:42:03 PM by StatenIslandWeather »
Saratoga
HomeWeatherStation
Meteotemplate
Homebrew
Ambient 1001 | ObserverIP | Meteobridge | MeteoPlug

Offline Larry29936

  • Member
  • *
  • Posts: 3
Re: How to store MeteoBridge data in a MySQL Database (Solved)
« Reply #19 on: March 29, 2017, 09:27:12 AM »
Followed your guide but MeteoBridge keeps giving me the following error:  Failed to connect to MYSQL database: Access denied for user 'meteobridge'@'meteobridge' (using password: YES). Using a Linksys 3020. On the MySQL side, created user and database with name of meteobridge and granted all privileges to user meteobridge. Any ideas on how to resolve this?
« Last Edit: March 29, 2017, 11:08:33 AM by Larry29936 »

Offline hymrog

  • Member
  • *
  • Posts: 30
    • NewAlbanyWeather
Re: How to store MeteoBridge data in a MySQL Database
« Reply #20 on: April 01, 2017, 04:51:25 PM »
Hi -

Please double check you are using the correct password for your database. I had the same issue when I set mine up.

 :grin:

Offline jrodriguez6

  • Member
  • *
  • Posts: 7
Re: How to store MeteoBridge data in a MySQL Database (Solved)
« Reply #21 on: February 14, 2018, 05:47:56 PM »
I just started trying to create MySQL following the steps in the "Storing Weather Station Data in MySQL from MeteoBridge" everything looks great except this 2/14/2018 and the PHPMyAdmin is that today there is no way to add a new user.  [ You are not allowed to view attachments ] I've attached a JPG of the screen. I do have access to the PHPMYAdmin from my Windows 10 Desktop, and I have my Raspberry Pi 3 configured and My Meteobridge is on line and working with my Davis Vantage Pro2. I really see you layout is very Clear but How do I create a new user on this version of PHPMYAdmin. Please Help... Joe   

 

anything