Creating spatial indexes programmatically in SQL Server 2008

Whilst I keep trying to teach myself the finer points of of SQL Server 2008 spatial functionality, I was presented with my first “real” task: To create a spatial index for a table containing some point features. So what’s the big deal, I hear you ask?  Well, first of all, I didn’t even realize that the table DID NOT have a spatial index! Duh! Being used to Oracle that complains if you try to do almost anything without a spatial index, it didn’t even occur to me. And then, I found out that in order to create the index you have to first find the table’s extent/envelope. In SQL Server there doesn’t seem to be anything equivalent to Oracle’s USER_SDO_GEOM_METADATA table (For a short and sweet explanation of this view see Adam Estrada’s post here). Then again even in Oracle you have to define the spatial extent at some point in USER_SDO_GEOM_METADATA. (Should really write a post comparing Oracle and SQLServer’s spatial functions…at some point)

So, on to the task at hand: rather than defining the extent and creating the spatial index by hand, I wrote a couple of procedures to do this automatically. I only tested these on this one table but should work for any. I used some default values for defining the Grid Density, however it should be easy enough to change this based on your data. In any case, creating spatial indexes on ANY database, is –from my experience- more art than science!

The first procedure is spgGetExtentDimension which will return a point geometry representing a corner of the table’s envelope or Minimum Bounding Rectangle (MBR) (which is, as the name suggests, a rectangle). The procedure will accept as input parameters the table name, the spatial column name and the vertex number of the rectangle (1 to 4).

CREATE PROCEDURE spgGetExtentDimension (@spatial_tab nvarchar(20)
                                      ,@spatial_col nvarchar(10)
                                      ,@vertex int
                                      ,@point geometry OUTPUT)
AS
BEGIN
    -- Declare variables
    DECLARE @extent geometry;
    DECLARE @sqlString nvarchar(500);
    DECLARE @paramDefinition nvarchar(500);
    -- The extent calculation sql string
    SET @sqlString=N'SELECT @extentOUT = dbo.GeometryEnvelopeAggregate('
                                                       +@spatial_col+')
                    FROM dbo.pois'
    -- Parameter definition string
    SET @paramDefinition=N'@extentOUT geometry OUTPUT';
    EXECUTE sp_executesql @sqlString, @paramDefinition
                                    , @extentOUT=@extent OUTPUT
    SELECT @extent;
    -- Now that we have the extent return its min or max point (1 or 3)
    SELECT @point = @extent.STPointN(@vertex);
END
GO

The main procedure to create the spatial index is the imaginatively named CreateSPIndex. The procedure accepts the table and spatial column names as input parameters, calls the spgGetExtentDimension procedure to retrieve the upper and lower corners of the extent (vertices 1 and 3) and executes a bit of dynamic sql to create the index.

CREATE PROCEDURE CreateSPIndex (@spatial_tab nvarchar(20)
                              ,@spatial_col nvarchar(10))
AS
BEGIN
    DECLARE @sqlCommand nvarchar(1000);
    DECLARE @minPoint geometry;
    DECLARE @maxPoint geometry;
    DECLARE @minx float;
    DECLARE @miny float;
    DECLARE @maxx float;
    DECLARE @maxy float;

    EXEC [dbo].[spgGetExtentDimension]
         @spatial_tab = @spatial_tab,
         @spatial_col = @spatial_col,
         @vertex = 1,
         @point = @minPoint OUTPUT;
    --SELECT @minPoint;

    EXEC [dbo].[spgGetExtentDimension]
         @spatial_tab = @spatial_tab,
         @spatial_col = @spatial_col,
         @vertex = 3,
         @point = @maxPoint OUTPUT;
    --SELECT @maxPoint;
    select @minx= @minPoint.STX;
    select @miny= @minPoint.STY;
    select @maxx= @maxPoint.STX;
    select @maxy= @maxPoint.STY;
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Spatial index creation statement
    SELECT @sqlCommand = 'CREATE SPATIAL INDEX POIS_SPIDX  ON '
                                + @spatial_tab+'(' + @spatial_col +')
                          WITH (BOUNDING_BOX = ('
                                + CAST(@minx as varchar(20)) +','
                                + CAST(@miny as varchar(20)) +','
                                + CAST(@maxx as varchar(20)) +','
                                + CAST (@maxy as varchar(20))
                        +' ) ,GRIDS = ( LEVEL_4 = HIGH, LEVEL_3 = MEDIUM ) )';
    EXEC sp_executesql @sqlCommand;
END
GO

Ok, back to my sandbox now…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s