Home

26 June

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.

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.

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.

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:

Technorati Tags: Bing Maps,Virtual Earth,SQL Azure,SQL Server 2008,Spatial,Find-Near-Route

10:19 GMT | Read comments(0)

29 June

The Bing Maps AJAX Control on Mobile Devices

When we launched version 6.3 of the Bing Maps AJAX control earlier this month, one of the new features was the “Core Mode”. There core-mode strips down some of the features from the standard-mode and reduces the size of the control significantly. The main idea of this core-mode is to load a basic entry-map more quickly but as a nice side-effect it also works quite well on mobile devices. You fill find more details on the supported methods of the core-mode here.

In order to activate the core mode you just add a little “c” at the end of the version number:

<script type="text/javascript" src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.3c"></script>

Have a look at this little sample here which uses the Bing Maps AJAX Control in core-mode on a mobile website and overlays some floor-plans. To make this all a bit more fancy I have created a Microsoft Tag pointing to this website. Currently supported devices for the tag reader are:

Windows Mobile
5.0
6.0
6.1
6.5

Android
1.5
1.6
2.0
2.0.1
2.1

BlackBerry
4.3
4.5
4.6
4.7
5.0

iPhone
3.0
3.1

Once you installed the tag-reader from http://gettag.mobi start the application, point the camera on your mobile device to the tag below and you will automatically be redirected to the mobile website. I realize of course that it doesn’t make too much sense for content that is already on the web but it might be a good idea to have this is print-media or on billboards.

Technorati Tags: Bing Maps,Virtual Earth,AJAX,Core-Mode,Mobile,Microsoft Tag,Tag

10:56 GMT | Read comments(0)

23 June

Drag-able Routes in the Bing Maps AJAX Control

Many people like the drag-able routes in the consumer facing implementation of Bing Maps. This feature basically allows you to mouse over a route-path and drag a point to a location that you want to drive past.

For example if I’m not happy with a route through the centre of Reading I can just drag a point in the route-path to the M4 and dynamically re-calculate the driving directions.

Unfortunately there is no such feature in the API but with a little hack we can do this ourselves. The first thing that helps us here is the multi-waypoint routing in Bing Maps. You can have up to 25 waypoints, i.e. a start-point, an end-point and up to 25 points in between within a single route-calculation. When we re-calculate the route we basically set an additional waypoint.

The next important thing to know is that the VEMap.GetDirections-method returns the route-path as an array of points. So rather than using the default behaviour we can suppress the automatic drawing of the route, get the points from a callback function and draw a polyline instead. I admit that sounds weird, why would we want to do something ourselves that a build-in feature could already provide for us? Well, the answer is, that we can use the Bing Maps event system with our polyline. We will attach an event that captures when we mouse over the polyline.

function GetDirections() {
    slRoute.DeleteAllShapes();
    pointArray = new Array();

    var options = new VERouteOptions();
    options.DrawRoute = false;
    options.RouteCallback = onGotRoute;
    map.GetDirections([document.getElementById("txtStart").value, document.getElementById("txtEnd").value], options);
}

function onGotRoute(route) {
…
    map.SetMapView(route.ShapePoints);
    var shape = new VEShape(VEShapeType.Polyline, route.ShapePoints);
    shape.SetLineColor(new VEColor(255, 165, 0,0.5));
    shape.SetLineWidth(5);
    shape.HideIcon();
    shape.SetTitle("MyRoute");
    shape.SetZIndex(1000, 2000);
    slRoute.AddShape(shape);
…

    map.AttachEvent("onmouseover", HandleMouseOverRoute);
}

When this event fires we can add an icon at the location of the cursor and attach another event that captures when we move the mouse. This icon will be our drag-point and it will be visible when we mouse over the route only. When the cursor is off the route we clear this remove this drag-point again, unless we keep the left mouse-button pressed down.

function HandleMouseOverRoute(e) {
    if (e.elementID != null) {
        if (map.GetShapeByID(e.elementID).GetTitle().match("MyRoute")) {
            map.DetachEvent("onmouseover", HandleMouseOverRoute);
            var x = e.mapX;
            var y = e.mapY;
            var LL = map.PixelToLatLong(new VEPixel(x, y));
            dragPoint = new VEShape(VEShapeType.Pushpin, LL);
            dragPoint.SetCustomIcon("./IMG/drag.png");
            dragPoint.SetZIndex(1000, 2000);
            slDragPoint.AddShape(dragPoint);
            map.AttachEvent("onmousemove", HandleMouseOverRouteMove);
        }
    }
}

function HandleMouseOverRouteMove(e) {
    if (e.elementID != null) {
        if (map.GetShapeByID(e.elementID).GetTitle().match("MyRoute")) {
            var x = e.mapX;
            var y = e.mapY;
            var LL = map.PixelToLatLong(new VEPixel(x, y));
            dragPoint.SetPoints(LL);
            map.AttachEvent("onmousedown", HandleMouseDown);
        }
    }
    else {
        map.DetachEvent("onmousedown", HandleMouseDown);
        map.DetachEvent("onmousemove", HandleMouseOverRouteMove);
        slDragPoint.DeleteAllShapes();
        map.AttachEvent("onmouseover", HandleMouseOverRoute);
    }
}

When we press the mouse-button down we will start the dragging and when we release it we will end the dragging and recalculate the route

function HandleMouseDown(e) {
    map.DetachEvent("onmousemove", HandleMouseOverRouteMove);
    map.AttachEvent("onmousemove", HandleDragPointMove);
    map.AttachEvent("onmouseup", HandleMouseUp);
}

function HandleDragPointMove(e) {
    var x = e.mapX;
    var y = e.mapY;
    var LL = map.PixelToLatLong(new VEPixel(x, y));
    dragPoint.SetPoints(LL);
    return true; //prevent default behaviour
}

function HandleMouseUp(e) {
    map.DetachEvent("onmousemove", HandleDragPointMove);
    map.DetachEvent("onmouseup", HandleMouseUp);

    var x = e.mapX;
    var y = e.mapY;
    var LL = map.PixelToLatLong(new VEPixel(x, y));
    pointArray.splice(pointArray.length-1,0,LL);
    
    Reroute();
}

function Reroute() {
    slRoute.DeleteAllShapes();
    var options = new VERouteOptions();
    options.DrawRoute = false;
    options.RouteCallback = onGotRoute;
    map.GetDirections(pointArray, options);
}

Pretty simple, isn’t it? You will find a quick demo here. The complete sample code is here.

Technorati Tags: Bing Maps,Virtual Earth,Drag-able Route,Route,drag & dop

17:21 GMT | Read comments(0)

17 May

Bing Maps & SharePoint 2010 – SQL Server 2008 R2 Reporting Services

In the first 2 parts of this series of blog posts we had a quick look at the build-in support for Silverlight Web Parts in SharePoint 2010 and the SharePoint Map Integration Layer (SMIL) which allows you to connect SharePoint lists and image libraries to a Bing Maps web part. In this 3rd part we will now have a quick look at the integration with SQL Server 2008 R2 Reporting Services and creating thematic maps from data that you store in SQL Server 2008.

A new feature that was released with SQL Server 2008 R2 Reporting Services and Report Builder 3 is the ability to integrate maps into your reports. Let’s have a lap around this new feature and see how simple it actually is.

After configuring SQL Server Reporting Services for SharePoint integrated mode and adding the Report Server content types to a library we can create reporting services directly from within a document library. From the library tools we select “New document” and then “Report Builder Report”.

This will launch the Report Builder and bring up the Wizard. Let’s select the Map Wizard.

You can create maps from a gallery of resources for the United States, from ESRI shape-files or from spatial data that you store in a SQL Server 2008. Let’s select “SQL Server spatial query”.

The first thing we will need is a new dataset.

You can create new data connections for this dataset or choose from existing ones. Let’s create a new new data connection.

We build our connection string, click “OK”…

…and select the new data source in our dialog.

Next we design our query by simply selecting the fields we need.

By ticking the box “Add a Bing Maps layer” we can use the Bing Maps road-maps, aerial-imagery or a hybrid of both as a background.

Now we choose a visualization type.

In the next step we can either select an existing dataset for the analytical data or create a new one. Let’s select our existing dataset.

Finally we apply a couple of rules for the styling.

Now we can cleanup the mapping part, create other visualization components in the same report and save it in our document library in SharePoint.

Finally we add a Report Viewer web part to our SharePoint site and browse to the report that we want to add.

That’s it. With just a bit of mouse-pushing we created our thematic map in SharePoint.

While this approach is very simple and quite powerful when you want to create different types of simple data visualization, the map is unfortunately quite static. When you are used to the Bing Maps AJAX or Silverlight controls you are probably missing the interactivity of the map with the smooth panning and zooming and the interaction with its layers. In the next part of this series of blog posts we will have a look at how to overcome this limitation and additionally at how to create heat- or density maps.

Johannes Kebeck's

Virtual Earth