Our Upcoming Builder API

Hi Folks,

We have one more upcoming pre-release before we’re done with SQL Server 2008, and while I’ve posted a few articles about the coordinate order swap, there’s another exciting piece we’re releasing: our builder API.

Let’s say you wanted to write a very simple method that shifted a geometry instance in space.  You could write this this today, but it would require writing code to digest one of the formats we support—WKT, WKB, or GML—perform the operation, and then recreate one of these data formats.  When the operation is so simple, the bulk of the effort needed will be in moving between formats.

This seems pretty silly, given the fact that we certainly have code that already digests these instances programmatically.  Why don’t we give you access?

Now we do.

If you scroll down, you’ll see a lot of C# code.  The solution we have can only be accessed directly through the CLR, but since the CLR is hosted in SQL Server, functionality developed can be deployed to the server and used through T-SQL.  This new functionality, which will be released in RC0, has three key parts:

  • IGeometrySink / IGeographySink interfaces.  These interfaces expose six methods—SetSRID, BeginGeometry, BeginFigure, AddLine, EndFigure, and EndGeometry—that can be called to describe a spatial instance with full fidelity.  Anyone can build a class that implements these methods, and pass an instance of their class to the…
  • …Populate() method on geography and geometry instances.  This method takes an IGeometrySink or IGeographySink instance and makes the appropriate calls to exactly describe the instance.  If, e.g., you wanted to build code to create KML from geography instances, you’d build a KML translator that implemented IGeographySink, and call Populate() with it.  If you wanted to write the shift example, you’d still have the problem of creating the result instance, and this is handled by…
  • …Builder classes for geometry and geography.  SqlGeometryBuilder and SqlGeographyBuilder implement their respective sink interfaces, and expose an additional property: ConstructedGeometry.  After a series of calls to the interface that create a complete instance, a geometry or geography instance will be available in the property.  Because the interfaces are the same for both digesting and creating spatial instances, it is very easy to pipeline input and output operations.

So, let’s take a look at a working example that solves our shifting problem.  The main part is to write our own implementation of IGeometrySink that will perform the shifting.  Our constructor will take the amount of shift we want in the x and y directions, as well as a target IGeometrySink instance.  The implementations of each of the IGeometrySink methods will simply pass the calls through to the target, except that every point passed through will be shifted first.

using Microsoft.SqlServer.Types;

namespace Tools
class GeometryShifter : IGeometrySink
IGeometrySink _target;
double _xShift;
double _yShift;

public GeometryShifter(double xShift, double yShift, IGeometrySink target)
_target = target;
_xShift = xShift;
_yShift = yShift;

// Each AddLine call will just move the endpoint by the required amount.
public void AddLine(double x, double y, double? z, double? m)
_target.AddLine(x + _xShift, y + _yShift, z, m);

// Each BeginFigure call will just move the start point by the required amount.
public void BeginFigure(double x, double y, double? z, double? m)
_target.BeginFigure(x + _xShift, y + _yShift, z, m);

// Just pass through without change.
public void BeginGeometry(OpenGisGeometryType type)

// Just pass through without change.
public void EndFigure()

// Just pass through without change.
public void EndGeometry()

// Just pass through without change.
public void SetSrid(int srid)

To use this in SQL Server, we need to wrap it in a function that can be registered and used in the server.  To do this, we create a function that puts the pipeline together and runs it.

using Microsoft.SqlServer.Types;

namespace Tools
public partial class Functions
public static SqlGeometry ShiftGeometry(SqlGeometry g, double xShift, double yShift)
SqlGeometryBuilder constructed = new SqlGeometryBuilder();
GeometryShifter shifter = new GeometryShifter(xShift, yShift, constructed);
return constructed.ConstructedGeometry;

Because of SQL Server’s CLR support, we can build this to Tools.dll and load it into the server.  After that, we can register the function and use it directly from T-SQL:

create assembly Tools from 'c:\tmp\Tools.dll'

create function ShiftGeometry(@g geometry, @x float, @y float)
returns geometry
as external name Tools.[Tools.Functions].ShiftGeometry

declare @g geometry = 'LINESTRING (0 0, 1 1, 12 3, 4 0)'

select @g.ToString()
union all
select dbo.ShiftGeometry(@g, 10, 10).ToString()

This yields:

LINESTRING (0 0, 1 1, 12 3, 4 0)
LINESTRING (10 10, 11 11, 22 13, 14 10)

This is a pretty simple example, but it illustrates the power of the approach.  Now you can build functions like this without all of the parsing glue.

Our spatial implementation makes use of this kind of API internally, and it was this internal use that inspired us to release it to users of the system as well.  We see many cases in which new spatial operations can be added to the server through this API, and we hope that you can find some as well.  (Looking at the Spatial forum, we can find many examples of problems that can be tackled with this approach, and this list is nowhere near exhaustive.)



Comments (9)

  1. Matt Penner says:

    This is great!  Geoffrey Emery and I gave a spatial presentation at the MS SQL 2008 Fire Starter event in Irvine, CA.  We had a lot of examples viewing spatial data in Virtual Earth.  We did this by having a web handler grab the GML output from SQL 2008 and converting it to a GeoRSS feed, which Virtual Earth reads natively.

    Using the builder API we could now create a routine to output GeoRSS straight from SQL 2008.

    Great job!

  2. Isaac @ MSDN says:

    Hi Folks, As we continue to shut down SQL Server 2008, our first release candidate has been released

  3. SQL Server 2008: Spatial Data, Part 8

  4. Kent Tegels says:

    Hummm. I’m not sure I like this particular part of the interface very much:

       public void AddLine(double x, double y, double? z, double? m) {

         throw new NotImplementedException();


    X and Y aren’t nullable, but Z and M are. The Z and M make sense as they are user-defined. But consider that you may be building against an invalid geometery that has null points in a collection.

    While this works for many of the use cases, that would break and I can’t see a good reason to let it.


  5. Isaac @ MSDN says:

    Hi Folks, I’ve just published a new project on CodePlex: SQL Server Spatial Tools .  The core idea

  6. The RTM version of SQL Server 2008 and recently release of the CLR updates with Visual Studio 2008 SP1

  7. Marko Tintor says:

    @Kent Tegels

    There is a way to specify empty points!


    // note: no begin/end figure calls


    In general, to figure out the call sequence for any posible geometry use SqlGeometry.Populate method with IGeometrySink implementation that just logs the calls.

  8. lester says:

    Hi, I am interested in using the Builder API you describe in a .NET project.

    Seems like a basic question but where is the Microsoft.SqlServer.Types located so I can add it to the my project?

    I am using VS2005 but have installed SQL Server 2008

  9. Swat says:


    I have a geometry of type SqlGeometry and then I convert it into varbinary.

    I then use a BinaryReader to read the bytes.

    BinaryReader r = new BinaryReader(new MemoryStream(byteGeomIn));

    Then I use SqlGeometry’s read method to read the binary.


    It should be working properly,but I am getting an error at the Read statement. It says Invalid Format or Spatial Reference Id should be between 0-9999. But while creating the geometry type in Sql, i have given the SRID as 0. I dont understand what the problem could be.