2

Let’s say an organization manages a lot of GIS data stored in Sql Server and leverages Esri software. To ensure that users can access the data with reasonable speed, I want to make sure the spatial indices are tuned for performance.

What criteria should I use to configure the parameters of a spatial index for a SQL Server table that has a GEOMETRY column?

I am looking for “rules of thumb” that are widely applicable to many data sets of varying specifications.

  • Please can you edit your question to give a bit more background detail about what you're trying to do, and why? – Midavalo Oct 19 '17 at 17:35
  • As requested, more details were included in the question, which I also answered. The response includes the details that were meant to be addressed. It is my hope that someone looking for this information won’t have to work as hard as I did to find it. Better still, others might offer suggestions to further improve the recommendations. – gischimp Oct 26 '17 at 18:45
2

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:

  1. Point data - treat all point data sets the same, regardless of row count
  2. Line data of any row count; or, polygon data with fewer than 50,000 rows
  3. Polygon data with 50k or more rows

For each category, above, the basic parameters for the spatial index applied include:

  1. GRIDS = (HIGH,HIGH,HIGH,HIGH)
  2. GRIDS = (LOW,MEDIUM,HIGH,HIGH) and CELLS_PER_OBJECT = 8192
  3. 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,
                  xmax=2598087.473509, ymax=7096654.967680),
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':
  cursor.execute(qryPt)
  cnxn.commit()
elif shpTyp == 'Polyline' or (shpTyp=='Polygon' and ct < 50000):
  cursor.execute(qryLP)
  cnxn.commit()
elif shpTyp == 'Polygon' and ct >= 50000:
  cursor.execute(qryP)
  cnxn.commit()
else:

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.

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.