Sample NWS Database System

Download | Documentation | Resources | Sites

This is an example application with explanation that allows authors to create a database of weather reports using FreeData_NWS with or without using the ColdFusion scheduling engine.

Download back to top

This system requires Macromedia's Cold Fusion development enviroment, version 4.1x or later. All downloads and code are covered by our Source Code Policy.

This archive contains the FreeData_NWS CFML Custom Tag, the FreeData_NWS_DBUpdate.cfm ColdFusion Template and the (empty) FreeData_NWS.mdb Microsoft Access database.

FreeData_NWS_DB.zip, 12.6kb Zip-Format Archive

This simple system lets website authors keep an archive of weather reports using the FreeData_NWS custom tag. This also makes page load times faster by moving the weather reports to the local system. This system can work with or without the ColdFusion scheduling engine.

System Installation

Installation procedures:

This system utilizes a Microsft Access Database, FreeData_NWS.mdb (included in the archive), and by default will look for an ODBC source named "FreeData_NWS". Check your ColdFusion manual for information on managing Datasources.
The system includes the FreeData_NWS CFML Custom Tag (also by the Depressed Press of Boston). By default the database update system and the custom tag are expected to be in the same folder.
If desired a ColdFusion scheduler profile can be created to call FreeData_NWSDBUpdate.cfm once an hour. This will ensure that reports are gathered regulary. Note that even though weather reports are only created by the NWS once an hour setting the scheduler to repeat two or three times an hour can help to ensure that every report is collected: if one attempt fails for some reason there's a second or third chance.
If no schedule is created then the FreeData_NWSDBUpdate.cfm template must be CFINCLUDed into another page on the site and traffic will dictate the number of reports gathered. The more popular the page hosting the FreeData_NWSDBUpdate.cfm the more reports gathered.

FreeData_NWSDBUpdate.cfm

This template, which can be included in other templates or called with the CFScheduling engine, checks for current reports and adds them to the database. In ensures that duplicate reports aren't added:

NWSDB: Code Listing 1
1 <cfset RanTag = "N"> 2 <cfset currenttime = #dateformat(now(), "MMM d, yyyy")# & " " & #timeformat(now(), "h:mmtt")#> 3 <cfquery datasource="FreeData_NWS" name="getreport"> 4 Select ReportTime from Weather 5 where ReportID = (select MAX(ReportID) from Weather) 6 </cfquery> 7 <cfset LastReportTime = getreport.ReportTime> 8 <cfif NOT #IsDate(getreport.ReportTime)#> 9 <cfset LastReportTime = #DateAdd("n", -100, Now())#> 10 </cfif> 11 <cfif #datediff("n",LastReportTime,currenttime)# GT 60> 12 <cfmodule template="FreeData_NWS.cfm" 13 State="MA" 14 City="BOSTON" 15 VarPrefix="NWS_" 16 NoDataVal="000"> 17 <cfif #WeatherFailed# NEQ "Y"> 18 <cfset RanTag = "Y"> 19 <cfif #NWS_ReportTime# NEQ #LastReportTime#> 20 <cfquery datasource="FreeData_NWS" name="AddReport"> 21 insert into WeatherReports 22 (ReportTime, 23 SkyCondShrt, 24 SkyCond, 25 Temp, 26 DewPT, 27 Humidity, 28 WindDir, 29 WindDirShrt, 30 WindSpeed, 31 GustSpeed, 32 Pressure, 33 PressureActivity) 34 values ('#NWS_ReportTime#', 35 '#NWS_SkyCondShrt#', 36 '#NWS_SkyCond#', 37 #NWS_Temp#, 38 #NWS_DewPT#, 39 #NWS_Humidity#, 40 '#NWS_WindDir#', 41 '#NWS_WindDirShrt#', 42 #NWS_WindSpeed#, 43 #NWS_GustSpeed#, 44 #NWS_Pressure#, 45 '#NWS_PressureActivity#'); 46 </cfquery> 47 </cfif> 48 </cfif> 49 </cfif>
Open this Code in a larger Window:
With Line Numbers or Without Line Numbers

(Line 1) First we set RanTag to "N" to indicate that we haven't run the FreeData_NWS tag. This variable can be used later in the host template to, for example use the results of the tag instead of doing a database select for the latest report.

(Line 2) Then set CurrentTime to the current server time, formatted in the same manner as the weather report time. Note that this is the sever time, if your server is in a different time zone than the area whose weather you're gathering you'll have to adjust this.

(Lines 3-7) Now we make a request of the database for the latest weather report. Then LastReportTime is set to the report time of that report.

(Lines 8-10) If the latest time is not a date (which will happen with an empty database), create a dummy time stamp over an hour old.

(Lines 11-16) If the latest report is more than 60 minutes older than the current time, call the FreeData_NWS tag to get the currently available report from the NWS. Note that many of the database fields are numeric so NoDataVal is set to a numeric ("000") which allows unknown or bad values to be added to the database easily.

(Lines 17-18) If the report returned is valid (WeatherFailed doesn't equal "Y") set RanTag to "Y":.

(Lines 19-46) If the datestamp on the frshly retrieved report is different from the latest report in the database then write a new report to the database. This prevents duplicate reports:

(Lines 47-49) Close all the CFIF statements:

Retrieving Data

To give new authors a head start here are some examples of common SQL calls to retrieve data from the database that you've created. Note that the "VarPrefix" (if any) is not stored in the database!

Most Recent Report
The CFQuery statement needed to pull the most recent weather report from the database:

NWSDB: Code Listing 2
1 <cfset RanTag = "N"> 2 <cfset currenttime = #dateformat(now(), "MMM d, yyyy")# & " " & #timeformat(now(), "h:mmtt")#> 3 <cfquery datasource="FreeData_NWS" name="getreport"> 4 Select ReportTime from Weather 5 where ReportID = (select MAX(ReportID) from Weather) 6 </cfquery> 7 <cfset LastReportTime = getreport.ReportTime> 8 <cfif NOT #IsDate(getreport.ReportTime)#> 9 <cfset LastReportTime = #DateAdd("n", -100, Now())#> 10 </cfif> 11 <cfif #datediff("n",LastReportTime,currenttime)# GT 60> 12 <cfmodule template="FreeData_NWS.cfm" 13 State="MA" 14 City="BOSTON" 15 VarPrefix="NWS_" 16 NoDataVal="000"> 17 <cfif #WeatherFailed# NEQ "Y"> 18 <cfset RanTag = "Y"> 19 <cfif #NWS_ReportTime# NEQ #LastReportTime#> 20 <cfquery datasource="FreeData_NWS" name="AddReport"> 21 insert into WeatherReports 22 (ReportTime, 23 SkyCondShrt, 24 SkyCond, 25 Temp, 26 DewPT, 27 Humidity, 28 WindDir, 29 WindDirShrt, 30 WindSpeed, 31 GustSpeed, 32 Pressure, 33 PressureActivity) 34 values ('#NWS_ReportTime#', 35 '#NWS_SkyCondShrt#', 36 '#NWS_SkyCond#', 37 #NWS_Temp#, 38 #NWS_DewPT#, 39 #NWS_Humidity#, 40 '#NWS_WindDir#', 41 '#NWS_WindDirShrt#', 42 #NWS_WindSpeed#, 43 #NWS_GustSpeed#, 44 #NWS_Pressure#, 45 '#NWS_PressureActivity#'); 46 </cfquery> 47 </cfif> 48 </cfif> 49 </cfif>
Open this Code in a larger Window:
With Line Numbers or Without Line Numbers

Specific Day's Report(s)
Populate "RequestedDate" with a valid date to get all the weather reports archived for that date. Note that depending on your archive method and archive size many dates will return a null recordset, so check for this in your code!: