SQL Server 2008 Spatial Data Application Series II: Spatial Index Foundation

Original: SQL Server 2008 Spatial Data Application Series II: the Spatial Index Foundation

In the previous blog post, we learned some basic knowledge about geographic information, and also learned about the spatial reference system, including the Earth ellipsoid, Datum, Prime Meridian, measurement unit, projection and other related theoretical knowledge, We can use these spatial reference system components to define a series of geometric images applied to the Earth's space to represent specific functions in the geospatial space, representing a particular point of location on the earth. This article mainly introduces the concept of geospatial index and the application of spatial index in Microsoft SQL Server R2.

One, spatial index

A spatial index is a data structure that is arranged in a certain order based on the position and shape of a spatial object or a spatial relationship between spatial objects, which contains the summary information of a spatial object. As an auxiliary spatial data structure, spatial index is between space operation algorithm and space object, it is eliminated by filtering function, and the space object which is independent of specific space operation is excluded, thus improving the speed and efficiency of space operation.

To the definition of the encyclopedia: in order to facilitate the positioning of space targets and various spatial data operations, the structure of the data is organized and stored by the location and shape of the feature or target, or some spatial relationship between the spatial objects.

More information about spatial indexes requires a deep understanding of EPSG and Srid, this article mainly introduces the implementation of the spatial data features based on Microsoft SQL Server 2008, so it does not introduce these basic theoretical knowledge points in detail, and is interested in having access to the recommended links at the end of the article. Learn more about the EPSG nuclear srid.

The spatial data type in SQL Server 2008 is executed as a CLR system type. SQL Server 2008 increases the maximum size of the CLR types in the database, increasing the 8000-byte limit that was originally in SQL Server 2005, which allows it to store very complex spatial data elements, such as polygons defined by many points. By storing spatial data in relational tables, SQL Server 2008 makes it possible to combine spatial data into any other business data type, eliminating the need to maintain a separate maintenance requirement for a single spatial data store and making it possible to do high-performance queries that do not require data from multiple external sources to be combined.

Support for spatial indexes in SQL Server 2008 further enhances query operations on spatial data. You can retrieve spatial data with a suitable multilevel grid index integrated in the SQL Server database engine. A spatial index contains a grid-based hierarchy in which each level of the index is subdivided into a grid region defined by the previous level. A probabilistic model diagram of a spatial index given by the official:

　　　　　　　　

The SQL Server query optimizer makes cost-based decisions about which indexes to use for a given query, and because spatial indexes are an integral part of the database engine, you can make cost-based decisions about whether to use special spatial indexes, just like other indexes.

Ii. spatial Reference System for SQL Server 2008

Microsoft SQL Server 2008 's Spatial system table (actually a system view) "Sys.spatial_reference_systems" stores detailed support for all geodetic spatial reference system standards, Each row of data in the table corresponds to a unique spatial reference system that can be used to define any form of spatial data in SQL Server 2008 based on these spatial reference systems.

Select *  fromSys.spatial_reference_systems

The system table "Sys.spatial_reference_systems" actually only stores the reference system that is applied to the geo-reference coordinates in SQL Server 2008, except that the reference system standards listed in this table can be used by the spatial reference system. You can also define spatial data based on any geographic projection based on these criteria.

Iii. Spatial Reference Text Format (WKT)

Well-known text (WKT) representation of Spatial Reference systems: As the name implies, see the coordinate reference system in a well-known text form, It is consistent with the coordinate text representation model of EPSG (2005 has been changed). The wkt string is used to determine the description of the components of a spatial reference system, and its definition consists mainly of the following 10 things:

1, a general coordinate system name

2, a geographic graphics coordinate system name
3, a plane definition
4, the name of an ellipsoid. Long axle (semi-major axis) and inverse flattening rate (inverse flattening)
5. Prime meridian name and its offset from Greenwich Meridian
6. Projection method type (e.g. horizontal IE)
7, projection parameter list (such as central Meridian, etc.)
8, the name of a unit and its conversion parameters to meters and radians units
9, the name and order of the Axis
10. Coding in a predefined authoritative coordinate system (e.g. EPSG)

SelectWell_known_text
fromSys.spatial_reference_systems
whereauthorized_spatial_reference_id=4326
andAuthority_name='EPSG'

Based on the Sys.spatial_reference_systems table defines a number of different spatial reference system WKT description, as the above SQL statement query is our usual in the map application development of the most used epsg:4326 space Reference system standards, then the WKT as follows:

Geogcs["WGS 84",
datum["World Geodetic System 1984",
ellipsoid["WGS", 6378137, 298.257223563]],
Primem["Greenwich", 0],
UNIT["Degree", 0.0174532925199433]
]

The above KWT string determines the component element description of the epsg:4326 spatial reference system, which uses four spatial configurations, namely: Datum (Datum), ellipse (ellipsoid), Prime Meridian (PRIMEM), and Unit (unit of measure). About these spatial reference system parameters are described in the previous article, here is not to do the repetition, the meaning of the above kwt string expression is roughly like this: "WGS 84 coordinate system, using the 1984 World Geographic Coordinate systems standard, the geographical standard radius: 6378137 ...".

Iv. Spatial index of SQL Server 2008

SQL Server 2008 introduces support for spatial data and spatial indexes. A spatial index is an extended index that allows you to index a spatial column. A spatial column is a table column that contains data for a spatial data type, such as geometry or geography. The topics in this section describe spatial indexes.

In SQL Server 2008, spatial indexes (stored in: sys.spatial_indexes tables) are constructed using B-trees, which means that these indexes must represent the two-dimensional spatial data in the linear order of the B-tree. Therefore, before the data is read into the spatial index, SQL Server 2008 first implements a layered, homogeneous decomposition of the space. The index creation process decomposes the space into a level four grid hierarchy. These levels refer to level1th (Top level), level 2nd, 3rd level, and 4th level.

Each successive level is further decomposed to its upper level, so each cell at the previous level contains the entire grid at the next level. At a given level, all meshes have the same number of cells along two axes (for example, 4x4 or 8x8), and the cells are the same size. Shows the case where the upper-right corner cell of each level of the grid hierarchy is decomposed into a 4x4 grid. In fact, all units are decomposed in this way. So, for example, a total of 65,536 fourth-level cells will be generated in a 4x4 grid that breaks down a space into four levels. Spatial decomposition for spatial indexes is independent of the unit of measure used by application data.

　　　　　　　　

The cells of a grid hierarchy are numbered linearly using a variety of Hilbert space fill curves. However, for demonstration purposes, here is a simple numbering by row, not a number actually generated by the Hilbert curve. In, several polygons representing the building and lines representing the streets have been placed in a 4x4 1-level grid. The 1th level unit is numbered 1 to 16, starting with the cell in the upper-left corner.

　　　　　　　　

The number of cells along the grid axis determines the density of the grid: The larger the number of cells, the greater the density of the mesh. For example, the 8x8 grid (which produces 64 cells) is denser than the 4x4 grid (which produces 16 cells). Grid density is defined on a per-level basis. Low number of grid hives: 4x4 = 16, Medium 8x8 = 64, high 16x16 = 256, default setting all levels are medium .

You can control the decomposition process by specifying a non-default grid density. For example, specifying different grid densities at different levels can be useful for optimizing indexes based on the size of the index space and the objects in the spatial column. The grid density of the spatial index is displayed in the Level_1_grid, Level_2_grid, Level_3_grid, and Level_4_grid columns of the Sys.spatial_index_tessellations catalog view.

After the index space is decomposed into a grid hierarchy, the spatial index reads the data in the spatial column line by row. After reading the data for a spatial object (or instance), the spatial index performs a "split procedure"for that object. The tessellation process makes the object fit in the grid hierarchy by linking the object to itscontact with the set of grid cells (the "contact unit"). Starting at level 1th of the grid hierarchy, the tessellation process processes the entire level in a "breadth-first" manner. Where possible, this process can process all four levels consecutively, one level at a time.

Note: The above content intercepts the SQL Server 2008 Help documentation, please see the online Help documentation for details.

V. Related information

[1], Epsg:http://en.wikipedia.org/wiki/european_petroleum_survey_group

[2], Srid:http://en.wikipedia.org/wiki/srid

[3], Wkt:http://en.wikipedia.org/wiki/well-known_text

[4], Spatial index Introduction: http://www.cnblogs.com/terryfeng/archive/2009/05/27/1490803.html

　　

Related instructions

This article is a study note article, willing to work with interested people to learn to communicate. Welcome to reprint, in order to preserve the author's passion for creation, but please mark the original text of this article connected in a prominent position.

Author: Beniao

Article source: http://beniao.cnblogs.com/or http://www.cnblogs.com/

SQL Server 2008 Spatial Data Application Series II: Spatial Index Foundation