1

I have used spatial index recently on one table to improve query which filter records by given date range, reference identifier and whether they are inside polygon.

Here's the example of the query

DECLARE
    @start DATETIME = '2021-09-01 00:00:00.000',
    @end DATETIME = '2021-09-22 00:00:00.000',
    @shape GEOGRAPHY
    
SET @shape = geography::STPolyFromText('POLYGON((
-5.0537109 55.5783447,
-3.9990234 51.0137547,
3.2299805 50.4295179,
4.0209961 54.8133484,
-5.0537109 55.5783447
))', 4326);

SELECT
    *
FROM [dbo].[getUsersByArea](@start, @end, @shape);

I've noticed that

  • Sometimes the spatial index is not even used.
  • If it appears in the query plan, the performance is still bad.

The user_location table has around 70mln records.

The reference table has around 100k records and usually there are few thousand user_location records referencing one table.

The user table has 100k records.

I also fiddled with OGC geography methods and i have tried to use STIntersects, STWithin, STContains ( surprisingly behaves the same as STWithin ) and Filter but it couldn't alleviate any problem mentioned ( STIntersects somehow appeared worse in query plans ).

Is it the right approach to use that type of index? It is quite a head-scratcher.

Tell me what you think.

Thanks in advance.

Here are schema definitions

CREATE TABLE [dbo].[user_location] (
    [Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ReferenceId] [uniqueidentifier] NOT NULL,
    [RecordedOn] [datetime] NOT NULL,
    [Lat] [float] NULL,
    [Lng] [float] NULL,
    [Location] AS (case when [Lat] IS NULL OR [Lng] IS NULL then NULL else [geography]::Point([Lat],[Lng],(4326)) end) PERSISTED,
    CONSTRAINT [PK_dbo_user_location] PRIMARY KEY CLUSTERED([Id] ASC)
)

CREATE TABLE [dbo].[reference]
(
    [Id] [uniqueidentifier] NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    CONSTRAINT [PK_dbo_reference] PRIMARY KEY CLUSTERED([Id] ASC)
)

CREATE TABLE [dbo].[user]
(
    [Id] [uniqueidentifier] NOT NULL,
    [FullName] [nvarchar](100) NULL,
    [EmailAddress] [nvarchar](255) NULL,
    CONSTRAINT [PK_dbo_user] PRIMARY KEY CLUSTERED([Id] ASC)
) 

CREATE NONCLUSTERED INDEX [IX_reference_userId] ON [dbo].[reference]
(
    [UserId] ASC
) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)

CREATE NONCLUSTERED INDEX [IX_user_location_referenceId_recordedOn] ON [dbo].[user_location]
(
    [ReferenceId] ASC,
    [RecordedOn] ASC,
) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)

CREATE SPATIAL INDEX [SPIX_user_location] ON [dbo].[user_location]
(
    [Location]
)USING  GEOGRAPHY_AUTO_GRID 
WITH (
CELLS_PER_OBJECT = 12, STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)

CREATE FUNCTION [dbo].[getUsersByArea]
(
    @start DATETIME,
    @end DATETIME,
    @shape GEOGRAPHY
)
RETURNS TABLE
AS
RETURN
(
    SELECT DISTINCT
        us.Id,
        us.FullName,
        us.EmailAddress
    FROM [dbo].[user_location] ul
    JOIN [dbo].[reference] rf
        ON ul.ReferenceId = rf.Id
    JOIN [dbo].[user] us
        ON us.Id = rf.UserId
    WHERE 
        ul.RecordedOn < @end
        AND ul.RecordedOn >= @start 
        AND ul.[Location] IS NOT NULL 
        AND @shape.STContains(ul.[Location]) = 1
)

Also one interesting thing, when you try to retrieve spatial index metadata with sys.sp_help_spatial_geography_index it executes infinitely.

EXEC sys.sp_help_spatial_geography_index
    @tabname = 'user_location',
    @indexname = 'SPIX_user_location',
    @verboseoutput = 1,
    @query_sample = 'POINT (0 0)'
3
  • 1
    I think you should try storing the @shape variable in another base table (temp table ?) and add a spatial index to that too Sep 23, 2021 at 7:26
  • Updated description Sep 23, 2021 at 8:30
  • @StephenMorris-Mo64 thank you, that helped! Can you make an answer? Ill mark it as accepted. Sep 23, 2021 at 9:26

1 Answer 1

2

I think you should try storing the @shape variable in another base table (temp table ?) and add a spatial index to that too

Example

CREATE PROCEDURE [dbo].[getUsersByArea]
    @start DATETIME,
    @end DATETIME,
    @shape GEOGRAPHY
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Shapes
    (
        [Id] [uniqueidentifier] NOT NULL,
        Shape GEOGRAPHY NOT NULL,
        CONSTRAINT [PK_Shapes] PRIMARY KEY CLUSTERED([Id] ASC)
    )

    INSERT INTO #Shapes
    VALUES
    (NEWID(), @shape)

    CREATE SPATIAL INDEX [SPIX_TMP_Shape] ON #Shapes ([Shape])
    -- YMMV
    USING GEOGRAPHY_GRID 
    WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4= 
    HIGH),
    CELLS_PER_OBJECT = 16)

    SELECT DISTINCT
        us.Id,
        us.FullName,
        us.EmailAddress
    FROM [dbo].[user_location] ul
    JOIN [dbo].[reference] rf
        ON ul.ReferenceId = rf.Id
    JOIN [dbo].[user] us
        ON us.Id = rf.UserId
    JOIN #Shapes sh
        ON sh.Shape.STContains(ul.[Location]) = 1
    WHERE ul.[Location] IS NOT NULL
        ul.RecordedOn < @end
        AND ul.RecordedOn >= @start
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.