The products listed above, and their associated names, icons and logos, are the intellectual property of Microsoft Corporation.
Rate this Article!
Average Rating: 
Whole StarWhole StarWhole StarWhole StarHalf Star
Total number of ratings: 2
Leave your own rating
Text/HTML

Introduction

SQL Server 2008 introduced two new datatypes - geometry and geography, which are specifically designed for working with spatial data. The addition of spatial functionality provides lots of exciting opportunities to combine Virtual Earth and SQL Server 2008 to create powerful spatial applications.

There are already many excellent articles explaining how to get data out of SQL Server to display on a Virtual Earth map control (such as http://www.viawindowslive.com/Articles/VirtualEarth/SQL2008Spatialafirstimpression.aspx). In this article, I'll show you a different way of combining the technologies - using Virtual Earth as a tool to help insert spatial data into SQL Server 2008. Specifically, I'll create a C# application that uses the VE Routing Service to create a route between any two destinations that can be imported as a LineString into SQL Server.

The following screenshot illustrates the application in action:



What You'll Need

Before you begin, you'll need the following:

SQL Server 2008
All editions of SQL Server 2008 support spatal data. You can download the freely-available SQL Server Express edition.
Visual Studio 2005/2008
If you don't have a full version of Visual Studio, you can use one of the freely-available Express versions. In this article, I'll be using the C# language, so download Visual C# 2008 Express Edition.
A Virtual Earth Developer Account
To use the Virtual Earth web services, you must have a valid Virtual Earth developer account. For testing purposes, you can sign up for a free developer account at https://mappoint-css.live.com/MwsSignup/

Setting up the project

Now that you've got all the required software, let's begin!

From Visual Studio, create a new C# application by selecting:
File -> New Project -> Visual C# -> Windows -> Windows Forms Application.

For this example, I'll assume that you name the project VEQLRouteGeneratorApp. If you choose a different name, remember to change the namespaces in the code later. Once you've created a new project, the first thing to do is to add the references to the various libraries and services that we will need to use:

Adding the Virtual Earth Web Service References

The Virtual Earth Web Services are split across four separate WCF services: Geocode, Imagery, Routing, and Search. There is also a common service that provides methods and classes used across all of the other services, including the token authentication system. In this project, we will use the Geocode, Imagery, Routing, and Token services, which means that we must add the necessary references into our project.

Adding the Token Service

All of the Virtual Earth web services require you to authenticate with a token provided by the token service. To add a reference to the Token service, follow these steps:

  1. Project -> Add Service Reference -> Advanced -> Add Web Reference
  2. Enter the following url: http://staging.common.virtualearth.net/find-30/common.asmx?wsdl
  3. Enter your VE Account ID and password, and click OK

  4. Note: For some reason you may get prompted multiple times to enter your password (I had to enter it 4 times) - just carry on going and eventually you'll get to the screen shown below.
  5. Change the Web reference name to something memorable - I used VECommonService
  6. Click Add Reference

These steps are shown in the following screenshot:



Adding the Geocode Service

Our application will allow you to calculate a route between any two named places. In order to do so, the start and destination locations must first be geocoded to a latitude/longitude coordinate pair. This requires us to use the VE Geocode service, so we must first add a reference as follows:

  1. Project -> Add Service Reference
  2. Enter the following url: http://staging.dev.virtualearth.net/webservices/v1/geocodeservice/geocodeservice.svc?wsdl
  3. Click Go
  4. Change namespace to VEGeocodeService
  5. Click OK

Adding the Route Service

The LineString that we will insert into SQL Server will be based on the route returned by the VE Route Service. To use the route service, we need to add a reference as follows:

  1. Project -> Add Service Reference
  2. Enter the following url: http://staging.dev.virtualearth.net/webservices/v1/routeservice/routeservice.svc?wsdl
  3. Click Go
  4. Change namespace to VERouteService
  5. Click OK

Adding the Imagery Service

Once the route has been calculated, it will be displayed on a minimap in the application. For this, we require the VE Imagery service:

  1. Project -> Add Service Reference
  2. http://staging.dev.virtualearth.net/webservices/v1/imageryservice/imageryservice.svc?wsdl
  3. Click Go
  4. Change namespace to VEImageryService
  5. Click OK

Adding the SqlServer.Types.dll reference

The spatial methods in SQL Server 2008 are implemented as .NET CLR datatypes, contained in a separate assembly called Microsoft.SqlServer.Types.dll. By including a reference to this assembly within your project, you can use the same spatial functions as contained within SQL Server 2008 from any .NET application. To include a reference to the assembly:

  1. Project -> Add Reference
  2. From the .NET tab, highlight Microsoft.SqlServer.Types
  3. Click OK

Configuring the buffer size

The routes received from the Virtual Earth Web Service can be complex and contain many thousands of points. To ensure that our application is capable of receiving the full route response, we need to adjust the buffer size. To do so:

  1. Double-click on the app.config file from the Solution Explorer pane.
  2. Scroll down to the following section of code:
    openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"
    allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
    maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536"
    messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
    useDefaultWebProxy="true">

  3. Change the values of both the maxBufferSize and maxReceivedMessageSize properties to 2000000 (2Mb). This should be sufficient for even the most complex routes.
  4. Close and save the app.config file

Adding the code

Now that we've got the project set up, it's time to add the actual code. There's quite a few lines and much of it is fairly generic. Therefore, I'll only pick out and describe particular sections of code that are specific to this application:

Including the Namespaces

In order to be able to easily reference the methods provided by the various Virtual Earth Web Services and the spatial methods provided by the Microsoft.SqlServer.Types.dll assembly, we include using directives for all of them at the very top of the code:

using VESQLRouteGeneratorApp.VECommonService;
using VESQLRouteGeneratorApp.VERouteService;
using VESQLRouteGeneratorApp.VEImageryService;
using VESQLRouteGeneratorApp.VEGeocodeService;
using Microsoft.SqlServer.Types;

Requesting a Token

Before using any of the Virtual Earth web service methods, the GetToken() function requests a token and assigns it to the VEToken variable. This token is then provided in the credentials of all future service requests:

VECommonService.CommonService commonService = new VECommonService.CommonService();
commonService.Credentials = new System.Net.NetworkCredential(txtAccountID.Text,txtPassword.Text);
VECommonService.TokenSpecification tokenSpec = new VECommonService.TokenSpecification();
VEToken = commonService.GetClientToken(tokenSpec);

Geocoding the Start and End locations

When using the application, you specify the start and end locations of the route using free-text boxes. In order to plot a route between these points, it is first necessary to geocode them using the Geocode() method. This method returns a Location object, which contains the latitude and longitude of each position.

To keep this example simple, I only use the top matching geocoded result. In a production tool, if there were more than one match for a given address, you would probably want to display a disambiguation box allowing the user to choose whether they meant 'Boston, MA', or 'Boston, Lincs, England', for example.


VEGeocodeService.GeocodeRequest myGeocodeRequest = new VEGeocodeService.GeocodeRequest();
myGeocodeRequest.Query = Address.ToString();
VEGeocodeService.GeocodeServiceClient myGeocodeServiceClient = new VEGeocodeService.GeocodeServiceClient();
VEGeocodeService.GeocodeResponse myGeocodeResponse = myGeocodeServiceClient.Geocode(myGeocodeRequest);

Requesting the Route

Having obtained Location instances representing the start and destination of our route, these are then passed to the CalculateRoute() method that calculates the route between them, using the specified options (Driving/Walking, Shortest/Quickest).

When the request for the route is made, it is important to set the route options to include the RoutePathType.Points property. This will ensure that the response includes all of the individual points that are used to construct the route, which we will use to create the LineString geometry of the route.

routeRequest.Options.RoutePathType = RoutePathType.Points;

Drawing the Route

Currently, there are no methods to automatically draw a route returned by the Route Service. To draw the route, I've used .NET drawing functions to plot the route on top of an image returned from the Imagery Service. All credit for this belongs to Richard Brundritt, who explained this technique in an excellent article here: http://rbrundritt.spaces.live.com/blog/cns!E7DBA9A4BFD458C5!488.entry

Converting the Route to a SqlGeography LineString

Remember that when we made the request to the route service, we specified that the RoutePathType was set to RoutePathType.Points. This means that, in the RouteResponse that is returned from the service, the Result.RoutePath.Points member contains an array of all of the individual points along the route.

To create the LineString geometry representing this route, the ConvertRouteToLineString() method uses the SqlGeographyBuilder class to loop through the array of points contained in the RoutePath element, and build up a LineString geography instance representing the route.

SqlGeographyBuilder gb = new SqlGeographyBuilder();

Once a new instance of the SqlGeographyBuilder class has been created, the SetSrid() method is called to set the relevant spatial reference identifier. All coordinates from the Virtual Earth Web Service are measured using SRID 4326 (the spatial reference identifier corresponding to the WGS84 spatial reference system, commonly used worldwide by GPS receivers and other devices).

gb.SetSrid(4326);

The first point of the LineString must be added using the BeginFigure() method:


if (start)
{
gb.BeginFigure(rt.Latitude, rt.Longitude, null, null);
start = false;
}

All subsequent points are added using the AddLine() method.

gb.AddLine(rt.Latitude, rt.Longitude, null, null);

Once you have constructed the geography instance, you can use the resulting ConstructedGeography with any of the provided spatial methods - you can buffer it using STBuffer(), simplify it using Reduce(), or use a SqlClient to insert it directly into a SQL database table. But to keep this example simple, we'll simply use the ToString() method to return a WKT representation of the LineString, which we'll display in a textbox.

txtResults.Text = LineString.ToString();

You can then copy the text string resulting from the ToString() method into SQL Server and use it with the STLineFromText() method.

The Code in full

The full code for the application is as follows. Right-click on Form1.cs in the Solution Explorer pane, select View Code and enter the following:

Form1.cs


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Net;
using System.IO;
using System.Text;
using System.Windows.Forms;

using VESQLRouteGeneratorApp.VECommonService;
using VESQLRouteGeneratorApp.VERouteService;
using VESQLRouteGeneratorApp.VEImageryService;
using VESQLRouteGeneratorApp.VEGeocodeService;

using Microsoft.SqlServer.Types;

namespace VESQLRouteGeneratorApp
{
public partial class Form1 : Form
{
// Declare a variable to hold our token
private string VEToken;

public Form1()
{
InitializeComponent();
}

// Request an authentication token
private void GetToken()
{
VEToken = "";
try
{
// Set Virtual Earth Platform Developer Account credentials to access the Token Service
CommonService commonService = new CommonService();
commonService.Credentials = new NetworkCredential(txtAccountID.Text, txtPassword.Text);
// Set the token specification properties
TokenSpecification tokenSpec = new TokenSpecification();
tokenSpec.ClientIPAddress = "0.0.0.0";
tokenSpec.TokenValidityDurationMinutes = 480;
VEToken = commonService.GetClientToken(tokenSpec);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}

private void btnCalculate_Click(object sender, EventArgs e)
{
// Make sure that the user has put in their credentials
if (txtAccountID.Text == "123456" && txtPassword.Text == "P@55w0rd")
MessageBox.Show("Please enter a valid VE Account ID and Password");
else
{
// Reset the text box and progress bar
txtResults.Text = "";
progressBar1.Value = 0;

// Request a token
txtResults.Text += "Requesting a token..." + Environment.NewLine;
Refresh();
GetToken();
if (VEToken != string.Empty) txtResults.Text += " - Assigned token " + VEToken + Environment.NewLine;
Refresh();

// Obtain coordinates for the start of the route
txtResults.Text += "Geocoding Start Location..." + Environment.NewLine;
Refresh();
VERouteService.Location StartLocation = Geocode(txtStartLocation.Text);
txtResults.Text += " - Start Address geocoded at (" + StartLocation.Latitude.ToString() + "," + StartLocation.Longitude.ToString() + ")" + Environment.NewLine;
progressBar1.PerformStep();
Refresh();

// Obtain coordinates for the destination of the route
txtResults.Text += "Geocoding End Location..." + Environment.NewLine;
Refresh();
VERouteService.Location EndLocation = Geocode(txtEndLocation.Text);
txtResults.Text += " - End Address geocoded at (" + EndLocation.Latitude.ToString() + "," + EndLocation.Longitude.ToString() + ")" + Environment.NewLine;
progressBar1.PerformStep();
Refresh();

// Calculate the route
txtResults.Text += "Calculating Route..." + Environment.NewLine;
Refresh();
RoutePath routePath = CalculateRoute(StartLocation, EndLocation);
txtResults.Text += " - Route contains " + routePath.Points.Length.ToString() + " points" + Environment.NewLine;
progressBar1.PerformStep();
Refresh();

// Always remember to reticulate the splines
txtResults.Text += "Reticulating Splines..." + Environment.NewLine;
Refresh();
System.Threading.Thread.Sleep(2000);
progressBar1.PerformStep();
txtResults.Text += " - Splines Reticulated" + Environment.NewLine;
Refresh();

// Display a map of the route
txtResults.Text += "Displaying Route" + Environment.NewLine;
Refresh();
DrawRoute(routePath);
progressBar1.PerformStep();
Refresh();

// Create a SqlGeography LineString of the route
txtResults.Text += "Converting Route to SqlGeography LineString" + Environment.NewLine;
Refresh();
SqlGeography LineString = ConvertRouteToLineString(routePath);
progressBar1.PerformStep();
txtResults.Text = LineString.ToString();
}
}

private VERouteService.Location Geocode(String Address)
{
try
{
// Instantiate a geocode request
GeocodeRequest geocodeRequest = new GeocodeRequest();
// Set the credentials for the request using a token
geocodeRequest.Credentials = new VEGeocodeService.Credentials();
geocodeRequest.Credentials.Token = VEToken;
// Set the request Query to the Address we are searching for
geocodeRequest.Query = Address.ToString();
// Create a new instance of the GeocodeServiceClient
GeocodeServiceClient geocodeServiceClient = new GeocodeServiceClient();
// Request the response
GeocodeResponse geocodeResponse = geocodeServiceClient.Geocode(geocodeRequest);
// Instantiate a new Location object to hold the results
VERouteService.Location results = new VERouteService.Location();
// Only return the top hit
results.Latitude = geocodeResponse.Results[0].Locations[0].Latitude;
results.Longitude = geocodeResponse.Results[0].Locations[0].Longitude;
// Return the results
return results;
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return null;
}
}

public RoutePath CalculateRoute(VERouteService.Location StartLocation, VERouteService.Location EndLocation)
{
try
{
// Instantiate a route request
RouteRequest routeRequest = new RouteRequest();
// Set the credentials for the request using a token
routeRequest.Credentials = new VERouteService.Credentials();
routeRequest.Credentials.Token = VEToken;
// Set the waypoints for the route
Waypoint[] waypoints = new Waypoint[2];
waypoints[0] = new Waypoint();
//waypoints[0].Description = "Start";
waypoints[0].Location = StartLocation;
waypoints[1] = new Waypoint();
// waypoints[1].Description = "End";
waypoints[1].Location = EndLocation;
routeRequest.Waypoints = waypoints;
// Create a route options class
routeRequest.Options = new RouteOptions();
// IMPORTANT! Need to request the points that make up the route
routeRequest.Options.RoutePathType = RoutePathType.Points;
// Create a walking route or a driving route
if (radDriving.Checked) routeRequest.Options.Mode = TravelMode.Driving;
else if (radWalking.Checked) routeRequest.Options.Mode = TravelMode.Walking;
// Create the route of shortest distance, or shortest time
if (radQuickest.Checked) routeRequest.Options.Optimization = RouteOptimization.MinimizeTime;
else if (radShortest.Checked) routeRequest.Options.Optimization = RouteOptimization.MinimizeDistance;
// Create a new instance of the RouteServiceClient
RouteServiceClient routeService = new RouteServiceClient();
// Calculate the route
RouteResponse routeResponse = routeService.CalculateRoute(routeRequest);
// Return the array of points that form the route
return routeResponse.Result.RoutePath;
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return null;
}
}

private SqlGeography ConvertRouteToLineString(RoutePath routePath)
{
// Create a new SqlGeographyBuilder
SqlGeographyBuilder gb = new SqlGeographyBuilder();
// Set the spatial reference ID in which coordinates are stated
gb.SetSrid(4326);
// Start the geography instance
gb.BeginGeography(OpenGisGeographyType.LineString);
// Keep track of whether it's the first point
bool start = true;
foreach (var rt in routePath.Points)
{
if (start)
{
// Add the first point using BeginFigure()
gb.BeginFigure(rt.Latitude, rt.Longitude, null, null);
start = false;
}
else
{
// Add the remaining points using AddLine()
gb.AddLine(rt.Latitude, rt.Longitude, null, null);
}
}
// End the figure
gb.EndFigure();
// End the geography instance
gb.EndGeography();
// Return the constructed SqlGeography instance
return gb.ConstructedGeography;
}

private void DrawRoute(RoutePath routePath)
{
/**
* 1.) Calculate the centre point and zoom level for the map
*/
// Declare initial values for the bounding rectangle
double maxLat = -85, minLat = 85, maxLon = -180, minLon = 180;
// Calculate the minimum bounding box for the route
for (int i = 0; i < routePath.Points.Length; i++)
{
if (routePath.Points[i].Latitude > maxLat) maxLat = routePath.Points[i].Latitude;
if (routePath.Points[i].Latitude < minLat) minLat = routePath.Points[i].Latitude;
if (routePath.Points[i].Longitude > maxLon) maxLon = routePath.Points[i].Longitude;
if (routePath.Points[i].Longitude < minLon) minLon = routePath.Points[i].Longitude;
}
// Define the location at the centre of the bounding box
VEImageryService.Location centrePoint = new VEImageryService.Location();
centrePoint.Latitude = (maxLat + minLat) / 2;
centrePoint.Longitude = (maxLon + minLon) / 2;
// Calculate the distance along the center latitude between the two longitudes
double meanDistanceX = HaversineDistance(centrePoint.Latitude, minLon, centrePoint.Latitude, maxLon);
// Calculate the distance along the center longitude between the two latitudes
double meanDistanceY = HaversineDistance(maxLat, centrePoint.Longitude, minLat, centrePoint.Longitude) * 2;
// Calculate scale of the map in the x and y dimensions
var mapScaleValueX = meanDistanceX / 256;
var mapScaleValueY = meanDistanceY / 256;
double mapScale;
// Use the greatest of the x and y scale for the overall map scale
if (mapScaleValueX > mapScaleValueY) mapScale = mapScaleValueX;
else mapScale = mapScaleValueY;
// Map resolution (metres/pixel) at each zoom level (http://msdn2.microsoft.com/en-us/library/aa940990.aspx)
double[] zoomResolutions = { 78271.517, 39135.7585, 19567.8792, 9783.9396, 4891.9698, 2445.9849, 1222.9925, 611.4962, 305.7481, 152.8741, 76.437, 38.2185, 19.1093, 9.5546, 4.7773, 2.3887, 1.1943, 0.5972, 0.2986, 0.1493, 0.0746, 0.0373, 0.0187 };
// Convert from map scale to the appropriate zoom factor
int zoom = 1;
for (var i = 1; i < 23; i++)
{
if (mapScale >= zoomResolutions[i])
{
zoom = i;
break;
}
}

/**
* 2.) Request a base map from the imagery service
*/
// Instantiate a map imagery request class
MapUriRequest mapUriRequest = new MapUriRequest();
// Set the credentials for the request using a token
mapUriRequest.Credentials = new VEImageryService.Credentials();
mapUriRequest.Credentials.Token = VEToken;
// Set the map centrepoint
mapUriRequest.Center = centrePoint;
// Create a new map options instance
MapUriOptions mapUriOptions = new MapUriOptions();
// Set the map style
mapUriOptions.Style = MapStyle.AerialWithLabels;
// Set the map zoom level
mapUriOptions.ZoomLevel = zoom;
// Set the size of the map image
mapUriOptions.ImageSize = new VEImageryService.SizeOfint();
mapUriOptions.ImageSize.Height = 256;
mapUriOptions.ImageSize.Width = 256;
// Attach the map options to the request
mapUriRequest.Options = mapUriOptions;
// Create a new instance of the ImageryServiceClient
ImageryServiceClient imageryService = new ImageryServiceClient();
// Make a request for the map image
MapUriResponse mapUriResponse = imageryService.GetMapUri(mapUriRequest);
// Retrieve the map image from the response
System.Net.WebRequest request = System.Net.WebRequest.Create(mapUriResponse.Uri);
System.Net.WebResponse response = request.GetResponse();
System.IO.Stream responseStream = response.GetResponseStream();
Image mapImage = Image.FromStream(responseStream);

/**
* 3.) Draw the route on top of the base map
*/
// Use the map image as a graphics object
Graphics graphics = Graphics.FromImage(mapImage);
// Create an array of each point in the route
Point[] points = new Point[routePath.Points.Length];
// Convert each point to a pixel location
for (int i = 0; i < routePath.Points.Length; i++)
{
points[i] = LatLongToPixel(routePath.Points[i], centrePoint, zoom);
}
// Define the pen colour and width
Pen pen = new Pen(Color.FromArgb(200, 255, 0, 0), 3);
// Draw the route
graphics.DrawLines(pen, points);
// Set the map image on the form
imgRoute.Image = mapImage;
}

// The Haversine formula gives the great circle distance between two points on the Earth
private double HaversineDistance(double lat1, double lon1, double lat2, double lon2)
{
// Convert all angular coordinates to Radians
lat1 = lat1 * Math.PI / 180;
lat2 = lat2 * Math.PI / 180;
lon1 = lon1 * Math.PI / 180;
lon2 = lon2 * Math.PI / 180;
double r = 6378137; // radius of the Earth (metres)
double dLat = (lat2 - lat1);
double dLon = (lon2 - lon1);
double a = Math.Sin(dLat / 2) * Math.Sin(dLat / 2) + Math.Cos(lat1) * Math.Cos(lat2) * Math.Sin(dLon / 2) * Math.Sin(dLon / 2);
double c = 2 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1 - a));
return r * c;
}

// Convert a latitude/longitude to a pixel on a map at a given zoom level
private Point LatLongToPixel(VERouteService.Location latlong, VEImageryService.Location centerPoint, int zoom)
{
// Calculate the pixel coordinates of the centre point of the map
double sinLatitudeCenter = Math.Sin(centerPoint.Latitude * Math.PI / 180);
double pixelXCenter = ((centerPoint.Longitude + 180) / 360) * 256 * Math.Pow(2, zoom);
double pixelYCenter = (0.5 - Math.Log((1 + sinLatitudeCenter) / (1 - sinLatitudeCenter)) / (4 * Math.PI)) * 256 * Math.Pow(2, zoom);
// Calculate the pixel coordinates of the location
double sinLatitude = Math.Sin(latlong.Latitude * Math.PI / 180);
double pixelX = ((latlong.Longitude + 180) / 360) * 256 * Math.Pow(2, zoom);
double pixelY = (0.5 - Math.Log((1 + sinLatitude) / (1 - sinLatitude)) / (4 * Math.PI)) * 256 * Math.Pow(2, zoom);
// Calculate the top left pixel coordinates of the map
double topLeftPixelX = pixelXCenter - (256 / 2);
double topLeftPixelY = pixelYCenter - (256 / 2);
// Calculate relative pixel coordinates of location
double x = pixelX - topLeftPixelX;
double y = pixelY - topLeftPixelY;
// Return a point at the relative location
return new Point((int)Math.Floor(x), (int)Math.Floor(y));
}

// Copy the string representation of the resulting SqlGeography instance to the clipboard
private void btnCopyToClipboard_Click(object sender, EventArgs e)
{
Clipboard.SetDataObject(txtResults.Text);
}

// Sign up for a VE account
private void lnkSignup_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
System.Diagnostics.Process.Start("https://mappoint-css.live.com/MwsSignup/");
}

}
}

Form1.Designer.cs

To add the design code, click on the small [+] icon next to Form1.cs in the Solution Explorer pane and double-click on Form1.Designer.cs. Enter the following code:

namespace VESQLRouteGeneratorApp
{
partial class Form1
{
private System.ComponentModel.IContainer components = null;

protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}

#region Windows Form Designer generated code

private void InitializeComponent()
{
this.imgRoute = new System.Windows.Forms.PictureBox();
this.lblStartLocation = new System.Windows.Forms.Label();
this.txtStartLocation = new System.Windows.Forms.TextBox();
this.lblEndLocation = new System.Windows.Forms.Label();
this.txtEndLocation = new System.Windows.Forms.TextBox();
this.btnCalculate = new System.Windows.Forms.Button();
this.txtResults = new System.Windows.Forms.TextBox();
this.progressBar1 = new System.Windows.Forms.ProgressBar();
this.txtAccountID = new System.Windows.Forms.TextBox();
this.txtPassword = new System.Windows.Forms.TextBox();
this.lblAccountID = new System.Windows.Forms.Label();
this.lblPassword = new System.Windows.Forms.Label();
this.lnkSignup = new System.Windows.Forms.LinkLabel();
this.grpCredentials = new System.Windows.Forms.GroupBox();
this.grpOptions = new System.Windows.Forms.GroupBox();
this.btnCopyToClipboard = new System.Windows.Forms.Button();
this.radShortest = new System.Windows.Forms.RadioButton();
this.radQuickest = new System.Windows.Forms.RadioButton();
this.grpMode = new System.Windows.Forms.GroupBox();
this.radDriving = new System.Windows.Forms.RadioButton();
this.radWalking = new System.Windows.Forms.RadioButton();
this.grpOptimise = new System.Windows.Forms.GroupBox();
((System.ComponentModel.ISupportInitialize)(this.imgRoute)).BeginInit();
this.grpCredentials.SuspendLayout();
this.grpOptions.SuspendLayout();
this.grpMode.SuspendLayout();
this.grpOptimise.SuspendLayout();
this.SuspendLayout();
//
// imgRoute
//
this.imgRoute.Location = new System.Drawing.Point(12, 109);
this.imgRoute.Name = "imgRoute";
this.imgRoute.Size = new System.Drawing.Size(256, 256);
this.imgRoute.TabIndex = 0;
this.imgRoute.TabStop = false;
//
// lblStartLocation
//
this.lblStartLocation.AutoSize = true;
this.lblStartLocation.Location = new System.Drawing.Point(9, 22);
this.lblStartLocation.Name = "lblStartLocation";
this.lblStartLocation.Size = new System.Drawing.Size(29, 13);
this.lblStartLocation.TabIndex = 2;
this.lblStartLocation.Text = "Start";
//
// txtStartLocation
//
this.txtStartLocation.Location = new System.Drawing.Point(44, 19);
this.txtStartLocation.Name = "txtStartLocation";
this.txtStartLocation.Size = new System.Drawing.Size(207, 20);
this.txtStartLocation.TabIndex = 3;
this.txtStartLocation.Text = "Los Angeles, USA";
//
// lblEndLocation
//
this.lblEndLocation.AutoSize = true;
this.lblEndLocation.Location = new System.Drawing.Point(12, 48);
this.lblEndLocation.Name = "lblEndLocation";
this.lblEndLocation.Size = new System.Drawing.Size(26, 13);
this.lblEndLocation.TabIndex = 4;
this.lblEndLocation.Text = "End";
//
// txtEndLocation
//
this.txtEndLocation.Location = new System.Drawing.Point(44, 45);
this.txtEndLocation.Name = "txtEndLocation";
this.txtEndLocation.Size = new System.Drawing.Size(207, 20);
this.txtEndLocation.TabIndex = 5;
this.txtEndLocation.Text = "San Jose, USA";
//
// btnCalculate
//
this.btnCalculate.Location = new System.Drawing.Point(655, 31);
this.btnCalculate.Name = "btnCalculate";
this.btnCalculate.Size = new System.Drawing.Size(86, 46);
this.btnCalculate.TabIndex = 6;
this.btnCalculate.Text = "Calculate";
this.btnCalculate.UseVisualStyleBackColor = true;
this.btnCalculate.Click += new System.EventHandler(this.btnCalculate_Click);
//
// txtResults
//
this.txtResults.Location = new System.Drawing.Point(274, 109);
this.txtResults.Multiline = true;
this.txtResults.Name = "txtResults";
this.txtResults.Size = new System.Drawing.Size(467, 256);
this.txtResults.TabIndex = 9;
//
// progressBar1
//
this.progressBar1.Location = new System.Drawing.Point(655, 93);
this.progressBar1.Name = "progressBar1";
this.progressBar1.Size = new System.Drawing.Size(86, 10);
this.progressBar1.Step = 20;
this.progressBar1.TabIndex = 10;
//
// txtAccountID
//
this.txtAccountID.Location = new System.Drawing.Point(94, 19);
this.txtAccountID.Name = "txtAccountID";
this.txtAccountID.Size = new System.Drawing.Size(80, 20);
this.txtAccountID.TabIndex = 13;
this.txtAccountID.Text = "123456";
//
// txtPassword
//
this.txtPassword.Location = new System.Drawing.Point(94, 45);
this.txtPassword.Name = "txtPassword";
this.txtPassword.Size = new System.Drawing.Size(80, 20);
this.txtPassword.TabIndex = 14;
this.txtPassword.Text = "P@55w0rd";
//
// lblAccountID
//
this.lblAccountID.AutoSize = true;
this.lblAccountID.Location = new System.Drawing.Point(27, 22);
this.lblAccountID.Name = "lblAccountID";
this.lblAccountID.Size = new System.Drawing.Size(61, 13);
this.lblAccountID.TabIndex = 15;
this.lblAccountID.Text = "Account ID";
//
// lblPassword
//
this.lblPassword.AutoSize = true;
this.lblPassword.Location = new System.Drawing.Point(35, 48);
this.lblPassword.Name = "lblPassword";
this.lblPassword.Size = new System.Drawing.Size(53, 13);
this.lblPassword.TabIndex = 16;
this.lblPassword.Text = "Password";
//
// lnkSignup
//
this.lnkSignup.AutoSize = true;
this.lnkSignup.Location = new System.Drawing.Point(129, 68);
this.lnkSignup.Name = "lnkSignup";
this.lnkSignup.Size = new System.Drawing.Size(45, 13);
this.lnkSignup.TabIndex = 17;
this.lnkSignup.TabStop = true;
this.lnkSignup.Text = "Sign Up";
this.lnkSignup.LinkClicked += new System.Windows.Forms.LinkLabelLinkClickedEventHandler(this.lnkSignup_LinkClicked);
//
// grpCredentials
//
this.grpCredentials.Controls.Add(this.txtAccountID);
this.grpCredentials.Controls.Add(this.lnkSignup);
this.grpCredentials.Controls.Add(this.txtPassword);
this.grpCredentials.Controls.Add(this.lblPassword);
this.grpCredentials.Controls.Add(this.lblAccountID);
this.grpCredentials.Location = new System.Drawing.Point(12, 12);
this.grpCredentials.Name = "grpCredentials";
this.grpCredentials.Size = new System.Drawing.Size(182, 91);
this.grpCredentials.TabIndex = 18;
this.grpCredentials.TabStop = false;
this.grpCredentials.Text = "VE Account Credentials";
//
// grpOptions
//
this.grpOptions.Controls.Add(this.txtEndLocation);
this.grpOptions.Controls.Add(this.lblStartLocation);
this.grpOptions.Controls.Add(this.txtStartLocation);
this.grpOptions.Controls.Add(this.lblEndLocation);
this.grpOptions.Location = new System.Drawing.Point(200, 12);
this.grpOptions.Name = "grpOptions";
this.grpOptions.Size = new System.Drawing.Size(259, 91);
this.grpOptions.TabIndex = 19;
this.grpOptions.TabStop = false;
this.grpOptions.Text = "Route Options";
//
// btnCopyToClipboard
//
this.btnCopyToClipboard.Location = new System.Drawing.Point(634, 372);
this.btnCopyToClipboard.Name = "btnCopyToClipboard";
this.btnCopyToClipboard.Size = new System.Drawing.Size(107, 23);
this.btnCopyToClipboard.TabIndex = 20;
this.btnCopyToClipboard.Text = "Copy to Clipboard";
this.btnCopyToClipboard.UseVisualStyleBackColor = true;
this.btnCopyToClipboard.Click += new System.EventHandler(this.btnCopyToClipboard_Click);
//
// radShortest
//
this.radShortest.AutoSize = true;
this.radShortest.Checked = true;
this.radShortest.Location = new System.Drawing.Point(6, 20);
this.radShortest.Name = "radShortest";
this.radShortest.Size = new System.Drawing.Size(64, 17);
this.radShortest.TabIndex = 21;
this.radShortest.TabStop = true;
this.radShortest.Text = "Shortest";
this.radShortest.UseVisualStyleBackColor = true;
//
// radQuickest
//
this.radQuickest.AutoSize = true;
this.radQuickest.Location = new System.Drawing.Point(6, 46);
this.radQuickest.Name = "radQuickest";
this.radQuickest.Size = new System.Drawing.Size(67, 17);
this.radQuickest.TabIndex = 22;
this.radQuickest.Text = "Quickest";
this.radQuickest.UseVisualStyleBackColor = true;
//
// grpMode
//
this.grpMode.Controls.Add(this.radDriving);
this.grpMode.Controls.Add(this.radWalking);
this.grpMode.Location = new System.Drawing.Point(466, 12);
this.grpMode.Name = "grpMode";
this.grpMode.Size = new System.Drawing.Size(84, 91);
this.grpMode.TabIndex = 23;
this.grpMode.TabStop = false;
this.grpMode.Text = "Mode";
//
// radDriving
//
this.radDriving.AutoSize = true;
this.radDriving.Checked = true;
this.radDriving.Location = new System.Drawing.Point(6, 46);
this.radDriving.Name = "radDriving";
this.radDriving.Size = new System.Drawing.Size(58, 17);
this.radDriving.TabIndex = 25;
this.radDriving.TabStop = true;
this.radDriving.Text = "Driving";
this.radDriving.UseVisualStyleBackColor = true;
//
// radWalking
//
this.radWalking.AutoSize = true;
this.radWalking.Location = new System.Drawing.Point(6, 20);
this.radWalking.Name = "radWalking";
this.radWalking.Size = new System.Drawing.Size(64, 17);
this.radWalking.TabIndex = 24;
this.radWalking.Text = "Walking";
this.radWalking.UseVisualStyleBackColor = true;
//
// grpOptimise
//
this.grpOptimise.Controls.Add(this.radShortest);
this.grpOptimise.Controls.Add(this.radQuickest);
this.grpOptimise.Location = new System.Drawing.Point(556, 12);
this.grpOptimise.Name = "grpOptimise";
this.grpOptimise.Size = new System.Drawing.Size(89, 91);
this.grpOptimise.TabIndex = 24;
this.grpOptimise.TabStop = false;
this.grpOptimise.Text = "Optimise";
//
// Form1
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(753, 432);
this.Controls.Add(this.grpOptimise);
this.Controls.Add(this.grpMode);
this.Controls.Add(this.btnCopyToClipboard);
this.Controls.Add(this.grpOptions);
this.Controls.Add(this.grpCredentials);
this.Controls.Add(this.progressBar1);
this.Controls.Add(this.txtResults);
this.Controls.Add(this.btnCalculate);
this.Controls.Add(this.imgRoute);
this.Name = "Form1";
this.Text = "Virtual Earth Route Generator for SQL Server 2008";
((System.ComponentModel.ISupportInitialize)(this.imgRoute)).EndInit();
this.grpCredentials.ResumeLayout(false);
this.grpCredentials.PerformLayout();
this.grpOptions.ResumeLayout(false);
this.grpOptions.PerformLayout();
this.grpMode.ResumeLayout(false);
this.grpMode.PerformLayout();
this.grpOptimise.ResumeLayout(false);
this.grpOptimise.PerformLayout();
this.ResumeLayout(false);
this.PerformLayout();

}

#endregion

private System.Windows.Forms.PictureBox imgRoute;
private System.Windows.Forms.Label lblStartLocation;
private System.Windows.Forms.TextBox txtStartLocation;
private System.Windows.Forms.Label lblEndLocation;
private System.Windows.Forms.TextBox txtEndLocation;
private System.Windows.Forms.Button btnCalculate;
private System.Windows.Forms.TextBox txtResults;
private System.Windows.Forms.ProgressBar progressBar1;
private System.Windows.Forms.TextBox txtAccountID;
private System.Windows.Forms.TextBox txtPassword;
private System.Windows.Forms.Label lblAccountID;
private System.Windows.Forms.Label lblPassword;
private System.Windows.Forms.LinkLabel lnkSignup;
private System.Windows.Forms.GroupBox grpCredentials;
private System.Windows.Forms.GroupBox grpOptions;
private System.Windows.Forms.Button btnCopyToClipboard;
private System.Windows.Forms.RadioButton radQuickest;
private System.Windows.Forms.RadioButton radShortest;
private System.Windows.Forms.GroupBox grpMode;
private System.Windows.Forms.RadioButton radDriving;
private System.Windows.Forms.RadioButton radWalking;
private System.Windows.Forms.GroupBox grpOptimise;
}
}

Using the application

Once you've copied the code above, compile the application by selecting Build -> Build Solution. Load the compiled VESQLRouteGeneratorApp.exe (in the bin/Debug directory) and you should see the screen at the top of this page.
To create a route, simply type in your VE account details, a start and end address, select the appropriate route options, and click Calculate.

Note: The geocode service works more effectively if you include country name after an address

When the result is generated, you can click on the Copy to Clipboard button and then insert the route into SQL Server using the STLineFromText() method, as follows:

SELECT geography::STLineFromText('PASTE OUTPUT IN HERE', 4326);

You can then use this route in all of your SQL Server functions - measuring the length of the route, finding locations with 5 miles, finding if it crosses a boundary or what state it is contained in etc.

What next

Hopefully this article has given you some more ideas about how SQL Server 2008 + Virtual Earth can be used together to create cool spatial applications. The application described in this article is only intended as an example and there is much to be improved:

  1. Error checking is not implemented - if one of the addresses cannot be geocoded then the application will fail.
  2. This example connects to the staging environment of the VE Web Services. This is for testing only, and cannot be used in production environments.
  3. Currently, the application only makes very limited use of the capabilities of the Microsoft.SqlServer.Types.dll library - to construct a geometry from an array of points and then return the WKT representation of that geometry. In practice, if you wanted to insert the result into a SQL Server 2008 database it would make more sense to work directly with the SqlGeometry instance rather than parsing it as text.
Copyright 2009. Sponsored by nsquared.   |  Terms Of Use  |  Privacy Statement
Content on this site is generated from the developer community and shared freely for your enjoyment and benefit. This site is run independently of Microsoft and does not express Microsoft's views in any way.