Azure SQL 2008 - Bing Maps - Near Route Demo

Azure SQL 2008 - Bing Maps - Near Route Demo

After the announcement http://bingmapswatch.blogspot.com/2010/03/bing-maps-sql-azure-support-april-2010.html that Azure SQL 2008 was implmenting Spatial Support (Geography and Geometry) this now enabled.

This is an example of combining, Windows Azuew, SQL Azure, Silverlight and Bing Maps to produce a find along a route example


Finding Petrol (Gas) Stations along a route (Boston to Chicago) within 1km (1000m) of the route.

The code inside Azure SQL 2008 doing this is

@myGEOM is the lat and lng (x1,y2)+(x2,y2) start and end points for the route
@myBuffer is the buffer distance (1000m default)
@myRoute is the (xy,xy,xy,xy) of the enitre route creating a polyline
This polyline is then buffered creating a polygon (area) around the route.
SQL 2008 Azure then uses Geoproccessing 'Buffer' and 'Intersect' to pull-out
any points (gas stations in this example) and then displays the points as markers
on bing maps

code:

 CREATE PROCEDURE [FindNearRoute] @myGEOM nvarchar(MAX), @myBuffer int
AS
   --Create the Buffer
   DECLARE @myRoute geography;
   SET @myRoute = @myGEOM;

   DECLARE @SearchArea geography;
   SET @SearchArea = @myRoute.STBuffer(@myBuffer);

   --Return all POI in the search area
   SELECT Lat, Lon, Name
   FROM PetrolStations
   WHERE (@SearchArea.STIntersects(GEOM)) = 1

Live Example (Silverlight required)
http://talkingdonkey.cloudapp.net/BM-SL-FindNearRoute/Default.htm

There is also a zip file (BM-FindNearRoute.zip) with all the source code required to create your own find near a route example.

Source
http://johanneskebeck.spaces.live.com/blog/cns!42E1F70205EC8A96!12533.entry