Introduction

With the myriad of available web mapping applications available, such as Google Maps, Yahoo Maps, and Microsoft’s Virtual Earth, there is little reason to not utilize these great (and free!) tools. These web services can add geographic visualization tools to your website, small or large. But unless you have a GIS (Geographic Information Systems) expert or costly GIS software deployed in your environment, getting those points on a map can be a little trickier than initially thought. Why, you ask? These services require two vital pieces of information to display "overlays", or layers of your own data: Latitude and Longitude. Enter Geocoding...

What's Geocoding? And what does this have to do with SQL?

Geocoding is the process converting physical mailing addresses to latitude and longitude points. Up to now, it has been expensive to geocode more than a few addresses. This cost comes in the form of software purchases: either complex GIS software and the ensuing need for GIS expertise, or geocoding packages with independent software companies that do the work for you.

So what does this have to do with SQL Server? No matter the organization, if your SQL Server databases hold address information and you need to view that data in a geospatial form (i.e. a map) whether for sales, marketing, planning, or research, you can use this address data & create visual tools by geocoding the addresses. Or maybe you want to calculate the distance between two points by using the Haversine formula.

So how do we Geocode?

And finally, how do we get this mysterious geocode? As of this writing, Google has not released a geocoding component to their popular API, and Microsoft’s geocoding service can be found wrapped up in their MapPoint Web Service, which of course, will cost you. Early in 2006, Yahoo has come to the rescue, offering a geocoding platform free to developers. This article will cover a practical example of using SQL 2005’s CLR integration to access the Yahoo geocoding web service. This small step-by-step procedure will reveal how to retrieve the desired latitude and longitude information from Yahoo. This Lat/Long data can then be used by your web developers to boost your site’s functionality.

Geocoding with CLR & SQL 2005 - a practical example

First, some prerequisites: you must have an instance of SQL 2005, and have enabled CLR integration through the Surface Area Configuration tool. You will also need an application ID from Yahoo.

Next, you want to go ahead and create the C# CLR procedure. Begin by opening up Visual Studio 2005 (otherwise known as Business Intelligence Development Studio) and choosing SQL Server type of project. I’ve named my project YahooGCSQL. Then go ahead and choose connection information for the database where you will be loading your C# assembly by choosing the server, database, and credentials.

Next, to start your CLR stored procedure, right click the project in solution explorer, then choose Add, Stored Procedure. I’ve chosen to name my procedure SPGeocode, but of course, you can choose your name.

The source for the C# assembly can be downloaded here. You will need to completely erase what's in the C# editor, and replace with this code. In short, we build the URL string, embedding our desired address information in the URL, then we capture the XML returned by Yahoo’s web service. For simplicity, we fill a dataset with the XML, then return it to the SQL client.

Build the assembly, by choosing Build, then Build YahooGCSQL. You should now have a DLL assembly sitting in the Debug folder beneath your solution. We can now close Visual Studio and open SQL Server Management Studio to load our assembly into the database. All of the following T-SQL steps are possible through the GUI, but I’ve chosen to include the T-SQL for simplicity sake.

Before loading & successfully executing the assembly, you may need to set your database to be trustworthy, by issuing the "ALTER DATABASE databasename SET TRUSTWORTY ON" command. This property indicates whether the SQL instance trusts the database, since now in 2005, you can load managed code directly into the database.

Issue the CREATE ASSEMBLY statement in order to load the assembly into the database. Notice that we are setting the permissions on the assembly to EXTERNAL_ACCESS, which will allow us to reach outside of SQL and out to the internet

CREATE ASSEMBLY [YahooGCSQL]
AUTHORIZATION [dbo]
FROM 'C:\Documents and Settings\userid\My Documents\Visual Studio 2005\Projects\YahooGCSQL\YahooGCSQL\bin\Debug\YahooGCSQL.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

Next issue the CREATE PROCEDURE statement to create the stored procedure in SQL. You will notice the parameters are the exact same as the method in the assembly.

CREATE PROCEDURE [dbo].[SPGeocode]
@Address [nvarchar](4000),
@City [nvarchar](4000),
@State [nvarchar](4000),
@appid [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [YahooGCSQL].[StoredProcedures].[SPGeocode]

Now we’re ready to geocode. Here we get the latitude and longitude of Grand Central Station in New York, NY.

SPGeocode

'100 East 42nd Street'

,'new york'

,'NY'

,'appidhere'

Now you have a stored procedure to call from anywhere you like - from other procedures, from triggers on your address data, and from applications. And best of all - as long as you stay under 50,000 hits per day, it’s free thanks to Yahoo. In the future I will post an example using Microsoft MapPoint Web Service. Good luck geocoding!