Click here to Skip to main content
Click here to Skip to main content

SQL Geometry Viewer

, 11 Sep 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
An on-screen visualizer for the SQL Geometry type

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 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

George Tryfonas
Engineer
Greece Greece
I am a software developer (mainly in C# and T-SQL) for a project management company in Athens, Greece. I have been working with computers since early 1987. I am adept at Pascal, C, C++, Java (my MSc was sponsored by Sun Microsystems), Lisp, Scheme, F#, C# VB.Net, Perl and some others that are too obscure to mention. When I want a quick and dirty solution to a programming problem I use a functional language, such as Haskell, Scheme or, more recently, F#.
 
I also play the keyboards and compose music.
 
---------------------------------------------------------
 
MSc Distributed Systems and Networks - University of Kent at Canterbury
BEng Computer Systems Engineering - University of Kent at Canterbury

Comments and Discussions

 
QuestionNothing Drawing on Canvas... PinmemberMember 882599313-Dec-12 8:39 
GeneralMy vote of 5 PinmvpKanasz Robert25-Sep-12 22:45 
Excellent article. You've got my 5.
QuestionWhat does data look like? [modified] PinmemberRedDK21-Oct-11 7:27 
AnswerRe: What does data look like? [modified] PinmemberGeorge Tryfonas28-Nov-11 22:56 
QuestionLarge number of Objects PinmemberMehdi Khezrian25-Nov-08 20:32 
GeneralPoor performance on complex types PinmemberAndries Olivier15-Sep-08 23:23 
GeneralRe: Poor performance on complex types PinmemberGeorge Tryfonas17-Sep-08 0:21 
GeneralRe: Poor performance on complex types PinmemberAndries Olivier17-Sep-08 23:00 
GeneralRe: Poor performance on complex types PinmemberAndries Olivier3-Nov-08 5:01 
Generalti les twra... PinmemberD Torolopoulos11-Sep-08 1:50 
GeneralGreat Idea Pinmembermerlin98110-Sep-08 4:04 
Generalcan't unzip your exe-files PinmemberLotharLanger8-Sep-08 13:39 
GeneralRe: can't unzip your exe-files [modified] PinmemberGeorge Tryfonas8-Sep-08 20:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.141015.1 | Last Updated 11 Sep 2008
Article Copyright 2008 by George Tryfonas
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid