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
go
----- 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
)
GO
---- create spatial index
CREATE SPATIAL
INDEX ind_earth_GeoLocation
ON
[dbo].[Earth] (GeoLocation)
USING
GEOGRAPHY_GRID
WITH
(
GRIDS=(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH)
,
CELLS_PER_OBJECT =
64
,
PAD_INDEX = OFF
,
SORT_IN_TEMPDB =
OFF
,
DROP_EXISTING =
OFF
,
ALLOW_ROW_LOCKS =
ON
,
ALLOW_PAGE_LOCKS =
ON
);
----- 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 )
GO
--- 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