Sql Server

SQL | Spatial index

by Deepak Talwar
August 15, 2023
0 Comments
3 minute read
138 Views
0 Likes

SQL | Spatial index

A spatial index is a type of extended index that allows you to index a spatial column
A spatial column is a table column that contains data of a spatial data type, such as geometry or geography.
Spatial indexes require that the database table that you are creating the spatial index on also has a clustered primary key index defined.

Why use a spatial index?
In General for testing if two points and/or areas intersect or are within a certain distance.
it allows the query to easily skip over column values that there is no chance of intersection.
When creating the index there are options available that increase the accuracy of the index but with that comes the drawback that the index will use more space.

Create a spatial index

CREATE TABLE Student(
    [ID] [int] NOT NULL,    
    [Title] [nvarchar](8) NULL,
    [FirstName] [dbo].[Name] NOT NULL,
    [MiddleName] [dbo].[Name] NULL,
    [LastName] [dbo].[Name] NOT NULL,   
    [Demographics] [xml](CONTENT [Student].[IndividualSurveySchemaCollection]) NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Student_ID] 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]
GO


CREATE TABLE StudentAddress(
    [AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [AddressLine1] [nvarchar](60) NOT NULL,
    [AddressLine2] [nvarchar](60) NULL,
    [City] [nvarchar](30) NOT NULL,
    [StateProvinceID] [int] NOT NULL,
    [PostalCode] [nvarchar](15) NOT NULL,
    [SpatialLocation] [geography] NULL, 
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED 
(
    [AddressID] 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]
GO


-- Create a Spatial Index
CREATE SPATIAL INDEX IX_Address_SpatialLocation StudentAddress(SpatialLocation);

Check Index Usage
The following query should use the spatial index in order to determine the 7 closest points to the specified location

DECLARE @g geography = 'POINT(-121.626 47.8315)'; 
SELECT TOP(7) SpatialLocation.ToString(), City FROM StudentAddress
WHERE SpatialLocation.STDistance(@g) IS NOT NULL 
ORDER BY SpatialLocation.STDistance(@g); 

Deepak Talwar

Technical Architect and Full Stack Developer with 18+ years of Professional Experience in Microsoft Technologies & PHP Platform. Hands on experience with C#, VB, ASP.NET, ASP.NET MVC, ASP.NET Core, ASP.NET Web API, Linq, ADO.NET, Entity Framework, Entity Framework Core, Sql Server, MYSQL, NoSql, Javascript, Angular, jQuery, AWS, Azure, React, Angular, Laravel, Codeingiter, Serenity, VBA, Cloud Computing, Microservices, Design Patterns, Software Architecture, Web Design and Development.