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