Find-Near-Route for Bing Maps powered by SQL Azure (Spatial)

Oh yes, SQL Azure goes spatial. Yesterday the SQL Azure team made several announcements and one of them was, that SQL Azure has now received the same spatial-treatment, that SQL Server 2008 had already since quite a while. Obviously this announcement demands a quick sample on how we could use the spatial-data types, – indexes and -functions of SQL Azure for Bing Maps applications.

Sometimes people want to filter points of interest (POI) and display only those that are within a certain distance of a route. For example: when I calculate a route from Las Vegas to San Francisco and I want to find petrol stations along the route it doesn’t help me at all when all 171 petrol stations in the map view are displayed. Some of them are 100 miles of the route and I certainly wouldn’t want to use them.

image

What I really would like to find are petrol stations which are no more than a certain distance off my route – let’s say 1 mile.

image

Here is how we can do it. In SQL Azure we have a table with our POI. One column holds data of type GEOGRAPHY (the GEOMETRY data type is supported as well). We also have a spatial index and a stored procedure that will actually do the work for us. We will look into this stored procedure in a second.

image

When we calculate a route in Bing Maps we can optionally return the points of the route-path:

   Dim myRouteOptions As New svcRoute.RouteOptions
   myRouteOptions.RoutePathType = svcRoute.RoutePathType.Points

When we receive the result of the route-request, we can concatenate the latitudes and longitudes of the route-path into a Well Known Text (WKT) string and then send it together with the size of the buffer in which we want to search to a web service:

   Dim myLineString As String = ""
   For Each Location In e.Result.Result.RoutePath.Points
       myLineString = myLineString + Location.Longitude.ToString + " " + Location.Latitude.ToString + ", "
   Next
   myLineString = "LINESTRING(" + myLineString.Substring(0, myLineString.Length - 2) + ")"

   AddHandler svc1.GetPoiNearRouteCompleted, AddressOf svc_GetPoiNearRouteCompleted
   svc1.GetPoiNearRouteAsync(myLineString, CInt(txtBuffer.Text))

The web service will call a stored procedure in SQL Azure and return a list of objects that we can then add to the map:

    Private Sub svc_GetPoiInViewCompleted(ByVal sender As Object, ByVal e As GetPoiInViewCompletedEventArgs)
        If e.Error Is Nothing Then
            For i = 0 To e.Result.Count - 1
                Dim image As New Image()
                Select Case e.Result(i)._Name
                    Case "BP"
                        image.Source = New BitmapImage(New Uri(baseURL + "/IMG/BP.png", UriKind.Absolute))
                    Case "Shell"
                        image.Source = New BitmapImage(New Uri(baseURL + "/IMG/Shell.png", UriKind.Absolute))
                    Case "Texaco"
                        image.Source = New BitmapImage(New Uri(baseURL + "/IMG/Texaco.png", UriKind.Absolute))
                End Select
                image.Stretch = Stretch.None
                Dim location As New Location(e.Result(i)._Lat, e.Result(i)._Lon)
                Dim position As PositionOrigin = PositionOrigin.Center
                slRoute.AddChild(image, location, position)
            Next
        Else
        End If
        lblInfo.Text = e.Result.Count.ToString + " POI in View"
    End Sub

The really interesting part happens in the database. The stored procedure receives the Well Known Text for the linestring that represents our route along with a parameter that indicates how far away from the route we still want to search for our POI. Then it creates a buffer around this linestring. The result will be a polygon and finally we search for the POI that are within this polygon

   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

You will find a live sample here. The source code and some sample data are available here:

About these ads
This entry was posted in Bing Maps. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s