Spatial Index can only be created on Spatial columns. That is, the type of column must be geometry or geography. Let’s create a table with a column of geography type with the help of the following script and insert several sample records into it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE [dbo].[SpatialIndexExampleTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [CompanyName] [varchar](250) NULL, [Coordinate] [geography] NULL, CONSTRAINT [PK_SpatialIndexExampleTable] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] INSERT INTO SpatialIndexExampleTable VALUES ('My Company',geography::STGeomFromText('LINESTRING(-1.1 1.1, -1.1 1.1 )', 4326)); INSERT INTO SpatialIndexExampleTable VALUES ('His Company',geography::STGeomFromText('LINESTRING(-2.3 2.2, -2.2 2.2 )', 4326)); |
As can be seen from the above script, we will store the information of which company will be in which coordinate in this example table.
With the help of the following script, we can create spatial index on the column of geography type.
|
1 |
CREATE SPATIAL INDEX [IX_SpatialIndexName] ON [dbo].[SpatialIndexExampleTable]([Coordinate]) |
If you want to create a normal index on spatial columns, you will receive an error as follows.
|
1 2 3 4 5 |
CREATE NONCLUSTERED INDEX [IX_SpatialNonclustedIndexName] ON [dbo].[SpatialIndexExampleTable] ( [Coordinate] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) |
Msg 1978, Level 16, State 1, Line 2
Column ‘Coordinate’ in table ‘dbo.SpatialIndexExampleTable’ is of a type that is invalid for use as a key column in an index or statistics.
