Blog

Using Infragistics XamMap Silverlight/WPF control with SQL Server Spatial

[Infragistics] Mihail Mateev / Friday, August 6, 2010

Introduction to SQL Server 2008 Spatial

Overview.

Microsoft SQL Server 2008 delivers comprehensive spatial support that enables organizations to seamlessly consume, use, and extend location-based data through spatial-enabled applications which ultimately helps end users make better decisions.

Geospatial Data Types

SQL Server 2008 supports two different spatial data types: GEOMETRY and GEOGRAPHY.

  • GEOMETRY - This data type stores data in projected planar surfaces.
  • GEOGRAPHY - This data type stores data in an ellipsoidal model.

The Geometry Classes define a hierarchy as follows:

Point

A point is an object representing a single location. It always has an X and Y co-ordinate and may additionally have an elevation Z and a measure M.

MultiPoint

A MultiPoint object is a collection of points. It differs from a LineString and a Polygon as there is no implied connections between the points in the collection. Because of this the boundary of a MultiPoint object is empty.

 LineString

A LineString is again a collection of points. However this differs from the Multipoint object, as the points are in sequence and the LineString object also represents the line segments connecting the points.

MultiLineString

A MultiLineString is simply a collection of LineStrings.

Polygon

A Polygon is a collection of points representing a two dimensional surface. A Polygon may conist of a exterior ring and a number of interior rings. For a Polygon object to be a valid instance the interior rings cannot cross one another.

MultiPolygon

A MultiPolygon is a collection of Polygons.

GeometryCollection

A GeometryCollection is a collection of geometry (or geography) objects.

 Import spatial data into SQL Server 2008 Spatial.

SQL Server 208 supports import and export spatial data in industry-standard formats, such as Well Known Text, Well Known Binary, and Geographic Markup Language (GML)

It is possible also  to import spatial data from several formats :

Shape file:

There are several Shape to SQL Database Conversion Utilities

The following is a list of information about software that converts Shape files to SQL database:

ShapeToSQL

shp2text

FME

Manifold

MS Dynamic Map Creator

 In this article we will use data, imported from shape files using ShapeToSQL tool from www.sharpgis.net

Requirements:

To be possible to build demo applications you need to install:

SQL Server 2008 R2 Express or higher license.

http://www.microsoft.com/express/Database/InstallOptions.aspx

For Silverlight Demo:

NetAdvantage for Silverlight Data Visualization 2010 vol.2

http://ko.infragistics.com/dotnet/netadvantage/silverlight/data-visualization.aspx#Downloads

For WPF Demo:

NetAdvantage for Silverlight Data Visualization 2010 vol.2

https://ko.infragistics.com/samples/wpf/#Downloads

Steps to create a Silverlight Demo Application:

  1. Create a new database in the SQL Server 2008 R2, named SqlSpatialDemo:
  2. Open the world.sql file with SQL Server Management Studio and run it.
  3. Create a Silverlight Application hosted in a Web Site
  4. Add Silverlight-enabled WCF service: SqlDbService
  5. Add XamMap control in the Silverlight client application .
  6. Implement a data loading from the SqlDbService WCF Service.

 

Create a new database in the SQL Server 2008 R2, named SqlSpatialDemo

Just create a new database with name SqlSpatialDemo.

Open the world.sql file with SQL Server Management Studio and run it.

Results after data has been imported

 

 Spatial results for world table

 

There is a stored procedure,
used from WCF service to get data:

USE [SqlSpatialDemo]

GO

/****** Object: StoredProcedure [dbo].[get_WorldData] Script Date: 08/06/2010 13:21:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,MIHAIL MATEEV>

-- Create date: <Create Date,,3/8/2010>

-- Description: <Description,,GetWorldLayer>

-- =============================================

CREATE PROCEDURE [dbo].[get_WorldData]

BEGIN

SET NOCOUNT ON;

SELECT geom, CNTRY_NAME, POP_CNTRY FROM world

END

GO

 

 Create a Silverlight Application Hosted in a Web Site

Add a WCF Service

Add a connection string in the Web.config

 <appSettings>

  <add key="token" value="AhyL1itKqs_HSBTekvefjurUR4O-eFGbahleUWXB5vB0e5zON9LSeWPwHghfQF_a"/>

  <add key="connectionString" value="Data Source=IGBGSOFSL01\SQLEXPRESS;Initial Catalog=SqlSpatialDemo;Integrated Security=True"/>

</appSettings>

 

Implement a method in the WCF Service that reads data from SQL Server and returns
a result as IEnumerable<Dictionary<string, string>>

[OperationContract]

public IEnumerable<Dictionary<string, string>> GetSpatialData()

{

    string connectionString =

            System.Configuration.ConfigurationManager.AppSettings["connectionString"];

    List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();

    SqlConnection sqlConnection = new SqlConnection();

    sqlConnection.ConnectionString = connectionString;

    SqlCommand sqlCommand = new SqlCommand();

    sqlCommand.Connection = sqlConnection;

    sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

    sqlCommand.CommandText = "get_WorldData";

    sqlConnection.Open();

    SqlDataReader reader = sqlCommand.ExecuteReader(); 

    if (reader != null)

        while (reader.Read())

        {

            Dictionary<string, string> valueDictionary = new Dictionary<string, string>(); 

            for (int i = 0; i < reader.FieldCount; i++)

            {

                valueDictionary.Add(reader.GetName(i), reader.GetValue(i).ToString());

            }

            list.Add(valueDictionary);

        }

    sqlConnection.Close();

    sqlConnection.Dispose();

    return list;

}

Add XamMap control in the Silverlight client application:

Add a XamMap Control with a MapLayer, named "World"

<ig:XamMap 

            HorizontalAlignment="Stretch"

            Margin="30"

            x:Name="xamMap"

            Loaded="XamMap_Loaded"

            VerticalAlignment="Top"

            ElementClick="XamMapElementClick"

           MapProjectionType="SphericalMercator"

            >

    <ig:XamMap.MapProjection>

        <ig:SphericalMercator/>

    </ig:XamMap.MapProjection>

    <ig:XamMap.Layers>

        <ig:MapLayer

                Name="World" Brushes="#4C2C42C0 #4C218B93 #4CDC1C1C #4C3D7835 #4C701B51"

                FillMode="Choropleth" WorldRect="{Binding ElementName=MainWindow, Path=WorldRect}"

                DataMapping="Name=Name;Value=Value;Caption=Value" VisibleFromScale="0"

                >

            <ig:MapLayer.Reader>

                <ig:SqlShapeReader DataMapping="Data=geom; Name=CNTRY_NAME; Value=POP_CNTRY; Caption=CNTRY_NAME; ToolTip=CNTRY_NAME">

                </ig:SqlShapeReader>

            </ig:MapLayer.Reader>

            <ig:MapLayer.ValueScale>

                <ig:LinearScale IsAutoRange="True"/>

            </ig:MapLayer.ValueScale>

        </ig:MapLayer>

    </ig:XamMap.Layers>

</ig:XamMap>

 

In DataMapping string for Data you need to set a field from type geometry

Add in the ctor of the main page an initialization of the SQL Server Spatiaql Data:

public MainPage()

{

    this._key = Application.Current.Resources["token"].ToString();

    InitializeComponent();

 

    #region  Initialize SQL Server Spatial Data

 

    SqlDbServiceReference.SqlDbServiceClient worldData = new SqlDbServiceReference.SqlDbServiceClient();

 

    worldData.GetSpatialDataCompleted += new EventHandler<GetSpatialDataCompletedEventArgs>(WorldDataGetSpatialDataCompleted);

 

    worldData.GetSpatialDataAsync();

 

    #endregion //Initialize SQL Server Spatial Data

}

When all spatial data is downloaded from the WCF service it must be loaded to the specified layer:

void WorldDataGetSpatialDataCompleted(object sender, GetSpatialDataCompletedEventArgs e)

{

    SqlShapeReader sqlReader = xamMap.Layers["World"].Reader as SqlShapeReader;

    if (sqlReader != null)

    {

        sqlReader.DataSource = e.Result;

        xamMap.Layers["World"].ImportAsync();

    }

}

 

When the XamMap control  is loaded, we need to set XamMap.WorldRect and XamMap.WindowsRect properties:

WorldRect specifies the limits of the Map as Rect. WindowRect specifies the visible area inside the XamMap control:

 

private void XamMap_Loaded(object sender, RoutedEventArgs e)

{

    XamMap map = sender as XamMap;

    if (map != null)

    {

        map.WorldRect = GetRecForLatitudeLongitude(-180, 75, 180, -75);

        map.Layers["World"].WorldRect = map.WorldRect;

        map.WindowRect = map.WorldRect;

    }

}

private Rect GetRecForLatitudeLongitude(double topLeftX, double topLeftY, double bottomRightX, double bottomRightY)

{

    Point winTopLeft = ProjectMapPoint(topLeftX, topLeftY);

    Point winBottomRight = ProjectMapPoint(bottomRightX, bottomRightY);

 

    return new Rect(Math.Min(winTopLeft.X, winBottomRight.X),

                Math.Min(winTopLeft.Y, winBottomRight.Y),

                Math.Abs(winTopLeft.X - winBottomRight.X),

                Math.Abs(winTopLeft.Y - winBottomRight.Y));

}

 

private Point ProjectMapPoint(double longitude, double latitude)

{

    return this.xamMap.MapProjection.ProjectToMap(new Point(longitude, latitude));

}

Run the application:

Data from SQL Server is loaded:

In the sample application data from SQL server is integrated with data from Bing Maps services.

 

Implementation of WPF application using SQL Server 2008 Spatial Data:

Implementation is very similar:

Steps to create a WPF Demo Application:

  1. Create a new database in the SQL Server 2008 R2, named SqlSpatialDemo:
  2. Open the world.sql file with SQL Server Management Studio and run it.
  3. Create a WPF Application
  4. Add a WCF Application with service: SqlDbService
  5. Add XamMap control in the WPF ent application .
  6. Implement a data loading from the SqlDbService WCF Service.

The difference is in data downloading from WCF Service:

public MainWindow()

{

    InitializeComponent();

 

    MakeAsynchronousCall();

}

 

private void MakeAsynchronousCall()

{

    BasicHttpBinding basicHttpBinding = new BasicHttpBinding("BasicHttpBinding_ISqlDbService");

    SqlDbServiceReference.SqlDbServiceClient c =

        new SqlDbServiceReference.SqlDbServiceClient();

    EndpointAddress endpointAddress = c.Endpoint.Address;

    SqlDbServiceReference.ISqlDbService iSqlDbService =

        new ChannelFactory<SqlDbServiceReference.ISqlDbService>

        (basicHttpBinding, endpointAddress).CreateChannel();

 

    AsyncCallback aSyncCallBack =

        delegate(IAsyncResult result)

        {

            try

            {

                IEnumerable<Dictionary<string, string>> data = iSqlDbService.EndGetSpatialData(result);

 

                this.Dispatcher.BeginInvoke((Action)delegate

                                                        {

                                                            SqlShapeReader sqlReader = xamMap.Layers["World"].Reader as SqlShapeReader;

                                                            if (sqlReader != null)

                                                            {

                                                                sqlReader.DataSource = data;

                                                                xamMap.Layers["World"].ImportAsync();

 

                                                            }

    });

            }

            catch (Exception ex)

            {

                this.Dispatcher.BeginInvoke((Action)delegate

                { MessageBox.Show(ex.Message); });

            }

        };

 

    try

    {

        iSqlDbService.BeginGetSpatialData(aSyncCallBack, iSqlDbService);

    }

    catch (Exception ex) { MessageBox.Show(ex.Message); }

}

Run the application:

Data from SQl Server is loaded