Recently we encountered a bug in SQL Server’s spatial aggregation algorithms. Sets of polygons that should have been combined strictly into WKT (well-known-text) POLYGON or MULTIPOLYGONs were being stored as GEOMETRYCOLLECTIONs instead. Turns out the algorithm was inserting one or two LINESTRINGs (of two points each) into the collection, forcing it to be stored this way. The bug was supposedly fixed a couple years ago, but I’m going to submit a new bug and sample dataset to Microsoft. Because I’ve double-checked we’re using the latest version of the DLL, etc.

So why do I think the GEOMETRYCOLLECTION is “evil” (or at least don’t think highly of it)? Primarily because it caused our customer to question both the integrity of our software and the use of SQL Server to store AND manipulate spatial data instead of relying on purely an ESRI ArcGIS solution. We were unable to open the SQL Server datasets via ArcCatalog or ArcMap (using the Database Connection in 10.1). On a broader note, I tend to side with ESRI in not supporting more than one data type in a layer, so I’m perplexed that the creators of the WKT standard even thought up such a datatype. I can see where it might make sense to group related geometric objects but believe there are better ways to do that.

I’m tempted (hey, in a post where the word evil is mentioned it seems appropriate word use) to say that this never would have happened in the first place if a GEOMETRYCOLLECTION wasn’t a possibility. But I know that even if the bug gets fixed, Microsoft and others will still adhere to the WKT specification for storing data and ESRI will continue to invalidate coverages that contain multiple datatypes. So we will improve on the hack below to ensure that what gets output in the end is valid by our customer’s definition.

// Well, semi-verified anyway. At least it won't contain any LINESTRINGs.
public SqlGeometry VerifiedPolygonGeometry(SqlGeometry inputGeometry)
{
 SqlGeometry outputGeometry = null;

try
 {
 var goodReservoirGeometriesList = new List<SqlGeometry>();

int numGeometries = (int)inputGeometry.STNumGeometries();

for (int i = 1; i <= numGeometries; i++)
 {

var geometry = inputGeometry.STGeometryN(i);

if (geometry.STGeometryType() != "LineString")
 {
 goodReservoirGeometriesList.Add(inputGeometry.STGeometryN(i));
 }
 }

var agg = new GeometryUnionAggregate();

agg.Init();

foreach (SqlGeometry geometry in goodReservoirGeometriesList)
 {
 agg.Accumulate(geometry);
 }

outputGeometry = agg.Terminate();
 }
 catch (Exception exception)
 {
 Debug.Write(exception.Message);
 }

return outputGeometry;
}