January 2008 - Posts

I've just posted two methods for creating geometries in client code. The examples use Stylus points from an ink canvas but any collection of points will go.

After doing that I ran some tests to compare the two.

I found that in generally the WKB method was ~70% faster to build the WKB and 40% faster to parse the WKB to generate the SqlGeometry instance.

In some cases i.e large numbers of points, WKB was 95% faster to build the WKB and 90% faster to parse.

I thought it could be the string array, but even with the a stringbuilder the performance was similar.

In the test I was using the ink canvas which generates decimal points, this means the string representations are large. I would suspect that if using small integer points the difference wouldn't be as much.

If you want to know the reason you can download a copy of reflector and look at the SqlServer.Types.dll. You will then see that the WKT parser has to find the boundaries of the points (spaces, commas and brackets). The WKB does as a float is stored as 8 bytes and so the parser just reads eight bytes to get the X and then another 8 to get the Y etc.


The following snippet will convert an strok collected using an Ink Canvas in WPF to a LINSTRING geometry.

The first uses the Well Known Text format WKT.

//Text representation of a line is LINESTRING (x1 y1,x2 y2 x3 y3....)

//create an string array to store the points

string[] pnts = new string[e.Stroke.StylusPoints.Count];


for (int i = 0; i < e.Stroke.StylusPoints.Count; i++)


  StylusPoint p = e.Stroke.StylusPoints[i];

  pnts[i] = string.Format("{0} {1}", p.X, p.Y);



string geomText = String.Format("LINESTRING({0})", String.Join(",", pnts));


SqlGeometry strk = SqlGeometry.Parse(geomText);

And this uses the Well Known Binary format WKB

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.





foreach (StylusPoint p in e.Stroke.StylusPoints)






SqlGeometry strk = SqlGeometry.STGeomFromWKB (new SqlBytes(ms),0);



Posted by simonsabin | with no comments

If you are planning on coming along to SQLBits on 1st March in Birmingham then why not have your say into what sessions we have.

The sessions that have been submitted are now on the web site http://www.sqlbits.com/information/PublicSessions.aspx.

 In order to vote you need to register on the site

Posted by simonsabin | with no comments
More Posts « Previous page