Entity Framework 5 & Bing Maps WPF

Many developers have been asking since the release of SQL 2008 for support of Spatial data types in the Entity Framework. In May of this year the release candidate for Entity Framework 5 (EF5) was announced.This version has increased performance when compared to earlier EF version and also has support for spatial types. The Spatial functionality in EF5 requires .NET 4.5. This means you will need Visual Studios 2012 installed. You can download the release candidate for VS 2012 here: http://www.microsoft.com/visualstudio/en-us

In this blog post I’ll show how to setup a basic WPF application that uses the Spatial types in EF5 with Bing Maps to load in spatial data from a SQL database that contains spatial data. To make this simple we will have a couple of buttons that, when pressed, will search for spatial data that is within 100 km of the center of the map. More complex spatial queries can be created if needed but we will keep things simple in this post.

Setting up the Visual Studio’s Project

To start off we will create a WPF project in Visual Studios called BingMapsEF_WPF. Once this is loaded we will use NuGet to load in EF5 into our project. To do this you will need to go to Tools –> Library Package Manager –> Package Manager Console.

NuGetMenu

This will open up a console panel. You will need to run this command: Install-Package EntityFramework –Pre Doing so should result in the Entity Framework being installed into your application.

image

Create the Spatial Data Model

First we will need a set of spatial data to work with. I have put together a backup of a simple database of spatial data (download – created using SQL 2012) which you can restore to your database. The name of the database is SpatialSample. Once you have restored the database you can create the entity model. To do this add a new ADO.NET Entity Data model to the project called SpatialDataModel.edmx.

image

On the next screen we will select Generate from database then press next. one the next screen you can connect to the sample database. Name the entities SpatialSampleEntities then press next.

image

On the next screen you will need to select the tables from the database that you want to add to the model. Select both the Cities and the Countries tables. Select he model namespace to SpatialSampleModel then press Finish.

image

Once the model is generated you should see the designer that shows the table layout. The database consists of two simple tables. The City table has a SQLGeography column called Location which contains the coordinates for a city. The Country table has a SQLGeography column called Boundary which contains the polygon data for the country boundaries.

image

Adding the Map

Adding a map to the WPF control is pretty straight forward. You first need to add a reference to the WPF Map control library (Microsoft.Maps.MapControl.WPF.dll) which is usually located in the C:\Program Files (x86)\Bing Maps WPF Control\V1\Libraries directory. While you are at it you might as well add a reference to the SQL Spatial Library (Microsoft.SqlServer.Types) located in the C:\Program Files (x86)\Microsoft SQL Server\110\Shared directory. This will make it easier for use to parse the geography data later, especially if you have already written code in the past that worked with the spatial types from this library.

Now that you have a reference to the map control in your project we can add a map in the xaml of the MainWindow.xaml file. While we are at it we will also add two simple buttons for doing nearby searches for our data and a third button to clear the map. Your xaml should look like this:

<Window x:Class="BingMapsEF_WPF.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:m="clr-namespace:Microsoft.Maps.MapControl.WPF;assembly=Microsoft.Maps.MapControl.WPF"
        Title="MainWindow" Height="350" Width="525">
    <Grid>
        <m:Map Name="MyMap" CredentialsProvider="YOUR_BING_MAPS_KEY">
            <m:Map.Children>
                <m:MapLayer Name="ShapeLayer"/>
            </m:Map.Children>
        </m:Map>

        <StackPanel HorizontalAlignment="Right">
            <Button Content="Find Nearby Cities" Click="CitySearch_Clicked"/>
            <Button Content="Find Nearby Countries" Click="CountrySearch_Clicked"/>
            <Button Content="Clear Map" Click="ClearMap_Clicked"/>
        </StackPanel>
    </Grid>
</Window>

If you build the project you should see a map with two buttons on it like this:

image

Adding in the Spatial Queries

Now that we have a map and a entity data model we just need to add the query logic to retrieve the cities and countries that are within 100 km’s of the center of the map. To perform a nearby search against our entities we first need to create a DbGeography object that represents the center of the map. This can easily be done by creating the Well Known Text for a point with the coordinates for the center of the map. The code for this looks like this:

DbGeography center = DbGeography.PointFromText("POINT(" + MyMap.Center.Longitude + " " + MyMap.Center.Latitude + ")", 4326);

Note the 4326 value is the spatial reference identifier (SRID) for the spatial data. This defines the project system that the spatial data belongs to. 4326 is the SRID used to represent the sphere for the WGS84 projection system which is used by most online mapping API’s and by GPS’s.

We can now use LINQ to create a simple nearby query. Essentially we want to find all locations that have a distance from the center of the map that is less than 100 km’s. Here is an example of what this type of query looks like.

using (SpatialSampleEntities e = new SpatialSampleEntities())
{
    //Search for all cities that are within 100KM of the center of the map
    var cities = (from c in e.Cities
                    where center.Distance(c.Location) <= SearchRadiusMeters
                    select c).ToList();

    //Logic to add the cities to the map as pushpins...
}

Once a query is done we just need to loop through each result and create a Bing Maps Shape equivalent to the DbGeography being returned in the query. To make things easier I’ll also use the SQL Spatial Types library to convert the DbGeography objects to SqlGeography objects. SqlGeography objects have more methods that are useful for parsing the spatial object. To keep things simple we will only add logic to support simple polygons. Polygons with holes are not currently supported in the WPF control and adding support for those is a whole other blog post for another day.

Putting all this together we end up with the complete source code for the MainWindow.xaml.cs file.

using System.Data.Spatial;
using System.Linq;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Media;
using Microsoft.Maps.MapControl.WPF;
using Microsoft.SqlServer.Types;

namespace BingMapsEF_WPF
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        #region Private Properties

        private double SearchRadiusMeters = 100000; //100KM

        #endregion

        #region Constructor

        public MainWindow()
        {
            InitializeComponent();
        }

        #endregion

        #region Private Methods

        private void CitySearch_Clicked(object sender, RoutedEventArgs args)
        {
            ShapeLayer.Children.Clear();

            //Create a DbGeography object that represents the center of the map.
            DbGeography center = DbGeography.PointFromText("POINT(" + MyMap.Center.Longitude + " " + MyMap.Center.Latitude + ")", 4326);

            using (SpatialSampleEntities e = new SpatialSampleEntities())
            {
                //Search for all cities that are within 100KM of the center of the map
                var cities = (from c in e.Cities
                                where center.Distance(c.Location) <= SearchRadiusMeters
                                select c).ToList();

                //Add the cities to the map as pushpins.
                foreach (City c in cities)
                {
                    if (c.Location.Latitude.HasValue && c.Location.Longitude.HasValue)
                    {
                        Pushpin pin = new Pushpin()
                        {
                            Location = new Location(c.Location.Latitude.Value, c.Location.Longitude.Value)
                        };

                        //Add tooltip with name of city
                        ToolTipService.SetToolTip(pin, c.CITY_NAME);

                        //Add pushpin to layer
                        ShapeLayer.Children.Add(pin);
                    }
                }
            }
        }

        private void CountrySearch_Clicked(object sender, RoutedEventArgs args)
        {
            ShapeLayer.Children.Clear();

            //Create a DbGeography object that represents the center of the map.
            DbGeography center = DbGeography.PointFromText("POINT(" + MyMap.Center.Longitude + " " + MyMap.Center.Latitude + ")", 4326);

            using (SpatialSampleEntities e = new SpatialSampleEntities())
            {
                //Search for all countries that are within 100KM of the center of the map
                var countries = (from c in e.Countries
                                 where center.Distance(c.Boundary) <= SearchRadiusMeters
                                 select c).ToList();

                //Add the countries to the map
                foreach (Country c in countries)
                {
                    //Convert the DbGeography object to an SqlGeography object to make it easier to parse and convert into Bing Maps shapes.
                    SqlGeography g = SqlGeography.STGeomFromWKB(new System.Data.SqlTypes.SqlBytes(c.Boundary.AsBinary()), 4326);
                    AddSqlGeographyToMapLayer(ShapeLayer, g, c.CNTRY_NAME);
                }
            }
        }

        private void ClearMap_Clicked(object sender, RoutedEventArgs e)
        {
            ShapeLayer.Children.Clear();
        }

        /// <summary>
        /// This method converts a SqlGeography object and converts it into a Bing Maps shape that can be displayed on the map.
        /// </summary>
        /// <param name="layer">MapLayer to add shape to.</param>
        /// <param name="geography">SqlGeography object to add to map.</param>
        /// <param name="tooltip">Tooltip string to display.</param>
        private void AddSqlGeographyToMapLayer(MapLayer layer, SqlGeography geography, string tooltip)
        {
            UIElement shape = null;

            switch (geography.STGeometryType().Value.ToUpper())
            {
                case "POINT":
                    shape = new Pushpin()
                    {
                        Location = new Location(geography.Lat.Value, geography.Long.Value)
                    };
                    break;
                case "LINESTRING":
                    shape = new MapPolyline()
                    {
                        Locations = GeographyRingToLocationCollection(geography),
                        Stroke = new SolidColorBrush(Color.FromArgb(150, 255, 0, 0))
                    };  
                    break;
                case "POLYGON":
                    //Only render the exterior ring of the polygon for now.
                    shape = new MapPolygon()
                    {
                        Locations = GeographyRingToLocationCollection(geography.RingN(1)),
                        Fill = new SolidColorBrush(Color.FromArgb(150, 0, 0, 255)),
                        Stroke = new SolidColorBrush(Color.FromArgb(150, 255, 0, 0))
                    };                    
                    break;
                case "MULTIPOINT":
                case "MULTILINESTRING":
                case "MULTIPOLYGON":
                case "GEOMETRYCOLLECTION":
                    int numGeoms = geography.STNumGeometries().Value;
                    for (int i = 1; i <= numGeoms; i++)
                    {
                        AddSqlGeographyToMapLayer(layer, geography.STGeometryN(i), tooltip);
                    }
                    break;
                default:
                    break;
            }

            if(shape != null){
                //Add tooltip
                ToolTipService.SetToolTip(shape, tooltip);

                //Add shape to layer
                layer.Children.Add(shape);
            }
        }

        /// <summary>
        /// Converts a ring of points from an SQLGeography into a LocationCollection.
        /// </summary>
        /// <param name="ring"></param>
        /// <returns></returns>
        private LocationCollection GeographyRingToLocationCollection(SqlGeography ring)
        {
            LocationCollection locations = new LocationCollection();
            int numPoints = ring.STNumPoints().Value;
            for (int i = 1; i <= numPoints; i++)
            {
                locations.Add(new Location(ring.STPointN(i).Lat.Value ,ring.STPointN(i).Long.Value));
            }
            return locations;
        }

        #endregion
    }
}

If we run this application and navigate the map to different areas and press the nearby search buttons we will end up with maps that look like these. Here is an example of nearby cities being displayed on the map.

image

Here is an example of nearby countries being displayed on the map.

image

About these ads

16 thoughts on “Entity Framework 5 & Bing Maps WPF

  1. Hi, nice stuff as usual. Anyway, I have a little criticism :)

    I believe you should have opted to use DbGeography instead of SqlGeography. I may be wrong, but I don’t see anything in your example that couldn’t be done with DbGeography, which would have the added benefit of being (in theory) database agnostic.

    If you need some specific function of SqlGeography that isn’t in DbGeography (like the Reduce function) you can use the System.Data.Objects.SqlClient.SqlSpatialFunctions class (obviously dropping the database agnosticism).

    Anyway, really nice job. I’ve been a follower of your blog for some time now :)

    • Thanks for pointing that out. I wasn’t aware of that library. The approach I showed using the SQlGeography is still really useful as I’m certain there are a lot of developers who have code written around those classes. I know I have a lot, so this approach makes it easier for me to reuse some existing code. Thanks again for pointing this out.

      • After some testing I noticed the spatial functions only really work when making the Linq call. Trying to use it later to process the data doesn’t work as I had expected. Using the SQL spatial library makes it so we can work with the spatial data in the client side of the application.

  2. Yeah, you’re right. Hadn’t noticed it and it’s rather disappointing. I guess the “System.Data” namespace was a hint but nevertheless.
    Anyway, my last hope is the new “System.Spatial” library that was built for WCF Data Services… if they change Entity Framework to support it, that is :)

  3. This is exactly what I have been looking for. A much smoother way to bring data from the DB and post in Bing Maps.

    Thanks,

  4. Hi nice post… I do not have SQL 2012 & could not restore your database back up file in sql 2008 … could you please let me know the data type of the columns so that I can create a new database in sql 2008 … Thanks in advance.

    • Here you go:

      City Table:
      ID : Int
      CITY_NAME : NVARCHAR(255)
      ADMIN_NAME : NVARCHAR(255)
      CNTRY_NAME : NVARCHAR(255)
      Location : SQLGeography

      Country Table:
      ID : Int
      ISO_2Digit : NVARCHAR(2)
      CNTRY_NAME : NVARCHAR(255)
      POP_CNTRY : Int
      COLOR_MAP : Int
      Boundary : SQLGeography

  5. Nice. This is exactly what I have been looking for . But i use SQL Server 2008, I think you should upload the script with schema and data instead SQL server 2012 bak. Thank you so much :)

  6. Hi Ricky,
    Thanks for the db table info (comment #7 was mine) … Thanks for the posts … I have learned lots of stuffs from your posts/blogs and have been following your blogs… I am using your blogs as my learning materials… Thank you so much … I have a quick question… I am new to WCF rest services and Bing Map APIs…
    I am just creating REST service app to learn few Bing Map fundamentals… Could you please help me with naming the APIs which I should use for my scenario:

    I am using this MSDN tutorial (http://msdn.microsoft.com/en-us/library/dd221354.aspx) as well as my guide to develop my project.

    This is what I am trying to accomplish:
    I am sending my current location (latitude, longitude) to my service as request…so then I want to find all the Star Bucks(for example) within one mile range from my current location… also I want to return the distance, time it will take to each Star Bucks within one mile radius …so that i can decide which Star Bucks to go to based on the shortest travel time and/or shortest distance…

    As the MSDN tutorial suggests (above link) , I have add service reference to the Bing Map services( GeocodeService,SearchService,ImageryService,RouteService)…

    So my question is ,(since this MSDN tutorial SOAP based web service),
    1. do I use the same Bing Map services that used in this MSDN example in my rest service project ? or different Bing Map services for REST Services?

    2. Is there any Bing Map API to find the places within certain range, like the one you used in your example above [center.Distance(c.Location) <= SearchRadiusMeters] ?

    Thank you so much.

  7. Awesome article, I am just diving into Spatial Development and it is great. Your posts have been very valuable to many.You did not show how to use my Credentials(Bing maps Key) to allow the application to make requests to the Map API?! How would I go about this in the App.config file?? I have always been able to add my credentials in the config file when I tried developing in ASP using the AJAX Control.

  8. Pingback: ESRI Shapefiles and Bing Maps - Ricky's Bing Maps Blog - Site Home - MSDN Blogs

  9. Pingback: How to Create a Spatial Web Service That Connects a Database to Bing Maps Using EF5 - Ricky's Bing Maps Blog - Site Home - MSDN Blogs

  10. Pingback: How to Create a Spatial Web Service That Connects a Database to Bing Maps Using EF5 | Blog Bechir Slimen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s