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.

Advertisements

21 thoughts on “Get GeoSpatial from Web Service in SQL

    1. I’m consuming it synchronously. I know this means you have to wait for a response but it was the best fit for the application I was using this for.

      I was using it to pull geolocation data for records that had addresses automatically. This way those that enter data didn’t have to look this up manually. It also meant we always had location data if someone forgot to enter it.

  1. Hi Nathon,

    I have a problem with this approach of using SOAP request from CLR function to MS Translator. Maybe you can help?

    After deploying the project I got following output:

    SqlClrDeploy:

    Beginning deployment of assembly CLRTranslate.dll to server localhost : SouthridgeDW

    The following error might appear if you deploy a SQL CLR project that was built for a version of the .NET Framework that is incompatible with the target instance of SQL Server: “Deploy error SQL01268: CREATE ASSEMBLY for assembly failed because assembly failed verification”. To resolve this issue, open the properties for the project, and change the .NET Framework version.

    C:\……\CLRTRANSLATE\BIN\DEBUG\CLRTRANSLATE.DLL(1,1,1,1): Deploy error SQL03006: Assembly: [CLRTranslate] has an unresolved reference to object [DOMAIN\login].

    Build FAILED.

    I tried .NET Framework 3.5 and 4.

    DBMS: SQL Server 2008 R2 Developer.

    ——————————————————————————–
    Best regards, Ivan Kosyakov

    1. Hi Ivan! Nice to meet you. SQL Server 2008 only supports .NET 3.5. I haven’t had a chance to check if R2 is different, but not likely.

      Does your application consist of a single assembly or are you consuming others as well (besides those in the .NET Framework)? If you’re referencing others, the first thing I would check is if any of the referenced assemblies are built in > .NET 3.5.

      If they are, you’ll need to figure out a way to either rebuild them in 3.5 (if they’re yours), use older versions (if available) or use different assemblies.

      Let me know if that doesn’t work or apply.

  2. I’m having the same issue –
    Deploy errror SQL03006: Assembly: [Msdn.SqlRegex] has an unresolved reference to object [domain\login]. <– where this is my acct.

    I've successfully deployed this to one SQL Server db (2008 R2 (RTM) SP2), and the failure is occuring on my locally installed db (2008 (SP2) – (Build 7601: Service Pack 1))

  3. I was able to resolve this by removing the [Assembly Owner] – which was set to my acct under the [Database] tab of project properties in VS.

    1. Hewbie, I’m glad you found the solution! Yea, you can get into trouble with accounts when you start using your scripts on other servers if you’re not careful. That’s why it’s important to make your scripts global and repeatable, meaning they create all dependent objects if they don’t exist & can be run over & over.

    1. Sotavento Barcelona,

      Thank you very much for your kind words! I thought a lot more people would look at that article than currently do. I’m glad this helped you out. Make sure you read my other articles and recommend the site to others if you like it.

      Thanks,
      Nathon

    1. No problem Terrance. I am very happy to blog and the comments people leave are the best reward! Have a great day and I hope you visit again soon!

  4. Hello! Someone in my Myspace group shared this website with us so I came to give it a look.
    I’m definitely loving the information. I’m bookmarking and
    will be tweeting this to my followers! Exceptional blog and
    great design.

    1. Marilou, thanks! I really appreciate the compliment. As I noted above, reading reader’s comments is one of the best parts of having a blog. I love seeing how I’ve helped others or seeing how people have enjoyed my blog. I look forward to your return visits!

  5. I think this is among the most significant info for me.
    And i’m glad reading your article. But wanna remark on some general things, The site style is perfect, the articles is really nice : D. Good job, cheers

  6. Link exchange is nothing else except it is simply placing the
    other person’s webpage link on your page at suitable place and other person will also do similar for you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s