Creating Models › Target Servers for Physical Models › Database Property Editors › SQL Azure Property Editors › SQL Azure Database Support in CA ERwin Data Modeler › Spatial Index Properties › Define a SQL Azure Table Spatial Index

Define a SQL Azure Table Spatial Index

A spatial index is an index on a table based on spatial data in the table (a spatial column). Spatial data uses the new data types, GEOMETRY and GEOGRAPHY. Different tessellation schemes are used depending on the data type of the spatial column; a spatial column assigned the GEOMETRY data type uses the GEOMETRY_GRID tessellation scheme, while a spatial column assigned the GEOGRAPHIC data type uses the GEOGRAPHY_GRID tessellation scheme.

The table spatial index options provide a way to define how the index behaves.

Tessellation options let you specify the density of the grid at each level of a tessellation scheme and define the number of tessellation cells per object that you can use for a single spatial index object during the tessellation process. There are four levels for which you can specify density, and there are three density values you can select:

Low

Specifies to use the lowest density for the grid. Low equals 16 cells, or a 4x4 grid.

Medium

Specifies to use the medium density for the grid at the given level. Medium equals 64 cells, or an 8x8 grid.

High

Specifies to use the highest possible density for the grid at the given level. High equals 256 cells, or a 16x16 grid.

Boundary box options are used to set the four coordinates of the bounding box when GEOMETRY_GRID is your tessellation scheme.

Use the SQL Azure Table Spatial Index Editor in a SQL Azure physical model to define general options, tessellation options, and boundary box options for an index on a table that contains spatial columns. You can specify more than one spatial index on a spatial column.

To define a SQL Azure table spatial index

1. Right-click a table spatial index in the Model Explorer and click Properties.

   The SQL Azure Table Spatial Index Editor opens.

2. Select the table from the Table drop-down that contains the table spatial index that you want to define.
3. Select the table spatial index in the Navigation Grid that you want to define and work with the following options:

   Note: Click New on the toolbar to create a new table spatial index. Use the Enter filter text box to filter a very large list of indexes to quickly locate the index that you want to define.

   Name

   Specifies the name of the table spatial index. You can change the name of the table spatial index in this field.

   Note: Table spatial index names must be unique within the table but do not have to be unique within the database.

   Column

   Specifies the spatial column upon which the table spatial index is based.

   Type

   Displays the data type (and therefore the tessellation scheme) of the spatial column. You do not explicitly define the data type here; the data type of the spatial column is defined in the SQL Azure Column Editor.

   Note: A spatial column assigned the GEOMETRY data type uses the GEOMETRY_GRID tessellation scheme, while a spatial column assigned the GEOGRAPHIC data type uses the GEOGRAPHY_GRID tessellation scheme.

   Generate

   Generates SQL during forward engineering. Clear the check box if you do not want to generate SQL.

4. Click the General tab and specify the following Spatial Index options:

   No Recompute

   Specifies whether distribution statistics are recomputed. Select the option from the drop-down.

   Drop Existing

   Specifies whether a named preexisting table spatial index is dropped and rebuilt. Select the option using the check box.

5. Specify the density of the grid at each level of a tessellation scheme and the cells per object value using the following tessellation options:

   Level 1 Density Type

   Lets you specify the density of the first (top) level grid. Select either Low, Medium, or High for the density level from the drop-down.

   Level 2 Density Type

   Lets you specify the density of the second level grid. Select either Low, Medium, or High for the density level from the drop-down.

   Level 3 Density Type

   Lets you specify the density of the third level grid. Select either Low, Medium, or High for the density level from the drop-down.

   Level 4 Density Type

   Lets you specify the density of the fourth level grid. Select either Low, Medium, or High for the density level from the drop-down.

   Note: Low density equals 16 cells (4x4 grid), Medium density equals 64 cells (8x8 grid), and High density equals 256 cells (16x16 grid).

   Cells Per Object

   Specifies the number of tessellation cells per object that can be used for a single spatial object in the index by the tessellation process. Enter a numerical value.

6. Specify the following Boundary Box options:

   Note: Boundary Box options are only available if the spatial column upon which your index is based is Geometric.

   Bound Lower Left X

   Lets you specify the value for the x-coordinate of the lower-left corner of the bounding box. Enter a numerical value.

   Bound Lower Left Y

   Lets you specify the value for the y-coordinate of the lower-left corner of the bounding box. Enter a numerical value.

   Bound Upper Right X

   Lets you specify the value for the x-coordinate of the upper-right corner of the bounding box. Enter a numerical value.

   Bound Upper Right Y

   Lets you specify the value for the y-coordinate of upper-right corner of the bounding box. Enter a numerical value.

7. (Optional) Click the Comment tab and enter any comments that you want to associate with the table spatial index.
8. (Optional) Click the UDP tab to work with user-defined properties for the table spatial index.
9. (Optional) Click the Notes tab to view history information and view or edit user notes.
10. Click Close.

    The table spatial index is defined and the SQL Azure Table Spatial Index Editor closes.

More information:

Add a Comment in a Property Editor

Add a UDP in a Physical Property Editor

Edit User Notes or View Object History