none
Automatic GeoSpatial Lookup From Address

    Question

  • I'm not very experienced with the advanced SQL techniques of using assemblies in SQL, so please forgive my ignorance in describing this. What I would like to know is if there is any way that when a new location record is created (with address info), I can install some kind of module, assembly, etc. that would allow for automated submission of address information and return of some sort of GeoSpatial information. Even lat/long would work so I can convert it into a geography value to store in the database.

    Basically I want to automate this as much as possible so that when a user enters a new record and they submit the address, city, state, zip, etc. it will automatically get the geospatial/lat/long information and store it in the record. Not sure if it's possible, but thought I'd ask. I'm open to developing something in .net and using that if someone knows of a web service that can be queried and how to integrate assemblies from .net into SQL and then use them.


    Nathon Dalton .NET Software Developer
    Tuesday, January 05, 2010 9:55 PM

Answers

  • I'm joking ;)

    There's nothing special about writing assemblies for SQL Server, other than knowing that you can only use a subset of methods from the full .NET namespace (System.Drawing, for example, is not that useful in SQL Server). But accessing web services is pretty much the same in SQLCLR as it would be in any other .NET application.
    Once you create and compile your .NET assembly, you import it into SQL Server using the CREATE ASSEMBLY syntax like this:

    CREATE ASSEMBLY Geocoder
    FROM 'C:\Geocoder.dll'  
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
    GO

    You need to grant the assembly EXTERNAL_ACCESS permission so that it can access the webservice.

    Then, you need to register a function that calls into the .NET method that you created in the assembly. So, let's say you called your geocoding method MyGeocodeFunction, and it was in the UserDefinedFunctions class of your assembly, in the Geocoder namespace.
    If your geocoding method accepted a string parameter representing the address to be geocoded, and it returned a geography POINT instance at the coordinates returned from the geocoding service, you would register it in SQL Server like this:

    CREATE FUNCTION dbo.Geocode(
      @Address nvarchar(255)
    )
    RETURNS geography
    AS EXTERNAL NAME
    Geocoder.[Geocoder.UserDefinedFunctions].MyGeocodeFunction;
    GO


    Once you've done this, you can call the geocoding function inline as part of a normal SQL statement (e.g. to INSERT/UPDATE or SELECT data from your table), as follows:

    SELECT dbo.Geocode('2855 Telegraph Avenue','Berkeley','CA','94705','USA');

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Nathon Dalton Wednesday, January 06, 2010 11:18 PM
    Wednesday, January 06, 2010 4:43 PM
    Answerer

All replies

  • Hi Nathon,

    Yes, this is perfectly possible - the process you are describing is known as geocoding - taking a textual description of a location (normally a street address, but could be just a place name) and then converting that into latitude/longitude coordinates.

    There are several webservices that you can use to do this - the Mappoint Web Service and the Bing Maps Geocoding Service are two which I know work well and are easy to set up. The Mappoint Web Service is an old-style .NET 2.0 asmx service, whereas the Bing Maps Geocoding Service is a WCF service, but they basically provide the same functionality.

    Since they can both be called from .NET, you can either perform the geocoding in the application layer, or you can import an assembly into SQL Server and register a function that executes the geocoding from the SQLCLR. You could then set up a trigger in the database that, everytime a new address was inserted, the geocoding function fired to update a geography column with the corresponding POINT location.

    The method reference for the Bing Maps Web Services is here:
    http://msdn.microsoft.com/en-us/library/cc980922.aspx

    And the Mappoint web service is here:
    http://msdn.microsoft.com/en-us/library/dd877971.aspx

    <plug>If you need more help, there's a full end-to-end example of performing geocoding in SQL Server 2008 in chapter 7 of "Beginning Spatial with SQL Server 2008"</plug>
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Tuesday, January 05, 2010 10:06 PM
    Answerer
  • Awesome. That's exactly what I was wanting to do. I don't have that book and don't have a book "fund", so are there any online tutorials or how-tos for doing this? Thanks for your help!
    Nathon Dalton .NET Software Developer
    Wednesday, January 06, 2010 3:46 PM
  • I don't know if there are any online tutorials for geocoding with Bing Maps/Mappoint in SQL Server (if there had been, I wouldn't have needed to write that chapter ;), but there are some alternatives you could try:

    Ed katibah (the program manager for SQL Server spatial) describes an alternative method, using a server-based geocoder from Yuri software:
    http://blogs.msdn.com/edkatibah/archive/2009/03/10/address-geocoding-with-sql-server-2008-spatial.aspx

    And this article uses the Yahoo geocoding service (although it appears to be in the application layer rather than directly in the DB):
    http://magmainteractive.net/tutorials/post/Yahoo-Geocode2c-SQL-Server-2008-and-ASPNET-AJAX.aspx


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Wednesday, January 06, 2010 3:57 PM
    Answerer
  • Oh, my... uh, sorry about that. :) Just to be clear, I would LOVE to buy your book. I would love to buy a LOT of other books too, but when you're given no budget for them and they cost between $50-$100 each well... you just can't get them very often. I will check out those other links though.

    I've been a .net developer for some time now (about 7 years) and have worked with SQL from that point of view, but haven't ever gotten this deep into the guts of SQL before. That's the reason for needing a reference. I've got the "SQL Server 2005 Bible" by Paul Nielson which has a chapter on developing with SQL server and specifically creating assemblies. It shows how to create a .net project and use that to create an assembly which is CLR compatible and can be used in SQL, but doesn't give an example on how to set that up in SQL and then the syntax to use it once it's setup.

    Thank you SO much for helping me! :)

    BTW, what do you think of this article I just found? It's talking about doing exactly what I want to do (access a web service from within SQL via an assembly).
    http://www.simple-talk.com/sql/t-sql-programming/practical-sql-server-2005-clr-assemblies/

    Nathon Dalton .NET Software Developer
    Wednesday, January 06, 2010 4:18 PM
  • I'm joking ;)

    There's nothing special about writing assemblies for SQL Server, other than knowing that you can only use a subset of methods from the full .NET namespace (System.Drawing, for example, is not that useful in SQL Server). But accessing web services is pretty much the same in SQLCLR as it would be in any other .NET application.
    Once you create and compile your .NET assembly, you import it into SQL Server using the CREATE ASSEMBLY syntax like this:

    CREATE ASSEMBLY Geocoder
    FROM 'C:\Geocoder.dll'  
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
    GO

    You need to grant the assembly EXTERNAL_ACCESS permission so that it can access the webservice.

    Then, you need to register a function that calls into the .NET method that you created in the assembly. So, let's say you called your geocoding method MyGeocodeFunction, and it was in the UserDefinedFunctions class of your assembly, in the Geocoder namespace.
    If your geocoding method accepted a string parameter representing the address to be geocoded, and it returned a geography POINT instance at the coordinates returned from the geocoding service, you would register it in SQL Server like this:

    CREATE FUNCTION dbo.Geocode(
      @Address nvarchar(255)
    )
    RETURNS geography
    AS EXTERNAL NAME
    Geocoder.[Geocoder.UserDefinedFunctions].MyGeocodeFunction;
    GO


    Once you've done this, you can call the geocoding function inline as part of a normal SQL statement (e.g. to INSERT/UPDATE or SELECT data from your table), as follows:

    SELECT dbo.Geocode('2855 Telegraph Avenue','Berkeley','CA','94705','USA');

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Nathon Dalton Wednesday, January 06, 2010 11:18 PM
    Wednesday, January 06, 2010 4:43 PM
    Answerer
  • I was able to write the assembly, install it in SQL and call it correctly. However, I'm now having a problem stepping through the UDF code I'm writing in my Database project in VS.NET 2008. It is giving me the following output.

    A first chance exception of type 'System.IO.FileNotFoundException' occurred in mscorlib.dll
    A first chance exception of type 'System.TypeLoadException' occurred in System.dll
    A first chance exception of type 'System.Net.Sockets.SocketException' occurred in System.dll
    A first chance exception of type 'System.Net.Sockets.SocketException' occurred in System.dll
    A first chance exception of type 'System.Net.WebException' occurred in System.dll
    A first chance exception of type 'System.Net.WebException' occurred in System.dll
    A first chance exception of type 'System.Net.WebException' occurred in System.dll

    This is the code that I'm trying to execute (atleast the part that's failing). The last line takes forever and gives me the above errors in the output.


    WebRequest objRequest;
    WebResponse objResponse;
    objRequest = WebRequest.Create(str_URL);
    objRequest.Timeout = 60000;
    objResponse = objRequest.GetResponse();

    The URL comes up in my browser window as returning an XML document though. I'm behind a proxy server, but thought that the object used IE's settings for proxy?

    Wednesday, January 06, 2010 11:22 PM
  • I know that some people have difficulties accessing web services over HTTPS from within SQLCLR (see, for example, http://social.msdn.microsoft.com/Forums/en/sqlnetfx/thread/df6db9cf-681d-4d13-8f2f-0dd257f9ce7d ), but I'm assuming that you're using a normal HTTP request?

    Although the error messages aren't that descriptive, my guess would be that it's more likely either a proxy issue or a credentials issue. To try to isolate which, is it possible to test your code from SQLCLR without the proxy? I don't know how authentication to the webservice you're using works - are you trying to use the SQL Server credentials, or your own credentials? Have you supplied explicit (valid) NetworkCredentials?

    If you want to share the rest of your code or a link to the compiled assembly then I'm happy to try testing it from here. 
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, January 07, 2010 7:41 AM
    Answerer
  • Sorry, from your reply I realized that I forgot to describe a whole bunch of things. Here are the answers to your questions.

    1. Using normal HTTP request? - Yes, it's going to http://local.yahooapis.com/MapsService/V1/geocode, Yahoo's map web service URL. Also, if I place the same URL in my IE window it pulls back the request just fine.
    2. Proxy issue? - I was curious whether the WebRequest.GetRequest(); method used the IE proxy settings or not. I also tried implementing the code found on this page to enable proxy settings, which I understand should pull from IE?
    3. Web service authentication? - When I setup my application with Yahoo, I chose not to use authentication for the request. This is verified by it working in my browser w/o logging in or passing authentication.
    4. Sharing the code? - I wouldn't mind sharing it except that it has my Yahoo AppID in it and won't work if I remove it. I'm a bit leary to post the assembly somewhere with the appid in it. Got any other ideas / have your own appid?

    Other things that I figure dout I should have put in that post.

    1. I have strong named the assembly and signed it, as I understand is required for SQLCLR to work.
    2. If I put a simple "hello world" procedure, it can be properly executed in SQL and works, so the CLR seems to be properly setup.

    Any other thoughts?


    Nathon Dalton .NET Software Developer
    Thursday, January 07, 2010 3:43 PM
  • YEAAA!!! It worked! I explicitely created the proxy instance, supplied credentials and URL, etc. and it worked perfectly!

    If you want to know what I did for this project, I've got an overview here on my blog.
    http://nathondalton.wordpress.com/2010/01/07/get-geospatial-from-web-service-in-sql/
    Thursday, January 07, 2010 4:52 PM
  • Cool. Now that this works, I'll get on my soapbox for a few ticks...

    I'd caution against putting any operation that requires the web, especially to a server you do not control or have a SLA with, inside a database trigger. At best, this ties the response of your database to the web response time, which can be quite variable. At worst, if the site (or network connectivity with it) that you're counting on is down, none of your database operations that rely on  that trigger can proceed. This may be acceptable in sode that you write for exposition, but maybe not in a production system. Unless the rest of your transaction processing really can't proceed with the geocoded address.

    Instead use service broker messages to encapsulate the web calls and update the database asynchronously. Like so:
    http://rusanu.com/2009/08/05/asynchronous-procedure-execution/
    http://rusanu.com/2009/08/18/passing-parameters-to-a-background-procedure/

    Introducing service broker will add an extra level of complexity to your application but might save your response time (or main processing) some day.

    Cheers,
    Bob Beauchemin
    SQLskills
    Thursday, January 07, 2010 6:51 PM
  • Very wise words, Bob. 

    One of the interesting things arising from the fact that sql server spatial features are encapsulated in a self-contained .NET library, and that geocoding services are typically accessible as web services (or WCF) is that the architecture of spatial applications can vary immensely. For example:
    - You can have a standalone windows application that imports the SQLServer.Types.dll assembly and calls into a geocode webservice entirely on the client.
    - Or, you can have an ASP.NET application in the client browser that calls a handler to perform geocoding in the webserver layer, and then connects to the DB to insert the geocoded results.
    - Or, as in this example, you can call a geocoding webservice directly from within the DB layer.

    This flexibility in spatial application design makes it particularly important to write your .NET assemblies in such a way as to make them easily portable between the different tiers of an application. With careful design, you can write re-usable methods that can be moved/shared between different layers with a minimum amount of re-work. So, your geocoding logic should remain the same, however that method gets called.

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, January 07, 2010 7:45 PM
    Answerer