THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server) and leader of the SQL User Group in Adelaide, Australia. Rob is also a Director of PASS, and runs training courses around the world in SQL Server and BI topics.

Fetching Latitude and Longitude Co-ordinates for Addresses using PowerShell

Regular readers of my blog (at sqlblog.com – please let me know if you’re reading this elsewhere) may be aware that I’ve been doing more and more with spatial data recently. With the now-available SQL Server 2008 R2 Reporting Services including maps, it’s a topic that interests many people.

Interestingly though, although many people have plenty of addresses in their various databases (whether they be CRM systems, HR systems or whatever), my experience shows that many people do not store the latitude and longitude co-ordinates for those addresses.

Luckily, the Bing Maps API provides everything you need!

Start by going to bingmapsportal.com, logging in using a LiveID and creating an account:

image

Then you can create a key using the link on the left. This key will be attached to a website, and looks something like: Apsjm7zVthPFMxlfpQqKhPPZrAupI-_aGH-CvT2b... Now you can use the Bing Maps API to fetch the information you need. Obviously check the terms and conditions to see if you will need to pay for your usage or not. The Bing Maps API works through web services, so it’s easy enough to use almost any system for this. You could easily make a CLR Function for use within T-SQL, but I’m going to show you how to do it using PowerShell.

Let’s start by creating a Web Service Proxy to the URL of the webservice.

$ws = New-WebServiceProxy -uri http://dev.virtualearth.net/webservices/v1/geocodeservice/geocodeservice.svc?wsdl;

By passing $ws into Get-Member (using the command: $ws | Get-Member), we can see that there is a Geocode method, which requires a parameter of type GeocodeRequest. Actually, the type to use is much more complicated, but it’s easy to create a variable for it using:

$wsgr = new-object Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1ervice_geocodeservice_svc_wsdl.GeocodeRequest;

This variable will take the address to look up in its Query property, but we’ll do that in a moment. First we need to provide credentials, which is that key we created on the website. I’ve stored mine in a variable called $key, so that I don’t have to display it in demonstrations that might be recorded.

$wsgrc = new-object Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1ervice_geocodeservice_svc_wsdl.Credentials;
$wsgrc.ApplicationId = $key;
$wsgr.Credentials = $wsgrc;

Now when we call the method on our web service, Bing Maps will know that it’s us that have called it.

Now I can make a request. If I use a single address, I can just use the Query property of the GeocodeRequest object, as I mentioned earlier. When I get my results from the Geocode() call, I can get multiple lines, and each of them has a bunch of useful information including (as I find most useful), the Formatted Address, and location co-ordinates. I can easily display this by passing the Results into a Select-Object call. I’m just handling the first result of each call, as will become clear in a moment.

$wsgr.Query = 'Adelaide, Australia';
$wsr = $ws.Geocode($wsgr);
$wsr.Results[0] | select {$_.Address.FormattedAddress}, {$_.Locations[0].Longitude}, {$_.Locations[0].Latitude};

As readers familiar with PowerShell will already appreciate, there is a good potential for looping through many addresses. I did this with locations in the world that have PASS chapters recently, but to simplify this, my example uses just four.

$uglist = 'Adelaide, AU', 'Aukland, NZ', 'South Africa', 'San Deigo, USA';
$uglist | % {$wsgr.Query = [string] $_; $wsr = $ws.Geocode($wsgr); $wsr.Results[0] | select {$_.Address.FormattedAddress}, {$_.Locations[0].Longitude}, {$_.Locations[0].Latitude};}

This gives the following results:

$_.Address.FormattedAddress                           $_.Locations[0].Longitude                $_.Locations[0].Latitude
---------------------------                           -------------------------                ------------------------
Adelaide, Australia                                            138.599731698632                        -34.925769791007
Auckland, New Zealand                                          174.765734821558                       -36.8473847955465
South Africa                                                   25.0630002468824                       -29.0459994971752
San Diego, CA                                                 -117.161724865437                        32.7156852185726

You’ll notice that the FormattedAddress property shows the address in a standard format. This is great, because it will handle spelling mistakes (see how I left the ‘c’ out of ‘Auckland’ when I wrote it – for me I did this on purpose, but in most user-input systems, spelling mistakes are a common problem), and it will provide a consistency for punctuation, abbreviations, etc. Notice that I used ‘AU’, ‘NZ’ and ‘USA’, which were all transformed into something else in the web-service call.

With the Lat/Long details here, it’s very simple to get this into a database, or a file, or whatever format is required. For me, I put them into a database along with all the other PASS Chapter locations I had looked up (using the public data from the website), and using the Bing Maps Silverlight control, came up with something like this:

image

It was a bit more work to colour the pushpins by the region, and putting tooltips in with extra information, but bridging the gap between a pile of addresses and a map is actually remarkably straight-forward with the Bing Maps API.

Published Sunday, May 23, 2010 6:59 PM by Rob Farley

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jacques Willemen said:

Hi Rob,

I try to imitate what you do because it seems very nice,

but I am not sure about the line:

$wsgr.Credentials = $wsgrb

because the variable $wsgrb is not defined before.

I guess that is why I get an error message:

'Exception calling "Geocode" with "1" argument(s): "Credentials are either invalid or unspecified."'

Or...?

Greetings from Breda, Netherlands

May 23, 2010 8:55 AM
 

Rob Farley said:

Ah - thanks Jacques. That was a typo. Fixed now. Should've been $wsgrc

Rob

May 23, 2010 7:09 PM
 

Matt Penner said:

Hey Rob,

Great article.  I used to do this in my last job a few years ago with the Google Maps API before Bing Maps was around.

I don't know about now, but back then the Google API limited geocoding to one about every couple of seconds.  This way they weren't flooded with users trying to geocode a thousand person mailing list through their API.

Does Bing not have any sort of limit on the amount or frequency of addresses you can geocode?

Thanks!

Matt Penner

June 1, 2010 3:39 PM
 

Rob Farley said:

Displaying information on an interactive map really isn’t that hard to do. My friends John & Bronwen

June 28, 2010 1:11 AM
 

Pushkar said:

Hello Rob,

I am using spatial database to create tiles over Bing maps and plot all restaurants/gas stations etc. in a specific area.

Can we have something on DB side(StoredProc or something) to locate places of interest in specific radius of a specified lat/long?

Thanks.

June 7, 2011 6:47 AM
 

Rob Farley said:

Yes, absolutely. Use the geography type in SQL, and put a spatial index on the field. Then you can easily use the functions such as STDistance to work out the closest, and so on.

June 7, 2011 6:54 AM
 

Pushkar said:

Thanks a lot. :)

June 8, 2011 7:37 AM
 

srinivas said:

hi,

Fetching Latitude and Longitude Co-ordinates for Addresses using php.

September 13, 2011 2:42 AM
 

Rob Farley said:

Srinivas,

If php lets you call web services, you should be able to translate the code easily enough. It's been too long since I did php for me to be able to help you.

Rob

September 13, 2011 4:17 AM
 

Brian Livingston said:

Great post.  I am trying to update the latitude/longitude via powershell but can't get it to work.  I am basically hitting the bing service with an address retrieving the longitude/latitude then trying to assign those values to the geocode for the list object I am working with.

Here is a small snippet of my code I am testing with.

$LIST_ITEM_TO_UPDATE['Geo Location'].Latitude = 10

$LIST_ITEM_TO_UPDATE['Geo Location'].Longitude = 10

$LIST_ITEM_TO_UPDATE.update()

March 12, 2014 4:31 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement