In my initial review of the MeteoBridge weather station interface device, I discussed its basic features, and briefly mentioned that it also included more advanced functionality, including the ability to store weather data from your weather station in a MySQL database (or a MySQL-compatible one, like MariaDB).

But after searching online for an example .sql file, or even an example database schema for weather data, I couldn’t find any — so I decided to write my own and share it here on my blog.

These instructions assume the following:

• You already have MeteoBridge setup and communicating with your weather station (see my how-to for help).
• You have at least a basic understanding of how to work with MySQL.
• You’ve already got a MySQL server running on a machine that’s accessible to your MeteoBridge. I recommend keeping your MySQL server machine on the same internal network as your MeteoBridge, behind your firewall. While it’s certainly possible for MeteoBridge to push your weather data to a remote MySQL server outside your firewall, running a MySQL server that’s open to the Internet exposes you to a number of additional security risks, so make sure you know how to lock down your MySQL server instance before doing something like that.
• Optionally, that you’ve installed phpMyAdmin on MySQL server host (makes it much easier to test that things are working).

Set Up a New MySQL Database and Database User

First, create a new database on your MySQL server to hold your weather data. For this example, I’ll use the name “meteobridge” for both the new database, and a new database user that’s allowed to access it. An easy way to create both at the same time is the Users tab in phpMyAdmin. Select the Add user link, then fill out the fields like this:

Create a new user and database for MeteoBridge

Make sure you select the option that says “Create database with same name and grant all privileges” and then press the Go button. Check your database list, and you should see a new database named meteobridge.

At this point, you could start manually creating new fields in your database for the weather data you want to store. But if you’d like to save a little time, feel free to use my meteobridge.sql dump (which is the database schema I created for my weather station data) as a starting point:

Import this file into your new database using the phpMyAdmin Import tab, or copying and pasting it into a query window, or just download the raw meteobridge.sql file and import it from the MySQL command line. Doing so will create a new table called mystation (you can rename it to anything you want) with the following fields:

• ID (which is also set as an auto-incrementing unique Primary Key)
• DateTime – Date and Time of Readings
• TempOutCur – Current Outdoor Temperature
• HumOutCur – Current Outdoor Humidity
• PressCur – Current Barometric Pressure
• DewCur – Current Dew Point
• HeatIdxCur – Current Heat Index
• WindChillCur – Current Wind Chill
• TempInCur – Current Indoor Temperature
• HumInCur – Current Indoor Humidity
• WindSpeedCur – Current Wind Speed
• WindAvgSpeedCur – Current Average Wind Speed
• WindDirCur- Current Wind Direction (in Degrees)
• WindDirCurEng – Current Wind Direction (in English)
• WindGust10 – Max Wind Gust for Past 10 Mins
• WindDirAvg10 – Average Wind Direction (Degrees) for Past 10 Mins
• WindDirAvg10Eng – Average Wind Direction (English) for Past 10 Mins
• RainRateCur – Current Rain Rate
• RainDay – Total Rain for Today
• RainYest – Total Rain for Yesterday
• RainMonth – Total Rain this Month
• RainYear – Total Rain this Year

Those are all the measurements I wanted to capture from my Davis Vantage Pro 2 weather station. If you have different sensors available on your station (such as UV or Solar), you should add fields to your database to support them.

Also, my example .sql file includes one example record containing sample values for all the fields, so you can compare to your live data. You can delete that record later once you’ve verified that your setup is working properly.

Configure your MySQL Server Settings

After setting up your MySQL database, the next step is telling MeteoBridge about your MySQL setup. Access your MeteoBridge’s web interface and click on the Push Services tab. On the bottom half of that tab, you’ll see where it’s asking for your MySQL info, like this:

MySQL database info for MeteoBridge

Type in the IP address (or DNS name) of your MySQL server in the Host field, then fill in the remaining fields with your database name, user name, and password (as shown above). The default port for MySQL connections is 3306, so you can leave it alone unless you’ve told your MySQL server to listen on a different port.

Press Save when you’re done, but MeteoBridge will warn you with some red text that the MySQL functions of MeteoBridge won’t be available until after a reboot — but don’t reboot your MeteoBridge just yet. Nothing bad will happen if you do, but it won’t work yet. The MySQL libraries take up a fair amount of storage, so at boot time, MeteoBridge checks to see if any MySQL event definitions exist — then only loads the libraries if they are needed.

Create a MySQL Event Definition

On your MeteoBridge’s Push Services tab, create a new event definition by dropping down the list that says Select Service and choosing MYSQL Request. Drop down the Select Event Type and choose Periodical. Hit the Add Service Event button, and you’ll see  a new definition appear with MYSQL. To keep things simple, you have only two options:

1. How often you want MeteoBridge to submit a query to your MySQL database, and
2. What query you want MeteoBridge to submit to your MySQL database.

Mine is configured to save my weather data every “full” 10 minutes, meaning at 6:10, 6:20, 6:30, etc. — but you can set yours for whatever time period you like. If you store your data more often, you’ll capture more accurate wind gust data, but the trade-off is that you’ll fill up your database (and eat disk space) faster. Don’t get too worried about disk space, however, since you’re only storing text, and MySQL is pretty efficient.

For the Query field, enter in a standard MySQL INSERT statement — without the semicolon on the end (I almost pulled my hair out trying to figure out why my test queries didn’t work until I removed the semicolon).  Like any other MySQL INSERT statement, your statement will need to include all the names of your database fields, followed by the values you want to insert into those fields. Using MeteoBridge’s Template feature, you’ll include the variable names of the values you want to store as part of the query, and MeteoBridge will translate those into the actual values from your weather station’s console at the time of the query. Here’s how my example query looks, using the fields from my example database:

INSERT INTO `lakewebster` (`ID`, `DateTime`, `TempOutCur`, `HumOutCur`, `PressCur`, `DewCur`, `HeatIdxCur`, `WindChillCur`, `TempInCur`, `HumInCur`, `WindSpeedCur`, `WindAvgSpeedCur`, `WindDirCur`, `WindDirCurEng`, `WindGust10`, `WindDirAvg10`, `WindDirAvg10Eng`, `RainRateCur`, `RainDay`, `RainYest`, `RainMonth`, `RainYear`) VALUES (NULL, '[YYYY]-[MM]-[DD] [hh]:[mm]:[ss]', '[th0temp-act=F]', '[th0hum-act]', '[thb0seapress-act=inHg.2]', '[th0dew-act=F]', '[th0heatindex-act=F]', '[wind0chill-act=F]', '[thb0temp-act=F]', '[thb0hum-act]', '[wind0wind-act=mph]', '[wind0avgwind-act=mph]', '[wind0dir-act]', '[wind0dir-act=endir]', '[wind0wind-max10=mph]', '[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]')

Here’s how my query looks in my Push Services tab (set up as Event Definition #03):

MeteoBridge Push Services tab with MySQL query included.

In addition to the variables, I’m also taking advantage of MeteoBridge’s ability to convert and format data on-the-fly. For example, for my current temperature, I’m using [th0temp-act=F] as my variable. MeteoBridge natively uses metric values for everything reads, so an equal sign tells MeteoBridge to convert the metric value into something else. In this example, the “=F” converts the current (act) reading of the outdoor temperature sensor (th0temp) into Fahrenheit.

By default, MeteoBridge provides numerical data with a single decimal place, so if you want to store more than one decimal place in your database, you’ll need to include that with the variable. My variable for barometric pressure, for example, is [thb0seapress-act=inHg.2]. That will grab my weather station’s current normalized pressure (computed to sea level) which is in hPa by default, converts it to inches of mercury with “=inHg,” then the “.2” tells MeteoBridge to spits out the result with two decimal places.

Another variable in my example query that uses conversion is [wind0dir-act=endir]. That takes the current wind direction and converts it into English, such as E, ESE, NNW, etc. You’ll also notice that all my rain data variable end with “=in.2” — meaning it’s all converted to inches and shown with two decimal places.

See MeteoBridge’s Template Wiki Page for all the available variables, conversion, and formatting options.

Of course, if you’ve modified your database’s structure from my example, you’ll have to modify my example query to match your changes.

Test and Tinker

Once you’ve got your MySQL query configured as you like it (remember, resist the urge to include a semicolon at the end), press the Save button at the bottom of the Push Services tab. Then manually reboot the MeteoBridge from the System tab so that it loads the MySQL libraries (it will this time, because you now have a MySQL event defined).

For testing, set the time period low (such as every 15 or 30 seconds) on your MySQL Event Definition. You can also check the Live Data tab and look in the Weather Network Status section to see if MeteoBridge is reporting a successful query. I failed query will show you some sort of error message that might help with troubleshooting. Check the obvious things first, like network connection to your MySQL database, firewall settings on your MySQL server (make sure port 3306 is open to internal traffic), and verify that the mysqld service is running. Double-check (and maybe triple-check) your tick marks, quote marks, commas, and parentheses in your MySQL query. Nine times out of ten, that will be your issue. Test and tinker until the queries start reporting successfully, then use phpMyAdmin to check your mystation table and verify that data is being stored. Make sure that any conversions you set up are working properly, and you’re getting the number decimal places you want.

Once your data is streaming in to your satisfaction, go back to MeteoBridge’s Push Services tab and set the time period you want for your queries, then clear out any incomplete or test queries from your database (including my example record that got imported with the meteobridge.sql file).

When you’re done, your MeteoBridge will not only be pushing data to weather networks like Weather Underground and CWOP, but you’ll also be storing data in your own MySQL database. Hooray! 🙂

If you run into problems, I really don’t recommend posting here for help. Instead, come over to the MeteoBridge/MeteoHub section of WXForum.net (where I often hang out) and post your question there. You’ll get a much faster (and probably more helpful) response.

But for anything other than support requests, I always welcome your questions, comments, and feedback below!