Weather data
Posted: Mon Apr 26, 2010 3:04 pm
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:
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):
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
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:
The format is: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
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
I should have in total about 340 data items to log every minute.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, ---, ---,
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