Weather data

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
st3fanos
Forum Newbie
Posts: 12
Joined: Tue Apr 13, 2010 1:30 am

Weather data

Post by st3fanos »

Hi,

I have a weather station and I was thinking of creating a website for it with some graphs and historic data.
The weather station software pushes data live to a server in 2 formats, a short quick string updated every 5 seconds via a get request with 59 data items:
F=3481293999, 21:06:39, ESE, 123.000, 1.000, 4.000, 76.000, 69.000, 70.200, 64.000, 30.025, 13.130, 65.000, 66.000, 63.000, 0.000, 65.000, 0.000, 0.161, 0.000, 0.000, 64.000, 73.902, 66.832, 53.638, 30.025, -96.974, 2590.417, 227.377, 66.744, 0.414, -72.123, -1.361, -0.626, 1.415, -3.138, 0.255, -1.361, 0.009, 0.000, 0.010, 0.009, -1.709, -0.509, 0.010, 0.000, 0.003, -0.107, -63.804, -1.361, 0.435, -1.749, -2.509, 0.009, 0.000, 0.000, 0.000, 0.000, 0.000
The format is:

Code: Select all

Timestamp (secs since 1/1/1900 00:00:00)
Time of update
Wind Direction (text)
Wind Direction (degrees)
Wind Speed
Wind Gust
In Humidity
Humidity
In Temp
Out Temp
Raw Barometer
Total Rain
Ch 1 Temp
Ch 1 Humidity
Ch 2 Temp
Ch2 Humidity
Ch 3 Temp
Ch 3 Humidity
Evapotranspiration
UV Index
Solar Radiation
Wind Chill
In Heat Ix
Out Heat Ix
Dew Point
SL Barometer
Press Alt
Cloud Base
Density Alt
Virtual Temp
Vapor Press
Wind Direction Rate
Wind Speed Rate
Wind Gust Rate
In Humidity Rate
Humidity Rate
In Temp Rate
Out Temp Rate
Raw Barometer Rate
Total Rain Rate
Ch 1 Temp Rate
Ch 1 Humidity Rate
Ch 2 Temp Rate
Ch2 Humidity Rate
Ch 3 Temp Rate
Ch 3 Humidity Rate
Evapotranspiration Rate
UV Index Rate
Solar Radiation Rate
Wind Chill Rate
In Heat Ix Rate
Out Heat Ix Rate
Dew Point Rate
SL Barometer Rate
And every minute a Long Full message with all the rest of the data, 289 data fields (not all used but different from the short data list):
S=21:06:34, ESE, 81.03, 2.36, 4.63, 74.58, 72.14, 69.94, 65.36, 30.02, 13.13, 64.99, 65.99, 64.71, 0.51, 64.99, 0.00, 0.16, 0.11, 63.89, 65.36, 73.47, 68.58, 56.15, 30.02, 0.00, 0.00, 0.00, 32.00, 0.00, 360.00, 14.00, 14.00, 79.00, 99.00, 70.20, 67.60, 30.04, 13.13, 65.00, 66.00, 95.00, 15.00, 65.00, 0.00, 0.16, 5.10, 933.00, 67.60, 74.00, 72.39, 63.90, 30.04, -60.06, 357.98, 4.59, 63.15, 0.49, 7:59am, 10:48am, 10:48am, 12:00am, 8:29am, 9:03pm, 12:03pm, 11:21am, 12:00am, 4:24pm, 4:24pm, 11:13am, 12:00am, 4:24pm, 12:00am, 9:00pm, 1:39pm, 2:01pm, 12:03pm, 12:00am, 1:56pm, 1:56pm, 11:21am, 12:00am, 12:00am, 12:00am, 12:00am, 12:00am, 5.00, 0.00, 0.00, 71.00, 69.00, 69.00, 58.50, 29.97, 13.13, 62.00, 63.00, 58.00, 1.00, 62.00, 0.00, 0.00, 0.00, 0.00, 58.50, 72.77, 64.90, 53.64, 29.97, -60.06, 357.98, 4.59, 63.15, 0.49, 8:01am, 12:00am, 12:00am, 7:16pm, 9:01pm, 6:26am, 3:33am, 3:44am, 12:00am, 3:38am, 12:00am, 12:42am, 8:51am, 3:38am, 12:00am, 12:00am, 12:00am, 12:00am, 3:33am, 7:16pm, 3:33am, 9:01pm, 3:44am, 12:00am, 12:00am, 12:00am, 12:00am, 12:00am, 301.13, 7.77, 5.04, 1.82, 5.47, 0.33, 2.51, 0.02, 0.00, 1.12, 1.01, 14.32, 1.61, 1.12, 0.00, 0.03, 1.28, 209.69, 2.66, 0.45, 1.48, 1.83, 0.02, 0.00, 0.00, 0.00, 0.00, 0.00, 12:00pm, 9:29am, 9:29am, 8:50pm, 1:50pm, 8:56am, 10:01am, 8:28am, 12:00am, 4:24pm, 4:24pm, 9:00am, 7:49pm, 4:24pm, 12:00am, 2:00pm, 11:32am, 10:22am, 10:03am, 9:03pm, 12:06pm, 3:16am, 8:28am, 12:00am, 12:00am, 12:00am, 12:00am, 12:00am, -323.95, -5.92, -7.10, -3.56, -11.64, -0.30, -1.51, -0.01, 0.00, -1.01, -0.70, -15.22, -14.51, -1.01, 0.00, -0.14, -1.37, -277.95, -1.51, -0.73, -1.87, -2.68, -0.01, 0.00, 0.00, 0.00, 0.00, 0.00, 8:20am, 10:37am, 7:00pm, 7:16pm, 11:08am, 7:11pm, 8:58pm, 3:11am, 9:04pm, 3:38am, 3:38am, 1:39pm, 8:52am, 3:38am, 12:00am, 12:00am, 6:32pm, 6:22pm, 8:58pm, 7:16pm, 9:01pm, 9:01pm, 3:11am, 12:00am, 12:00am, 12:00am, 12:00am, 12:00am, 0.00, 0.00, 0.00, 0.00, 86.33, 76.60, 0.00, 12.86, 0.80, 2286.19, 0.00, 3621.24, 10859.11, 0.00, 21336.84, ---, Comfortable, Increasing+clouds+with+little+temperature+change.+Precip+possible+within+24+to+48+hrs, Rising, Rising, Calm, 26/4/10, 9:04pm, 6:47am, 8:29pm, 6:30pm, 5:09am, East+South+East, , V14.00, 18.535, 34.651, 131.2, ---, ---,
I should have in total about 340 data items to log every minute.

My problem is how should I go about logging this into mysql. Should I group similar data into tables. Should I have 1 big tables with the string? Should I have 1 table with lots of columbs? Or is it better to only log the data for the graphs and keep the data in 2 text files?

The next question I have is should I, every minute open and close a connection to mysql or is there a more efficient method?

I am looking for any ideas on the best way to go forward.

(If you need to see the FULL data for some reason it's not showing on the screen and you need to see the page source.)

Kind Regards
Stephen
Last edited by Benjamin on Mon Apr 26, 2010 3:39 pm, edited 1 time in total.
Reason: Added spaces after commas to allow for word wrapping. Added [quote] tags.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Weather data

Post by AbraCadaver »

I can't envision how you would use the short and long forms of the data together, because other than the time frame there is nothing to correlate one with the other. Unless I we assume that once a long message is received that all successive short messages are related until another long message is received?

If we can assume that this is the standard way that all data will be sent, and it won't change, then I would setup two tables, one for short and one for long with a column for every piece of data. But if the data ever changes, then you either don't save the additional data or you have to add columns to the database. If there may come a time when the format changes or data is added or removed, then I would store the time values in their own column and store the remainder as one long string, but then if anything changes, you'll most likely need to change the code that retrieves and parses this data.

I would probably go with columns for each piece of data. Also, assuming the first assumption, you could auto-increment the long messages id column and then save that as the parent_id in each successive short message until a new long message is received.

Just some preliminary thoughts.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Post Reply