Share via

Our Upcoming Builder API

  • Article

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

Comments

  • Anonymous
    May 30, 2008
    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!

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

  • Anonymous
    June 23, 2008
    SQL Server 2008: Spatial Data, Part 8

  • Anonymous
    July 09, 2008
    The comment has been removed

  • Anonymous
    August 01, 2008
    Hi Folks, I've just published a new project on CodePlex: SQL Server Spatial Tools .  The core idea

  • Anonymous
    August 15, 2008
    The RTM version of SQL Server 2008 and recently release of the CLR updates with Visual Studio 2008 SP1

  • Anonymous
    September 27, 2008
    @Kent Tegels There is a way to specify empty points! ... BeginGeometry(OpenGisGeometryType.Point) // note: no begin/end figure calls EndGeometry() ... In general, to figure out the call sequence for any posible geometry use SqlGeometry.Populate method with IGeometrySink implementation that just logs the calls.

  • Anonymous
    November 17, 2008
    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

  • Anonymous
    June 16, 2009
    The comment has been removed