So I’ve spent most of the day in Leeds, attending one of the stops in the Safe FME “World Tour” event. I’m familiar with the name of the FME product – it gets mentioned a lot on the SQL Server MSDN forums as the ETL tool of choice to fix all manner of spatial data loading/conversion problems – but I’ve never actually used it until very recently.
Then, last month, I had some email contact with Don Murray, the president of Safe, and he mentioned he was coming to do two FME events in the UK and would I like to come along. And I’m glad I accepted his offer. The event was very well organised and hosted (courtesy of Dotted Eyes – Safe’s primary UK partner), with some great speakers and a good mix of sessions. The day featured some “Show ‘n’ Tell” slots (as we did at the last Bing Maps UK Usergroup meeting) from the likes of the British Geological Survey and the London 2012 Olympic Games amongst others. I always find it interesting to see what other folks are doing with their spatial data, as well as the problems they’ve faced along the way. There was some nice eye-candy too, primarily courtesy of some Google Earth network links to FME Server and 3D surface models in Adobe Acrobat.
What I found most interesting though is that, despite having never used FME before, it all felt surprisingly familiar. The FME workbench, where you design spatial ETL tasks, will look very familiar to any SQL Server developers who have ever created a SSIS package – you drag various sources, destinations, and transformation elements onto the page, connected into a logical workflow, and you can define environment variables, conditional steps, counters etc. Likewise, the function of the transformers themselves felt very familiar – many of them corresponding directly to OGC methods defined by the geometry and geography datatypes in SQL Server. For example:
- The Clipper function basically determines the STIntersection() between two geometries.
- DonutBuilder subtracts one Polygon from another, a la STDifference().
- The ConxexHullReplacer is STConvexHull().
- BoundingBoxReplacer? That’s STEnvelope().
- And CenterOfGravityReplacer is STCentroid().
- Bufferer is, unsurprisingly, STBuffer().
Some of the slightly more complex transformers have no simple direct equivalent method in SQL Server, but can be created fairly easily in a SQLCLR procedure using the SqlGeometryBuilder or SqlGeographyBuilder classes. LineCloser, for example, turns LineStrings into Polygons by setting the end point equal to the start point. Offsetter adds constant offsets to each coordinate value, rather like the GeometryShifter SqlGeometryBuilder demo by Isaac Kunen.
I’m looking forward to trying out my evaluation version of Safe FME 2011, and particularly in seeing what it can offer from a SQL Server / Bing Maps development point of view. I’ll be posting any interesting findings back here….