For some reason the maxmum wind direction value in my MySQL database is 16! Now I know the prevailing wind here is from one direction (westerly) but we have had winds from other directions in the last 20 months. I’ve just tested the wind_direction database field and there isn’t a problem with it…I can store large values in it.
Any idea why this is happening?
ah, it must be using only 1 to 16 cardinal
i.e *22.5 to get the correct reading
I willl look into that
but maybe I cant change it now
The odd thing is that your MySQL data table has the full 0-359 degree value and the other data table we are playing with also has the full 0-359 degree value in it from my station. I don’t think it’s an ordinal (22.5 degrees) problem. The numbers range from 0-16. 0 and 16 would effectively be the same thing.
I’ve just been doing some more checking. I do have some values > 16 in them, but none for dates before 26th September this year (when did we get WDMYSQL working…was it at the end of September?) However there are only 10,000 of them (out of approx 144,000 records). This doesn’t seem like enough, especially as I’d expect my prevailing wind dir to be 270-ish.
Hmmm…I think I know the difference now. Data created using a live feed seems to have the correct 0-359 values. Monthly data backfills from stored data only have values of 0-16. I’ve not proved this yet, but another thing is leading me to think this. If you have your live feed running each minute, the records tend to get uploaded at 1 second past the minute intervals, e.g. 08:08:01. The data backfill doesn’t have seconds available to it, so uploads records timed with zero seconds, e.g. 08:08:00. I’ve got records like these…
Blackpool- 2004-09-26 13:59:00 11
Blackpool- 2004-09-26 13:59:01 262
If you assume that the calculation is(11+1) * 22.5 then you get 270degrees which is the closest cardinal point to 262degrees. Does the stored monthly data only have cardinal points stored for wind direction?
This also highlights another problem. If you backfill missing data for months that you have been running a live data feed you will almost certainly end up with duplicate records, e.g. the same data for 13:59:00 and 13:59:01.
I’m not sure how we fix this yet and I really should go down to see my wife and kids now! I’ll have a think whilst I’m eating my breakfast and see if I can come up with a cunning plan to fix this (which might involve a modified WDMYSQL and me re-uploading all my missing data).
Chris
I had this problem last week with WDDatabase stopping and missing a couple of days records.
Because I’m crap at SQL I used access, imported the data from MYSQL removed the seconds so I only had the time serial with date hours and minutes. Did the same with the live data and then did the import.
There’s probably a better way but this worked
Cheers
Keith
Once Brian has worked out why the wind direction is being entered in 16 point cardinal values I’ll probably re-load a lot of the old data. I don’t have Access at home, but even if I did I think that unloading over 800,000 records to pass them through Access and then re-loading them into MySQL might take a while! I don’t think it should be too trick to come up with a SQL statement to delete the duplicates, but WDMYSQL really should be modified to drop the seconds anyway because they don’t really add anything to the data.