The CircularString Geometry in SQL Server 11 (Denali)

SQL Server Denali supports several new types of spatial geometry, including the CircularString. The CircularString, like the LineString, is an instantiable geometry derived from the abstract curve class. However, unlike the LineString which uses linear interpolation to connect points using straight lines, the paths drawn between the points of a CircularString are circular arcs.

Since there are an infinite number of circular arcs that can be drawn to connect two points, whereas a LineString can consist of only two points, a CircularString must contain at least 3 points – a start point, end point, and an “anchor” point that lies somewhere on the circular arc between the start and end points.

Here’s an example of the WKT for a basic CircularString:

CIRCULARSTRING(1 5, 6 2, 7 3)

and here’s the Circular String it represents (grey dotted line added just to illustrate the complete circle from which the arc is formed):

Here’s another CircularString between the same start and end points, but this time the anchor point has been placed at (5,5) instead:

CIRCULARSTRING(1 5, 5 5, 7 3)

Each additional segment added to the CircularString requires an additional two points – one end point to determine where the segment ends, and one “anchor” point to determine which circular arc path is used to get there. It is worth noting that to define a complete circle requires 5 points (not 3, as you might expect). This is because three points alone would not be sufficient to specify the orientation of the CircularString – it would be ambiguous as to whether the points should be traversed in clockwise or anti-clockwise order.

Instead you must define a circle as follows:

CIRCULARSTRING(4 1, 7 4, 4 7, 1 4, 4 1)

Compare this to geometry created if the same set of points were defined instead as a LineString:

LINESTRING(4 1, 7 4, 4 7, 1 4, 4 1)

 

Drivetime Polygons with MapPoint and Bing Maps

There was a time, a few years ago, when the MapPoint CD could regularly be found in my computer’s CD drive… back then, it was the best tool for examining maps, plotting routes, and creating your own sets of pushpin data.

Nowadays, Bing maps, Google maps, and other online web-mapping providers provide much of the functionality that could previously only be found in desktop applications such as MapPoint, for free, with continuously updated road and aerial imagery data. The terabytes of global data that sit behind web applications such as Bing Maps could never easily be packaged up and distributed in a desktop application any more. And since these webservices are readily available from any web browser, (including almost all new mobile phones), there are less obvious reasons why you’d still want to use the MapPoint desktop application on your computer.

However, there are still some functions that MapPoint provides that are not easily available in Bing Maps or Google Maps (or, as far as I’m aware, any other web-mapping providers). One of these features is the ability to plot drivetime polygons – the area containing all those places that can be reached by road within a certain amount of time starting from a particular location.

Fortunately, MapPoint can be accessed through a COM interface, which means that we can create a drivetime polygon programmatically through code, and then export it as a SQL Server geography polygon or Bing Maps polygon to display using the Bing Maps control.

To do so, you first need to import a reference to the MapPoint Object Library into your application. Create a new Visual Studio project, select Add Reference from the Project menu, select the COM tab, and scroll down to select the Microsoft MapPoint Object Library.

Note that I’m using Microsoft MapPoint Europe 2010, so my library is version 17.0 – if you have a different version installed on your computer then the name of the library will differ accordingly, but you should still be able to use the same code.

Once you’ve imported the COM assembly, you can access the MapPoint map functions via the ApplicationClass of the MapPoint namespace. The following code demonstrates a simple C# console application that initialises the MapPoint application, creates a drivetime polygon that determines all locations that can be reached within 60 minutes drive from “10 Downing Street, London”, and then retrieves the points in those shapes and converts them to Well-Known Text:

using System;
using System.Collections.Generic;
using System.Text;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {

            MapPoint.ApplicationClass app = new MapPoint.ApplicationClass();
            MapPoint.Map map = app.ActiveMap;

            object index = 1;
            MapPoint.Location location =
       (MapPoint.Location)map.FindResults("10 Downing Street, London, England").get_Item(ref index);

            //Add a 60 minute drivetime zone around a location
            MapPoint.Shape shape =
               map.Shapes.AddDrivetimeZone(location,
                          60 * MapPoint.GeoTimeConstants.geoOneMinute);

            string WKT = "POLYGON((";

            //Now get the vertices for this polygon
            object[] vertices = shape.Vertices as object[];
            foreach (object vertex in vertices)
            {
                MapPoint.Location loc = vertex as MapPoint.Location;
                WKT = WKT + loc.Longitude.ToString() + " " + loc.Latitude.ToString() + ", ";
            }
            WKT = WKT.Substring(0, WKT.Length - 2);
            WKT = WKT + "))";
            Console.Write(WKT);
        }
    }
}

Drivetime polygons created using this method can be stored in SQL Server 2008’s geography datatype, and then retrieved and plotted on Bing Maps.

Here’s some drivetime polygons generated using the method described above illustrating those areas reachable within  20 minutes, 40 minutes, and 60 minutes drive starting from my house in Norwich, displayed on Bing Maps v7:

using System;

using System.Collections.Generic;

using System.Text;

namespace ConsoleApplication1

{

    class Program

    {

        static void Main(string[] args)

        {

            MapPoint.ApplicationClass app = new MapPoint.ApplicationClass();

            MapPoint.Map map = app.ActiveMap;

            object index = 1;

            MapPoint.Location location =

       (MapPoint.Location)map.FindResults("10 Downing Street, London, England").get_Item(ref index);

            //Add a 60 minute drivetime zone around a location

            MapPoint.Shape shape =

               map.Shapes.AddDrivetimeZone(location,

                          60 * MapPoint.GeoTimeConstants.geoOneMinute);

            string WKT = "POLYGON((";

            //Now get the vertices for this polygon

            object[] vertices = shape.Vertices as object[];

            foreach (object vertex in vertices)

            {

                MapPoint.Location loc = vertex as MapPoint.Location;

                WKT = WKT + loc.Longitude.ToString() + " " + loc.Latitude.ToString() + ", ";

            }

            WKT = WKT.Substring(0, WKT.Length - 2);

            WKT = WKT + "))";

            Console.Write(WKT);

        }

    }

}

The bing.com/maps site, the Bing Maps key, and the Elevation REST Service

http://www.bing.com/maps is Microsoft’s consumer-facing map site. The functionality that it exposes (geocoding, routing, map display etc.) is based on, although not identical to, the features available through the Bing Maps AJAX Control and Silverlight control.

A recent question on the MSDN forum asked whether it would be possible to implement a particular feature from the bing.com/maps site (draggable routes) that wasn’t included “out-of-the-box” in the Bing Maps API. Out of curiosity, this prompted me to have a look at the source code for bing.com/maps, to see how it was achieved there.

Since the AJAX map control used on www.bing.com/maps is just javascript, it’s possible to examine all the source used to create the map by just viewing the page source in a browser (although if has been somewhat obfuscated and minified). But before even getting round to the section of code responsible for draggable routes, I uncovered two interesting, unrelated things:

I’ll Show You My Key if you Show Me Yours

Firstly, the key used to access the Bing Maps services from http://www.bing.com/maps is in plain view in the source code of the page. Bing Maps keys are tied to a particular URL, so there shouldn’t be an issue with letting your key be known publicly. (In fact, since the AJAX map control is executed client-side, there is no way of using the key authentication method without somehow letting your client know the key). However, there had been some discussion on the Bing Maps Developer forum about whether it was wise to try to obfuscate your key from casual snoopers. If we assume that Microsoft lead by example in terms of setting best practice on the bing.com site, since they haven’t hidden their key is it safe to assume that we as developers shouldn’t worry about key obfuscation either?

The Elevation Service

The second interesting thing was a definition of an elevationServiceUrl, as follows:

"elevationServiceUrl","{urischeme}dev.virtualearth.net/REST/v1/Elevation/BoundingRect/{south},{west},{north},{east}/{rows}/{cols}?jsonp=microsoftMapsNetworkCallback&jsonso={jsono}&key={credentials}"

This seems to be an, as yet undocumented, elevation service that can be called via REST – and the URL schema and parameters follow a similar pattern as the existing Geocode, Locations, and Routing REST services.

Experimenting with this elevation service to find out what it did, I tried calling it as follows:

http://dev.virtualearth.net/REST/v1/Elevation/BoundingRect/52,1,53,2/4/4?jsonp=microsoftMapsNetworkCallback&key=AhGSgD1Twhjx9WqxjJZznCBCSzddrrBzkD7k6MjIaLGnp3b3hupQUVbNdv6Wb0qW

The result was a series of decimal “alt” values, rows * cols in length, together with a single “lod” value of 8, in JSON format:

microsoftMapsNetworkCallback({“alt”:[45, -1, 0, 0, 38, -6, -24, -8, 46, 2, -36, -12, 50, -27, -23, 0 ],”lod”:8})

Following the syntax of the other REST services, the result is passed to a callback function specified in the (optional) jsonp parameter – in this case a function called microsoftMapsNetworkCallback. Given that this URL is described as an elevation service, it seems fair to assume that the “alt” values are altitude. “lod” might stand for “level of detail”, but I’m not sure how it relates to the other values. What concerned me more, however, was the alt values themselves. What are they measured relative to, and in what units are they expressed?

The BoundingRect I supplied contains the northeast corner of East Anglia, extending out into the North Sea. I was therefore surprised to see so many negative values. Even assuming that altitude is measured relative to the ellipsoid rather than to sea-level, the altitude coordinates don’t seem to line up with the terrain I know….

To make the comparison simpler, I tried to request the elevation for a single point, by setting both the rows and columns parameters to 1, but this resulted in a “Bad Request” error – it seems that the service is only designed to produce elevation matrix of an area, and you’ll get an error from the service unless both these values are greater than 1. So, the next best thing was setting them both to 2:

http://dev.virtualearth.net/REST/v1/Elevation/BoundingRect/52,1,53,2/2/2?key=AhGSgD1Twhjx9WqxjJZznCBCSzddrrBzkD7k6MjIaLGnp3b3hupQUVbNdv6Wb0qW

which results in:

{“alt”:[45,-1,50,-50],”lod”:11}

A different “lod” value, and four elevation values, as expected. Next is to take a guess at how the rows and columns are assigned across the assigned BoundingRect range. If we were to include the range boundaries themselves, we might expect the previous results to correspond to the readings at each corner of the BoundingRect. Testing these results from the Google elevation service instead:

http://maps.googleapis.com/maps/api/elevation/xml?locations=52,1|52,2|53,1|53,2&sensor=false

Gives the following four elevations:

47.3150635, -27.7267170, -5.8248205, -27.2653141

These values look to be interpolated, whereas the Bing values are probably not, but they still don’t seem to line up particularly well.

Perhaps, instead, the Bing results represent the centrepoint of each cell having defined the BoundingRect into cols * rows. In other words, the four results correspond to the locations at:

http://maps.googleapis.com/maps/api/elevation/xml?locations=52.25,1.25|52.25,1.75|52.75,1.25|52.75,1.75&sensor=false

Which, according to Google, are located at elevations of:

60.4444427, -18.9525394, 25.1086426, -24.8328476

These results at least share the same sign as those from Bing and, accounting for the interpolation, could even be using the same dataset. It’s a bit hard to be certain though.

Under the Sea?

The last mystery remained about explaining the negative values – how could a location in the sea apparently have an altitude below sea level…. unless, the elevation service also includes bathymetry data, and is therefore reporting negative values for depth below sea level of locations on the ocean floor. Yes! Or so I thought, until I tried testing the elevation of an area somewhere near the Mariana Trench:

http://dev.virtualearth.net/REST/v1/Elevation/BoundingRect/11,142,12,143/2/2?key=AhGSgD1Twhjx9WqxjJZznCBCSzddrrBzkD7k6MjIaLGnp3b3hupQUVbNdv6Wb0qW

Fully expecting an answer of something like –10,000, instead I got the following:

{“alt”:[46,2,-11,3],”lod”:8}

Oh well. I guess that’s what you get for playing around with undocumented APIs….

Bing Maps UK Usergroup Meeting – January 19th 2011

The videos from the last Bing Maps UK UG meeting are now available online at the group homepage – http://bingmapsuk.ning.com.

Apologies for the poor audio quality – as a group we’ve always felt that there was a benefit in moving around and using different venues for our meetings. On this occasion, we were delighted that the British Computer Society offered to host our meeting (our last two meetings were held at Infusion and Microsoft, respectively).

Unfortunately, one downside of using different meeting venues is that we have to adapt to a variety of location setups and facilities, and we don’t always know in advance details of things like what (if any) sound systems are available, the quality of the internet connection, the acoustic conditions etc. etc. On this occasion the videos are just copies of the video that was streamed live over a limited bandwidth wireless internet connection, and the presenters were not individually mic’ed so the audio is just from a low-quality room mic.

If you’d like clarification of any of the points you (can’t quite) hear on the videos, please let me know. I thought I’d draw your attention to a couple of points that Johannes makes in his session:

• Carla Parry has left Microsoft and taken up a new role at Argos.
• Chris Pendleton has also moved on into a new role in the Bing mobile team. Brian Hendricks has taken over some of his role in managing the Bing Maps blog account etc.
• The MS Bing Maps team itself has been integrated into the Bing Mobile team (so Chris Pendleton still isn’t very far away!)
• Bing Maps applications now exist for WP7, iPhone, and Android. The v7 AJAX control is also officially supported on mobile devices.
• AJAX control v7 is smoother faster, supports HTML 5, and now officially supported on mobile devices.
• Additional features will be added to the v7 control in a modular fashion. i.e. there will be a “routing” library add-in, a “geocoding” library add-in. Only including those libraries required in a given application will help keep download size small and efficient.
• Since v7 is a (significantly) breaking change to the API, v6.3 is expected to have a long end-of-life, and will probably continue to be supported for the next 2 years.
• There has been significant investment in the REST services, with more development still to come. The spatial data services, for example, will add in the ability to load custom POI data to replace the existing functionality of the Mappoint Customer Data Service.
• There is no current plan to implement the Extended modes for Silverlight (Birdseye, Streetside) in a production environment. These features may in fact surface via the AJAX control first.
• Infobox support will be added to the v7 AJAX control in the next release, probably due February 2011.
• For batch geocoding, the spatial data services geocode dataflow is free, and can be used to process batches of 200,000 records or 100mb. Up to 10 requests can be made to the service in any 24hr period.

Also be sure to check out the other videos from members of the group telling us about how they’ve implemented Bing Maps:

• The Woodland Trust tell us how they’re using Bing Maps to create and display a database of natural woodland, and using it to support their advocacy and campaigning to save England’s ancient forests.
• Earthware show how they’ve used Bing Maps and Silverlight as part of a Business Intelligence dashboard for an American ministry.
• 2020 Imaging use the Bing Maps 3D control together with video streams, image recognition, document management and other technologies in a WPF-based security control application.

Massive thanks to those members who volunteered to speak, and please let me know if you’re interested in showing anything at a future meeting!