Sql-server – MSSQL Spatial performance (point in polygon)

performancequery-performancespatialsql server

Discolsure: This is a cross post from GIS, however the group over there isn't necessarily focused

I have a query, below, that works – however its very slow, looping through roughly 24,000 points per second. This is a rather high end production DB server with MSSQL and SDE, and in other instances a 10,000,000 point shape can be loaded in to view in ArcMap in ~5 seconds.

The question is: Can the SQL query be structured to perform any better? My polygon is being given as points, and being converted to geography. My data has a Shape column that according to MSSQL is stored as geography. In order to get the query to work I'm asking every field to be converted via STAsText – and I think that's a performance hangup. For the life of me I cannot seem to get the query to work any other way. If I remove this, the query fails to run.

Ultimately a similar query will be run on a table with 20,000,00 points, so the performance as it is currently is not really acceptable (That would be 833 seconds).

The code as it is now:

DECLARE @geog1 geography;
SET @geog1 = (geography::STPolyFromText('POLYGON((153.013377 -27.385831, 153.018303 -27.399108, 153.030438 -27.395896, 153.028725 -27.386901, 153.013377  -27.385831))', 4326));

SELECT * FROM [GISDB].[GISUSER].[POINTS]
WHERE @geog1.STIntersects([POINTS].[Shape].STAsText()) = 1;

.

The data in the shape field is stored like such (Well Known Binary, I'm guessing)

SELECT [Shape] from [GISDB].[GISUSER].[POINTS]
WHERE [OBJECTID] = '100000';

    Returns: 0xBB100000010C300A689B1B4B3BC0400E645B061F6340

Best Answer

Firstly, make sure there is a spatial index on the [Shape] column.

Secondly, there is no need to call STAsText() in the WHERE clause since that will force each row to convert to string unnecessary (and disqualify the use of any spatial indexes I think).

Lastly, inspect the execution plan to see if the spatial index is used. You may have to use a index hint since SQL Server don't always choose the spatial index in my experience. See the last example here.

Related Question