Yesterday I wrote a post about the release of a project that can convert shapefiles and KML files to SqlGeography objects for use in a SQL Server 2008 spatial database. The big issue was that the SqlGeography class won't create valid objects from any collection of points. First of all the drawn polygon cannot be larger than a hemisphere. You would think that Microsoft's coders would be smart enough to render the input as the smaller of the two options. Google Earth does not require your KML files to be ordered in a special way. The second problem is that polygon border cannot cross themselves. Again you would think that this could have been handled by some input validation, but no.
I received a comment from k.van.daalen who explained how he had managed to clean his database by SQL script. Since the SQL scripts call CLR functions, it is possible to do the same using code. Below you can see the process of generating valid SqlGeography objects. The input is a GooglePolygon which can be created either using the Shapefile reader or by feeding a KML definition to a GoogleOverlayCollection.
private static void WritePolygon(GooglePolygon shape, SqlGeographyBuilder b)
{
if (shape.Points[0] != shape.Points[shape.Points.Count - 1])
{
shape.Points.Add(shape.Points[0].Clone());
}
b.BeginGeography(OpenGisGeographyType.Polygon);
b.BeginFigure(shape.Points[0].Latitude, shape.Points[0].Longitude);
for (int i = 1; i < shape.Points.Count; i++)
{
b.AddLine(shape.Points[i].Latitude, shape.Points[i].Longitude);
}
b.EndFigure();
b.EndGeography();
}
When the polygon has been written to the SqlGeographyBuilder the SqlGeography object can be retrieved from the ConstructedGeography. If the written polygon is not valid an exception is thrown. The builder doesn't have any way to know if the built object is valid, so you are left with catching the exception.
The catch code goes like this:
As k.van.daalen told, the SqlGeometry class has a MakeValid method which is not present in the SqlGeography class (why??). So the polygon is recreated as a SqlGeometry object which is then 'made valid'.
catch
{
SqlGeometryBuilder gb = GetGeometryBuilder();
WriteGeometryPolygon(shape, gb);
SqlGeometry geom = gb.ConstructedGeometry.MakeValid();
return ConvertToSqlGeography(geom);
}
The last line call a method the where the SqlGeometry object is converted to a SqlGeography object by writing it out as a string and letting the SqlGeography parse that string, like so:
return SqlGeography.STPolyFromText(geom.STAsText(), 4326);
As you can see generating valid SqlGeography object can be quite a heavy operation.Depending on how large your dataset is you may have to let the computer crunch it for about an hour (which is what my 1 CPU laptop took to crunch 500MB of shapefiles), which is still managable. The above code is available in the SVN trunk at svn://svn.reimers.dk/sqlspatial