FiguRe 14-20 Grid index in VS .NET

Encode QR Code in VS .NET FiguRe 14-20 Grid index

Each cell in each level of the grid is given an internal identifier . These identifiers are structured so that the SQL Server engine can quickly determine whether a cell at one level

either contains or is contained by another cell . In addition to these cells, an additional cell is added that covers the entire space outside of the bounding box . This allows for graceful (although inefficient) handling of objects that fall outside the bounding box . With this strategy, any spatial object can be represented by a set of disjoint cells that cover the object . The region covered by this set of cells is a superset of that covered by the original object . Note that because the entire bounding box is covered by each level of the index, there is more than one set of such cells . For example, we could cover an object using a small number of cells at the top level of the index, by a large number of cells at the bottom level, or by some combination . Using many small cells will more closely match the object at the expense of using more cells . We ll come back to how these are chosen in a moment . To understand how these cells are used, let us reconsider the previous query . The STIntersects filter can now be split into a primary and secondary filter . Although the secondary filter essentially remains the original predicate, the primary filter becomes a test to see whether @point and the ZIP code region have any overlapping cells . If two objects have no overlapping cells, they cannot possibly intersect, and the secondary filter need not be run . But although we have split the filter, we do not yet have an index . When a spatial index is created on a column, SQL Server creates an internal table to contain the cells for each object in the column . In addition to a cell, each row contains a reference back to the original table . This internal table, called the spatial index, is itself indexed on the cell identifier using a standard B-tree . Given a cell, the system can now probe the spatial index to quickly find which spatial objects share the cell, and it can also quickly retrieve those spatial objects from the original table . With this structure, we can now see how a spatial index on the GEOM column of the Zipcodes table would be used to answer the query above . When the query object @point is seen, the system determines its cells . This computation is performed once for the query . The system then probes the spatial index with each cell, retrieving the spatial objects that share the cell . After duplicate rows are eliminated, the remaining GEOM values (few, we hope) are tested one by one against @point using the expensive STIntersects() predicate .

Before we create a spatial index, we must first ensure that the table we are indexing has a primary key . The reason for this can be seen in the procedure we outlined earlier: the spatial index needs to be able to refer back to the base table, and this is done by storing the primary key of the base table in the index . Once we have a primary key, creating a spatial index is relatively simple . For example, we can create a spatial index for the GEOM column of the Zipcodes table with the following command:

CREATE SPATIAL INDEX ZIPCODES_GEOM_IDX ON Zipcodes(GEOM) USING GEOMETRY_GRID WITH ( BOUNDING_BOX = ( XMIN= 2801277, YMIN= 217712, XMAX= 13305064, YMAX= 6446996), GRIDS = (MEDIUM, MEDIUM, MEDIUM, MEDIUM), CELLS_PER_OBJECT = 16 );

Let s pick this apart . You should recognize the start of the statement as the familiar CREATE INDEX with the addition of the keyword SPATIAL . This lets the system know that a spatial index is being created . The USING GEOMETRY GRID tells the system what kind of spatial index to use . Currently, GEOMETRY_GRID, the default for GEOMETRY columns, is the only choice for GEOMETRY columns, although this could be expanded in the future . The WITH clause lists three parameters . For GEOMETRY, the BOUNDING_BOX is required, and sets the range to be indexed . This should be set to fully encompass the objects in the column . Failing to do so will not affect results, but it can dramatically affect performance . To calculate the BOUNDING_BOX specification that fully encompasses the objects in a GEOMETRY column, you can use the user-defined aggregate function GeometryEnvelopeAggregate(), which is included in the Sample_USA database, as follows:

DECLARE @boundBox GEOMETRY; SELECT @boundBox = dbo.GeometryEnvelopeAggregate(GEOM) FROM Zipcodes; SELECT FLOOR(@boundBox.STPointN(1).STX) AS MinX, FLOOR(@boundBox.STPointN(1).STY) AS MinY, CEILING(@boundBox.STPointN(3).STX) AS MaxX, CEILING(@boundBox.STPointN(3).STY) AS MaxY;

This generates the following output:

XMIN ------2801277 YMIN -----217712 XMAX -------13305064 YMAX ------6446996

This method of calculating the bounding box works because GeometryEnvelopeAggregate returns a rectangular polygon whose lower left-hand corner (and first point) contains the minimum x- and y-coordinate values and whose upper right-hand corner (and third point) contains the maximum x- and y-coordinate values . We use the T-SQL FLOOR() and CEILING() functions to enlarge the box slightly, truncating the minimum values and rounding the maximum values up . Finally, the GRIDS and CELLS_PER_OBJECT clauses describe the number of divisions at each level of the index, as well as the maximum number of cells the system should use to represent each object . Setting the GRIDS values to HIGH makes a finer index; setting them