SQL Server 2008 Spatial Indexing

Topics: Data Access, General Topics
Feb 11, 2008 at 4:09 PM
Perhaps this is off topic since my issue SQL Server 2008 related.

Has anyone tried Bill Dollins provider for SQL Server 2008?
http://www.codeplex.com/WorkItem/View.aspx?ProjectName=SharpMap&WorkItemId=14149

I got it to work but it is very slow compared to the OleDBPoint provider. My results are 13 seconds using Katmai.cs and sub second results using the OleDBPoint. This is for a result of 39 records.

I suspect that my spatial index is not getting used. I tried forcing the index using an index hint to specify the spatial index. With the index hint I get the following error.

"The query processor could not produce a query plan for a query with a spatial index hint. Reason: Could not find required binary spatial method in a condition. Try removing the index hints or removing SET FORCEPLAN."

Has anyone run into this before?
Coordinator
Feb 14, 2008 at 12:38 AM
Hi mtidd -

I (sadly) haven't been able to look at Bill's provider.

The error message is cryptic. Which condition couldn't it find a binary spatial method for? And which binary method is required? Do you known what SQL generated this? That would probably give us the first clues...
Feb 14, 2008 at 1:08 PM
Actually my tests where done with the SQL that I captured from Katmai.cs using profiler.

Here are the results

--- sql with index hint added ---
SELECT tblPoints.Geom.STAsBinary() FROM tblPoints with (index(SPATIAL_tblPoints)) WHERE tblPoints.Geom.STIntersects(geometry::STGeomFromText('POLYGON ((575496.878831515 5170990.31742311, 576060.45359146 5170990.31742311, 576060.45359146 5171364.84495345, 575496.878831515 5171364.84495345, 575496.878831515 5170990.31742311))', 4267)) = 1

--- output error --
Msg 8635, Level 16, State 8, Line 6
The query processor could not produce a query plan for a query with a spatial index hint. Reason: Could not find required binary spatial method in a condition. Try removing the index hints or removing SET FORCEPLAN.

-- sql without index hint, this is the sql produced by Katmai.cs --
SELECT Geom.STAsBinary() FROM tblPoints WHERE Geom.STIntersects(geometry::STGeomFromText('POLYGON ((575496.878831515 5170990.31742311, 576060.45359146 5170990.31742311, 576060.45359146 5171364.84495345, 575496.878831515 5171364.84495345, 575496.878831515 5170990.31742311))', 4267)) = 1

-- results --
77 records returned in 12 to 15 seconds

The execution plan without the index hint makes no mention of the spatial index. There is a clustered index scan on the primary key and then a filter which takes 98% of the processing time. I cannot get the execution plan when using the index hint because it throws the same error as above.

I am thinking that perhaps the spatial features of the November CTP are not quite fully baked. I have found some posts about the spatial indexes not being used all the time. I have not found anything about the actual error I am getting. My plan is to continue to use the OleDBPoint and ShapeFile data sources. It is looking like SQL Server 2008 is not going to make it into my product this year; maybe next year we can take full advantage of a spatial database.
Coordinator
Feb 14, 2008 at 6:21 PM
Well, I'm going to go way out on a limb here, but let's take a look at try to interpret what SQL Server is doing, since the query looks fine.

It mentions that it can't use the index in both cases. The reason is that it can't find a binary spatial method in a condition. Let's presume the condition is the predicate, which is an intersection. Intersection is a binary spatial relation, so this fits, even if it isn't the only candidate. Why can't it perform an intersection using the index? Perhaps SQL Server is broken, as you mention. However, perhaps the index is hosed, too. You could try rebuilding it, and see what that gets you.
Feb 18, 2008 at 11:46 AM
I agree that the query looks fine. It is setup the same as other examples I have seen on the web. I tried to rebuid the index and it won't let me. It throws another error. I have tried different spatial indexes on other tables with the same results. I am wondering if my install is bad. Unfortunately I have to get on with things, so I don't have time to work it out. Perhaps I will try again when the next CTP is released.

Thanks
Coordinator
Feb 22, 2008 at 12:20 AM
Edited Feb 22, 2008 at 12:20 AM
@mtidd -

Were you using the February 2008 CTP of Sql Server 2008?
Feb 22, 2008 at 11:11 AM
Edited Feb 22, 2008 at 11:12 AM
No, I am using the November CTP. I don't think the new CTP was released at the time but I see that it is now. I will try that and let you know.

Thanks,