Step 1: Preparing the data
To accomplish these tasks I used SQL Server 2008 (Codename ‘Katmai’) which will be available in a public CTP very soon. SQL Server 2008 provides a number of OGC-compliant spatial data-types, indexes and functions – so far I discovered 76 functions. First of all I had to get the data into SQL Server 2008. Well the downside of new or not even released products is that there are not so many tools for it available so I started to write my own ETL-tool but then I got lucky again and Safe provided me with a beta-version of their FME. FME is a powerful spatial ETL (Extract, Transform and Load) platform that enables organizations to seamlessly extract, translate, transform, integrate and distribute spatial data in over 200 GIS, CAD, raster and database formats. The beta version I learned to love has already a connector for SQL Server 2008 and it loaded the data in the blink of an eye.
Now that I have the data in SQL Server 2008 I start with some preliminary checks. The OGC Simple Feature Specification does not allow self intersecting polygons like shown below:
Fortunately SQL Server 2008 can already help you out:
UPDATE FromBlom SET Shape = (SELECT Shape.MakeValid() FROM FromBlom WHERE ID=24) WHERE ID=24
The resulting shape is valid and clean