Geocoding sweetness

Okay, normally I’m not such a big fan of geocoding, or spherical coordinate systems in general.  However, the business intelligence team that I am on keeps getting requests for cost and profit-per-mile statistics.

I found a really cool chunk of data on the internet that contains lat/lon for over 47000 zip codes, along with the city, state, county info.  You can find that by googling zip_codes.zip  it shows up in the top 10.

I also found a site that allows you to bulk-geocode some data by putting it in tab-delimited format and plopping it on a web site. Very cool.  The site is called www.batchgeocode.com

Also, I went onto www.codeproject.com and found the source code for a SQL Server UDF (User Defined Function) that can calculate the distance between two points (lat/lon) in miles (or km).  That was cool.

Finally, I had a crazy, crazy idea.  What if I wrote a CLR Stored Procedure for SQL Server that would look up the lat/lon for an address.  I used the Google Map APIs.  I found out that they are actually free but you are only allowed to do 15000 requests per day and you can’t do them all at once (there needs to be a second between requests or they will think you are attacking them). 

The geo-coding part of the CLR SP was pretty simple (after I harvested some free code from codeproject.com).  Getting the CLR SP was more complicated.  Basically, the SQL Server had to be configured to allow you to run CLR.  It was weird because you couldn’t do it globally, you had to configure the Database itself (for instance, Northwind) to allow CLR execution.  The setting is shown via MgtStudio under the section “Options” and “Trustworthy” but you can’t set it there.  You have to run a TSQL command like this:

ALTER DATABASE Northwind SET TRUSTWORTHY ON

Then, your best bet is to use Visual Studio 2005/2008 to “Deploy” your CLR SP to SQL Server.  Doing it by hand is just a pain.

Oh, starting from scratch on a CLR SP is the suck.  Visual Studio doesn’t seem to have any templates for CLR SP projects.  Its template for database projects allows you to add SPs but not CLR SPs.  I got around this by going into the SQL SDK and making a copy of one of those projects.  This can be found in

C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR

I just grabbed the HelloWorld project and made a copy.  Once that was opened, I could add all sorts of CLR SPs, Triggers, UDFs, custom Aggregates.  It was pretty awesome!

Anyway.  CLR integration with SQL server totally rocked my world.  I can see a lot of use for it in future projects.  If you ever want to catch lunch and talk about pros/cons of it, I’m game.

Advertisements

About Tim Golisch

I'm a geek. I do geeky things.
This entry was posted in .net, Database. Bookmark the permalink.

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