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:
- Create a new database in the SQL Server 2008 R2, named SqlSpatialDemo:
- Open the world.sql file with SQL Server Management Studio and run it.
- Create a Silverlight Application hosted in a Web Site
- Add Silverlight-enabled WCF service: SqlDbService
- Add XamMap control in the Silverlight client application .
- 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:
-- Create date:
-- Description:
-- =============================================
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>
[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:
- Create a new database in the SQL Server 2008 R2, named SqlSpatialDemo:
- Open the world.sql file with SQL Server Management Studio and run it.
- Create a WPF Application
- Add a WCF Application with service: SqlDbService
- Add XamMap control in the WPF ent application .
- 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 ChannelFactoryISqlDbService>
(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
