# Not using SQL Server spatial index

**URL:** <https://community.thinkgeo.com/t/not-using-sql-server-spatial-index/7188>\
**Category:** Services\
**Created:** [January 20, 2016, 1:35pm UTC](https://community.thinkgeo.com/t/not-using-sql-server-spatial-index/7188 "2016-01-20T13:35:19Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![John1](https://community.thinkgeo.com/letter_avatar_proxy/v4/letter/j/e9bcb4/32.png) [@John1](https://community.thinkgeo.com/u/John1)\
**Post date:** [January 20, 2016, 1:35pm UTC](https://community.thinkgeo.com/t/not-using-sql-server-spatial-index/7188/1 "2016-01-20T13:35:19Z")

</div>

I&nbsp;have&nbsp;some&nbsp;code&nbsp;that&nbsp;is&nbsp;currently&nbsp;using&nbsp;ThinkGeo&nbsp;Map&nbsp;Suite&nbsp;Services&nbsp;Edition&nbsp;to&nbsp;query&nbsp;a&nbsp;SQL&nbsp;Server&nbsp;2008&nbsp;database&nbsp;for&nbsp;the&nbsp;shape(s)&nbsp;that&nbsp;contain&nbsp;a&nbsp;point.&nbsp;&nbsp;That&nbsp;code&nbsp;is&nbsp;currently&nbsp;using&nbsp;Map&nbsp;Suite&nbsp;6,&nbsp;but&nbsp;I&nbsp;am&nbsp;attempting&nbsp;to&nbsp;switch&nbsp;over&nbsp;to&nbsp;using&nbsp;Map&nbsp;Suite&nbsp;9.  
  
  
  
Dim&nbsp;sectionFeatures&nbsp;As&nbsp;Collection(Of&nbsp;Feature)  
  
Dim&nbsp;sectionColumns&nbsp;As&nbsp;String()&nbsp;=&nbsp;{“SECTION”,&nbsp;“TOWNSHIP”}

&nbsp;sectionFeatures&nbsp;=&nbsp;sectionLayer.QueryTools.GetFeaturesContaining(inPoint,&nbsp;sectionColumns)

When&nbsp;I&nbsp;use&nbsp;Map&nbsp;Suite&nbsp;6,&nbsp;the&nbsp;query&nbsp;that&nbsp;gets&nbsp;thrown&nbsp;at&nbsp;the&nbsp;database&nbsp;is&nbsp;  
  
  
  
exec&nbsp;sp\_executesql&nbsp;N’SELECT&nbsp;[SECTION],[TOWNSHIP],[Shape].STAsBinary()&nbsp;as&nbsp;[Shape],[SECTION\_ID]&nbsp;FROM&nbsp;SECTION&nbsp;**with(index(S868\_idx))**&nbsp;WHERE&nbsp;(geography::STGeomFromText(@geography,4269).STIntersects(Shape)=1);’,N’@Geography&nbsp;varchar(170)’,@Geography=‘POLYGON((-110.7703329915&nbsp;47.737164355451,-110.7703329915&nbsp;47.737154355451,-110.7703229915&nbsp;47.737154355451,-110.7703229915&nbsp;47.737164355451,-110.7703329915&nbsp;47.737164355451))’&nbsp;  
  
  
  
When&nbsp;I&nbsp;use&nbsp;Map&nbsp;Suite&nbsp;9&nbsp;(yesterday’s&nbsp;daily&nbsp;build)&nbsp;the&nbsp;query&nbsp;instead&nbsp;is

exec&nbsp;sp\_executesql&nbsp;N’SELECT&nbsp;[SECTION],[TOWNSHIP],[Shape].STAsBinary()&nbsp;as&nbsp;[Shape],[SECTION\_ID]&nbsp;FROM&nbsp;SECTION&nbsp;WHERE&nbsp;(geometry::STGeomFromText(@Geography,4269).STWithin(geometry::STGeomFromWKB(Shape.STAsBinary(),4269))=1);’,N’@Geography&nbsp;varchar(38)’,@Geography='POINT(-110.7703279915&nbsp;47.737159355451)'

The&nbsp;top&nbsp;(Map&nbsp;Suite&nbsp;6)&nbsp;version&nbsp;is&nbsp;supplying&nbsp;the&nbsp;spatial&nbsp;index&nbsp;hint&nbsp;whereas&nbsp;the&nbsp;bottom&nbsp;(Map&nbsp;Suite&nbsp;9)&nbsp;version&nbsp;is&nbsp;not.&nbsp;&nbsp;The&nbsp;top&nbsp;query&nbsp;completes&nbsp;in&nbsp;less&nbsp;than&nbsp;a&nbsp;second,&nbsp;the&nbsp;bottom&nbsp;one&nbsp;times&nbsp;out&nbsp;after&nbsp;the&nbsp;default&nbsp;30&nbsp;second&nbsp;timeout.&nbsp;&nbsp;My&nbsp;question&nbsp;is&nbsp;what&nbsp;do&nbsp;I&nbsp;need&nbsp;to&nbsp;do&nbsp;to&nbsp;get&nbsp;Map&nbsp;Suite&nbsp;9&nbsp;to&nbsp;generate&nbsp;SQL&nbsp;that&nbsp;takes&nbsp;advantage&nbsp;of&nbsp;the&nbsp;spatial&nbsp;index&nbsp;of&nbsp;the&nbsp;table?&nbsp;&nbsp;I&nbsp;can&nbsp;see&nbsp;by&nbsp;inspecting&nbsp;the&nbsp;properties&nbsp;of&nbsp;the&nbsp;layer&nbsp;in&nbsp;Visual&nbsp;Studio&nbsp;that&nbsp;the&nbsp;layer&nbsp;knows&nbsp;the&nbsp;name&nbsp;of&nbsp;the&nbsp;spatial&nbsp;index,&nbsp;but&nbsp;it&nbsp;isn’t&nbsp;currently&nbsp;using&nbsp;that&nbsp;index&nbsp;name&nbsp;in&nbsp;the&nbsp;query&nbsp;sent&nbsp;to&nbsp;the&nbsp;database&nbsp;and&nbsp;for&nbsp;some&nbsp;reason&nbsp;the&nbsp;database&nbsp;doesn’t&nbsp;appear&nbsp;to&nbsp;be&nbsp;smart&nbsp;enough&nbsp;to&nbsp;use&nbsp;the&nbsp;index&nbsp;unless&nbsp;it’s&nbsp;told&nbsp;to&nbsp;do&nbsp;so&nbsp;by&nbsp;the&nbsp;query.  
  
  
  
Any&nbsp;advice&nbsp;would&nbsp;be&nbsp;appreciated.  
  
Thanks,  
  
John

---

<div class="post-metadata">

**Author:** ![Peter](https://community.thinkgeo.com/user_avatar/community.thinkgeo.com/peter/32/19042_2.png) [@Peter](https://community.thinkgeo.com/u/Peter)\
**Post date:** [January 20, 2016, 9:12pm UTC](https://community.thinkgeo.com/t/not-using-sql-server-spatial-index/7188/2 "2016-01-20T21:12:03Z")

</div>

Hi&nbsp;John,  
  
  
  
Please&nbsp;try&nbsp;setting&nbsp;the&nbsp;sectionFeatures.SpatialIndexName&nbsp;=&nbsp;“ **S868\_idx** ”.  
  
  
  
Usually,&nbsp;we&nbsp;don’t&nbsp;need&nbsp;to&nbsp;set&nbsp;the&nbsp; **SpatialIndexName&nbsp;** property,&nbsp;the&nbsp;MsSql2008FeatureLayer&nbsp;will&nbsp;set&nbsp;it&nbsp;automatically&nbsp;as&nbsp;the&nbsp;first&nbsp;spatial&nbsp;index&nbsp;if&nbsp;exists.&nbsp;If&nbsp;there&nbsp;are&nbsp;more&nbsp;than&nbsp;one&nbsp;indices&nbsp;here&nbsp;and&nbsp;we&nbsp;need&nbsp;to&nbsp;set&nbsp;the&nbsp;property&nbsp;as&nbsp;the&nbsp;index&nbsp;we&nbsp;want&nbsp;to&nbsp;use.  
  
  
  
Hope&nbsp;it’s&nbsp;helped.  
  
  
  
Any&nbsp;questions&nbsp;please&nbsp;let&nbsp;me&nbsp;know.  
  
  
  
Regards,  
  
Peter

---

<div class="post-metadata">

**Author:** ![John1](https://community.thinkgeo.com/letter_avatar_proxy/v4/letter/j/e9bcb4/32.png) [@John1](https://community.thinkgeo.com/u/John1)\
**Post date:** [January 21, 2016, 3:24pm UTC](https://community.thinkgeo.com/t/not-using-sql-server-spatial-index/7188/3 "2016-01-21T15:24:39Z")

</div>

I've&nbsp;tried&nbsp;setting&nbsp;the&nbsp;SpatialIndexName&nbsp;as&nbsp;you&nbsp;suggest,&nbsp;but&nbsp;doing&nbsp;so&nbsp;did&nbsp;nothing&nbsp;to&nbsp;alter&nbsp;the&nbsp;query&nbsp;being&nbsp;sent&nbsp;to&nbsp;the&nbsp;database.&nbsp;&nbsp;As&nbsp;I&nbsp;mentioned&nbsp;in&nbsp;my&nbsp;original&nbsp;post,&nbsp;by&nbsp;inspecting&nbsp;the&nbsp;properties&nbsp;of&nbsp;the&nbsp;layer&nbsp;while&nbsp;running&nbsp;the&nbsp;app&nbsp;in&nbsp;Visual&nbsp;Studio,&nbsp;I&nbsp;could&nbsp;see&nbsp;that&nbsp;it&nbsp;already&nbsp;identified&nbsp;the&nbsp;first&nbsp;spatial&nbsp;index&nbsp;and&nbsp;was&nbsp;already&nbsp;setting&nbsp;it&nbsp;correctly;&nbsp;the&nbsp;spatial&nbsp;index&nbsp;hint&nbsp;just&nbsp;isn't&nbsp;making&nbsp;it&nbsp;into&nbsp;the&nbsp;SQL&nbsp;that&nbsp;is&nbsp;thrown&nbsp;at&nbsp;the&nbsp;database.

I&nbsp;think&nbsp;there&nbsp;is&nbsp;a&nbsp;further&nbsp;problem&nbsp;than&nbsp;just&nbsp;the&nbsp;failure&nbsp;to&nbsp;include&nbsp;the&nbsp;index&nbsp;hint.&nbsp;&nbsp;If&nbsp;I&nbsp;take&nbsp;the&nbsp;statement&nbsp;being&nbsp;sent&nbsp;to&nbsp;the&nbsp;database,&nbsp;modify&nbsp;it&nbsp;to&nbsp;add&nbsp;the&nbsp;spatial&nbsp;index&nbsp;hint&nbsp;(as&nbsp;immediately&nbsp;below),&nbsp;  
  
  
  
exec&nbsp;sp\_executesql&nbsp;N'SELECT&nbsp;[SECTION],[TOWNSHIP],[Shape].STAsBinary()&nbsp;as&nbsp;[Shape],[SECTION\_ID]&nbsp;FROM&nbsp;nationaldb.sde.SECTION&nbsp;**with(index(S868\_idx))**&nbsp;WHERE&nbsp;(geometry::STGeomFromText(@Geography,4269).STWithin(geometry::STGeomFromWKB(Shape.STAsBinary(),4269))=1);',N'@Geography&nbsp;varchar(38)',@Geography='POINT(-110.7703279915&nbsp;47.737159355451)'  
  
  
  
and&nbsp;manually&nbsp;execute&nbsp;it&nbsp;in&nbsp;SQL&nbsp;Server&nbsp;Management&nbsp;Studio,&nbsp;then&nbsp;I&nbsp;get&nbsp;the&nbsp;error&nbsp;"The&nbsp;query&nbsp;processor&nbsp;could&nbsp;not&nbsp;produce&nbsp;a&nbsp;query&nbsp;plan&nbsp;for&nbsp;a&nbsp;query&nbsp;with&nbsp;a&nbsp;spatial&nbsp;index&nbsp;hint.&nbsp;Reason:&nbsp;Could&nbsp;not&nbsp;find&nbsp;required&nbsp;binary&nbsp;spatial&nbsp;method&nbsp;in&nbsp;a&nbsp;condition.&nbsp;Try&nbsp;removing&nbsp;the&nbsp;index&nbsp;hints&nbsp;or&nbsp;removing&nbsp;SET&nbsp;FORCEPLAN.".  
  
  
  
My&nbsp;guess&nbsp;(and&nbsp;only&nbsp;a&nbsp;guess&nbsp;at&nbsp;this&nbsp;point)&nbsp;is&nbsp;that&nbsp;the&nbsp;"STWithin(geometry::STGeomFromWKB(Shape.STAsBinary(),4269))"&nbsp;portion&nbsp;of&nbsp;this,&nbsp;which,&nbsp;unless&nbsp;I'm&nbsp;misunderstanding&nbsp;this,&nbsp;is&nbsp;turning&nbsp;every&nbsp;shape&nbsp;in&nbsp;the&nbsp;table&nbsp;one-record-at-a-time&nbsp;into&nbsp;it's&nbsp;WKB&nbsp;representation&nbsp;and&nbsp;then&nbsp;from&nbsp;WKB&nbsp;into&nbsp;a&nbsp;Geometry&nbsp;object&nbsp;so&nbsp;that&nbsp;it&nbsp;can&nbsp;then&nbsp;test&nbsp;for&nbsp;whether&nbsp;a&nbsp;point&nbsp;is&nbsp;within&nbsp;it,&nbsp;prevents&nbsp;SQL&nbsp;Server&nbsp;from&nbsp;being&nbsp;able&nbsp;to&nbsp;use&nbsp;a&nbsp;spatial&nbsp;query&nbsp;hint&nbsp;(or&nbsp;the&nbsp;query&nbsp;optimizer&nbsp;from&nbsp;being&nbsp;able&nbsp;to&nbsp;use&nbsp;a&nbsp;spatial&nbsp;index&nbsp;for&nbsp;that&nbsp;matter)&nbsp;even&nbsp;if&nbsp;it&nbsp;is&nbsp;included.

---

<div class="post-metadata">

**Author:** ![Peter](https://community.thinkgeo.com/user_avatar/community.thinkgeo.com/peter/32/19042_2.png) [@Peter](https://community.thinkgeo.com/u/Peter)\
**Post date:** [January 22, 2016, 5:11am UTC](https://community.thinkgeo.com/t/not-using-sql-server-spatial-index/7188/4 "2016-01-22T05:11:48Z")

</div>

Hi&nbsp;John,  
  
  
  
We&nbsp;did&nbsp;some&nbsp;changes&nbsp;from&nbsp;6.0&nbsp;to&nbsp;9.0.&nbsp;In&nbsp;MsSql&nbsp;Server&nbsp;2008&nbsp;only&nbsp;intersects,&nbsp;disjoint,&nbsp;topologicalequal&nbsp;are&nbsp;available&nbsp;for&nbsp;geography.&nbsp;&nbsp;The&nbsp;code&nbsp;

> `Dim sectionFeatures As Collection(Of Feature)`  
> `Dim sectionColumns As String() = {"SECTION", "TOWNSHIP"}`  
> `sectionFeatures = sectionLayer.QueryTools.GetFeaturesContaining(inPoint, sectionColumns)`

  
will&nbsp;generate&nbsp;the&nbsp;different&nbsp;SQL&nbsp;statement&nbsp;between&nbsp;6.0&nbsp;and&nbsp;9.0&nbsp;as&nbsp;you&nbsp;attached&nbsp;before.&nbsp;In&nbsp;6.0&nbsp;the&nbsp;logical&nbsp;operation&nbsp;is&nbsp; **STIntersects&nbsp;** but&nbsp;it’s&nbsp;SWithin&nbsp;in&nbsp;9.0.&nbsp;Please&nbsp;call&nbsp;the&nbsp;**sectionLayer.QueryTools.GetFeaturesIntersecting(inPoint,&nbsp;sectionColumns)&nbsp;**method&nbsp;in&nbsp;9.0&nbsp;to&nbsp;get&nbsp;the&nbsp;same&nbsp;result&nbsp;as&nbsp;call&nbsp;the&nbsp;**sectionLayer.QueryTools.GetFeaturesContaining(inPoint,&nbsp;sectionColumns)**&nbsp;method&nbsp;in&nbsp;6.0.  
  
  
  
Thanks,  
  
Peter

---

<div class="post-metadata">

**Author:** ![John1](https://community.thinkgeo.com/letter_avatar_proxy/v4/letter/j/e9bcb4/32.png) [@John1](https://community.thinkgeo.com/u/John1)\
**Post date:** [January 22, 2016, 10:24am UTC](https://community.thinkgeo.com/t/not-using-sql-server-spatial-index/7188/5 "2016-01-22T10:24:58Z")

</div>

Thanks!&nbsp;&nbsp;That&nbsp;appears&nbsp;to&nbsp;work&nbsp;perfectly&nbsp;for&nbsp;me.&nbsp;  
&nbsp;&nbsp;  
&nbsp;Thanks,&nbsp;  
&nbsp;John

---

<div class="post-metadata">

**Author:** ![Peter](https://community.thinkgeo.com/user_avatar/community.thinkgeo.com/peter/32/19042_2.png) [@Peter](https://community.thinkgeo.com/u/Peter)\
**Post date:** [January 24, 2016, 6:43pm UTC](https://community.thinkgeo.com/t/not-using-sql-server-spatial-index/7188/6 "2016-01-24T18:43:04Z")

</div>

Hi&nbsp;John,&nbsp;  
&nbsp;&nbsp;  
&nbsp;Very&nbsp;glad&nbsp;to&nbsp;hear&nbsp;it&nbsp;worked.&nbsp;  
&nbsp;&nbsp;  
&nbsp;Thanks,&nbsp;  
&nbsp;Peter