How can I best geocode a table of addresses in SQL Server?

Question

I've got a SQL Server 2008 table with addresses. I've got some C# code that can individually geocode the addresses. I've got a Google Maps API for geocoding. Now I'm trying to figure out the most efficient way to use these resources.

I could write a console app that manually updates the tables using my C# library, but the data I have is updated periodically. I will be performing an import routine of some sort and I'm thinking it would be 'simplest' to perform the geocoding as the import occurs. I'm not so strong on SQL Server capabilities, so I'm looking for advice.

I've considered letting the import call an assembly I create that would be referenced in SQL Server, but read that Sql Server 2008 has made it virtually impossible to reference your own DLL. So my next guess is having the import call a web service to pass in the address and update the table with the results, but I've not had much luck in finding info on this method. Any advice?

Answer 1

Google Maps has a WebService API that allows Geocoding. Your Volume of addresses should be an acceptable amount (I think google allows one geocode per 5 seconds after a while of using it). Be aware that you have to decouple the geocoding as a plugin from your application, because you cannot implemented it in ta commercial application without being a google customer.