Get GeoSpatial from Web Service in SQL

I was recently creating a new database for an existing application. We were already storing latitude and longitude information for the locations, but it was based on people physically looking up the address and updating the record with the latitude and longitude. Needless to say this was labor intensive, wasn’t very scalable and when times got busy, didn’t happen.

When considering how to rectify this situation in the new database I decided to design the new database in SQL 2008 for it’s support of GeoSpatial objects. I created one GeoSpatial Geography field instead of the two latitude and longitude fields in the old database.

After looking at various ways to try to get the latitude and longitude of the addresses being entered into the database I decided to create a .NET assembly that consumed a web service and use SQL CLR to consume it in SQL. Here is a basic overview of what I did.

1. Created a Yahoo Maps account in order to get an AppID which has to be passed on the query string when requesting the lookup.
   • I chose this because Yahoo is free for what they consider non-commercial uses, which is considered less than 5000 requests a day and it’s easy to use.
2. I created a new Database project in Visual Studio 2008, configured the data connection, created a new User Defined Function and wrote the code to consume the web service.
   • Things to consider with this are to ensure you configure the Permission Level to External by selecting the Database tab in the project properties and selecting External from the Permission level drop down.
   • Ensure you strong name the assembly by running “sn.exe /k KEYNAME.snk” at the command line, place this file in your project directory and in the Signing tab of the project properties, click Sign the assembly, choose the file from the drop down and save project. This signs and strong names the assembly.
   • In the project set SqlFunction attribute to “[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]”.
   • If you’re behind a proxy server, create a new proxy instance instead of doing the GetDefaultProxy method. For some reason it just won’t fetch the proxy settings from IE.
   • Depending on the account you’re SQL Server service is setup to use, you might also need to setup impersonation so that you can have access through a proxy or to whatever other resources are needed to perform this task.
3. Tested the project, then deployed to SQL by right clicking on project and selecting Deploy after choosing Release mode. This creates the assembly, deploys it to SQL, installs it and creates the functions.
   1. In SQL, you need to remember to set TRUSTWORTHY ON by running the command, “Alter Database <DBNAME> Set TRUSTWORTHY ON”.
   2. You also must turn on SQL CLR by executing each of these commands in order (without the quotes).
      1. “sp_configure ‘clr enabled’, 1”
      2. “reconfigure”
   3. You can then verify SQL CLR is enabled by running the following commands in order.
      1. “sp_configure ‘show advanced options’, 1”
      2. “sp_configure”
      3. The value in the “run_value” column for “clr enabled” should be 1 if SQL CLR is enabled.
4. From here using the new functions is as easy as calling the function. If you’re unsure as to the syntax to call the function, right click on the function, select Script Function As -> Select To -> New Query Editor Window. This will script the select statement for you and you can customize from there.

If you ever copy and then move the database to another server, you might possibly wind up getting errors. If so, hopefully this MS KB 918040 article will save you some time. Also verify that the instructions in #3 are set on the new server.

I hope this has helped out. This took me a while to figure out since I’d never even created an assembly for SQL before, let alone actually installing it, consuming it and consuming a web service through it.