Now I’ve got some spatial data in my SQL Server database, I would like to be able to read them and show them, for example in a Windows application. That’s why I created a little WPF test application. All I wanted to do for now was to extract data from the database, convert them, and finally show them in the form of a simple map.
I chose WPF above Windows Forms because:
1. WPF makes use of a more advanced drawing technology than Windows Forms
2. WPF has a couple of Geometry objects that closely resemble the SqlGeometry objects in the
Microsoft.SqlServer.Types namespace which I used in my earlier projects.
The first thing I did was create a simple WPF application. The XAML looks like this:
<Window x:Class="BasicWpfMapApplication.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="MainWindow" Height="350" Width="525">
<Grid>
<Canvas Name="mainCanvas" Margin="18,20,16,38">
</Canvas>
<Button VerticalAlignment="Bottom" Name="OKButton" Height="25" Width="100" Click="OKButton_Click" Margin="106,0,106,7">Show Data</Button>
</Grid>
</Window>
Upon clicking the OKButton the application is supposed to access a spatial table in my SQL Server database, read the SqlGeometries in that table, convert them to WPF Geometries, and finally show them as a map on the Canvas named “mainCanvas”.
First of all I created a class for accessing the SQL Server database table. I reused part of the Gateway class contained in my last project.
I removed some stuff I didn’t need for this test application and added a public method:
public IList<Geometry> ReadAll(string table, string geometryFieldName)
{
IList<Geometry> geometryList = new List<Geometry>();
if (!Connected)
{
return geometryList;
}
using (SqlCommand sqlCommand = new SqlCommand(String.Format("select {0} from {1}",
geometryFieldName, table), sqlConnection))
{
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
{
while (sqlDataReader.Read())
{
SqlGeometry sqlGeometry = (SqlGeometry)sqlDataReader[geometryFieldName];
Geometry wpfGeometry = sqlGeometry.ToWpfGeometry();
if (extent.IsEmpty == true)
extent = sqlGeometry.GetExtent();
else
extent.Union(sqlGeometry.GetExtent());
geometryList.Add(wpfGeometry);
}
}
}
return geometryList;
}
This method uses a SqlDataReader to read all SqlGeometries from a spatial table. I’m reading just the contents of the SqlGeometry column as I’m not doing anything with non-spatial data at the moment.
As soon as I have a SqLGeometry I’m converting it to a WPF Geometry using an extension method. I’ll discuss this method later on. As I need the total extent of all the geometries I created a field extent of type System.Windows.Rect. By each time unioning the Rect with the earlier Rect (except of course at the first SqlGeometry, when the extent field is empty, and I need to assign the extent of that SqlGeometry to the field), I end up with a Rect encompassing all geometries read. This Rect is exposed in the public readonly method Extent on the Gateway class.
Just like the method ToWpfGeometry(), the Method GetExtent() is an extension method. I defined both methods in a separate class, named SqlGeometryHelper. This class is relatively small, so I can show it here:
public static class SqlGeometryHelper
{
private static double defaultPointSize = 0.01;
public static Geometry ToWpfGeometry(this SqlGeometry sqlGeometry)
{
return ToWpfGeometry(sqlGeometry, defaultPointSize);
}
public static Geometry ToWpfGeometry(this SqlGeometry sqlGeometry, double pointSize)
{
StreamGeometry streamGeometry = new StreamGeometry();
using (StreamGeometryContext streamGeometryContext = streamGeometry.Open())
{
if (sqlGeometry != null && !sqlGeometry.IsNull)
{
sqlGeometry = sqlGeometry.MakeValid(); // It's not guaranteed that the geometry stored
//in the database is valid, so let's make sure of that first!
for (int geometryIndex = 0; geometryIndex < (int)sqlGeometry.STNumGeometries();
geometryIndex++)
{
SqlGeometry subGeometry = sqlGeometry.STGeometryN(geometryIndex + 1);
if (subGeometry.STGeometryType() == "Polygon" ||
subGeometry.STGeometryType() == "MultiPolygon")
{
Point[] points = GetPointsFromSqlGeometry(subGeometry.STExteriorRing());
AddSegmentToGeometry(streamGeometryContext, points, true);
for (int interiorRingIndex = 0;
interiorRingIndex < subGeometry.STNumInteriorRing(); interiorRingIndex++)
{
points = GetPointsFromSqlGeometry(subGeometry.STInteriorRingN(interiorRingIndex + 1));
AddSegmentToGeometry(streamGeometryContext, points, true);
}
}
else if (subGeometry.STGeometryType() == "MultiPoint" ||
subGeometry.STGeometryType() == "Point")
{
Point[] points = GetPointsFromSqlGeometry(subGeometry);
AddCircleToGeometry(streamGeometryContext, points, pointSize);
}
else if (subGeometry.STGeometryType() == "LineString" ||
subGeometry.STGeometryType() == "MultiLineString")
{
Point[] points = GetPointsFromSqlGeometry(subGeometry);
AddSegmentToGeometry(streamGeometryContext, points, false);
}
}
}
}
return streamGeometry;
}
public static Rect GetExtent(this SqlGeometry sqlGeometry)
{
// It's not guaranteed that the geometry stored in the database is valid, so let's make sure of that first!
sqlGeometry = sqlGeometry.MakeValid();
Point[] points = GetPointsFromSqlGeometry(sqlGeometry.STEnvelope());
double xMin = double.MaxValue;
double xMax = double.MinValue;
double yMin = double.MaxValue;
double yMax = double.MinValue;
foreach (Point point in points)
{
if (point.X < xMin)
{
xMin = point.X;
}
if (point.X > xMax)
{
xMax = point.X;
}
if (point.Y < yMin)
{
yMin = point.Y;
}
if (point.Y > yMax)
{
yMax = point.Y;
}
}
return new Rect(new Point(xMin, yMin), new Point(xMax, yMax));
}
private static void AddCircleToGeometry(StreamGeometryContext streamGeometryContext,
Point[] points, double pointSize)
{
foreach (Point point in points)
{
streamGeometryContext.BeginFigure(new Point(point.X - (pointSize / 2),
point.Y - (pointSize / 2)), true, true);
streamGeometryContext.ArcTo(new Point(point.X - (pointSize / 2) - 0.0001, point.Y - (pointSize / 2)),
new Size(pointSize, pointSize), 360, true, SweepDirection.Clockwise, true, false);
}
}
private static void AddSegmentToGeometry(StreamGeometryContext streamGeometryContext,
Point[] points, bool close)
{
for (int i = 0; i < points.Length; i++)
{
if (i == 0)
{
streamGeometryContext.BeginFigure(points[i], true, false);
}
else
{
streamGeometryContext.LineTo(points[i], true, true);
}
}
if (close)
{
streamGeometryContext.LineTo(points[0], true, true);
}
}
private static Point[] GetPointsFromSqlGeometry(SqlGeometry sqlGeometry)
{
Point[] points = new Point[(Int32)(sqlGeometry.STNumPoints())];
for (int i = 0; i < sqlGeometry.STNumPoints(); i++)
{
SqlGeometry pointGeometry = sqlGeometry.STPointN(i + 1);
points[i] = new Point((float)pointGeometry.STX.Value, (float)pointGeometry.STY.Value);
}
return points;
}
}
ToWpfGeometry() creates the WPF version of a SqlGeometry. It is supposed to work on all SqlGeometry types, but to be honest, I haven’t extensively tested it yet, so it could still fail on some complicated SqlGeometry instances.
The method uses the Open Geospatial Consortium operators on the SqlGeometry type to extract information from a SqlGeometry. I overloaded it because I needed a pointSize argument for displaying point geometries. Points of course have no dimension, so I needed a circle to display them. The pointSize argument refers to the diameter of that circle. I wanted to have the possibility to skip this argument and use a default point size instead.
Inside the method ToWpfGeometry() I used the WPF StreamGeometry class and the associated StreamGeometryContext class because they are more light-weight and efficient for constructing geometries than the alternative: the PathGeometry class.
The three methods AddCircleToGeometry() (in case we deal with point geometries), AddSegmentToGeometry() (in case we deal with other geometries) and GetPointsFromSqlGeometry() do most of the work. They don’t need much explanation as they perform simple tasks.
The OKButton in my MainWindow triggers all this functionality. It calls a method ShowData() which looks like this:
private void ShowData()
{
this.mainCanvas.Children.Clear();
Gateway databaseGateway = new Gateway();
databaseGateway.Connect(true, databaseName);
IList<Geometry> geometryList = databaseGateway.ReadAll(tableName, geometryFieldName);
Rect viewPortExtent = new Rect(new Size(this.mainCanvas.ActualWidth, this.mainCanvas.ActualHeight));
Transform transform = CalculateResizeTransform(databaseGateway.Extent, viewPortExtent);
foreach (Geometry geometry in geometryList)
{
geometry.Transform = transform;
Path path = CreatePath(geometry, fillNeeded);
this.mainCanvas.Children.Add(path);
}
}
Its contents is rather straightforward. After clearing the contents of the mainCanvas (relevant if the button is pressed more than once), a connection to the database is made and all the geometries from a specified table are extracted in the form of a list of WPF geometries.
One last important action is needed before the geometries can be added to the mainCanvas: the X and Y coordinates of the Geometry points have to be transformed in such a way that they fit on the canvas. We can do this by way of first scaling and then translating each geometry to the correct position. WPF has built-in classes to do this. I’m using a ScaleTransform and a TranslateTransform object to do this. I’m not going to show the code here, because I borrowed it from this article. The calculations needed for initialising these objects are explained there, so there’s no need to do that here.
Adding geometries to the Canvas can be done using a WPF Path object. We need to give that a Geometry, an outline color, an outline thickness value, and, if it is a Point or a Polygon, a fill color as well:
private Path CreatePath(Geometry geometry, bool fillNeeded)
{
Path path = new Path { Data = geometry, Stroke = Brushes.Black, StrokeThickness = 1 };
if (fillNeeded)
{
path.Fill = Brushes.MediumSlateBlue;
}
return path;
}
My application, which is attached to this post, performs well when reading and drawing smaller datasets. Reading and drawing the OpenStreetMap Polygons from Malta, which I had imported into my SQL Server database, took about 250 milliseconds on my machine. But this polygon dataset contains only 1053 items. Reading and displaying the 7861 LineStrings from the OpenStreetMap Malta table in the same database took almost 2.5 seconds. But the OpenStreetMap Dutch point table in the SQL Server database, being the smallest Dutch dataset (85312 items), took almost 7.5 minutes to draw!
So the application works, but it is far too slow for large spatial datasets. We’re certainly not done yet! In my next post I’m going to explore an alternative method of displaying Geometries in WPF.
BasicWpfMapApplication.zip (17,51 kb)