[GIS] MSSQL performance with Geography (point within polygon)

pointsql serverst-intersectswell-known-binarywithin

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

The answer in this instance was related to the SRID. What was actually happening was the DB was attempting transforms on the fly.

Contrary to the answer that Vince gave I was able to correct the SRID and perform the query on the table with 20,000,000 rows in less than 1 second.

The SRID of the input polygon needed to match the SRID of the stored object (Which I though it did, but it didn't.)

Related Question