I am currently whittling away, unfortunately rather slowly, on a new blog post series that makes use of the spatial data capabilities provided by both SQL Azure + SQL Server 2008 R2 that then exposes the content to be consumed by a WP7 client.

In the meantime while I chip away on that I was sitting wondering why my recent flight to the US was faster from San Fran to Sydney then from Sydney to LA. Taking away the variables such as head/tail winds, airspeed and flight plan, yes all important factors, we can quite easily get a raw view of the direct distance from San Fran to Sydney and LA to Sydney using spatial data and the OGC methods on the geography type – cool

But first we need some data i.e the latitude and longitude of the three airports. You can quite easily retrieve these manually from bing maps by saving the points in your My places with a Right Click >> Add Push Pin then once you have all three clicking Actions >> Export you can retrieve the lat + long of each place. If you wanted to automate the process you could using the Bing Maps API to geocode the address to your lat/long

The result of the manual export from bing maps is as follows, we are only interested in the lat and long for this post:

<?xml version="1.0" encoding="utf-8"?>
<gpx xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" version="1.1" creator="Bing Maps" xmlns="http://www.topografix.com/GPX/1/1">
<metadata>
<name>Unsaved places</name>
<desc />
</metadata>
<wpt lat="37.6235624402761" lon="-122.383551299572">
<name>San Francisco Int'l Airport</name>
<desc>San Francisco, CA 94128</desc>
</wpt>
<wpt lat="33.946468" lon="-118.384506">
<name>Los Angeles Airport Marriott</name>
<desc>5855 W Century Blvd, Los Angeles, CA</desc>
</wpt>
<wpt lat="-33.936897" lon="151.168334">
<name>Sydney Airport Medical Centre</name>
<desc>International Terminal, Mascot, NSW 2020</desc>
</wpt>
</gpx>

Calculating the distance:

-- using the geography datatype for round earth calculations rather then geometry(flat earth)
DECLARE @SanFran geography
DECLARE @LA geography
DECLARE @Syd geography

-- define the points
SET @SanFran = geography::Point(37.6235624402761, -122.383551299572, 4326)
SET @LA = geography::Point(33.946468, -118.384506, 4326)
SET @Syd = geography::Point(-33.936897, 151.168334, 4326)

-- calculate the distance between points in km
SELECT @Syd.STDistance(@SanFran)/1000 as SydToSanFran
SELECT @Syd.STDistance(@LA)/1000 as SydToLA

Results:
Sydney to San Fran: 11936.3 km
Sydney to LA: 12053 km

From this simple calc we can see that San Fran is in fact closer to Sydney then LA.

While this is a simple example you can apply what you have learnt here to perform radial searches. e.g if you have a table of Airports with their co-ordinates located in a Location column and wanted to see what airports were within a 13000 km radius you could do:

DECLARE @Origin geography
SET @Origin = geography::Point(<YourLat>,<YourLong> , 4326)

SELECT *
FROM dbo.Airports
WHERE Location.STDistance(@Origin) <= 13000000

Enjoy,
Nick