Our Upcoming Builder API

Article
05/30/2008

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)
        {
            _target.BeginGeometry(type);
        }

        // Just pass through without change.
        public void EndFigure()
        {
            _target.EndFigure();
        }

        // Just pass through without change.
        public void EndGeometry()
        {
            _target.EndGeometry();
        }

        // Just pass through without change.
        public void SetSrid(int srid)
        {
            _target.SetSrid(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);
            g.Populate(shifter);
            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'
go

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

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

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

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.)

Cheers,

-Isaac

Our Upcoming Builder API

Additional resources