The criteria developed might be a bit primitive in its assumptions, but it was refined based on the results gained from an Esri ArcGIS Desktop testing tool, PerfQA. There are other approaches to measuring the performance of spatial indices.
As the data in question is all used in GIS processes, two key considerations are rendering time and selection time.
The data sets used followed the Esri rules for geometry data:
- Only one geometry type per table (feature class): either point, line or polygon
- All rows have the same coordinate system (SRID)
There are only three categories of spatial data that are considered, which should capture any data set that follow the stipulated rules:
- Point data - treat all point data sets the same, regardless of row count
- Line data of any row count; or, polygon data with fewer than 50,000 rows
- Polygon data with 50k or more rows
For each category, above, the basic parameters for the spatial index applied include:
- GRIDS = (HIGH,HIGH,HIGH,HIGH)
- GRIDS = (LOW,MEDIUM,HIGH,HIGH) and CELLS_PER_OBJECT = 8192
- GEOMETRY_AUTO_GRID and CELLS_PER_OBJECT = 20
So, for example, the T-SQL code to build a spatial index "SIdx_Points_shape" on a point table "Points" with the GEOMETRY column "Shape" would look like:
Create Spatial Index SIdx_Points_shape On
Points (Shape) With (
BOUNDING_BOX = (xmin=2406292.490931, ymin=6884084.490682,
Grids = (HIGH, HIGH, HIGH, HIGH),PAD_INDEX = OFF)
Some settings applied to all spatial indices include:
- PAD_INDEX = OFF
- Define a BOUNDING_BOX
I am sure that these categories and settings could be refined. But after using these settings for the past three years, performance has been decent.
In PowerShell or python (using arcpy and pyodbc), one could build a generic create index function to drop and rebuild spatial indices, incorporating these criteria.
For example, in python, the arcpy.Describe() shapeType option can give you the geometry type. Or, you can get it from STGeometryType(), via pyodbc. An example of the arcpy approach is:
shpTyp = (arcpy.Describe(fc).shapeType)
This information, plus the row count (again, using T-SQL or arcpy methods to acquire), can be used to construct a straight-forward test for a data set in python code:
if shpTyp == 'Point':
elif shpTyp == 'Polyline' or (shpTyp=='Polygon' and ct < 50000):
elif shpTyp == 'Polygon' and ct >= 50000:
The cursor.execute() statement run a particular set of T-SQL code, depending on the geometry type and the row count. Each query implements the spatial index criteria described in (1)-(3), above.
Thanks to boomphisto for a deep dive into testing spatial index performance.