Thursday, 25 November 2010

FDO RFC48: Easing some of the pain of copying data to SQL Server

The next beta of FDO Toolbox will come bundled with a beta version of FDO 3.6.0

The reason for the move is because FDO 3.6 incorporates RFC48, which provides APIs allowing us to correct the orientation of polygons.

Where is this API most relevant? Copying data to SQL Server 2008.

For those of us who fell for the "Microsoft Technology v1.0" trap (because frankly Microsoft never gets things right on the first attempt!), SQL Server has strict rules regarding geography types (FDO will create geography data types if your FDO Geometric Property uses lat/long coordinates). If you try to insert polygons with invalid orientation into a geography column, SQL Server will automatically reject the input. A post-insert MakeValid is not possible.

This basically meant that copying polygons (with lat/long coordinates) into SQL Server had a high chance of failure, with no way to correct the problem.

Apparently the next version of SQL Server (codenamed "Denali") will be more lenient with regards to geometry correctness (among other things) rendering this point moot. But that is the future, and we are in the present! With the next beta of FDO Toolbox, RFC48 APIs will be used exclusively for copying data to SQL Server 2008, ensuring a more pleasant bulk copy experience.

These new APIs are generic, so I can look at making the geometry correction available to all providers in the future, but for now this is only for SQL Server as it is the most relevant provider requiring such services.

No comments: