1

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
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.