 It's
a rainy day, crops in, cows long gone as is almost all the decades old
web customers either retired, dead or moving on...
SO why not code?
After all the weather station is long repaired, monthly
summery tables are conveniently shared (stolen, borrowed for free)
by Weather Underground (and not on an ancient dying pc drive) ... call
it a win. So...
First we need to drag out the old 2012 Excel database and modify a
few entries to swallow the new text based table (which we get off of
WU with a select, control-c to copy and control-p paste into a quadrant
of Excel)
For the math on GDU: North
Dakota Ag Weather Network is as simple to understand as any
NOW the fun part. Weather Undergound adds " °F" so we
are going to have to get the length of the temperatures (I assume we
will see +100 days so gotta mess with that unknown) & as rain ends
in " in" inches ... well actually the same excel string to
data conversion formula will work (just the cells we inspect change)
=IF(VALUE(M4>""),VALUE(LEFT(M4,LENGTH(M4)-3)),0)
Where M4 is the column and row we are in (April 1 in this case).
And to be a bit anal regressive I want a "0" in days yet to
come (just hate to see an "ERR" in a future column merely
waiting for that day to arrive)
OK now the pain in the ass, so I want to put this in an SQL
database (why not! For 20 years I have been inventing elevator price
charting, keeping as you go harvest records, posting interest rate
updates, maintaining herd livestock records locally and more crazy to
self designed SQL PHP CGI driven crap. Why slum it now!)
So that plain Jane 4/1 is pretty much non compatible AND it MUST be
YYYY:MM:DD so simple math (gotta pad days and months only 1 digit
long) not going to fly:
="2022:"&RIGHT("0"&STRING(MONTH(L4),0),2)&":"&RIGHT("0"&STRING(DAY(L4),0),2)
Where again the L column is where I begin pasting the Weather
Underground data
NOW the nightmare begins. You are going to have to copy and paste
the 4 columns of Excel results into notepad (to get values not
formulas) then select and paste notepad content into a blank Excel to
get to a point you can save it as a "comma delimited"
separated values (.CSV)
"Got Values" ... BUT WAIT, we don't have an SQL table to import
them into
(no problem, hackers go away, not going to explain that.)
BUT those who are not lost yet, sure simply go into your web panel
and create the SQL database, user & password...
then the 1 table to go with
CREATE TABLE `GDU` (
`DATE` date NOT NULL DEFAULT '0000-00-00',
`HI` decimal(4,1) DEFAULT NULL,
`LO` decimal(4,1) DEFAULT NULL,
`RAIN` decimal(5,2) DEFAULT NULL
);
If you are totally newbie think of it as a drive
(your site) with a folder (database) containing one file (table).
Horribly over simplified but same as a Website is Vanity Plates
(domain name) on a Vehicle (host) driven by you or someone like me
(web designer) who both increasingly have to be the security at the
door (no fun at all, trust me, unpaid slave labor keeping people all
too safe who have no clue the threats ... I digress)
NOW it is simply a matter of importing the Excel based GDU.CSV file
(created earlier) or manually entering information periodically. Of
course that is much more complicated PHP admin page creation (like
used to add harvest tickets, daily grain bids, periodic bank rate
increases, add records to an angus sale listing ... not for the faint
of heart but for now at least a way to add clips of weekly hi/low/rain
a manual way. Part III if you will.
The math will be HI no greater then 86° and low no lower then
50°.
Average, Divide by 2 and subtract 50. Any HI/LO below 50° is just a 0
GDU day. In Excel (for line 4) if your HI is in column B and LO in
column C then formula would be:
=((IF($B4>50,IF(B4>86,86,B4),50)+IF(C4<50,50,C4))/2)-50
Part II is some way to pull raw data now stored and draw
pretty charts. In Perl (pulling from an SQL database) one simple method is:
$gdh = $Row->{HI};
if($gdh > 86){ $gdh=86 };
if($gdh < 50){ $gdh=50 };
$gdl = $Row->{LO};
if($gdl < 50){ $gdl=50 };
$gdd = (($gdh + $gdl)/2)-50;
$gdd = sprintf("%.1f", $gdd);
yes I am assuming no overnight lows higher then 86°
In the interim we just use Windows Snippet and upload
screen shots time to time so we can enjoy semi-retirement as in we
never will think we are rich enough to fully retire. WORK keeps us
from spending money foolishly after all ! ?
(Coming maybe if it keeps raining)
|
