• Learn
• Courses
• Microsoft SQL Server 2008 R2 Update for Developers Training Course
• SQL Server 2008 - Location Awareness via Spatial Technologies
• Hands On Lab: Using Spatial Data in Managed Code

Overview

The spatial data type support that has been added to SQL Server 2008 allows you to model two types of spatial data: Geometry (i.e. describing a planar (flat) system such as a building floor plan), and Geography (i.e. describing an elliptical system such as the Earth). The difference is that the Geometry type is for use with flat surfaces and the Geography type is for modeling an elliptical surface, i.e. the Earth. This lab will generally focus on the Geometry type and specify when the Geography methods differ.

Geometry and Geography both deal with up to two dimensions. While both types support holding information about two additional dimensions (z and m) these are not used in any calculations, they just provide additional information about the spatial instance which could be used by the user.

Spatial data types are implemented using the CLR functionality in SQL Server. This approach hides the complexity of the spatial calculations by providing a couple of types on which there are methods that perform the calculations.

Spatial data is very powerful and can be used in many scenarios, not just location-aware services. One of the obstacles to understanding spatial data is working in more than one dimension. This lab contains exercises that:

• Provide a mechanism for visualizing spatial data
• Show the use of spatial data in managed code

The spatial data types are CLR based, which means we can use them in managed code. You will see in this lab that the interface to the spatial types is the same in managed code.

Throughout this lab will refer to both Geometry and Geography as just Geometry because most of the principles, methods, and properties are the same. Where they are different, the differences will be highlighted.

Objectives

In this Hands-On Lab, you will learn how to:

• Integrate spatial data into a managed code application
• Query spatial data
• Find relationships between different instances of spatial data
• Move spatial data between managed code and the database
• Create spatial data graphically using WPF and ink

System Requirements

You must have the following items to complete this lab:

• Microsoft SQL Server 2008 R2 Express or any edition of Microsoft SQL Server 2008 R2
• Microsoft Visual Studio 2008

Setup

All the requisites for this lab are verified using the Configuration Wizard. To make sure that everything is correctly configured, follow these steps.

1. Launch the Configuration Wizard for this Lab by running the Setup.cmd script located under the Setup folder in the Source folder of this lab. Install any pre-requisites that are missing (rescanning if necessary) and complete the wizard. Note:For convenience, much of the code you will be managing along this lab is available as Visual Studio code snippets. The Setup.cmd file launches the Visual Studio installer file that installs the code snippets. The Configuration Wizard also creates the 'SQLServerTrainingKitAlias' sever alias used in this lab to establish a connection with the database server.

2. After completing it, you can run the Cleanup.cmd script to revert any changes made to your system.

Exercises

The following exercises make up this Hands-On Lab:

1. Drawing Spatial Data
2. Finding Relationships Between Shapes
3. Storing Spatial Data in the Database
4. Querying Spatial Data

Exercise 1: Drawing Spatial Data

One of the challenges in understanding spatial data is visualizing the geometries. In this exercise, you will build an application that allows you to draw geometries, perform spatial calculations in memory, save the geometries to a database, and use spatial indexes to assist in the retrieval of spatial data.

Task 1 - Creating the WPF Application

1. Start Microsoft Visual Studio 2008 from Start | All Programs | Microsoft Visual Studio 2008.
2. Create a new WPF project. In Visual Studio select File | New | Project. In the New Project Dialog select Visual C# | Windows in the tree view on the left, and select WPF Application (make sure that .NET Framework 3.5 is selected in the drop-down list at the top right of the dialog box).
3. Type WPFSpatialViewer as the name of the project, set the name of the solution to Begin, select the Create directory for solution option, set the Location to Ex1-DrawingSpatialData in the Source folder of this lab and finally click OK.

   

   Figure 1 Creating a new WPF Application project

4. Add a reference to the Microsoft.SqlServer.Types .NET assembly. To do this, in Solution Explorer, right-click the References node and select Add Reference. Browse to %ProgramFiles(x86)%\Microsoft SQL Server\100\SDK\Assembliesand select Microsoft.SqlServer.Types.dll.
5. In the XAML editor, change the Title property of the Window element to WPF Spatial Viewer.
6. Add an Ink Canvas to the Window and name it DrawingPad. To do this, add the following code (shown in bold) into the Grid element. 12345  "Gray" BorderThickness="1" >  "DrawingPad" />  
7. Add an event handler for the StrokeCollected event by writing code in the XAML editor (this should generate the event handler method as well). At this point, your XAML code should look like this. 123456789101112 x:Class=" WPFSpatialViewer.Window1" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" Title="WPF Spatial Viewer" Height="300" Width="300">             

   

   Figure 2 Designing the Spatial Viewer main window

8. Press F5 to run the application and draw some shapes on the Ink Canvas.

   

   Figure 3 Running the application

9. Close the application.

Task 2 - Converting Ink Strokes Into Geometries

In this task, you will check the number of points a shape contains. If it contains only one point you create a POINT geometry otherwise you create a LINESTRING.

1. Switch to the Window1 code view and add the following using statements for the libraries that will be used. 12345using System.Data.SqlTypes; using Microsoft.SqlServer.Types; using System.IO; using System.Windows.Ink;
2. Create a ConvertStrokeToGeometry method that takes an Ink Stroke, displays the text representation of the geometry and also, returns the geometry. This method needs to loop through the StylusPoints of the Ink Stroke and build a value that can be used to create a SqlGeometry.

   To do this, add the following method to the Window1 class. This code builds the Well Known Binary (WKB) representation.

   Note:Alternatively, for creating the SqlGeometry object, you can use Well Known Text (WKT) or XML (GML) formats. View the Appendix sections at the end of the lab for more details on these formats.

   (Code Snippet – Using Spatial Data in Managed Code Lab – Ex01 – ConvertStrokeToGeometry method)

   123456789101112131415161718192021222324252627282930313233private SqlGeometry ConvertStrokeToGeometry(Stroke strokeAdded) {  MemoryStream ms = new MemoryStream();  BinaryWriter bw = new BinaryWriter(ms);    //  We need to write some geometry information before the points.  // the byte order, the geometry type and the number of points.  bw.Write((byte)1);    if (strokeAdded.StylusPoints.Count == 1)  {    bw.Write((uint)1); // point  }  else {  bw.Write((uint)2); // linestring  bw.Write((uint)strokeAdded.StylusPoints.Count);   }    foreach (StylusPoint p in strokeAdded.StylusPoints)    {  bw.Write((double)p.X);  bw.Write((double)p.Y);  }    SqlGeometry strkGeom = SqlGeometry.STGeomFromWKB(new SqlBytes(ms),0);  bw.Close();  ms.Close();  return strkGeom; }
3. In the StrokeCollected event handler, pass the Stroke added (e.Stroke) to the method created previously to obtain the geometry. To do this, add the following line (shown in bold) to the DrawingPad_StrokeCollected event handler. 12345private void DrawingPad_StrokeCollected(object sender, System.Windows.Controls.InkCanvasStrokeCollectedEventArgs e) {    SqlGeometry strk = this.ConvertStrokeToGeometry(e.Stroke); }
4. Display information to the user including the string representation of the geometry. Add the following line (shown in bold) in the DrawingPad_StrokeCollected event handler. 123456private void DrawingPad_StrokeCollected(object sender, System.Windows.Controls.InkCanvasStrokeCollectedEventArgs e) {    SqlGeometry strk = this.ConvertStrokeToGeometry(e.Stroke);  MessageBox.Show(String.Format("Created a line {0}, with length {1}", strk.ToString(), strk.STLength())); }
5. Press F5 to run the application and create some shapes. When you create each line you will be notified what the WKT of the line is, along with its length.

   

   Figure 4 Showing the WKT representation of a stroke converted into a geometry

6. Close the application.

Task 3 - Supporting Polygons Creation

The following task assumes that closed lines, where the start and end points are the same, are POLYGONs (this does not have to be the case; a closed line can still be a LINESTRING geometry). To force this, you will add a check box, that when checked will close the line and create a polygon.

1. To add a checkbox to the form you need to add row definitions to your grid so you can have the check box on one row and the ink canvas on another. To do this, add the following code to the Grid element of the Window1.xaml file. 123456789101112131415 xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"  xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"    Title="WPF Spatial Viewer" Height="300" Width="300">              ...    
2. Move the Drawing Pad Ink Canvas to the second row of the grid. To do this, add the following attribute (shown in bold) to the Border element. 1
3. Add a Wrap Panel to the first row of the grid to hold your checkbox and future buttons. To do this, add the following code to the Grid element. 12345678910111213141516             VerticalAlignment="Center">  Close Strokes       StrokeCollected="DrawingPad_StrokeCollected"    Grid.Row="1" />
4. You need to change your DrawingPad_StrokeCollected event handler to close a stroke if the checkbox is checked. Switch to the code view and add the following code (shown in bold) at the beginning of the event handler. 123456789101112131415private void DrawingPad_StrokeCollected(object sender, System.Windows.Controls.InkCanvasStrokeCollectedEventArgs e) {  if (CloseShapes.IsChecked.Value)    {  if (!e.Stroke.StylusPoints[0].Equals(e.Stroke.StylusPoints[e.Stroke.StylusPoints.Count - 1]))  {  e.Stroke.StylusPoints.Add(e.Stroke.StylusPoints[0]);  }  }    SqlGeometry  strk = this.ConvertStrokeToGeometry(e.Stroke);  MessageBox.Show(String.Format("Created a line {0}, with length {1}", strk.ToString(), strk.STLength())); }
5. Change the ConvertStrokeToGeometry method to look at the end points and see if it should create a POLYGON or a LINESTRING. You need to also check the number of points as a POLYGON type needs 3 or more points for it to be valid. To do this, replace the ConvertStrokeToGeometry method with the following code.

   (Code Snippet – Using Spatial Data in Managed Code Lab – Ex01 – ConvertStrokeToGeometry method 2)

   1234567891011121314151617181920212223242526272829303132333435363738394041private SqlGeometry ConvertStrokeToGeometry(Stroke strokeAdded) {  MemoryStream ms = new MemoryStream();  BinaryWriter bw = new BinaryWriter(ms);    //  We need to write some geometry information before the points  // the byte order, the geometry type and the number of points  bw.Write((byte)1);    if (strokeAdded.StylusPoints.Count >= 3 && strokeAdded.StylusPoints[0].Equals(strokeAdded.StylusPoints[strokeAdded.StylusPoints.Count  - 1]))  {  bw.Write((uint)3); // polygon  bw.Write((uint)1); // contains one polygon  bw.Write((uint)strokeAdded.StylusPoints.Count);  }  else if (strokeAdded.StylusPoints.Count == 1)    {  bw.Write((uint)1); // point  }  else {  bw.Write((uint)2); // linestring  bw.Write((uint)strokeAdded.StylusPoints.Count);  }    foreach (StylusPoint  p in strokeAdded.StylusPoints)  {  bw.Write((double)p.X);  bw.Write((double)p.Y);  }    SqlGeometry strkGeom = SqlGeometry.STGeomFromWKB(new SqlBytes(ms), 0);  bw.Close();    ms.Close();  return strkGeom; }
6. Press F5 to run the application and create some POLYGONs. Select the Close Strokes check box and try creating a polygon like the following.

   

   Figure 5 Drawing a POLYGON

   You will get an error if you do this. You will note from the T-SQL scenario that this is an invalid polygon, if you have drawn it with the two lines crossing. The error occurs because we are calling the STLength() method in the MessageBox call. STLength() requires the geometry to be valid. To rectify this we need to use the MakeValid() method.

   

   Figure 6 Exception thrown while calling STLength method with an invalid geometry

7. Press SHIFT+F5 to stop debugging. Change the ConvertStrokeToGeometry method to make the geometry valid if it is not. To do this, add the following code (shown in bold) before the return statement. 1234567891011121314private SqlGeometry ConvertStrokeToGeometry(Stroke strokeAdded) {  ...    SqlGeometry strkGeom = SqlGeometry.STGeomFromWKB(new SqlBytes(ms), 0);  bw.Close();  ms.Close();    // Make the geometry valid if it isn't  if (!strkGeom.STIsValid()) strkGeom = strkGeom.MakeValid();    return strkGeom; }
8. Rerun the application and draw the same shape. The message box will say you have created a MULTIPOLYGON or a GEOMETRYCOLLECTION.

   

   Figure 7 Showing a valid geometry

9. Close the application.

Task 4 - Storing the Geometries in a Collection

To enable you to perform some calculations on the geometries you are creating, you need to store them in a collection. You will use a BindingList.

1. Add the following namespace directive to the Window1 class. 1using System.ComponentModel;
2. Create a class field to contain the geometries. Use a BindingList to enable displaying the geometries in a list. Call it geoms. To do this, add the following field to the Window1 class. 12345678910public partial class Window1 : Window {  BindingList geoms = new BindingList();      public Window1()  {  InitializeComponent();  }  ...
3. In the DrawingPad_StrokeCollecteds method, remove the call to MessageBox.Show and instead store the geometry in the List just created. To do this, use the following code (shown in bold). 123456789101112131415private void DrawingPad_StrokeCollected(object sender, System.Windows.Controls.InkCanvasStrokeCollectedEventArgs e) {  if (CloseShapes.IsChecked.Value)  {    if (!e.Stroke.StylusPoints[0].Equals(e.Stroke.StylusPoints[e.Stroke.StylusPoints.Count - 1]))  {  e.Stroke.StylusPoints.Add(e.Stroke.StylusPoints[0]);  }  }    SqlGeometry strk = this.ConvertStrokeToGeometry(e.Stroke);    this.geoms.Add(strk); }
4. Now you need to add a ListView to the form to display the geometries. To do this, you need to add another row to the Grid and give it a fixed height. Change the row definitions as follows. 123456 >      
5. Because the spatial information of the SqlGeometry is exposed as a set of methods instead of properties, you need to use an IValueConverter to bind the columns in your list to the methods of the geometries. This requires an additional class to be added to the Window and a resource added to the XAML. To do this, follow these steps:
   1. Using Solution Explorer, create a new class and name it GeomConverter.
   2. Replace the new class code with the following content.

      (Code Snippet – Using Spatial Data in Managed Code Lab – Ex01 – GeomConverter class)

      123456789101112131415161718192021222324252627282930namespace WPFSpatialViewer {  using System;  using System.Windows.Data;  using Microsoft.SqlServer.Types;    public class GeomConverter : IValueConverter    {  public GeomConverter()  {  }    public object Convert(  object value,  Type targetType,  object parameter,  System.Globalization.CultureInfo culture)    {  return typeof(SqlGeometry).GetMethod((string)parameter).Invoke(value, null);  }    public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)    {  return null;  }  } }
6. Add a GeomConverter object as a window resource. To do this, replace the Window start tag and add the Window.Resources element using the following code. 12345678910111213141516 xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"  xmlns:src="clr-namespace:WPFSpatialViewer"  Title="WPF Spatial Viewer" Height="480" Width="550">       />        ...  
7. Add a List View to the form after the Ink Canvas with columns for the geometry, area, length and centroid. 123456789101112131415161718192021222324  ...     >                         ConverterParameter=STCentroid, Converter={StaticResource GeomConverter}}"/>        ...   
8. Bind the ListView to the BindingList created above. To do this, add this code to the Window1 constructor after the call to the InitializeComponent method. 12345public Window1() {  InitializeComponent();  GeometryList.ItemsSource = this.geoms; }
9. Press F5 to run the application. Shapes that are now drawn will appear in the list rather than being displayed in a message box.

   

   Figure 8 Shapes appearing in the list box

10. Close the application.

Task 5 - Cleaning the Drawing Surface

Strokes can be removed from an Ink Canvas by using the Clear method on the Strokes collection.

1. Add a button to our wrap panel with a name of ClearDrawing. To do this, add the following code inside the WrapPanel element. 12345   Margin="10,0,10,0">Close Strokes  Clear Drawing
2. Add the event handler to clear the strokes from the canvas and the list of geometries. To do this, add the following method to the Window1 class. 12345private void ClearDrawing_Click(object sender, RoutedEventArgs e) {  DrawingPad.Strokes.Clear();  this.geoms.Clear(); }
3. Press F5 to run the application. Draw some shapes and try the Clear Drawing button.

   

   Figure 9 Shapes appearing in the list box

   Note:Notice that lines do not have an area or a centroid as they are only 1-dimensional.