Spatial Indexes (SQL Server)

SQL Server 2008 and higher supports spatial data types and indexes. PowerDesigner supports these new features through table indexes with the type set to SPATIAL.

Creating a Spatial Index

To create a spatial index:

Create a table containing a column of type geography or geometry.
Create a standard index and, on the General tab, select SPATIAL in the Type field. The Columns tab is renamed to Spatial Options.
Click the Spatial Options tab, select your spatial column in the Indexed column field, and complete the remaining properties.

Spatial Index Properties

You can modify an object's properties from its property sheet. To open a spatial index property sheet, double-click its Browser entry. The following extended attributes are available on the Spatial Options tab:

Name	Description
Indexed column	Specifies the spatial column on which the index is based Scripting name: IndexedColumn
Tessellation scheme	Specifies the tessellation scheme for the spatial index. Scripting name: TesselationType
Bounding box	Specifies a numeric four-tuple that defines the four coordinates of the bounding box: the x-min and y-min coordinates of the lower, left corner, and the x-max and y-max coordinates of the upper right corner. Scripting name: BoundingBoxDefn
Cells per object	Specifies the number of tessellation cells (any integer between 1 and 8192, inclusive) per object that can be used for a single spatial object in the index by the tessellation process. Scripting name: CellsPerObject
Grids	Specifies the density of the grid at each level of a tessellation scheme. Scripting name: GridsDefn
Fill factor	Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild. Scripting name: FillFactor
Index padding	Specifies index padding. Scripting name: PadIndex
Max degree of parallelism	Overrides the max degree of parallelism configuration option for the duration of the index operation. Use MAXDOP to limit the number of processors (up to 64) used in a parallel plan execution. Scripting name: MaxDop
Allow row locks	Specifies whether row locks are allowed. Scripting name: AllowRowLocks
Allow page locks	Specifies whether page locks are allowed. Scripting name: AllowPageLocks
Store sort result	Specifies to store temporary sort results in tempdb. Scripting name: SortInTempDB
Do not recompute statistics	Specifies to recompute distribution statistics. Scripting name: StatisticsNoRecompute
Drop if exist	Specifies that the named, preexisting clustered, nonclustered, or XML index is dropped and rebuilt. Scripting name: DropExisting