Introduction
SQL Server 2008 includes two new data types: geography and geometry. Table columns of these data types hold data compatible to the Open Geospatial Consortium's standards. However, the tools supplied with SQL Server do not provide a way to visualize the data. Enter this little tool, which does exactly that for the geometry type.
What the Tool Does
The tool accepts input in two modes: User Input and Database Input. In both input modes, the given geometries are added to a grid for tabular view, and to a canvas for visualization.
User Input
In user input mode, we may type a WKT description of a geometry, for instance, POLYGON((0 0, 50 0, 50 50, 0 50, 0 0)), which describes a 50x50 square with its bottom-left corner at the origin of the axes.
Database Input
In database input mode, after we have established a connection to a SQL Server 2008 database, we type a SQL query returning at most two columns of data. In the case of two columns, the first column is used to identify the geometry, and the second is the geometry itself. In the case of one column, the column itself is the geometry, identified by an auto-generated GUID.
Background
The inspiration for this little tool was Simon Sabin's SpatialViewer. Indeed, a tiny portion of Simon's code has been copied verbatim. SpatialViewer provides the ability to generate WKT from the geometries drawn with the mouse on a canvas and to show it to the user. However, it suffers from two limitations:
- It is incapable of accepting input from a database (a shortcoming which can be easily corrected); and
- It is written for Windows Forms, therefore failing to take advantage of WPF's excellent data binding and drawing capabilities.
Dissecting the Code
The main point of interest is the file GeometryInfo.cs, which consists of two classes: GeometryInfo and GeometryCollection. The former encapsulates a geometry, and provides properties that return the geometry's area, its length, and whether it is a valid geometry or not. The geometry itself is stored in the Data property, which is of type SqlGeometry.
The Geometry read-only property returns the geometry as an instance of the WPF Geometry class. To achieve this, the Decode() method parses the geometry's WKT and returns the appropriate Geometry instance:
private Geometry Decode(SqlGeometry g)
{
PathGeometry result = new PathGeometry();
switch (g.STGeometryType().Value.ToLower())
{
case "point":
PathFigure pointFig = new PathFigure();
pointFig.StartPoint = new Point(g.STX.Value - 2, g.STY.Value - 2);
LineSegment pointLs = new LineSegment(new Point(g.STX.Value + 2,
g.STY.Value + 2), true);
pointFig.Segments.Add(pointLs);
result.Figures.Add(pointFig);
pointFig = new PathFigure();
pointFig.StartPoint = new Point(g.STX.Value - 2, g.STY.Value + 2);
pointLs = new LineSegment(new Point(g.STX.Value + 2,
g.STY.Value - 2), true);
pointFig.Segments.Add(pointLs);
result.Figures.Add(pointFig);
return result;
case "polygon":
string cmd = new string(g.STAsText().Value).Trim().Substring(8);
string[] polyArray = (cmd.Substring(1, cmd.Length - 2) +
", ").Split('(');
var polys = from s in polyArray
where s.Length > 0
select s.Trim().Substring(0, s.Length - 3);
PathFigure fig;
foreach (var item in polys)
{
fig = new PathFigure();
var polyPoints = from p in item.Split(',')
select p.Trim().Replace(" ", ",");
fig.StartPoint = Point.Parse(polyPoints.ElementAt(0));
for (int i = 1; i < polyPoints.Count(); i++)
{
LineSegment ls = new LineSegment(
Point.Parse(polyPoints.ElementAt(i)), true);
fig.Segments.Add(ls);
}
result.Figures.Add(fig);
}
return result;
case "linestring":
PathFigure lsfig = new PathFigure();
lsfig.StartPoint = new Point(g.STPointN(1).STX.Value,
g.STPointN(1).STY.Value);
for (int i = 1; i <= g.STNumPoints(); i++)
{
LineSegment ls = new LineSegment();
ls.Point = new Point(g.STPointN(i).STX.Value,
g.STPointN(i).STY.Value);
lsfig.Segments.Add(ls);
}
result.Figures.Add(lsfig);
return result;
case "multipoint":
case "multilinestring":
case "multipolygon":
case "geometrycollection":
GeometryGroup mpG = new GeometryGroup();
for (int i = 1; i <= g.STNumGeometries().Value; i++)
mpG.Children.Add(Decode(g.STGeometryN(i)));
return mpG;
default:
return Geometry.Empty;
}
}
The Decode() method queries the geometry's type using the STGeometryType() method of the SqlGeometry class. If the geometry is a geometry collection (one of MultiPoint, MultiLineString, MultiPolygon, or GeometryCollection), it creates a GeometryGroup and adds to it the collection's geometries by recursively calling Decode() on each of the collection's children. Otherwise, it creates a PathGeometry that represents the given SqlGeometry. Note that points are added as crosses, with an envelope of side 4.
The GeometryCollection class inherits DependencyObject to provide three read-only dependency properties:
TranslateX and TranslateY, which give the offset that the geometry collection must be translated in order for its top left corner to be placed at the axes origin; and BoundingBox, which returns the geometry collection's envelope - the smallest rectangle that contains the whole geometry.
The class exposes a fourth property, Geometries, of type ObservableCollection<GeometryInfo>. When the underlying collection changes, the other three properties are updated. This is accomplished by attaching an event handler to the collection's CollectionChanged event:
void GeometriesCollectionChanged(object sender,
System.Collections.Specialized.NotifyCollectionChangedEventArgs e)
{
if (Geometries.Count > 0)
{
var minX = (from p in Geometries
select p.Data.STEnvelope().STPointN(1).STX.Value).Min();
var maxX = (from p in Geometries
select p.Data.STEnvelope().STPointN(2).STX.Value).Max();
var minY = (from p in Geometries
select p.Data.STEnvelope().STPointN(1).STY.Value).Min();
var maxY = (from p in Geometries
select p.Data.STEnvelope().STPointN(4).STY.Value).Max();
SetValue(BoundingBoxPropertyKey, new Rect(minX, minY, maxX - minX, maxY - minY));
SetValue(TranslateXPropertyKey, -BoundingBox.TopLeft.X);
SetValue(TranslateYPropertyKey, -BoundingBox.TopLeft.Y);
}
else
{
SetValue(BoundingBoxPropertyKey, new Rect(0, 0, 0, 0));
SetValue(TranslateXPropertyKey, 0.0);
SetValue(TranslateYPropertyKey, 0.0);
}
}
Using the Tool
Use the tab pages at the bottom of the main window to switch between the User Input mode and the Database Input mode.
In the User Input mode, type the geometry commands, each on a new line. These are parsed as you type, and the parse result is displayed below the commands. If the geometry is valid, click the Add button to add it to the viewer. If it is not, but a valid geometry can be constructed from it, click the Add Valid button.
In the Database Input mode, first establish a connection to a SQL Server 2008 database that contains tables with spatial data (connection status is displayed on the status bar). Then, type a SQL command following the guidelines in the introduction, and finally click the Add button.
Note that adding a geometry does not remove the geometries already added. To delete a geometry, select it from the grid and press the Delete button. To clear the geometry list completely, click the Clear Canvas button on the toolbar.
Use the slider on the toolbar to zoom in or out. The zoom factor ranges from 0.1 to 30.
Click and drag the canvas to pan.
Points of Interest
A couple of interesting challenges were implementing the canvas and implementing the panning. Solving one problem turns out to solve the other. The canvas comprises of a Border, which contains a Canvas, which contains an ItemsControl. The ItemsControl is bound to a GeometryCollection and is styled such that its ItemsPanel contains a Grid (for absolute positioning) and so that each item presents itself as a Path whose geometry is bound to the item's Geometry property:
<Style TargetType="ItemsControl">
<Setter Property="ItemsPanel">
<Setter.Value>
<ItemsPanelTemplate>
<Grid>
<Grid.RenderTransform>
<TransformGroup>
<TranslateTransform
X="{Binding Path=TranslateX}"
Y="{Binding Path=TranslateY}" />
</TransformGroup>
</Grid.RenderTransform>
<Grid.LayoutTransform>
<TransformGroup>
<ScaleTransform
CenterX="0" CenterY="0"
ScaleX="{Binding ElementName=sldZoom,
Path=Value}"
ScaleY="{Binding ElementName=sldZoom,
Path=Value}" />
</TransformGroup>
</Grid.LayoutTransform>
</Grid>
</ItemsPanelTemplate>
</Setter.Value>
</Setter>
</Style>
<DataTemplate DataType="{x:Type local:GeometryInfo}">
<Path Data="{Binding Path=Geometry}"
Stroke="Black" StrokeThickness="0.2"
Fill="{Binding Path=Fill}">
<Path.ToolTip>
<StackPanel Width="250" TextBlock.FontSize="12">
<TextBlock FontWeight="Bold" Text="
{Binding Path=Id}" />
<StackPanel Orientation="Horizontal">
<TextBlock Text="Area: " />
<TextBlock Text="{Binding Path=Area}" />
<TextBlock Text=" units" />
</StackPanel>
<StackPanel Orientation="Horizontal">
<TextBlock Text="Length: " />
<TextBlock Text="{Binding Path=Length}" />
<TextBlock Text=" units" />
</StackPanel>
</StackPanel>
</Path.ToolTip>
</Path>
</DataTemplate>
Finally, the Border handles the PreviewMouseUp, PreviewMouseDown, and PreviewMouseMove events so that they apply an appropriate TranslateTransform to the ItemsControl to implement panning. Note that a MatrixTransform and a TranslateTransform is applied to the Border in order not to draw the canvas upside down (in a Cartesian coordinate system, Y-coordinates increase as you move upwards, whereas in the WPF coordinate system, Y-coordinates increase as you move towards the bottom of the screen):
<Border Margin="4,0,4,4" BorderThickness="0.5,0.5,0.5,0.5"
BorderBrush="{DynamicResource
{x:Static SystemColors.ActiveCaptionTextBrushKey}}"
Background="Transparent" Name="masterCanvas"
PreviewMouseMove="masterCanvas_PreviewMouseMove"
PreviewMouseDown="masterCanvas_PreviewMouseDown"
PreviewMouseUp="masterCanvas_PreviewMouseUp">
<Border.RenderTransform>
<TransformGroup>
<MatrixTransform Matrix="1,0,0,-1,0,0" />
<TranslateTransform
X="0"
Y="{Binding ElementName=masterCanvas, Path=ActualHeight}" />
</TransformGroup>
</Border.RenderTransform>
<Canvas ClipToBounds="True">
<ItemsControl Name="drawingCanvas"
IsTabStop="False"
ItemsSource="{Binding Path=Geometries}">
<ItemsControl.RenderTransform>
<TranslateTransform X="{Binding Path=BoundingBox.X}"
Y="{Binding Path=BoundingBox.Y}" />
</ItemsControl.RenderTransform>
</ItemsControl>
</Canvas>
</Border>
History
- 2008-9-8: First version released
- 2008-9-9: Demo updated
