A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


SQL Server 2012 CLR Function - Google Web Services : Address Distance Matrix API

Google Documentation:  https://developers.google.com/maps/documentation/distance-matrix/

 

I created a CLR function that goes out to the google api and pulls in distances between two addresses. 

Doing this through the CLR was not the most ideal scenario but it worked for this project. I probably wouldn't have an API call in the middle of my set based operations but this function will be used infrequently and the results of distances between common points will be stored in a look up table. This exercise will not include that look up table. We will just go through the steps to get and API key for the Google Address API and Create the CLR Function. 

Result: 

 

Step 1. Get a google api address key. 

https://developers.google.com/maps/documentation/javascript/get-api-key

Sign in with your google account. 

Agree to the terms if you want to use someone else's hard work. 

Click "Create and Enable API". You should see the screen below. 

We have enough to begin development. 

You can test your key by using the following url : add your key. 

URL Test:

https://maps.googleapis.com/maps/api/distancematrix/json?origins=Vancouver+BC|Seattle&destinations=San+Francisco|Victoria+BC&key=AIzaSyDKWneL-smvFthXjZo8PXXN4MMlhcgCjbg

You should get a JSON response similar to this. 

 

Step 2. Now, Open Visual Studio 2012 

 Select a new SQL Server Project. If you don't see SQL Server Database Projects, you may need to install "SQL Server Data Tools".

It can be found here : https://msdn.microsoft.com/en-us/library/mt674919.aspx


Name your project  , click ok. 

Right click your solution and add a new item. 

 

Choose SQL CLR C# > SQL CLR  C# user Defined Function > Name your function > Click Add. 
(if these options are not available , install sql server data tools

 

 

You should now see a code stub like this: 

 

Add some code logic here. You may want to add try or catch statements where, and also maybe handle query limit errors from google which is returned when you've hit account limits. You'll find none of that here, just the meat of the logic.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
using System.IO;
using System.Xml;
using System.Data;
using System.Web;


public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString clr_f_address_distance_calculator_in_miles(string origin, string destination, string api_key)
    {

        string request_url = "https://maps.googleapis.com/maps/api/distancematrix/xml?units=imperial&origins=" + origin + "&destinations=" + destination + "&key=" + api_key;
        WebRequest request = WebRequest.Create(request_url);
        WebResponse response = request.GetResponse();
        Stream dataStream = response.GetResponseStream();
        StreamReader reader = new StreamReader(dataStream);
        XmlDocument xml = new XmlDocument();
        xml.LoadXml(reader.ReadToEnd());

        string distance_in_miles = xml.SelectSingleNode("/DistanceMatrixResponse/row/element/distance/text").InnerText.Replace(" mi", "");
      
        return new SqlString(distance_in_miles);
    }
}

 

Now it's time to deploy. There are a couple of things to do before the deploy. 

Make sure your DB is setup for CLR integration

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'clr enabled', 1
GO 
RECONFIGURE  

Also, make sure the db running the function is trustworthy:
ALTER DATABASE <YOUR DB> SET TRUSTWORTHY ON
Set the project permission levels of your new function to "UNSAFE". 




Also, make sure your target platform is setup correctly.



You now have new options under the build menu for this project type. While focused on the .cs file, Click "Build".
You'll see the option to publish. Click publish ...

Choose your database , click publish.



You must be sa to do all of the above in SQL.
Now test your function :
select dbo.clr_f_address_distance_calculator_in_miles(
	'3833 Main St, Culver City, CA 90232'
	,'1517 Lincoln Blvd, Santa Monica, CA 90401'
	,'<your api key>') [miles]


 

 

 

 

 

 

 

 

 

Create SQL Server CLR Function (Date Time Conversion) UTC to PDST

 

Step 1. Enable the CLR integration with SQL 
sp_configure 'show advanced options', 1
GO

RECONFIGURE

GO

sp_configure
'clr enabled', 1
GO

RECONFIGURE
Step 2. Make sure SQL Server Data Tools is Installed.
   a. Create a SQL Server Project
   b. add new item to the project. User Defined Function  
 
 
 
 // Create Your Code. This snippet converts utc to something else....
 
 

using System;

using System.Data;
using
System.Data.SqlClient;
using
System.Data.SqlTypes;
using
Microsoft.SqlServer.Server;

 public partial class UserDefinedFunctions{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString convert_timezone(DateTime datetime, String source_time_zone = "UTC", String destination_time_zone = "Pacific Standard Time")

   {        //set defaults

  return new SqlString(TimeZoneInfo.ConvertTime(datetime, TimeZoneInfo.FindSystemTimeZoneById(source_time_zone), TimeZoneInfo.FindSystemTimeZoneById(destination_time_zone)).ToString());
   }

}

 

 

 
 
 
 
 Build the Code.
 
 Connect to your DB:
 
 
 Generate the Script:
 
 
 
Run the script on your Database Server or use the Publish functionality of Visual Studio
 
 
test your sql function in SSMS:
 Select dbo .convert_timezone('2013-11-21 08:00:00.000','GMT Standard Time','Pacific Standard Time')
 
 
Errors and Debugging..... 
 
You may come across the following issue due to CLR permissions... 
 

Msg 6522, Level 16, State 2, Line 3

A .NET Framework error occurred during execution of user-defined routine or aggregate "convert_timezone":

System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.

 

The protected resources (only available with full trust) were: All

The demanded resources were: MayLeakOnAbort

 
 
The first thing you want to do is set the DB Trust.... Run the following SQL: 
 
ALTER DATABASE <YOUR DB> SET TRUSTWORTHY ON
 
 
 Set the project to "Unsafe".
 
View the Project Propeties... The following window should appear. 
Set the Permission level to "UNSAFE". 
 
 
 
 
Redeploy,   Test your function again.
 
 
 
The beauty of this....... When converting from GMT , you can take daylight savings time into account without knowing when the "1 hour" switch took place. For instance. The following examples are all Midnight GMT for 4 consecutive days  with PDST (Pacific Daylight Savigs Time) taken into consideration on the 3rd.
 
 
 
-Don