I am attempting to move simple Geoprocessing routines from ESRI based processes to SQL Server. My assumption is that it will be far more efficient. For my initial test I am working on an intersection routine to associate overlapping linear data.
In my WCASING table I have 1610 records. I am trying to associate these Casings with their associated mains. I have ~277,000 Mains. I have ~1,600 Casings.
I am running the query below to get a general sense of how long it will take to find individual matches. This query returned 5 valid intersections in 40 seconds.
SELECT Top 5 [WCASING].[OBJECTID] As CasingOBJECTID, [WPUMPPRESSUREMAIN].[OBJECTID] AS MainObjectID, [WCASING].[Shape] FROM [dbo].[WPUMPPRESSUREMAIN] JOIN [WCASING] ON [WCASING].[Shape].STIntersects([WPUMPPRESSUREMAIN].[Shape]) = 1
My Primary questions;
Will this process faster depending on the search order?
- Finding 'A' inside of 'B' vs
- Finding 'B' inside of 'A'
- Initial return on 5 records from these datasets is that it does not matter
Will this process faster, if I first buffer to limit to a smaller main set and then search?
Can I use SQL Server Tuning to work with Geometry based queries?
SELECT WCASING.OBJECTID AS CasingOBJECTID, WPUMPPRESSUREMAIN.OBJECTID AS MainObjectID, WCASING.UFID AS UFID, WPUMPPRESSUREMAIN_IPS.UFID AS MainUFID, WCASING.SHAPE INTO WCASING_INTDefsV6 FROM WCASING with (index([FDO_ShapeWC])) INNER JOIN [WPUMPPRESSUREMAIN_IPS] ON [WPUMPPRESSUREMAIN_IPS].Shape.STIntersects(WCASING.SHAPE) = 1
This new query has improved definitions.
- Now both tables have spatial indexes
- Previously Casing Table (Smaller) did not have a Spatial Index
- It did contain a Non-Clustered Index
The query also has the with index statement.
The new query took 37 minutes. The old query took 44 minutes.
I was hoping for better results and will keep testing.