Buck Woody in Adelaide via LiveMeeting

The URL for attendees is https://www.livemeeting.com/cc/usergroups/join?id=ADL1005&role=attend . This meeting is with Buck Woody. If you don’t know who he is, then you ought to find out! He’s a Program Manager at Microsoft on the SQL Server team, and anything else I try to say about him will not do him justice. So it’s great to have him present to the Adelaide SQL Server User Group this week. The talk is on the topic of Data-Tier Applications (new in SQL 2008 R2), and I’m sure it will be a great time.

Why not join us? Buck will be joining at us 8pm on Monday night (May 24th) in his time zone (US Pacific), which is 4am Tuesday morning (May 25th) in the UK and 12:30pm Tuesday lunchtime (May 25th) here in Adelaide. But if you can come in person, then register using this URL and join us for pizza around noon.

Fetching Latitude and Longitude Co-ordinates for Addresses using PowerShell

Regular readers of my blog 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:


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</FONT>

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:


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.

Spatial data from shapefiles (for T-SQL Tuesday #006)

I’m giving a presentation on May 12th at the Adelaide .Net User Group, around the topic of spatial data, and in particular, the visualization of said data. Given that it’s about one the larger types, this post should also count towards Michael Coles’ T-SQL Tuesday on BLOB data.

I wrote recently about my experience with exploded data, but what I didn’t go on to talk about was how using a shapefile like this would translate into a scenario with a much larger number of shapes, such as all the postcode areas in the US and Australia, plus high-level postcodes from the UK or Canada (US and Aus roughly have a postcode per city/suburb, whereas the UK & Canada use a postcode for a much smaller group of addresses, with the city/suburb being typically the first half of the postcode).

The issue comes down to the fact that the shapefile isn’t filtered. It contains all the shapes. So if you want to display the ones near a point of interest, you’re having to trawl through the lot still. Maybe not what you’re after. So the trick is to use polygons stored in a geography field in SQL, and use that instead. Basically rejecting that “ESRI shapefile” option that Report Builder 3.0 presented. And in particular, put a spatial index on that geography field.

I could go into a ton of detail about the way that spatial indexes work, about how they apply a grid over the world, and then break the squares that result into smaller squares, until they get into quite some level of detail – but I’ll let you research that through Books Online or the like. I just want to point out that the geography type CAN be indexed, and that this allows you to handle a much larger set of regions without incurring the performance hit that you’d get if you had massive shapefiles.

Unfortunately, shapefile data isn’t trivial to get into a geography type, but Morten Nielsen has put a great tool together which you can use for this. It works very well indeed.

In my presentation, I’ll go into a lot more stuff that I’ve learned about shapefiles and the like, but I’ll let you come along and discover that in person. If there’s interest in this stuff, I might even submit a talk on this for some of the upcoming conferences, such as TechEd AU/NZ or PASS.