Thursday, July 9, 2015

SQL - Spatial Indexes

Spatial Indexes
SQL Server 2008 introduces support for spatial data and spatial indexes. A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometry or geography data type. The spatial index reduces the number of objects on which relatively costly spatial operations need to be applied.

Spatial indexes work differently depending on the data source. The spatial index is used during editing and the loading of data.

A spatial index is defined on a table column that contains spatial data (a spatial column). Each spatial index refers to a finite space. For example, an index for a geometry column refers to a user-specified rectangular area on a plane.

Spatial Data in SQL Server has special indexing because it has to perform specialized functions. This includes support for a planar spatial data type, geometry, which supports geometric data—points, lines, and polygons—within a Euclidean coordinate system. The geography data type represents geographic objects on an area on the Earth's surface. Database systems such as IBM, DB2, and Oracle have been supporting spatial data for some time. 

SQL Server supports two spatial data types:
Geometry: Stores the X and Y coordinates that represents lines, points, or polygons.
Geography: Stores the latitude and longitude coordinates that represent lines, points, or polygons.

A spatial index can be created only on a spatial column. We can create spatial indexes on any spatial column in a table that supports spatial indexes as well as we can create multiple spatial indexes on a given spatial column.

In SQL Server, spatial indexes are built using B-trees, which means that the indexes must represent the 2-dimensional spatial data in the linear order of B-trees.
As we know that B-tree index contains at least two levels: the root and the leaf where root is the top most node and can have multilevel child nodes.

Therefore, before reading data into a spatial index, SQL Server implements a hierarchical uniform decomposition of space. The index-creation process decomposes the space into a four-level grid hierarchy. These levels are referred to as level 1 (the top level), level 2, level 3, and level 4.

Use tempdb

----- create table earth
----- composite column as Geolocation
CREATE TABLE [dbo].[Earth] (
    [ID]                INT IDENTITY(1, 1) primary key,
    [LandmarkName]      VARCHAR(100),
    [Location]          VARCHAR(50),
    [Latitude]          FLOAT,
    [Longitude]         FLOAT,
    [GeoLocation] AS ( geography::Point([Latitude], [Longitude], 4326)) PERSISTED


---- create spatial index
CREATE SPATIAL INDEX ind_earth_GeoLocation
   ON [dbo].[Earth] (GeoLocation)
     , CELLS_PER_OBJECT = 64
     , PAD_INDEX = OFF

----- insert data into earth table
INSERT INTO [dbo].[Earth] ( [LandmarkName], [Location], [Latitude], [Longitude] )
VALUES ( 'Statue of Liberty', 'New York, USA', 40.689168,-74.044563 ),
       ( 'Eiffel Tower', 'Paris, France', 48.858454, 2.294694),
       ( 'Leaning Tower of Pisa', 'Pisa, Italy', 43.72294, 10.396604 ),
       ( 'Great Pyramids of Giza', 'Cairo, Egypt', 29.978989, 31.134632 ),
       ( 'Sydney Opera House', 'Syndey, Australia', -33.856651, 151.214967 ),
       ( 'Taj Mahal', 'Agra, India', 27.175047, 78.042042 ),
       ( 'Colosseum', 'Rome, Italy', 41.890178, 12.492378 )

--- drop table Landmark
drop table dbo.Earth

Restrictions on Spatial Indexes
Note: Primary key metadata cannot be changed while a spatial index is defined on a table.
  • A spatial index can be created only on a column of type geometry or geography.
  • Spatial indexes can be defined only on a table that has a clustered primary key. The maximum number of primary key columns on the table is 15.
  • The maximum size of index key records is 895 bytes. Larger sizes raise an error.
  • Spatial Indexes cannot be specified on indexed views. Creating more than one spatial index on the same spatial column can be useful. An index build cannot make use of available process parallelism.
Type of Indexes in SQL

No comments:

Post a Comment