Using Infragistics XamMap Silverlight/WPF control with SQL Server Spatial

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://www.infragistics.com/dotnet/netadvantage/silverlight/data-visualization.aspx#Downloads

For WPF Demo:

NetAdvantage for Silverlight Data Visualization 2010 vol.2

http://www.infragistics.com/dotnet/netadvantage/wpf/data-visualizationdownloads.aspx#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:

Source code of the demo application you could find here:SQLSpatialDemo.zip


Comments  (1 )

wookwang
on Tue, Feb 28 2012 8:35 PM

system.componentModel.asyncCompletedEventArgs.RaiseExecptionIfNecessary()

MYAPP.sqlDbserviceReference.GetSpatialDataCompletedEventArgs

get_Result()

MYAPP.MainPage.worldDataGetSpaialDataCompleted(object sender,GetSpatialDatacompletedEvent Args e)

MYAPP.sqlDbserviceClientOnGetSpatialDataCompleted(object state)

why generate this error? iis issue? code defect?

Add a Comment

Please Login or Register to add a comment.