I recently learned a valuable lesson about query syntax and how SQL Server spatial indexes are used in queries that condition on spatial methods.  The lesson I learned comes down to using 1 or “True” when evaluating a geometry method with Boolean output.

As an example, I may want to use STIntersects() to determine whether two geometry instances intersect with each other.

Using a query such as the following, SQL Server does not use a spatial index on the table.

SELECT TOP 1 A.PolygonA
           , B.PolygonB
FROM dbo.SpatialIndexDemoA AS A
 , dbo.SpatialIndexDemoB AS B
WHERE A.PolygonA.STIntersects(B.PolygonB) = 'True'
ORDER BY A.SpatialIndexDemoAID

However, using the query below with one small change, SQL Server does use the spatial index.

SELECT TOP 1 A.PolygonA
 , B.PolygonB
FROM dbo.SpatialIndexDemoA AS A
 , dbo.SpatialIndexDemoB AS B
WHERE A.PolygonA.STIntersects(B.PolygonB) = 1
ORDER BY A.SpatialIndexDemoAID

Notice the only difference in the two queries is the first one used ‘True’ and the second used 1  in the WHERE clause when determining STIntersects().  Both queries return the same results.  Using ‘True’ is valid syntax.  But using ‘True’ instead of 1 alters the execution plan such that it does not use an available spatial index.

Here is the execution plan of the first query using “True.”  Trust me for now that both tables in the query have a spatial index on their respective geometry fields.  The plan looks simple.  But notice it does not use any spatial indexes.

SpatialIndex_ExecutionPlanNoIndex
Execution Plan – Spatial Index Not Used

Now, here is the execution plan of the second query using 1 in the WHERE clause.  The plan is much more complex, so I’m only showing a portion of it.  But you’ll notice it does, in fact, use a spatial index.

SpatialIndex_ExecutionPlanWithIndex
Execution Plan – Spatial Index Used

When I run the two queries in the same batch, note the relative cost of each query.  In this case, the query not using the spatial index accounted for 96% of the total query cost!

SpatialIndex_BatchExecutionPlan.jpg
Batch Execution Plan – Relative Cost

Of course, I want SQL Server to use the spatial index to improve query performance.

The Lesson Learned

The lesson here is when evaluating geometry methods, SQL Server only uses available spatial indexes if the query uses 1 instead of “True”, “False”, or 0.

This may seem obvious from reading the MSDN documentation.  However, I didn’t realize how much difference using 1 instead of “True” would make.

Spatial Indexes Overview – The “Methods Supported By Spatial Indexes” section describes how to ensure your query will use an available spatial index.

Try it yourself

I’ve developed a brief demo you can use to try this.  The script below creates two tables, each with a geometry data type field and a spatial index on the geometry field.

CREATE TABLE [dbo].[SpatialIndexDemoA]
(
 [SpatialIndexDemoAID] INT NOT NULL IDENTITY(1,1),
 PolygonA GEOMETRY NOT NULL
 CONSTRAINT PK_SpatialIndexDemoAID PRIMARY KEY CLUSTERED (SpatialIndexDemoAID)
)

GO

CREATE SPATIAL INDEX SIndx_SpatialIndexDemoA_PolygonA
 ON SpatialIndexDemoA(PolygonA) 
 WITH ( BOUNDING_BOX = ( xmin=-100, ymin=-100, xmax=100, ymax=100 ) );

GO

CREATE TABLE [dbo].[SpatialIndexDemoB]
(
 [SpatialIndexDemoBID] INT NOT NULL IDENTITY(1,1),
 PolygonB GEOMETRY NOT NULL
 CONSTRAINT PK_SpatialIndexDemoBID PRIMARY KEY CLUSTERED (SpatialIndexDemoBID)
)

GO

CREATE SPATIAL INDEX SIndx_SpatialIndexDemoB_PolygonB
 ON SpatialIndexDemoB(PolygonB) 
 WITH ( BOUNDING_BOX = ( xmin=-100, ymin=-100, xmax=100, ymax=100 ) );

The next script populates the tables with sample data.  I used integer values available in sys.columns to create a point instance at a given X,Y coordinate, then added a buffer around the point to create a shape resembling a circle.

/* Load sample data into SpatialIndexDemoA */
INSERT INTO dbo.SpatialIndexDemoA
(
 PolygonA
)
SELECT TOP 100000 PolygonA = geometry::STGeomFromText('POINT(' + CAST([C1].[precision] AS VARCHAR(5)) + ' ' + CAST([C1].[scale] AS VARCHAR(5)) + ')', 4326).STBuffer([C1].[column_id])
FROM sys.columns AS C1,
 sys.columns AS C2
WHERE [C1].[precision] > 0

/* Load sample data into SpatialIndexDemoB */
INSERT INTO dbo.SpatialIndexDemoB
(
 PolygonB
)
SELECT TOP 100000 PolygonB = geometry::STGeomFromText('POINT(' + CAST([C1].[precision] AS VARCHAR(5)) + ' ' + CAST([C1].[scale] AS VARCHAR(5)) + ')', 4326).STBuffer([C1].[column_id])
FROM sys.columns AS C1,
 sys.columns AS C2
WHERE [C1].[precision] > 0

Use the SELECT queries below to determine the cases in which SQL Server uses a spatial index.

/* 1 */
SELECT TOP 1 A.PolygonA
 , B.PolygonB
FROM dbo.SpatialIndexDemoA AS A
 , dbo.SpatialIndexDemoB AS B
WHERE A.PolygonA.STIntersects(B.PolygonB) = 1
ORDER BY A.SpatialIndexDemoAID

/* 'True' */
SELECT TOP 1 A.PolygonA
 , B.PolygonB
FROM dbo.SpatialIndexDemoA AS A
 , dbo.SpatialIndexDemoB AS B
WHERE A.PolygonA.STIntersects(B.PolygonB) = 'True'
ORDER BY A.SpatialIndexDemoAID

/* <> 1 */
SELECT TOP 1 A.PolygonA
 , B.PolygonB
FROM dbo.SpatialIndexDemoA AS A
 , dbo.SpatialIndexDemoB AS B
WHERE A.PolygonA.STIntersects(B.PolygonB) <> 1
ORDER BY A.SpatialIndexDemoAID

/* 0 */
SELECT TOP 1 A.PolygonA
 , B.PolygonB
FROM dbo.SpatialIndexDemoA AS A
 , dbo.SpatialIndexDemoB AS B
WHERE A.PolygonA.STIntersects(B.PolygonB) = 0
ORDER BY A.SpatialIndexDemoAID

/* False */
SELECT TOP 1 A.PolygonA
 , B.PolygonB
FROM dbo.SpatialIndexDemoA AS A
 , dbo.SpatialIndexDemoB AS B
WHERE A.PolygonA.STIntersects(B.PolygonB) = 'False'
ORDER BY A.SpatialIndexDemoAID

 

 

 

Advertisements