I'm having some fun trying to pick a decent SQL Server 2008 spatial index setup for a data set I am dealing with.
The dataset is polygons, representing contours over the whole globe. There are 106,000 rows in the table, the polygons are stored in a geometry field.
The issue I have is that many of the polygons cover a large portion of the globe. This seems to make it very hard to get a spatial index that will eliminate many rows in the primary filter. For example, look at the following query:
SELECT "ID","CODE","geom".STAsBinary() as "geom" FROM "dbo"."ContA"
WHERE "geom".Filter(
geometry::STGeomFromText('POLYGON ((-142.03193662573682 59.53396984952896,
-142.03193662573682 59.88928136451884,
-141.32743833481925 59.88928136451884,
-141.32743833481925 59.53396984952896,
-142.03193662573682 59.53396984952896))', 4326)
) = 1
This is querying an area which intersects with only two of the polygons in the table. No matter what combination of spatial index settings I chose, that Filter() always returns around 60,000 rows.
Replacing Filter() with STIntersects() of course returns just the two polygons I want, but of course takes much longer (Filter() is 6 seconds, STIntersects() is 12 seconds).
Can anyone give me any hints on whether there is a spatial index setup that is likely to improve on 60,000 rows or is my dataset just not a good match for SQL Server's spatial indexing ?
