SimonS Blog on SQL Server Stuff

SQL Server 2008 Spatial - Using it in .Net code

So you want to use the SqlGeometry and SqlGeogrpahy collections in .Net. I found the lack of friendly collection based properties and methods suprising, i.e. no iterators, no collections which you can add to.(http://sqlblogcasts.com/blogs/simons/archive/2007/12/09/Katmai-Spatial---First-thoughts.aspx)

What this means is you can't create a line and then simply extend it. Or even extend a polygon to cover another point (easily) i.e. myPolygon.AddPoint(new Point(x,y)). This is sort of understadable as there are many ways the point could be added, to the exterior ring, or extend the polygon to cover the point (a pit like pulling an elastic band)

I've found that you either have to create your string representations of Geometry or use the WKB format. As these are standards based they shoud be fine. I think the safest is to use WKB but you need to know the binary structure

If you want to understand the format required for WKB the easiest to do use reflector against the Microsoft.SqlServer.Types dll.

What I've learnt so far is that the format is,

Point = [Byte order][Type][X value][Y value]

Line = [Byte order][Type][PointCount][X Value][YValue][X value][Y value].....

MultiLine = [Byte order][Type][LineCount]<Line1><Line2> .....

Polygon = [Byte order][Type][Polygon Count][PointCount][X Value][YValue][X value][Y value].....

MultiPolygon = [Byte order][Type][Polygon Count]<Polygon1><Polygon2>....

where

[Byte Order] is a singe byte indicating the endian or the rest of the bytes i.e. is 1 0x01000000 or 0x00000001

Type is 4 byte unsigned integer indicating the type of shape

   GeometryCollection = 7,
    LineString = 2,
    MultiLineString = 5,
    MultiPoint = 4,
    MultiPolygon = 6,
    Point = 1,
    Polygon = 3,
    Unknown = 0
PointCount, LineCount and polygon Count are unsigned integers and are used to iterate through the remainder of the bytes.

X Value and Y Value are 8 byte floating points.

<Shape> are repetitions of the relevant shape specification.

Creating a WKB in TSQL is quite neat see http://sqlblogcasts.com/blogs/simons/archive/2007/12/29/SQL-Server-2008-Spatial---How-many-ways-to-make-your-point-.aspx

Unfortunately the SqlGeometry and SqlGeograohy types are sealed so you can't inherit them. You would have to have a local instance and repeat all the method calls. You would have to implement your own parser to be able to add points and this would suck from a perf perspective, although very little validation is done when you create a geometry.