In my last blog post I left off having discussed how to leverage just one of the many T-SQL geography features to analyze spatial information, such as “What are our nearest store or branch locations for a particular mobile user that’s currently browsing our site?” If we don’t already have the geography information (longitude and latitude) for each store address in our system, we can retrofit our data by utilizing Microsoft’s Geocode services (i.e., Bing Maps SOAP Services).

First, we have to add an SQL Geography column to the database table containing our address detail.

ALTER TABLE Store.Address
ADD Geog geography;
GO

Second, using MS Visual Studio 2008 or 2010, create an application of your choice to connect to the Bing Maps SOAP GeocodeService. The GeocodeService offers methods to both geocode addresses and reverse geocode locations. Follow the example on the MSDN website that demonstrates how to operate on a single address value in a text box and display the geocoding results in a label. Note that you’ll first need to register for a Bing Developer API key to use the Geocode service. 

Third, in your Visual Studio application add a method to connect to your database address table. I added a second method to perform the database update on the record once the geocode location has been ascertained from the Bing service.

The method to get the geocode detail:

private void GeoCodeCustomers()
{
// Use the method of your choice to get a collection of location objects from your database. (e.g., SqlConnection, Entity Framework, LINQ to SQL class, etc.) For simplicity’s sake I’ve left the specifics of that out of this sample.

// Iterate through your collection and connect to the Bing GeoCode service:
foreach (Customer cust in customerList)
{
   // Build the address for the webservice to return GeoCode Latitude/Longitude
   _geoAddress = string.Format("{0}, {1}, {2}, {3}, {4}", cust.Address, cust.City,
cust.StateProvinceCode, cust.CountryRegionCode, cust.PostalCode);

// Connect to the GeoCode Servide w/ the object’s address:
GeocodeService.Location latlong = GeocodeAddress(_geoAddress);
double latitude;
double longitude;
if (latlong != null)
{
   latitude = latlong.Latitude;
   longitude = latlong.Longitude;

   // Now update your location records the GeoCode service detail
   UpdateGeoCodes(latitude, longitude, cust.AddressID);
  }

The method to update your database location table with the geocode detail:

private void UpdateGeoCodes(double lat, double lng, int addressID)
{
string updateString = string.Format("UPDATE Store.Address SET Geog = geography::Point({0}, {1} , 4326) WHERE AddressID = '{2}'", lat, lng, addressID);

using (SqlConnection connection = new SqlConnection(connStr))
{
   SqlCommand command = new SqlCommand(updateString, connection);
   command.Connection.Open();
   command.ExecuteNonQuery();
 }
}

Conclusion

By leveraging the steps in this article (and the MSDN referenced above), you can easily retrofit your customer, store, etc. location database table by utilizing the Bing Maps SOAP Geocode Service from any Windows desktop application.