How To: Manually calculate the spatial index within SQL Server 2005


Prior to ArcSDE 9.2, the spatial index was not automatically calculated. Instructions provided describe the steps to manually calculate the spatial index for SQL Server 2005.


  1. Open Microsoft SQL Server Management Studio and connect using either Windows Authentication or SQL Server Authentication.
  2. Click 'New Query'.
  3. Run the following query:

    use <database>
    SELECT layer_id FROM sde.sde_layers WHERE table_name = '<feature class>'
    [O-Image] query1

    Recall the 'layer_id' value returned from this query.
  4. Run the following query:

    use vector
    SELECT avg (((emaxx-eminx) + (emaxy-eminy)) / 2) avg_edge FROM <owner>.<layer_id>

    Enter in the value returned from the first query for <layer_id>
    [O-Image] O-query2

    A good starting size for a spatial index grid is roughly around 3 to 5 times the size of the avg_edge value.