Forecast vs Actual

Hi Javier,
Thanks for that, I have had a few hours sleep now and felt bad not supplying a process.

1: Edit : " 1wxsim_settings.php " with your info.
1A: Upload entire Folder : " wxsimcsvmysql " with all it’s files, to your server.

2: In your browser run : " your website address /wxsimcsvmysql/2wxsim_create_tables_views_run_once_only.php "
2A: Check in Cpanel/MySql (or whatever you use) to ensure new Tables : “wxsim_raw_csv” , “wxsim_historic” and View : “wxsim_current_csv_vw” have been created, they will be empty but structure there.

3: In your browser run : " your website address /wxsimcsvmysql/3wxsim_create_views_views_run_once_only.php "
3A: Check in Cpanel/MySql (or whatever you use) to ensure new Views : “RealtimeComparevw” , “WxsimComparevw” and “WxSimActTempLowHighDelta” have all been created, they will be empty but structure there.

4: In your browser run : " your website address /wxsimcsvmysql/4wxsim_cron_csv_import.php "
4A: This will Import “latest.csv” file contents into “wxsim_raw_csv” and also use the “wxsim_current_csv_vw” concat the Raw DateTimes field and Add the output of that view into the “wxsim_historic”.
4B: Check in Cpanel/MySql (or whatever you use) to ensure All Tables and Views now contain data. (you can now run one of the Graph Files which should display data (though historic data will only be from current run). That will build over time.

5: Finally, You need to set the " 4wxsim_cron_csv_import.php " file to run via Cron after your WxSim Run and after “latest.csv” has uploaded to your server.
( eg. every 6 hours or however often you upload ).

6: You can then delete :"2wxsim_create_tables_views_run_once_only.php " and "3wxsim_create_views_views_run_once_only.php " from your server.

Hopefully that all works.
I have no idea what other country “latest.csv” files are like so possability it may fail. If so look at :
$fieldseparator = “,”; // CSV Field Seperator
$lineseparator = “\n”; // CSV Line Seperator

Other than that I can not help, you will need to get assistance from gurus / modify to suite.
Just realised I did not make the Units Variable, and won’t so maybe someone else can, you’ll need to change those in the Graph files if you need to eg. C to F etc. note: no conversions occur as it is wxsim data.

Kind regards,

Regards Tony

I feel that you have slept little (I suspect that we have the same profession) :D.

I have installed your script on my server; the tables and the views have been created perfectly.

The only view that has been populated with data is “RealtimeComparevw”.

http://kocher.es/wxsimcsvmysql/4wxsim_cron_csv_import.php

http://kocher.es/wxsimcsvmysql/latestwxsimsqlsave.txt

All other tables and views remain empty.

Maybe it’s a problem with the decimal sign (,)

Thank you very much for your interest and expertise :smiley:

I made the following change to the file “4wxsim_cron_csv_import.php”:

Change decimal sign (,) to (.)

foreach(explode($lineseparator,$csvcontent) as $line) {

    $lines++;
    
    $line = str_replace(",",".",$line); // replace the decimal sign (,) by decimal point (.)

    $line = trim($line," \t");

    $line = str_replace("\r","",$line);

    /************************************
    This line escapes the special character. remove it if entries are already escaped in the csv file
    ************************************/
    $line = str_replace("'","\'",$line);
    
    $line = str_replace(",",".",$line); // cambia las comas decimales(,) por puntos (.))

And, now it seems that the decimal sign is correct:

http://kocher.es/wxsimcsvmysql/latestwxsimsqlsave.txt

However, the tables and views are not populating #-o

       [url=http://view-source:http://kocher.es/wxsimcsvmysql/latestwxsimsqlsave.txt[/url]

Hi Javier,

mm I had not thought of people having wxsim in hour intervals (mine is in 30 min intervals).

You will need to manually (Drop/Delete) the 3 views :“RealtimeComparevw” , “WxsimComparevw” , “WxSimActTempLowHighDelta”.

Then run the attached file “3wxsim_create_views_views_run_once_only2.php”

Script changed from (30 * 60) to (60 * 60)

oh and if that works, you will need to manually change all graph files Subtitle text from “Rolling 7 Days + 30 min Realtime” to "Rolling 7 Days + 1 hour Realtime

I’m at work now, so no more from me for a while.

Regards


3wxsim_create_views_views_run_once_only2.zip (1.28 KB)

Hi Javier,

The issue with yours seems to be that your latest.csv only contains around 43 fields where as mine has 76 (I thought all csv files were same - obviously not).
I cannot rewrite for you nor assist until this weekend.

But what you can do is manually delete the “wxsim_raw_csv” table.
Then in “2wxsim_create_tables_views_run_once_only.php” you will need to remove lines from below section of create table to match/ leaving only the ones in your csv.
Then check all other files do not have a field that is not present in your csv.


// sql to create "wxsim_raw_csv" table
$sql = "CREATE TABLE IF NOT EXISTS $wxsimrawcsvtable (
  `Year` int(4) DEFAULT NULL,
  `Month` int(2) DEFAULT NULL,
  `Day` int(2) DEFAULT NULL,
  `Time` decimal(10,1) DEFAULT NULL,
  `WXType1` varchar(20) DEFAULT NULL,
  `WXType2` varchar(20) DEFAULT NULL,
  `Temperature` decimal(4,1) DEFAULT NULL,
  `HiTemp` decimal(4,1) DEFAULT NULL,
  `LowTemp` decimal(4,1) DEFAULT NULL,
  `RelHum` decimal(4,1) DEFAULT NULL,
  `DewPt` decimal(4,1) DEFAULT NULL,
  `WetBulb` decimal(4,1) DEFAULT NULL,
  `WindSpd` decimal(4,1) DEFAULT NULL,
  `WindDir` decimal(4,1) DEFAULT NULL,
  `TotPrcp` decimal(4,2) DEFAULT NULL,
  `SnowDpth` decimal(4,1) DEFAULT NULL,
  `SLP` decimal(6,1) DEFAULT NULL,
  `StnPres` decimal(6,1) DEFAULT NULL,
  `WindChl` decimal(4,1) DEFAULT NULL,
  `HeatInd` decimal(4,1) DEFAULT NULL,
  `VlyTmp` decimal(4,1) DEFAULT NULL,
  `HillTmp` decimal(4,1) DEFAULT NULL,
  `TLvl1` decimal(4,1) DEFAULT NULL,
  `T850mb` decimal(4,1) DEFAULT NULL,
  `10p5Thk` decimal(6,1) DEFAULT NULL,
  `LI` decimal(4,1) DEFAULT NULL,
  `SkyCov` decimal(4,1) DEFAULT NULL,
  `SCL1` decimal(4,1) DEFAULT NULL,
  `SCL2` decimal(4,1) DEFAULT NULL,
  `SCL3` decimal(4,1) DEFAULT NULL,
  `SCL4` decimal(4,1) DEFAULT NULL,
  `SCL5` decimal(4,1) DEFAULT NULL,
  `VisTrans` decimal(4,1) DEFAULT NULL,
  `VIS` decimal(4,1) DEFAULT NULL,
  `SunAlt` decimal(4,1) DEFAULT NULL,
  `SolarRad` decimal(4,1) DEFAULT NULL,
  `UVIndex` decimal(3,1) DEFAULT NULL,
  `FreezingLevelhighest` decimal(6,1) DEFAULT NULL,
  `SnowLevel` decimal(6,1) DEFAULT NULL,
  `Convectionindex` decimal(4,2) DEFAULT NULL,
  `Severeindex` decimal(4,2) DEFAULT NULL,
  `1minGust` decimal(4,1) DEFAULT NULL,
  `10minGust` decimal(4,1) DEFAULT NULL,
  `1hrGust` decimal(4,1) DEFAULT NULL,
  `6hrGust` decimal(4,1) DEFAULT NULL,
  `15m50ftTemperature` decimal(4,1) DEFAULT NULL,
  `GrassTemperature` decimal(4,1) DEFAULT NULL,
  `SoilSurfaceTemperature` decimal(4,1) DEFAULT NULL,
  `SoilTemperatureDepth1` decimal(4,1) DEFAULT NULL,
  `SoilTemperatureDepth2` decimal(4,1) DEFAULT NULL,
  `SoilTemperatureDepth3` decimal(4,1) DEFAULT NULL,
  `SoilTemperatureDepth4` decimal(4,1) DEFAULT NULL,
  `SoilTemperatureDepth5` decimal(4,1) DEFAULT NULL,
  `SoilMoistureDepth1` decimal(4,1) DEFAULT NULL,
  `SoilMoistureDepth2` decimal(4,1) DEFAULT NULL,
  `SoilMoistureDepth3` decimal(4,1) DEFAULT NULL,
  `SoilMoistureDepth4` decimal(4,1) DEFAULT NULL,
  `SoilMoistureDepth5` decimal(4,1) DEFAULT NULL,
  `ET0ShortReferenceCrop` decimal(6,4) DEFAULT NULL,
  `ET0LongReferenceCrop` decimal(6,4) DEFAULT NULL,
  `ActualEvapotransipration` decimal(6,4) DEFAULT NULL,
  `TotalET0ShortReference` decimal(5,4) DEFAULT NULL,
  `TotalET0LongReference` decimal(5,4) DEFAULT NULL,
  `TotalActualEvapotranspiration` decimal(5,4) DEFAULT NULL,
  `IRDown` decimal(4,1) DEFAULT NULL,
  `IRUp` decimal(4,1) DEFAULT NULL,
  `SensibleHeat` decimal(4,1) DEFAULT NULL,
  `LatentHeat` decimal(4,1) DEFAULT NULL,
  `NetRadiation` decimal(6,1) DEFAULT NULL,
  `HeatConductedUptoSurface` decimal(4,1) DEFAULT NULL,
  `SoilTensionDepth1` int(4) DEFAULT NULL,
  `SoilTensionDepth2` int(4) DEFAULT NULL,
  `SoilTensionDepth3` int(4) DEFAULT NULL,
  `SoilTensionDepth4` int(4) DEFAULT NULL,
  `SoilTensionDepth5` int(4) DEFAULT NULL,
  `TotalIrrigation` int(4) DEFAULT NULL
)";

I also see many others have downloaded the files, though not given any feedback.
l also attached copy of my latest.csv for you to compare.

regards,


latest (5).zip (44.4 KB)

Thank you very much Tony

I appreciate your help and the effort you make for it.

I’m not in any hurry and I do not want to create problems in your work.

Thank you very much :wink:

Javier

Hi Javier,

Manually delete all previously created (Tables and Views) in php myadmin.

Then try attached:

regards,
Tony


wxsimcsvmysql.zip (207 KB)

Everything works :smiley:

http://kocher.es/wxsimcsvmysql/realtime_graphs_wxsim_Act_Temp.php

http://kocher.es/wxsimcsvmysql/realtime_graphs_wxsim_Act_WX.php

http://kocher.es/wxsimcsvmysql/realtime_graphs_wxsim_Act_WX_Delta.php

http://kocher.es/wxsimcsvmysql/realtime_graphs_wxsim_cloud.php

http://kocher.es/wxsimcsvmysql/realtime_graphs_wxsim_Snow_Level_SA.php

I must say that I am very grateful to Tony for:

- The quality and complexity of its creation; you have to think a lot to carry out this idea.

- For the patience and kindness that has shown

This weekend I will comment more opinions about Tony’s work

Greetings from San Sebastian

Hi Javier,

Thank you for the kind words, no problem (my pleasure).

I hurriedly doing fix last night I missed one change, it means the new runs will not be updated.
Sorry : Fixed file attached : just upload / overwrite original with this one.

Kind regards,


4wxsim_cron_csv_import.zip (1.81 KB)

wxsimcsvmysql.zip (209 KB)

Hi for those who have it running,
Here are some options to think about.
I added additional tables/views to look at each 00z/06z/12z/18z run.
Then quickly as an example produced the following: (excuse colors = was a quick example) to show Actual vs each Run.
Personally I will not be using the charts displayed below, but will eventually use the tables/views in additional compare table outputs and charts against the Cumulus Hi/Low from the Dayfile Table on a daily min/max total, with maybe some scoring applied
I have many more options, though they will take me months to refine. Now that you have the Import concept you can do whatever you like.
Another rough example of wxsim data : Sun Altitude Forecast : https://southfranklinweather.com/wxsimcsv/realtime_graphs_wxsim_sun.php could be done on day basis etc. etc.

Oh another real rough one for now, no additional runs added yet (that will be a long way off) nor are there Std. Dev / Success% etc. etc. as in Jim’s brilliant script as yet, time will hopefully change that.
It actually compares WxSim High / Low “Temperature” for the Day against Cumulus Dayfile Table data, also does same for WxSim’s Low and High Range forecast, which are LowTemp and HiTemp in CSV.
Example : https://southfranklinweather.com/weather/wxsimcompare.php

Ah well decided to add all 4 runs, not much data in those tables yet.

Images below Updated with current output.

kind regards,


Hi Updated previous post, uploaded incorrect image. Now rectified.
Kind regards,

I’m still surprised with this Tony job:

http://kocher.es/wxsimcsvmysql/realtime_graphs_wxsim_Act_Temp.php

The graphics place us in the next hour of the current day, with the current values and predicted by WXSIM; through lines of different colors, we can see the values that a certain parameter will take and the predicted future values.

The secret lies in the creation of tables and views that change dynamically (values are added depending on the current time and other older ones are deleted (I apologize to Tony if I explain myself wrongly).

I like the way of presenting the information and, from what I see, this has not ended.
Very interesting to see the comparison tables WxSim High / Low “Temperature” for the Day against Cumulus Dayfile.

The comparative graphs of the MAE (mean absolute error), allow us to see at a glance, the tendency of the successes of WXSIM

Summing up a bit, I think that this work is an incredible and indispensable tool that should accompany WXSIM

Thank you very much for this impressive work

Saludos desde San Sebastian, Spain

Hi Tony,
before all thank you so much for sharing this script, very nice and usefully!

I’ve red your istruction but about point 2) i’ve this error:

Created : wxsim_raw_csv (table) 

Created : wxsim_current_csv_vw (table) 
Error creating wxsim_historic (table) : Invalid default value for '6hrGust'

Any idea?
Thanks in advance.
Regards,

Alessandro


latest.zip (28.7 KB)

Hi apologies to all,

No time for responses to earlier posts nor to adjust core zip files with following change, or updates (as nothing new). Job/Work extreme at present.

Alessandro,

It must be a MySql version issue or something as it has worked for me on many occasions during testing and (although many have downloaded without feedback = I assume it did not work for them or they now have the concept and are developing further) aside from Javier who ran OK.
I think the issue is in the " 2wxsim_create_tables_views_run_once_only.php " file.

Change : ( 6hrGust decimal(4,1) DEFAULT NULL ) to ( 6hrGust decimal(4,1) DEFAULT NULL, ) (note: the coma after NULL,)

From:


// sql to create "wxsim_historic" table
$sql = "CREATE TABLE IF NOT EXISTS $wxsimhistorictable (
  $cu_realtime_datefield datetime NOT NULL,
  `WXType1` varchar(20) DEFAULT NULL,
  `WXType2` varchar(20) DEFAULT NULL,
  `Temperature` decimal(4,1) DEFAULT NULL,
  `HiTemp` decimal(4,1) DEFAULT NULL,
  `LowTemp` decimal(4,1) DEFAULT NULL,
  `RelHum` decimal(4,1) DEFAULT NULL,
  `DewPt` decimal(4,1) DEFAULT NULL,
  `WetBulb` decimal(4,1) DEFAULT NULL,
  `WindSpd` decimal(4,1) DEFAULT NULL,
  `WindDir` decimal(4,1) DEFAULT NULL,
  `TotPrcp` decimal(4,2) DEFAULT NULL,
  `SnowDpth` decimal(4,1) DEFAULT NULL,
  `SLP` decimal(6,1) DEFAULT NULL,
  `StnPres` decimal(6,1) DEFAULT NULL,
  `WindChl` decimal(4,1) DEFAULT NULL,
  `SkyCov` decimal(4,1) DEFAULT NULL,
  `VisTrans` decimal(4,1) DEFAULT NULL,
  `VIS` decimal(4,1) DEFAULT NULL,
  `SunAlt` decimal(5,1) DEFAULT NULL,
  `SolarRad` decimal(5,1) DEFAULT NULL,
  `UVIndex` decimal(4,1) DEFAULT NULL,
  `SnowLevel` decimal(6,1) DEFAULT NULL,
  `Convectionindex` decimal(4,2) DEFAULT NULL,
  `Severeindex` decimal(4,2) DEFAULT NULL,
  `1minGust` decimal(4,1) DEFAULT NULL,
  `10minGust` decimal(4,1) DEFAULT NULL,
  `1hrGust` decimal(4,1) DEFAULT NULL,
  `6hrGust` decimal(4,1) DEFAULT NULL
  PRIMARY KEY ($cu_realtime_datefield)
)";

To:


// sql to create "wxsim_historic" table
$sql = "CREATE TABLE IF NOT EXISTS $wxsimhistorictable (
  $cu_realtime_datefield datetime NOT NULL,
  `WXType1` varchar(20) DEFAULT NULL,
  `WXType2` varchar(20) DEFAULT NULL,
  `Temperature` decimal(4,1) DEFAULT NULL,
  `HiTemp` decimal(4,1) DEFAULT NULL,
  `LowTemp` decimal(4,1) DEFAULT NULL,
  `RelHum` decimal(4,1) DEFAULT NULL,
  `DewPt` decimal(4,1) DEFAULT NULL,
  `WetBulb` decimal(4,1) DEFAULT NULL,
  `WindSpd` decimal(4,1) DEFAULT NULL,
  `WindDir` decimal(4,1) DEFAULT NULL,
  `TotPrcp` decimal(4,2) DEFAULT NULL,
  `SnowDpth` decimal(4,1) DEFAULT NULL,
  `SLP` decimal(6,1) DEFAULT NULL,
  `StnPres` decimal(6,1) DEFAULT NULL,
  `WindChl` decimal(4,1) DEFAULT NULL,
  `SkyCov` decimal(4,1) DEFAULT NULL,
  `VisTrans` decimal(4,1) DEFAULT NULL,
  `VIS` decimal(4,1) DEFAULT NULL,
  `SunAlt` decimal(5,1) DEFAULT NULL,
  `SolarRad` decimal(5,1) DEFAULT NULL,
  `UVIndex` decimal(4,1) DEFAULT NULL,
  `SnowLevel` decimal(6,1) DEFAULT NULL,
  `Convectionindex` decimal(4,2) DEFAULT NULL,
  `Severeindex` decimal(4,2) DEFAULT NULL,
  `1minGust` decimal(4,1) DEFAULT NULL,
  `10minGust` decimal(4,1) DEFAULT NULL,
  `1hrGust` decimal(4,1) DEFAULT NULL,
  `6hrGust` decimal(4,1) DEFAULT NULL,
  PRIMARY KEY ($cu_realtime_datefield)
)";

Hopefully that helps,

I should add that my current structure is nothing like the original supplied earlier, as (I mentioned many posts ago) it will take me months / due to my main job/work and then when time permits enhance a final outcome. I now have about 14 additional views/tables being run by 4 separate cron files run after each Zulu Run, to store each in separate tables, then other scripts displaying the results.
It is however no where near what the final display will be, as currently I have no (like Jim’s brilliant script) created a way to capture (+) days for compare, I could do it via more tables/views or by php queries creating similar to Jim’s (in an output to json/txt file whatever, though all that is as mentioned months (+) away, so don’t hold your breath waiting for a final script, as it …is a long way off :roll:

The idea of injecting WxSim into MySql, has been a passion of mine for ages, so now a functional concept is available, I hope others enhance it, make it more robust and eventually share as well. Fell free to do what you like (in fact please do) as I will take ages and mine will never be as good as the gurus out there.

I should also add that the core underlying reason I have for doing this is to not only try and display Forecast vs Actual in a more Dynamic / Realtime way, but also to be able to eventually utilize a database of WxSim outputs from an analytical point, relating to validity of Zulu Runs etc. Like the old (" 00Z and 12Z runs are more accurate than the 06Z and 18Z runs, probably because there’s more real data (including upper air soundings) going into the 00Z and 12Z ") and hopefully display that and utilize findings of how and when to run WxSim. I appreciate WxSim’s inboard analysis is brilliant, I would just like to do further checks and hopefully visualize them in a nice way (along the way). I have never been one for Mines Better than Yours approach to historic weather display, I’d rather walk around (if you like in my underpants) and display all for whoever to see, butonly these days against my own forecast. I lost the passion a while back to compare against what resulted in an inconsistent availability of some data that was available over time.

I hold the highest regard for Tom (WxSim) always will and recall his response to the above question regrading variation in runs:

Note that any increase in accuracy of, say 00Z over 06Z might be compensated for in short term forecasts, just by being more up to date, so I've never really recommended NOT using 06 or 18Z. It's hard to say whether or not they represent any real improvement over their 6-hour-earlier counterparts.

Tom

Brilliant, the only issue I have is that since the implementation of ( 0.25 res GFS vs 0.5 GFS ) is that I will have Torrential Rain one run then None the next, or Snow to 150 metres one run and 1,280 metres the next. Really gets to me.

Kind regards,

Thanks for you reply Tony.
Second step it’s ok now.

I’ve an error on third step:

3: In your browser run : " your website address /wxsimcsvmysql/3wxsim_create_views_views_run_once_only.php "
Error creating RealtimeComparevw (view) : Table 'dvjfcnae_4.Realtime' doesn't existError creating WxsimComparevw (view) : Table 'dvjfcnae_4.RealtimeComparevw' doesn't existError creating WxSimActTempLowHighDelta (view) : Table 'dvjfcnae_4.RealtimeComparevw' doesn't exist

Any idea?
Regards,

Alessandro

Hi Alessandro,

It is telling you that there is no ‘Realtime’ Table in your ‘dvjfcnae_4’ MySql Database.

As stated in earlier posts these comparison parts of the script are for Cumulus users who are uploading data to a Realtime Table, if that is not the case it will not work.

1: Must be using Cumulus.
2: Must be uploading to a ‘Realtime’ Table, ensure you have the following set correctly in ’ 1wxsim_settings.php '. (is it ‘Realtime’ or is it ‘realtime’.


$cu_realtimename = "Realtime"; 		// Name of your Cumulus realtime table ( Note : case Sensative ) is it "Realtime" or "realtime" change accordingly
$cu_realtime_datefield = "LogDateTime"; 	// Name of your Cumulus realtime Date Field ( Note : case Sensative ) is it "LogDateTime" or "DateTime" or "datetime" change accordingly
$cu_realtime_days = 7; 			    	// Number of Days your Cumulus realtime Table retains Data. or some people only upload 2 days etc.

If you have the above and set correctly, it should work.

kind regards,

Hi Tony,
you did a nice job with this script, thank you so much for sharing

but I’ve same error as Alessandro on third step:

Error creating RealtimeComparevw (view) : Table 'Sql686728_4.realtime' doesn't existError creating WxsimComparevw (view) : Table 'Sql686728_4.RealtimeComparevw' doesn't existError creating WxSimActTempLowHighDelta (view) : Table 'Sql686728_4.RealtimeComparevw' doesn't exist

in first step I’ve this setup:

/* Main Cumulus Realtime Table Settings */												
/**************************************************/				
$cu_realtimename = "realtime"; 				    	// Name of your Cumulus realtime table ( Note : case Sensative ) is it "Realtime" or "realtime" change accordingly
$cu_realtime_datefield = "LogDateTime"; 			// Name of your Cumulus realtime Date Field ( Note : case Sensative ) is it "LogDateTime" or "DateTime" or "datetime" change accordingly
$cu_realtime_days = "7"; 			    		// Number of Days your Cumulus realtime Table retains Data.

I be using Cumulus v. 1.9.4

Regards


realtime.txt (259 Bytes)

Hi Paolo F.

All I can think of : is your (date/Time) field in yor ( realtime / table ) : “LogDateTime” or something else like “DateTime” or “logdatetime” etc.

Sorry aside from that, I am unsure., I am not in my office at present, about to have tea and off to bed.

I will look at scripts after work tomorrow night.

kind regards,

Thanks for you reply

I’ve tried probably all combinations of “datetime” but without success. is there a way to know the correct name?

Look in phpmyadmin for the actual name or look in your "Import Cumulus " file.