Weather Scraper to Get Weather Information For Your Data Warehouse and Reporting/Analytical Needs

Introduction

Weather Scraper is used to get weather information for your data warehouse and reporting/analytical needs.

Create a SQL table to store the weather information:

CREATE TABLE [dbo].[Weather](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [InsertDate] [varchar](255) NULL,
    [ZipCode] [varchar](255) NULL,
    [CityID] [varchar](255) NULL,
    [CityName] [varchar](255) NULL,
    [CoordLong] [varchar](255) NULL,
    [CoordLat] [varchar](255) NULL,
    [Country] [varchar](255) NULL,
    [SunriseStart] [varchar](255) NULL,
    [SunriseSet] [varchar](255) NULL,
    [TemperatureAvg] [varchar](255) NULL,
    [TemperatureMin] [varchar](255) NULL,
    [TemperatureMax] [varchar](255) NULL,
    [TemperatureUnit] [varchar](255) NULL,
    [HumidityValue] [varchar](255) NULL,
    [HumidityUnit] [varchar](255) NULL,
    [PressureValue] [varchar](255) NULL,
    [PressureUnit] [varchar](255) NULL,
    [WindSpeedValue] [varchar](255) NULL,
    [WindSpeedName] [varchar](255) NULL,
    [WindDirectionValue] [varchar](255) NULL,
    [WindDirectionCode] [varchar](255) NULL,
    [WindDirectionName] [varchar](255) NULL,
    [CloudValue] [varchar](255) NULL,
    [CloudName] [varchar](255) NULL,
    [PrecipitationMode] [varchar](255) NULL,
    [WeatherNumber] [varchar](255) NULL,
    [WeatherValue] [varchar](255) NULL,
    [WeatherIcon] [varchar](255) NULL,
    [LastUpdateValue] [varchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

After Table is created, we will use the http://api.openweathermap.org RESTful API to access and store the Weather Information.

You can see sample weather information returned from query by accessing this link.

SSIS Package

The package is very simple:

1. Get List all the ZipCodes
2. Loop through each ZipCode and Get Current Weather Information For.

Details:

SELECT DISTINCT [ZipCode] FROM [dbo].[ZipCodes] order by ZipCode Desc 

Store results in variable.

Loop through each individual ZipCode in the ForEachLoop container.

Map the individual Zip Codes to a ZipCode variable.

Pass the “loaded” ZipCode variable in the ForEachLoop container to the script task so as to pull the weather information for a particular ZipCode.

Edit the script task. To see the code:

This is the key:

• Build your URL using the ZipCode in order to get the result.
• I specify USA in the string to return only US results. There is much documentation on the openweathermap website on how to search for specific data.
• http://api.openweathermap.org/API#search_city

var url = @"http://api.openweathermap.org/data/2.5/weather?q="+ZipCode+",USA&mode=xml"; 

The two methods in my implementation are the main() method and the SaveWeatherData():

MainMethod builds URL, makes call to API, and parses out the resulting XML.

SaveWeatherData method is called by main method. It takes parameter values and persists them in the database table.

Each time the script tasked is called, the weather data for that ZipCode be returned and inserted into your table.

#region Help:  Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
 * a .Net application within the context of an Integration Services control flow. 
 * 
 * Expand the other regions which have "Help" prefixes for examples of specific ways to use
 * Integration Services features within this script task. */
#endregion

#region Namespaces

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net;
using System.IO;
using System.Xml;
using System.Text;
using System.Data.SqlClient;

#endregion

namespace ST_e848ffd56e444ae4a1307413b7ff543d
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        /// </summary>
        /// http://api.openweathermap.org/data/2.5/weather?q=55441&mode=xml
        /// http://api.openweathermap.org/data/2.5/forecast/daily?q=London&mode=xml&units=metric&cnt=33 forcast
        string DatabaseName = null;
        string ServerName = null;

        public void Main(){
            string CityID = null;
            string CityName = null;
            string CoordLong = null;
            string CoordLat = null;
            string Country = null;
            string SunriseStart = null;
            string SunriseSet = null;
            string TemperatureAvg = null;
            string TemperatureMin = null;
            string TemperatureMax = null;
            string TemperatureUnit = null;
            string HumidityValue = null;
            string HumidityUnit = null;
            string PressureValue = null;
            string PressureUnit = null;
            string WindSpeedValue = null;
            string WindSpeedName = null;
            string WindDirectionValue = null;
            string WindDirectionCode = null;
            string WindDirectionName = null;
            string CloudValue = null;
            string CloudName = null;
            string PrecipitationMode = null;
            string WeatherNumber = null;
            string WeatherValue = null;
            string WeatherIcon = null;
            string LastUpdateValue = null;
            string ZipCode = Dts.Variables["ZipCode"].Value.ToString();
            DatabaseName = Dts.Variables["$Project::DatabaseName"].Value.ToString();
            ServerName = Dts.Variables["$Project::ServerName"].Value.ToString();
            var url = @"http://api.openweathermap.org/data/2.5/weather?q="+
                      ZipCode+",USA&mode=xml";

            // Synchronous API Consumption
            var syncClient = new WebClient();
            var content = syncClient.DownloadString(url);

            StringBuilder output = new StringBuilder();

            // Create an XmlReader
            using (XmlReader reader = XmlReader.Create(new StringReader(content)))
            {
                XmlWriterSettings ws = new XmlWriterSettings();
                ws.Indent = true;
                using (XmlWriter writer = XmlWriter.Create(output, ws))
                {
                    // Parse the file and display each of the nodes.
                    while (reader.Read())
                    {
                        // Only detect start elements.
                        if (reader.IsStartElement())
                        {
                            //MessageBox.Show("ReadName: " + reader.Name);
                            switch (reader.Name.Trim())
                            {
                                case "city":
                                    CityID = reader.GetAttribute("id");                                  
                                    CityName = reader.GetAttribute("name");
                                    break;
                                case "coord":
                                    CoordLong = reader.GetAttribute("lon");
                                    CoordLat = reader.GetAttribute("lat");
                                    break;
                                case "country":
                                    Country = reader.ReadElementString();
                                    break;
                                case "sun":
                                    SunriseStart = reader.GetAttribute("rise");
                                    SunriseSet = reader.GetAttribute("set");
                                    break;
                                case "temperature":
                                    TemperatureAvg = reader.GetAttribute("value");
                                    TemperatureMin = reader.GetAttribute("min");
                                    TemperatureMax = reader.GetAttribute("max");
                                    TemperatureUnit = reader.GetAttribute("unit");
                                    break;
                                case "humidity":
                                    HumidityValue = reader.GetAttribute("value");
                                    HumidityUnit = reader.GetAttribute("unit");
                                    break;
                                case "pressure":
                                    PressureValue = reader.GetAttribute("value");
                                    PressureUnit = reader.GetAttribute("unit");
                                    break;
                                case "speed":
                                    WindSpeedValue = reader.GetAttribute("value");
                                    WindSpeedName = reader.GetAttribute("name");
                                    break;
                                case "direction":
                                    WindDirectionValue = reader.GetAttribute("value");
                                    WindDirectionCode = reader.GetAttribute("code");
                                    WindDirectionName = reader.GetAttribute("name");
                                    break;
                                case "clouds":
                                    CloudValue = reader.GetAttribute("value");
                                    CloudName = reader.GetAttribute("name");
                                    break;
                                case "precipitation":
                                    PrecipitationMode = reader.GetAttribute("mode");
                                    break;
                                case "weather":
                                    WeatherNumber = reader.GetAttribute("number");
                                    WeatherValue = reader.GetAttribute("value");
                                    WeatherIcon = reader.GetAttribute("icon");
                                    break;
                                case "lastupdate":
                                    LastUpdateValue = reader.GetAttribute("value");
                                    break;
                            }
                        }
                    }
                } 
            }
            //Save the Results to Database
            this.SaveWeatherData(ZipCode, CityID,  CityName, CoordLong,  CoordLat,  
              Country,  SunriseStart,  SunriseSet,  TemperatureAvg,  TemperatureMin,  
              TemperatureMax,  TemperatureUnit,  HumidityValue,  HumidityUnit,  PressureValue,  
              PressureUnit,  WindSpeedValue,  WindSpeedName,  WindDirectionValue,  WindDirectionCode,  
              WindDirectionName,  CloudValue,  CloudName,  PrecipitationMode,  WeatherNumber,  
              WeatherValue, WeatherIcon,  LastUpdateValue);
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        
        public void SaveWeatherData(string ZipCode, string CityID, string CityName, 
          string CoordLong, string CoordLat, string Country, string SunriseStart, 
          string SunriseSet, string TemperatureAvg, string TemperatureMin, 
          string TemperatureMax, string TemperatureUnit, string HumidityValue, 
          string HumidityUnit, string PressureValue, string PressureUnit, 
          string WindSpeedValue, string WindSpeedName, string WindDirectionValue,
          string WindDirectionCode, string WindDirectionName, string CloudValue, 
          string CloudName, string PrecipitationMode, string WeatherNumber, 
          string WeatherValue, string WeatherIcon, string LastUpdateValue)
        {
            try
            {
                string connectionString = @"Replace Me";
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    SqlCommand Storproc = new SqlCommand(@"INSERT INTO [dbo].[Weather] (ZipCode, CityID, CityName,
                            CoordLong,
                            CoordLat,
                            Country,
                            SunriseStart,
                            SunriseSet,
                            TemperatureAvg,
                            TemperatureMin,
                            TemperatureMax,
                            TemperatureUnit,
                            HumidityValue,
                            HumidityUnit,
                            PressureValue,
                            PressureUnit,
                            WindSpeedValue,
                            WindSpeedName,
                            WindDirectionValue,
                            WindDirectionCode,
                            WindDirectionName,
                            CloudValue,
                            CloudName,
                            PrecipitationMode,
                            WeatherNumber,
                            WeatherValue,
                            WeatherIcon,
                            LastUpdateValue)
                            VALUES (
                            @ZipCode,
                            @CityID,
                            @CityName,
                            @CoordLong,
                            @CoordLat,
                            @Country,
                            @SunriseStart,
                            @SunriseSet,
                            @TemperatureAvg,
                            @TemperatureMin,
                            @TemperatureMax,
                            @TemperatureUnit,
                            @HumidityValue,
                            @HumidityUnit,
                            @PressureValue,
                            @PressureUnit,
                            @WindSpeedValue,
                            @WindSpeedName,
                            @WindDirectionValue,
                            @WindDirectionCode,
                            @WindDirectionName,
                            @CloudValue,
                            @CloudName,
                            @PrecipitationMode,
                            @WeatherNumber,
                            @WeatherValue,
                            @WeatherIcon,
                            @LastUpdateValue
                            );", conn);

                    Storproc.Parameters.AddWithValue("@ZipCode", ZipCode);
                    Storproc.Parameters.AddWithValue("@CityID", "10");
                    Storproc.Parameters.AddWithValue("@CityName", CityName);
                    Storproc.Parameters.AddWithValue("@CoordLong", CoordLong);
                    Storproc.Parameters.AddWithValue("@CoordLat", CoordLat);
                    Storproc.Parameters.AddWithValue("@Country", Country);
                    Storproc.Parameters.AddWithValue("@SunriseStart", SunriseStart);
                    Storproc.Parameters.AddWithValue("@SunriseSet", SunriseSet);
                    Storproc.Parameters.AddWithValue("@TemperatureAvg", TemperatureAvg);
                    Storproc.Parameters.AddWithValue("@TemperatureMin", TemperatureMin);
                    Storproc.Parameters.AddWithValue("@TemperatureMax", TemperatureMax);
                    Storproc.Parameters.AddWithValue("@TemperatureUnit", TemperatureUnit);
                    Storproc.Parameters.AddWithValue("@HumidityValue", HumidityValue);
                    Storproc.Parameters.AddWithValue("@HumidityUnit", HumidityUnit);
                    Storproc.Parameters.AddWithValue("@PressureValue", PressureValue);
                    Storproc.Parameters.AddWithValue("@PressureUnit", PressureUnit);
                    Storproc.Parameters.AddWithValue("@WindSpeedValue", WindSpeedValue);
                    Storproc.Parameters.AddWithValue("@WindSpeedName", WindSpeedName);
                    Storproc.Parameters.AddWithValue("@WindDirectionValue", WindDirectionValue);
                    Storproc.Parameters.AddWithValue("@WindDirectionCode", WindDirectionCode);
                    Storproc.Parameters.AddWithValue("@WindDirectionName", WindDirectionName);
                    Storproc.Parameters.AddWithValue("@CloudValue", CloudValue);
                    Storproc.Parameters.AddWithValue("@CloudName", CloudName);
                    Storproc.Parameters.AddWithValue("@PrecipitationMode", PrecipitationMode);
                    Storproc.Parameters.AddWithValue("@WeatherNumber", WeatherNumber);
                    Storproc.Parameters.AddWithValue("@WeatherValue", WeatherValue);
                    Storproc.Parameters.AddWithValue("@WeatherIcon", WeatherIcon);
                    Storproc.Parameters.AddWithValue("@LastUpdateValue", LastUpdateValue);

                    conn.Open();

                    Storproc.ExecuteNonQuery();
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                conn.Close();
            }
        } 

        #region ScriptResults declaration

        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        ///  
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion 
    }
}

The execution looks like this:

Your results should look like this:

Now, you have detailed weather information with date and zip codes at your disposal. You can tie this with location information in your database or data warehouse to do extensive querying. For example:

• How does rain affect my sales by region
• How does humidity affect sales
• How does cloud cover affect sales
• How does weather affect tips
• How does weather affect Employee productivity

The job can be scheduled to run hourly, daily, weekly or whatever frequency you want.

The sky (pun intended) is virtually the limit on this.

Good luck!