Please read the posts in the Announcements section about the current status of Cumulus development now that I have retired

Please read this post before posting

Latest Cumulus release v1.9.4 (build 1099) - Nov 28 2014
Latest Cumulus MX release - v3.0.0 build 3043 Jan 20 2017. See this post for download

Importing FreeWx data into daj's SQL database

Discussion and support for 3rd-party (non-Sandaysoft) tools for Cumulus
serowe
Posts: 484
Joined: Tue Aug 03, 2010 6:23 am
Weather Station: WM918
Operating System: Win Server 2008 R2
Location: Ferntree Gully, VIC, Oz
Contact:

Importing FreeWx data into daj's SQL database

Postby serowe » Sat Aug 07, 2010 12:43 am

I have (heavily) modified David Jamieson's (daj) CumulusImportFile script to allow it to import historical data generated by the FreeWx program. If anyone wants a copy of this, let me know ( stephen at ser dot id dot au ) and I will send them a copy.

There are some important points to note about this however.

1. This is NOT meant as a repalcement for David's import script - it was modified purely to allow me to import over 6 years worth of data I had generated from FreeWx before I moved to Cumulus. Without David's script I probably would have struggled to think of a way to preserve this data.

2. Because of the way FreeWx names and structures its log files and daily summaries, it can't just be imported into the SQL file David has set up for his script. Because of this there are some (many!) fields within FreeWx that do NOT get imported into the SQL databases. Also, there are a couple (two from memory) fields within the Cumulus data that have no equivalent field from FreeWx's files and one field (dealing with daily rain) that is also not present in the FreeWx files. IN this latter case I have made a decision to 'drop' the cumulative daily rain into the TodayRainSoFar field knowing that this may not be what some users want. The script is commented to alter this if this is not what you want.

3. Because I have no idea what directory structures are in use in the myriad of web sites out there, this FreeWx import version requires that you place the FreeWx log files you want to import into the ROOT directory of your web site! When you have picked yourself up after reading this statement, remember this - you only have to import the data ONCE. After you have confirmed the data has been imported, you can DELETE the files from the sites root directory. The reason for this is that FreeWx does NOT store the YEAR of the data anywhere INSIDE the log file - this has tp be extracted from the file name. I could, I suppose, have written something that removes any sub-directories from the file name path, but this would have added another level of complexity for a once only task, so I opted not to go down this route. If this isn't to your liking - you could always modify the scripot and test, and test, and test :)

4. I have removed, from this version, the need to add in the URL to run the script, the need to say which table you want to add the data into. Because of differing terminologies between FreeWx and Cumulus, the program will determine this for you and add the data into the correct table. This also applies to the import of the Cumulus dayfile and monthly file. (Yes, the program will continue to import these two files BUT if David modifies and releases an upgraded version, be aware that this version will NOT be modified again - once again, this was written to perfom a specified task once only.

5. This next point is very important - if the rainfall in your area exceeeds 1000 mm in the year (as it did inMelbourne 2 years ago) you MUST modify the SQL monthly table by increasing the field width for the following two fields:

TodayRainSoFar - originally 4,1 but needs to become 5,1
and
Raincounter - originally 6,2 but needs to become 7,2

If you don't do this, the import of data for ANY days where the rain total has become 1000 mm or more WILL FAIL. You can then modify the data and reimport it (I retained David's REPLACE SQL statements so it will UPDATE existing data) without any problems. But you have been warned!

6. FreeWx includes TEXT in its report. I have programmed out wind chill and/or heat index reports of 'n.a.' and replace these values with a zero. These won't present a problem. What WILL cause some concern if you see it, is the word 'ERROR" in the data file. FreeWx includes this if a sensor becomes faulty or is disconnected. In my case I have a temperature sensor that appears to go open circuit every summer for about 2 weeks when the temperature is above 35 Celsius and reports an outside temp of -6C (minus 6 Celsius) and 3 or 4 fields get reported as an ERROR. I haven't programmed these out these errors because I normally completely remove the line from the log file as they represent an equipment malfunction. If you have any of these lines, the program will simply display a 'failed to insert data' error - if this happens just sing the song 'Elusive Butterfly' to yourself* :)

7. I have also moved the database details - name, user name, password as well as a few other details to OUTSIDE of the program to the dbconfig.php file (this is similar to the one contained in the Historic scripts to read the SQL database). I did this to make it easier to maintain details on the database and also ensure they are never inadvertantly displayed by anyone accessing this file. You could, if you want, place these items back into the file but - hey, it works this way :)

How does it work?

FreeWx generates two distinct log files - al<month><year>.csv and log<month><year>.csv.

The file starting with al is the equivalent of the Cumulus dayfile.txt - ie it contains a single entry for each day giving a summary of that day. Differing to Cumulus, these daily records are bundled by the month. You could, theoretically, join all of one year together BUT the script needs the file name to determine the year the data was generated and, to be honest, it took me maybe 15 minutes to import 6 years of these files into the database - a once only job.

The file starting with log is the equivalent of the Cumulus file monthly.txt BUT contains a lot more information than the Cumulus file - and in a completely different order, so this had to be dealt with programmatically. These files may cause you some headaches, depending on the logging frequency of the data. For the first 2-3 years I logged every 15 minutes - a monthly file of this size (around 200-350k) will take 2-5 minutes to import - usually no problem on most servers. However, in 2008 I changed to one minute logging - and this caused some headaches because the script kept timing out when I ran it. I had to increase the maximum execution time on my server (I run my own so I could do this) in order to completely import each month from Jan 2008. If you can adjust this time yourself, great, if not the only solution I can see is to split the file into manageable chunks - say 300-500k at a time (my monthly files with 1 minute logginng were around 1.6 Mb). BUT if you do this you CANNOT rename then a, b, c etc - each section would HAVE to retain the original name for the entire file. Sorry, but it isn't worth the time and effort modifying a once only script to accomodate this. My larger files took anywhere between 5 and 10 minutes to completely process.

So - what is the command line for the URL? In my case I use this:

Monthly log file:
http://192.168.0.101/ICF.php?type=freewxmonthly&key=<my secret key>&file=logJanuary10.csv
Daily log file:
http://192.168.0.101/ICF.php?type=freewxdaily&key=<my secret key>&file=alJanuary10.csv

This would be connected to my internal server, 192.168.0.101; I am importing the monthly FreeWx data file (the code for the 'daily' log is freewxdaily - very imaginative!); the key as described in David's original script (which is now defined in the dbconfig.php file); and the file name EXACTLY as FreeWx created it - do NOT try to rename the files by shortening the month as the script uses this, and the year in the file name, to make some assumptions and corrections.

There is minimal feedback - it will tell you when the script is finished and also confirm the name of the log file that has been 'consumed' (it came in handy as, at one stage, I had 8 files all being processed in different tabs on two PC's at the same time!)

Now - what do you do with the imported data? Well, that's for you to work out :) I am looking at David's Historic scripts - if I can get them working, I may use them, but I will also play aroind with a few other things as I liked FreeWx's Ghostwriter system that gave me annual, monthly and detailed summary pages.

I also have another 18 months of Weather Display (WD) data to import and about 3-4 months of Virtual Weather Station (VWS) data - I tried all of these before settling on FreeWx in 2004 (and the reason I no longer use FreeWx? - I now have a 64 bit file server and FreeWx cannot operate on it). If I write or modify this script for either WD or VWS data I may include it in this script, but that isn't a given. I then have basic data (temp, press and rain) for 18 years to key and import, so I may also end up with a data entry screen to allow me to input this data into the SQL databases (it is all handwritten so I have to tackle that problem slightly doifferently).

Only after all this will the nearly 30 years of weather data I have been collecting here at home finally be complete and (hopefully) available on line! (And in case of the obvious question - yes, I did used to work for the AUstralian Bureau of Meterology...)

If you want to use this, email me, but remember - whilst I have run it in its present form, and it works for me, this may not always be the case for others. I will help where I can, but as I keep pointing out - this was written to perform a once only task and, for me, won't be needed again now all of the FreeWx data has been imported into the SQL databases.


* And listen to the line 'Don't be concerned' - sorry, I have a very warped sense of humour!
Punctuation is the difference between 'Let's eat, grandma' and 'Let's eat grandma'

serowe
Posts: 484
Joined: Tue Aug 03, 2010 6:23 am
Weather Station: WM918
Operating System: Win Server 2008 R2
Location: Ferntree Gully, VIC, Oz
Contact:

Re: Importing FreeWx data into daj's SQL database

Postby serowe » Sat Aug 07, 2010 1:18 am

Just as an addition to this - I finished importing all of my data - in total I ended up with 2,254 daily summary records and 636,288 detailed records! Now to begin the fun of accessing this data!
Punctuation is the difference between 'Let's eat, grandma' and 'Let's eat grandma'

camballinger
Posts: 9
Joined: Sun Feb 19, 2012 2:52 am
Weather Station: Oregon Scientific WMR928
Operating System: Windows 7 (x64)
Location: Jindabyne, NSW, Australia

Re: Importing FreeWx data into daj's SQL database

Postby camballinger » Mon Feb 20, 2012 9:37 am

After my Oregon Station seems to have finally died I am looking to migrate hardware (to Davis) and software to Cumulus. I have 7 years of records in FreeWX format which would be great to get into MySQL, I started to work on some MySQL script when i came across your script. I tried to find some PM details but coudn't see any. Is there any chance you could forward it to me? My email address is camballinger@gmail.com.


Return to “3rd Party Tools”

Who is online

Users browsing this forum: No registered users and 1 guest