September 2010

Volume 25 Number 09

SQL Server and MapPoint - Making MapPoint 2010 and SQL Server Spatial Work Together

By [Eric Frost | September 2010

After Bing Maps, two of the most visible geospatial technologies from Microsoft are Microsoft MapPoint 2010 and the spatial functionality in SQL Server 2008 R2. However, even though SQL Server is an ideal store for geospatial data, and MapPoint makes a good geospatial viewer, communicating between the two is not nearly as straightforward as it could be.

This article will demonstrate how to read point and polygon objects from SQL Server and render them in MapPoint. We’ll also demonstrate how to write points and polygons back to SQL Server using the Entity Framework 4.0 included with Visual Studio 2010.

For purposes of illustration, we’ll be using the “Al’s Beef” Chicago-based company’s restaurant locations and hypothetical trade areas. In retail analysis and modeling, trade areas can be defined using various parameters and can be used for different aims. Typically, they’re defined as the smallest region around a store that contains areas that meet a specific threshold—for example, where 50 percent or 75 percent of customers live or work. All of the trade areas used in this article were generated using the MapPoint Create Drivetime Zone feature, so they represent a hypothetical trade area based on driving times.

As a chain with fewer than a couple dozen locations, Al’s Beef is a relatively small business, but the same concepts and techniques can be applied to large retailers with thousands of locations and in other industries and applications.

Both the sample code and the “Al’s Beef” dataset (as a SQL script) are available for download at

While this isn’t an overly technical article covering arcane aspects of the latest language or technology, it serves as a practical how-to for a useful marriage of common Microsoft technologies. A couple of hurdles include the inability of the Entity Framework to directly understand geography objects, and the SQL Server Spatial requirement for polygons to be counterclockwise, which isn’t required by MapPoint. Hopefully, this article will help even seasoned developers who may be reluctant to jump into the geospatial arena for lack of prior experience, and at the same time show MapPoint developers how to successfully leverage SQL Server 2008 R2.

Setting up the Database

In order to follow along with the code samples included here, download the SQL script and run it against SQL Server to set up the database and objects. The data is stored in a SQL Server database called “Corporate” and includes one table, one view and one stored procedure. Al’s Beef locations are stored in a table called “Locations” (see Figure 1).

image: The Table and View Included in the Sample Database

Figure 1 The Table and View Included in the Sample Database

This includes the stores’ addresses, attributes (for example, is it a drive-in?) and a location point geography datatype. The hypothetical trade area polygons are also stored in the “Locations” table, in a field called TradeArea using the geography datatype.

The vLocations view exposes the point and polygon geography fields into datatypes that can be understood and read into the Entity Framework.

The point geography field is decomposed into latitude and longitude fields and passed back to the client as a varbinary field. This is because the Entity Framework can’t deal with geography datatypes directly, but it can handle varbinary fields. The application can later convert these back into geography objects.

Here’s the stored procedure uspAddLocation, which, as the name suggests, is used to insert new locations from MapPoint back into SQL Server:

CREATE VIEW [dbo].[vLocations]
SELECT LocID,Location.Long As Longitude,
       Location.Lat As Latitude,
       CAST(Location AS VARBINARY(MAX)) AS Location,
       Locations.TradeArea.STAsText() As TradeAreaWKT,
       CAST(TradeArea AS VARBINARY(MAX)) AS TradeArea
FROM Locations

We’ll come back to this later.

Setting up the Application

Our project is a C# Windows Form application that incorporates the MapPoint Control. The control is included with MapPoint 2010, and the full version of MapPoint 2010 must be installed for it to be available. Records can be navigated using buttons to walk through the records and to display the store and its trade area. Stores can also be selected by clicking on the store’s pushpin icon. The form also has a checkbox to display the trade area as a convex hull and a button to add new locations. By default, the application shows the polygon as it is stored in the database (see Figure 2).

image: The Al’s Beef App, Showing the Chicago Heights Store and Territory as Defined in the Database

Figure 2 The Al’s Beef App, Showing the Chicago Heights Store and Territory as Defined in the Database

If the View Trade Area As Convex Hull checkbox is set, a line (the convex hull) is wrapped around the trade area similar to wrapping a rubber band around the polygon (see Figure 3).

image: The Chicago Heights Store, with a Convex Hull Wrapped Around the Territory Shown in Figure 2

Figure 3 The Chicago Heights Store, with a Convex Hull Wrapped Around the Territory Shown in Figure 2

Before we can implement the map display, we need to add Entity Data Objects that point to the database table and view. To establish the Entity Data Objects, right-click on the application in Visual Studio Solution Explorer and go to Add | New Item | Visual C# Items | ADO.NET Entity Data Model. Click Add and choose Generate from Database. In the Choose Your Database Objects dialog, select the table Locations and the view vLocations. After you click Finish, the wizard will create the objects and generate the code necessary to connect to the database.

To add the MapPoint 2010 Control to the Windows Form, it’s necessary to first add the MapPoint COM control component to the Visual Studio toolbox. COM isn’t the most fashionable technology, but it continues to be an important part of the Windows ecosystem. Many applications, including MapPoint, only implement an API through a COM interface, and COM support from within Visual Studio isn’t going away anytime soon.

Open the Visual Studio toolbox and in the general section, right-click and select Choose Items. Go to the COM Components tab and select Microsoft MapPoint Control 17.0. The MapPoint Control 17.0 refers to MapPoint 2010 (North America or Europe). Older versions of MapPoint (2002 onward) can also be used, but minor name changes (for example, the toolbar name and symbol identifier) will be required.

On both AxInterop.MapPoint and Interop.MapPoint assemblies, set the Embed Interop Type property to False and the Copy Local to True.The MapPoint Control can now be dragged onto the form and used within the application.

Initializing the Map Form: Loading MapPoint

The Map form declares several member variables, including the database connection to the Entity Framework, a list of the store information and a parallel list of the stores’ geographical information that will be read from the view. The variable curLoc keeps track of the current store ID within the application, and objMap is used to reference the MapPoint control’s map object, as shown here:

namespace AlsBeef
 public partial class Map : Form
  CorporateEntities db;
  List<Location> locationList;
  List<vLocation> vlocationList;
  int curLoc = -1;    // <0 indicates 'not set'
  MapPoint.Map objMap;
  MapPoint.Symbol objSymb;

When the form is created, the method CreateNewMapObject is called to initialize the map control and open a new map using the default North America map template. The toolbars are set, objMap is defined and Point of Interest is turned off so as not to clutter the map (see Figure 4). “Points of Interest” are MapPoint predefined places, restaurants and theaters, for example.

Figure 4 Creating a Form

public Map()
private void CreateNewMapObject()
  object barObj = "advanced";
  MPctrl.Toolbars.get_Item(refbarObj).Visible = true;
  MPctrl.Toolbars.LargeToolbarButtons = false;
  objMap = MPctrl.ActiveMap;
  // Make sure all points of interest are turned off
  objMap.PlaceCategories.Visible = MapPoint.GeoTriState.geoFalse;

The form’s Load method populates both lists of store information. The locationList contains all the regular non-geographic information, and vloc­ationList reads the geographic fields as transformed by the database view:

private void Map_Load(object sender, EventArgs e)
 db = new CorporateEntities();
 locationList = new List<Location>();
 vlocationList = new List<vLocation>();
 ObjectQuery<Location> locationQuery =
 ObjectQuery<vLocation> vlocationQuery =
 locationList = locationQuery.ToList();
 vlocationList = vlocationQuery.ToList();

The last two lines effectively start the application by initializing the map. They add a pushpin for each store location (InitMapPins), and position the map and form controls to point to the data for the first store location (SetLocation).

Adding Pushpins to the Map

Things become more interesting in the InitMapPins method:

private void InitMapPins()
    MapPoint.Pushpin objPin = null;
    for (int i = 0; i < locationList.Count;
     MapPoint.Location objLoc =
     objPin = objMap.AddPushpin(objLoc,   
     objPin.Symbol = 145; // Red fork and knife
                         // (food, restaurant)

Looping over the locationList, we retrieve the latitude and longitude values that were calculated and exposed by the view. These are used to create MapPoint Location objects, which are then used to create map pushpins (MapPoint Pushpin objects). The store name is used for the PushpinName property, which will be later used to search and position the map. The pushpins are depicted with the MapPoint built-in red restaurant pushpin symbol (symbol #145). A complete list of MapPoint 2010 built-in symbols can be found at This page also has links to pushpin listings for earlier versions of MapPoint.

Positioning to Current Record and Adding Polygons to the Map

New records are selected and displayed using the IncDecLocation and SetLocation methods. IncDecLocation simply applies an increment (cnt) to the current position (curLoc) and passes this new record position to SetLocation:

private void IncDecLocation(int cnt = 0)
 // Apply the increment/decrement, wrapping around if necessary
 int newLoc = (curLoc + cnt + locationList.Count) % locationList.Count;

The SetLocation routine is the workhorse of the application. SetLocation selects a new record position and displays it on the map. SetLocation also removes the highlight from the previous pushpin (if any) and clears all previous trade area polygons from the map (see Figure 5).

Figure 5 TheSetLocation Routine Is the App Workhorse

private void SetLocation(int newLoc)
  MapPoint.Pushpin objPin = null;
  // Unhighlight previous pushpin
  If (curLoc>= 0)
    objPin = (MapPoint.Pushpin)
    objPin.Highlight = false;
  // Clear all previous shapes
  while(objMap.Shapes.Count> 0)
  // Set the new location
  curLoc = Math.Min( Math.Max(newLoc,0), locationList.Count-1);
  objPin = (MapPoint.Pushpin)
  objMap.Location = objPin.Location;

The next section becomes a little tricky. First, the application checks the status of the View Trade Area As Convex Hull checkbox. If it hasn’t been set, it takes the Well-Known Text (WKT) string that defines the polygon and passes it to the custom RenderPolygon method to be parsed and rendered as a polygon on the map.

If the checkbox has been set, it pulls the territory polygon’s varbinary object and converts it to a geography object using the System.IO.MemoryStream class and BinaryReader method. STConvexHull is one of the methods included with SQL Server 2008; it allows you to modify instances of geography or geometry data. STConvexHull, in particular, only works with geometry datatypes. The differences between SQL Server’s geometry and geography datatypes is covered extensively elsewhere, but for now consider that geometry data is defined in a flat (2D Euclidean) Cartesian plane, whereas geography data is projected onto the spheroidal Earth surface using a spherical coordinate system (datum, projection, prime meridian and unit of measurement).

The trade area is stored in the database with a geography field type and is then cast into a varbinary by the view. This needs to be read into a geography object, which can then be converted into a geometry object in order to run the STConvexHull method.

Because of the small areas being covered, calculations performed by STConvexHull on the (planar) geometry object are practically the same as would have resulted had the convex hull been calculated for the actual spheroidal geography object.

In the next part of SetLocation, the original trade area is drawn as a thin black line and the convex hull is rendered as a thicker red line. The code is shown in Figure 6.

Figure 6 Drawing the Original Trade Area and the Convex Hull

 // Draw trade area
if (checkBox1.Checked == false)
  // Need to add C:\Program Files\Microsoft SQL
  // Server\100\SDK\Assemblies\Microsoft.SqlServer.Types.dl
  // to references
  SqlGeographyTradeAreaGeog = new SqlGeography();
  using (var stream = new
   using (var rdr = new System.IO.BinaryReader(stream))
  SqlGeometry TAConvexHullGeom = new SqlGeometry();
  TAConvexHullGeom =  
   SqlGeometry.STPolyFromText(TradeAreaGeog.STAsText(), 4326);
  TAConvexHullGeom = TAConvexHullGeom.STConvexHull();
  RenderPolygon(TradeAreaGeom.ToString(), 3355443, 0); // Gray80

So what does this WKT string look like and what does RenderPolygon do with it? You’ve already seen the results (in Figures 2and3). Let’s dive into the internals.

WKT is an Open Geospatial Consortium (OGC) standard format for formatting geospatial vector data in the form of text. A WKT polygon string looks like this (much abbreviated):

POLYGON ((-111.918823979795 33.6180476378649, -111.91810682416 33.6096635553986, -111.911686453968 33.6078672297299, -111.907403888181 33.599476357922, -111.907403888181 33.6060674674809, -111.903121406212 33.6060674674809))

The word “POLYGON” precedes a list of coordinates that are surrounded by two sets of parentheses. Individual coordinate pairs are separated by commas. We use the MapPoint AddPolyLine method to draw the polygon on the map and to add it to the MapPoint Shapes collection. This takes an array of MapPoint Location objects as a parameter. Converting the WKT string to an array of Location objects requires half a dozen lines of code. RenderPolygon performs this by stripping the “POLYGON” prefix and parentheses before splitting the string into coordinates using the comma separator. Individual coordinates are then parsed into pairs of doubles (longitude, latitude) that are used to create MapPoint Location objects. The resulting array of Location objects is then passed to AddPolyline to create the new polygon.

RenderPolygon takes additional parameters for the color and line thickness (see Figure 7).

Figure 7 TheRenderPolygon Method

private void RenderPolygon(string polystring, 
 int forecolor = 128, int weight = 3)
  polystring = polystring.Replace("POLYGON ((", "");
  polystring = polystring.Replace("))", "");
  string[] stringList = polystring.Split(',');
  MapPoint.Location[] objLoc = 
   new MapPoint.Location[stringList.Count()];
  for (int i = 0; i <stringList.Count(); i++)
   string[] coords = stringList[i].Trim().Split(' ');
   objLoc[i] = objMap.GetLocation(Convert.ToDouble(coords[1]),  
    Convert.ToDouble(coords[0]), 0);
  MapPoint.Shape objShape;
  objShape = objMap.Shapes.AddPolyline(objLoc);
  objShape.Line.ForeColor = forecolor;
  objShape.Line.Weight = weight;

A more complete RenderPolygon could take additional parameters for whether or not the shape is filled in, the fill color, a shape name (an internal string that can be assigned to shapes) and the zOrder (to position the shape in front of or behind roads and other shapes).

Drawing and annotation can be placed on a MapPoint map by both the user and a program. MapPoint supports a total of 40 different colors for this annotation. Although the programming interface appears to support the standard 3-byte RGB (16,777,216 different) colors, in reality these numbers merely provide a useful way to specify the color to use. The 40 colors supported by MapPoint can be seen at

Historically, this limitation helped with image update efficiency, but today it primarily aids map clarity by helping to ensure colors are different.

We now come to the final part of SetLocation (see Figure 8).

Figure 8 The Final Part of SetLocation

   // Reset zoom level
   objMap.Altitude = 30;
   objPin.Highlight = true;
   Double distance;
   distance = 
     NearestLocation(curLoc) * 0.000621371192; //convert to miles
   label1.Text = "ID: " + locationList[curLoc].LocID.ToString();
   label2.Text = locationList[curLoc].Name + " - " +  
   label3.Text = locationList[curLoc].Address + ", " + 
     locationList[curLoc].City + ", " + locationList[curLoc].State;
   label4.Text = "Distance to next closest store: " + 
     String.Format("{0:#,0.0}", distance) + " miles";
private double NearestLocation(int curLoc)
 SqlGeography AllLocations = new SqlGeography();
  SqlGeography CurLocation = new SqlGeography();
  for (int i = 0; i <locationList.Count; i++)
    SqlGeography TempLocation = new SqlGeography();
    using (var stream = new 
     using (var rdr = new System.IO.BinaryReader(stream))
   if (i == curLoc)
     CurLocation = TempLocation;
     AllLocations = AllLocations.STUnion(TempLocation);
  return (Double)AllLocations.STDistance(CurLocation); //meters

This highlights the new pushpin, sets the zoom level (using the Map object Altitude property), reports the store information (from the locationList array) and finds the distance to the nearest store location.

This distance is calculated by NearestLocation. This loops through the locations and uses the SQL Server Spatial STUnion method to combine the Location geography points into a MultiPoint geography instance. The exception is the current store location, which is skipped—otherwise the distance would always be zero miles! The application then uses the STDistance method to calculate the distance in meters between the current store location and the MultiPoint geography instance. STDistance reports the distance to a MultiPoint as the shortest distance to any component point within the MultiPoint.

The button to add a new site location removes any trade-area polygons from the map and then simply changes the mouse pointer to a crosshair:

private void button1_Click(object sender, EventArgs e)
  // Clear all previous shapes
  while(objMap.Shapes.Count > 0)
  MPctrl.MousePointer = MapPoint.GeoPointer.geoPointerCrosshair;

In order to deal with MapPoint events, the form requires an event handler defined in the form designer. The events can be added using the form designer or added manually to Map.Designer.cs. Handlers are added to two of the MapPoint events: SelectionChange and BeforeClick, as shown in Figure 9.

Figure 9 Adding Handlers to MapPoint Events

// MPctrl
this.MPctrl.Enabled = true;
this.MPctrl.Location = new System.Drawing.Point(13, 13);
this.MPctrl.Name = "MPctrl";
this.MPctrl.OcxState = 
this.MPctrl.Size = new System.Drawing.Size(674, 389);
this.MPctrl.TabIndex = 0;
this.MPctrl.SelectionChange += 
  new AxMapPoint._IMappointCtrlEvents_SelectionChangeEventHandler
this.MPctrl.BeforeClick += 
  new AxMapPoint._IMappointCtrlEvents_BeforeClickEventHandler

The SelectionChange event is used to detect if the user has selected a pushpin. This is then used to move the current record to this pushpin’s record. Figure 10 shows the event handler’s implementation.

Figure 10 Implementing a SelectionChange Event Handler

private void MPctrl_SelectionChange(object sender,  
 AxMapPoint._IMappointCtrlEvents_SelectionChangeEvent e)
  // Has the user just selected a pushpin?
  if (e.pNewSelection is MapPoint.Pushpin)
    MapPoint.Pushpin ppin = e.pNewSelection as MapPoint.Pushpin;
    // Find the corresponding location object, and select it
    for (int iloc = 0; iloc < locationList.Count; iloc++)
      if (locationList[iloc].Name == ppin.Name)
      { // Found it: select, and move to it

This checks that the newly selected object is, indeed, a pushpin. Then we perform a simple search through the local locationList for a matching record. MapPoint pushpins can have duplicate names, so this code assumes that all location records (and hence pushpins) have unique names. You should also compare geographic coordinates for situations where this can’t be relied upon.

The map’s BeforeClick event handler is used in the “add new store location” functionality. The handler checks to see if the mouse pointer is a crosshair—that is, the user is trying to insert a new site location. It lets MapPoint handle the click event if the pointer isn’t a crosshair. If the mouse pointer is a crosshair, the program traps the click action and adds a new pushpin at the mouse pointer location using the red restaurant symbol. At this point, to simplify things, rather than have the user draw the trade area, the program uses the MapPoint AddDrivetimeZone method to generate a hypothetical (travel-time-based) trade area around the new site.

In the interest of getting this shape into SQL Server, the shape is first decomposed into vertices, which are then converted into a polygon WKT (text) definition. This will then be written to SQL Server.

To pass the point and polygon back into SQL Server and update the geography columns, we can’t use the normal Entity Framework-supported stored procedures, because the geography datatype isn’t supported. However, because execution of arbitrary stored procedures is now supported by Entity Framework 4.0, we are able to import the stored procedure and execute it like a normal function.

This code sets up the parameters and then executes the usp­AddLocation stored procedure:

object[] parameters =
  new SqlParameter("Latitude",objLoc.Latitude),
  new SqlParameter("Longitude",objLoc.Longitude),
  new SqlParameter("PolyWKT",PolyWKT)
var retValue = db.uspAddLocation(objLoc.Longitude, 
 objLoc.Latitude, PolyWKT);

Last, this routine resets the map (CreateNewMapObject), requeries the list of locations from the database (InitMapPins) and selects the new store as the current record ( SetLocation ):

// Re-query and re-initialize map
 ObjectQuery<Location> locationQuery = db.Locations;
 ObjectQuery<vLocation> vlocationQuery = db.vLocations;
 locationList = locationQuery.ToList();
 vlocationList = vlocationQuery.ToList();
 objMap.Saved = true;
 SetLocation( locationList.Count – 1 );
 e.cancel = true;

The line e.cancel=true; prevents MapPoint from further processing the click event. The stored procedure uspAddLocation follows (see Figure 11).

Figure 11 The uspAddLocation Stored Procedure

CREATE PROCEDURE [dbo].[uspAddLocation]
@Longitude FLOAT,
@Latitude FLOAT,
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
DECLARE @NewLocID int = 0
SELECT @NewLocID = MAX(LocID)+1 FROM Locations
DECLARE @NewPoly geography
SET @NewPoly = geography::STPolyFromText(@PolyWKT,4326)
INSERT INTO Locations(LocID,Name,Address,City,State,Format,Location,TradeArea)
VALUES(@NewLocID, 'New Location ' + CAST(@NewLocID As varchar(3)), '123 Main',   
 'Anywhere', 'ST', 'Food', geography::Point(@Latitude,@Longitude,4326), 

You can see a new geography instance and variable being created for the polygon before the INSERT statement, whereas the Point location is created via the Point method inside the INSERT method. Either approach is valid.

The event-handling code in Figure 12 handles the “previous” and “next” buttons, the convex hull checkbox and form closing—this completes the application.

Figure 12 Completing the App

private void prev_Click(object sender, EventArgs e)
private void next_Click(object sender, EventArgs e)
private void checkBox1_CheckedChanged(object sender, EventArgs e)
private void Map_FormClosing(object sender, FormClosingEventArgs e)
  MPctrl.ActiveMap.Saved = true;

Viewing and Visually Editing SQL Server Geospatial Data

This article covered a lot of ground, but it demonstrates a complete end-to-end application with SQL Server, showing how to pass data in and out using both SQL Server Spatial and MapPoint methods to display and edit real-world information.

The principles shown here can be taken further. Keeping a local copy of the data ensures fast map updates but wouldn’t be practical for huge datasets. For these situations, the data should be fetched on a record-by-record basis, ideally with a caching mechanism.

MapPoint is capable of some useful geospatial operations (for example, the drive-time zone calculation), but it lacks many of the geospatial operations expected of a full GIS. Here we leverage two such operations, STConvexHull and STDistance, from the SQL Server Spatial extensions. Other advanced functions available in the spatial extensions include the ability to measure the length and extent of geographic features, as well as finding unions and intersections of polygons. These functions could be used to create a sophisticated territory-management application. This could combine territories or find overlaps where one store is cannibalizing the business of another.

Similarly, MapPoint’s strengths could be leveraged. MapPoint is capable of offline geocoding. Our example uses existing coor­dinates, but the MapPoint geocoder could be used to locate street addresses instead. MapPoint also ships with a number of demographic databases at the county, ZIP code and census tract levels. This data could be plotted on a store map, allowing easy comparisons to be made—for example, how store sales compare to local populations and income levels.

Looking ahead, SQL Server Spatial is likely to have a generational leap forward with the next version of SQL Server, and the MapPoint product has been enjoying a renaissance of new development and features with the last two versions, and this is set to continue. Furthermore, the Entity Framework is likely to continue to add support for new field types, including spatial datatypes, which should improve communication between SQL Server and MapPoint. Taken together, these technologies form a robust and powerful evolving platform for mapping application development.

Eric Frost is a Microsoft MVP and business application developer specializing in GIS/mapping applications. He manages the active Microsoft mapping technology forum and can be reached at

Richard Marsden is a Microsoft MVP and freelance software developer. He sells a number of MapPoint extensions at and operates the GeoWeb Guru Web site at

Thanks to the following technical experts for reviewing this article: Bob Beauchemin*,* Ed Katibah and Amar Nityananda

About the Author