SQL 2008 and Virtual Earth : Converting a VERoute to a SqlGeography
At the SQL 2008 unleashed event I demoed some code that
incorporated VE with SQL 2008 to find those people attending SQLBits that where
near your route to the conference.
I've been asked to provide the code for that, unfortunately the code is to
entangled with the SQLbits site and so I've decided to break it into small
chunks that you can then put together.
The first of these is some code that converts VERoutes into a sql geography
type. This is needed if you want to to any spatial searching based on
routes.
Its really neat that VE provides routing, one downside is that the standard
VE service only provides waypoints and not the precise route. For that reason if
you travel from London to Leeds along the M1 you will only get the entry and
exit junctions of the M1. If you join you points together it will look as though
you travelled across country and not along the M1. The commercial and developer
licensed versions supposedly give the full route. However I haven't been able to
get this working.
Anyway, To get a route you need to call the GetDirections method.
In this example I have two layers each with a point on that marks the venue
(destination) and the starting location. Note : Use of layers is very useful
to control displaying and loading points. However you have to set the
"SendLayersToServer" property of the map control to true.
RouteOptions ro = new RouteOptions
();
ro.DistanceUnit = RouteDistanceUnit.Mile ;
ro.DrawRoute = true;
ro.SetBestMapView = true;
ro.RouteOptimize = RouteOptimize.MinimizeTime ;
//Get the location of the venue (the
destination)
var Venue =
Map1.GetShapeLayerByIndex(LAYER_BASE).Shapes[0];
//Only proceed if the user has said where to
start from
if
(Map1.GetShapeLayerByIndex(LAYER_LOCATION).GetShapeCount() >
0)
{
//Get the location of the start
point
Shape House =
Map1.GetShapeLayerByIndex(LAYER_LOCATION).Shapes[0];
//Ask the control to get the directions.
//This is an async call, the result is
returned in the callback method
Map1.GetDirections(new List<LatLongWithAltitude>() { Venue.Points[0],
House.Points[0] }, ro);
}
I got this far and was then stumped as to how I got hold of the route that
was planned, I thought it was a special layer. After digging through the client
side script and loking at the eventhandlers defined for the control I realised
the route is past pack using the ServerGetDirections postback event.
Whats odd is that it takes a MapRouteEventhandler, it seems the changed from
Route to Directions but not everywhere.
Once you've defined your evenhandler you need to do something with the route
that is passed back. In this example code I'm building a SqlGeography instance
using the new SqlGeographyBuilder api. Its a very easy interface to use even if
there are a few gotchas. The main gotcha is the first point has to be defined
with the BeginFigure method (from what I could work out), after that you can
call AddLine.
The key hear is looping through the different collections that comprise the
Route object. The final step is to do something with the instance you've built.
In this case I stored the shape as text in a field on the page. I'm sure this
isn't great as this could get quite large. I haven't found a way of getting at
the Route after this event has fired so you have to do whatever you want with
the route in this event, otherwise your stuck.
void
Map1_ServerGetDirections(object sender,
Microsoft.Live.ServerControls.VE.Route
e)
{
MapID.Text = "Route
found";
//create a SQLGeography from the route
generated
SqlGeography G = new SqlGeography();
SqlGeographyBuilder gb = new SqlGeographyBuilder();
gb.SetSrid(4326);
gb.BeginGeography(OpenGisGeographyType.LineString);
bool first = true;
foreach (var r in
e.RouteLegs)
{
foreach (var i in
r.Itinerary.Items)
{
//In the commercial interface you get the full
shape of the route
//otherwise you just get the co-ordinates of
the ends of each leg (i.e. junctions)
if (i.Shape != null)
{
//Look through points in the
shape
foreach (var p in
i.Shape.Points)
{
if (first)
{
//First points so start the Geography
figure
gb.BeginFigure(p.Latitude, p.Longitude);
first =
false;
}
else
{
//Add a line from the last point to this
point
gb.AddLine(p.Latitude, p.Longitude);
}
}
}
#region
OnlyPointsNoShapes
else
{
if (first)
{
gb.BeginFigure(i.LatLong.Latitude,
i.LatLong.Longitude);
first = false;
}
else
gb.AddLine(i.LatLong.Latitude,
i.LatLong.Longitude);
}
#endregion
}
}
gb.EndFigure();
gb.EndGeography();
RouteText.Value =
gb.ConstructedGeography.STAsText().ToSqlString().Value;
I hope that is useful, its using the VE server control rather than the
javascript version.
I will post more in the future
If you want a developer license then you can signup here
https://mappoint-css.live.com/MwsSignUp/Default.aspx
Once signed
up you need to enable the app to get a token and use that token in requests.
Details on how to get the token per session are
here:
http://msdn.microsoft.com/en-us/library/bb924353.aspx
Johannes has
a great example here:
http://johanneskebeck.spaces.live.com/blog/cns!42E1F70205EC8A96!4504.entry
-