Extracting Data from the Weather Underground APIEric NessBlockedUnblockFollowFollowingFeb 26Photo by Jeremy Bishop on UnsplashMany companies are beginning to bring external data into their data infrastructure.
One of the most common and easily available types of external data is weather data.
There are several excellent sources of weather data.
If you need historical weather data then Fetch Climate is a great resource.
The data has been combined from many sources and patched together into a consistent whole.
The Microsoft Research project was conceived of and led by Drew Purves.
Fetch Climate doesn’t include current weather data, though.
Recently I was working with a client that needed a daily average temperature inserted into a table in their SQL Server database.
Another resource on the web, Weather Underground, publishes current daily weather data.
It also has an API that you can program against.
Since there isn’t much documentation out there about how to connect to the Weather Underground API using a .
Net client, I decided to document how I accessed it and inserted the data into SQL Server using a CLR assembly.
The first step is to sign up for a developers account on Weather Underground.
This will give you a personal key that you can use to authenticate against the API.
The free Developer account gives you 500 API calls per day for free.
This should be plenty to get started and it’s easy to upgrade later if your business case justifies it.
You use a http request to call the API and it returns either a JSON or XML object with the requested weather data included.
Since we’ll be accessing the API using .
Net, XML will be the easiest to parse.
We’ll need to create a C# Class Library to store the code for the SQL Server CLR library.
I used Visual Studio 2010 as the development environment.
The end result that we want to achieve is a SQL function that takes a date and a Weather Underground key and returns a decimal with the average temperature for that day.
We’ll use the .
Net XML library as well as the SQL Server API to achieve this end.
The code that can fetch the data looks like:Let’s walk through a few parts of this code.
The weatherUndergroundAddress variable stores the http address to fetch the weather for Minneapolis.
It’s simple to get weather data for any major U.
S.
city by substituting its state code and name into the URL.
We’ll substitute the key and date into the string as parameters.
The tempXPath stores the path to extract the temperature from the XML.
There are multiple temperature readings per day that the code averages out.
The first step in the function code creates a new XmlDocument to store the retrieved XML.
This variable is then loaded by using Load to retrieve data from the http address.
A foreach loop then averages out the temperatures during the day and finally the result is returned to the caller.
At this point I created an application to wrap the library in for testing.
It was simply a C# Console project with a reference to the class library.
I’ll provide the code, but won’t go into detail on it.
It simply displays the average temperature to the console window.
Once the class library has been tested successfully, all we need to do is to install the assembly into SQL Server as a CLR library.
Be sure to enable CLR before you take the next steps.
The first step is to make sure that the database will run the CLR code securely.
Two ways to do this are to cryptographically sign the code and the other is to set the TRUSTWORTHY property to ON.
Be sure to understand the implications of this choice in your own environment.
We’ll use the TRUSTWORTHY property to keep things simple.
Next we’ll create a function that uses the assembly.
This is essentially a wrapper for the CLR function.
Finally we’ll test the function.
Once the function has been created you can use it like any other function in SQL Server.
Note: This story was originally published on my blog decisivefigures.
com on 2013–04–01.
It has been moved here since that blog has been shut down.
.