MAF Tiger Data Spatial Index Creator

--CREATE PROCEDURE usp_RefactorRawTigerTables
--AS
PRINT CONVERT(VARCHAR, GETDATE()) + ': Script started!'

DECLARE @Index NVARCHAR(1024) = '',
        @Catalog NVARCHAR(1024) = '',
        @Schema NVARCHAR(1024) = '',
        @Table NVARCHAR(1024) = '',
        @CurrentTable NVARCHAR(1024) = '',
        @Sql NVARCHAR(1024) = '',
        @FullSql NVARCHAR(MAX) = '',
        @ExecSql NVARCHAR(MAX),
        @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)

PRINT 'Retrieving Spatial Indexes for deletion'

SELECT  t.name AS [Table],
        ind.name AS [Index]
INTO    #DeleteIndex
FROM
     sys.indexes ind
INNER JOIN
     sys.tables t ON ind.object_id = t.object_id
WHERE
     ind.is_primary_key = 0
ORDER BY
     t.name, ind.name, ind.index_id

SELECT  @Table = A.[Table], @Index = A.[Index]
FROM    #DeleteIndex A

WHILE @@ROWCOUNT <> 0
BEGIN
    SET ROWCOUNT 0

    PRINT 'Dropping Index ''' + @Index + ''' on [' + @Table + ']'

    SET @Sql = 'DROP INDEX ' + @Index + ' ON [' + @Table + ']'

    EXEC(@sql)

    DELETE FROM #DeleteIndex
    WHERE       [Table] = @Table
    AND         [Index] = @Index

    SELECT  @Table = A.[Table], @Index = A.[Index]
    FROM    #DeleteIndex A
END

DROP TABLE #DeleteIndex

PRINT CONVERT(VARCHAR, GETDATE()) + ': Retrieving tables for processing!'

SELECT  *
INTO    #Tables
FROM    INFORMATION_SCHEMA.TABLES A
WHERE 'geometry' IN (SELECT DATA_TYPE
                    FROM    INFORMATION_SCHEMA.COLUMNS B
                    WHERE   B.TABLE_CATALOG = A.TABLE_CATALOG
                    AND     B.TABLE_SCHEMA = A.TABLE_SCHEMA
                    AND     B.TABLE_NAME = A.TABLE_NAME)

SELECT  @Catalog = A.TABLE_CATALOG,
        @Schema = A.TABLE_SCHEMA,
        @Table = A.TABLE_NAME
FROM    #Tables A

WHILE @@ROWCOUNT <> 0
BEGIN
    SET ROWCOUNT 0

    PRINT CONVERT(VARCHAR, GETDATE()) + ': Processing Table [' + @Table + ']'

    IF EXISTS (SELECT   1
                FROM    INFORMATION_SCHEMA.COLUMNS A
                WHERE   A.TABLE_CATALOG = @Catalog
                AND     A.TABLE_SCHEMA = @Schema
                AND     A.TABLE_NAME = @Table
                AND     A.COLUMN_NAME = 'PointGeom')
    BEGIN
        PRINT CONVERT(VARCHAR, GETDATE()) + ': Dropping PointGeom column from [' + @Table + ']'
        SET @Sql = 'ALTER TABLE [' + @Table + '] DROP COLUMN PointGeom'
        EXEC(@Sql)
    END

    IF EXISTS (SELECT   1
                FROM    INFORMATION_SCHEMA.COLUMNS A
                WHERE   A.TABLE_CATALOG = @Catalog
                AND     A.TABLE_SCHEMA = @Schema
                AND     A.TABLE_NAME = @Table
                AND     A.COLUMN_NAME = 'MinLat')
    BEGIN
        PRINT CONVERT(VARCHAR, GETDATE()) + ': Dropping MinLat column from [' + @Table + ']'
        SET @Sql = 'ALTER TABLE [' + @Table + '] DROP COLUMN MinLat'
        EXEC(@Sql)
    END

    IF EXISTS (SELECT   1
                FROM    INFORMATION_SCHEMA.COLUMNS A
                WHERE   A.TABLE_CATALOG = @Catalog
                AND     A.TABLE_SCHEMA = @Schema
                AND     A.TABLE_NAME = @Table
                AND     A.COLUMN_NAME = 'MinLong')
    BEGIN
        PRINT CONVERT(VARCHAR, GETDATE()) + ': Dropping MinLong column from [' + @Table + ']'
        SET @Sql = 'ALTER TABLE [' + @Table + '] DROP COLUMN MinLong'
        EXEC(@Sql)
    END

    IF EXISTS (SELECT   1
                FROM    INFORMATION_SCHEMA.COLUMNS A
                WHERE   A.TABLE_CATALOG = @Catalog
                AND     A.TABLE_SCHEMA = @Schema
                AND     A.TABLE_NAME = @Table
                AND     A.COLUMN_NAME = 'MaxLat')
    BEGIN
        PRINT CONVERT(VARCHAR, GETDATE()) + ': Dropping MaxLat column from [' + @Table + ']'
        SET @Sql = 'ALTER TABLE [' + @Table + '] DROP COLUMN MaxLat'
        EXEC(@Sql)
    END

    IF EXISTS (SELECT   1
                FROM    INFORMATION_SCHEMA.COLUMNS A
                WHERE   A.TABLE_CATALOG = @Catalog
                AND     A.TABLE_SCHEMA = @Schema
                AND     A.TABLE_NAME = @Table
                AND     A.COLUMN_NAME = 'MaxLong')
    BEGIN
        PRINT CONVERT(VARCHAR, GETDATE()) + ': Dropping MaxLong column from [' + @Table + ']'
        SET @Sql = 'ALTER TABLE [' + @Table + '] DROP COLUMN MaxLong'
        EXEC(@Sql)
    END

    DECLARE @GeomColumn VARCHAR(1024) = (SELECT TOP 1 A.COLUMN_NAME
                                            FROM    INFORMATION_SCHEMA.COLUMNS A
                                            WHERE   A.TABLE_CATALOG = @Catalog
                                            AND     A.TABLE_SCHEMA = @Schema
                                            AND     A.TABLE_NAME = @Table
                                            AND     A.DATA_TYPE = 'geometry'),
            @LongColumn VARCHAR(1024),
            @LatColumn VARCHAR(1024)

    PRINT CONVERT(VARCHAR, GETDATE()) + ': Adding MinLong column to [' + @Table + ']'

    SET @Sql = 'ALTER TABLE [' + @Catalog + '].[' + @Schema + '].[' + @Table + ']' + @NewLineChar
            + 'ADD [MinLong] AS [' + @GeomColumn + '].STEnvelope().STPointN((1)).STX PERSISTED  NOT NULL'

    EXEC(@Sql)

    PRINT CONVERT(VARCHAR, GETDATE()) + ': MinLong column added to [' + @Table + ']'
    PRINT CONVERT(VARCHAR, GETDATE()) + ': Adding MinLat column to [' + @Table + ']'

    SET @Sql = 'ALTER TABLE [' + @Catalog + '].[' + @Schema + '].[' + @Table + ']' + @NewLineChar
            + 'ADD [MinLat] AS [' + @GeomColumn + '].STEnvelope().STPointN((1)).STY PERSISTED       NOT NULL'

    EXEC(@Sql)

    PRINT CONVERT(VARCHAR, GETDATE()) + ': MinLat column added to [' + @Table + ']'
    PRINT CONVERT(VARCHAR, GETDATE()) + ': Adding MaxLong column to [' + @Table + ']'

    SET @Sql = 'ALTER TABLE [' + @Catalog + '].[' + @Schema + '].[' + @Table + ']' + @NewLineChar
            + 'ADD [MaxLong] AS [' + @GeomColumn + '].STEnvelope().STPointN((3)).STX PERSISTED  NOT NULL'

    EXEC(@Sql)

    PRINT CONVERT(VARCHAR, GETDATE()) + ': MaxLong column added to [' + @Table + ']'
    PRINT CONVERT(VARCHAR, GETDATE()) + ': Adding MaxLat column to [' + @Table + ']'

    SET @Sql = 'ALTER TABLE [' + @Catalog + '].[' + @Schema + '].[' + @Table + ']' + @NewLineChar
            + 'ADD [MaxLat] AS [' + @GeomColumn + '].STEnvelope().STPointN((3)).STY PERSISTED       NOT NULL'

    EXEC(@Sql)

    PRINT CONVERT(VARCHAR, GETDATE()) + ': MaxLat column added to [' + @Table + ']'

    PRINT CONVERT(VARCHAR, GETDATE()) + ': Creating index on bounding box coordinates!'

    SET @Sql = 'CREATE INDEX idx_' + @Table + '_MinLong_MinLat_MaxLong_MaxLat ON [' + @Table + ']([MinLong], [MinLat], [MaxLong], [MaxLat])'

    EXEC(@Sql)

    PRINT CONVERT(VARCHAR, GETDATE()) + ': Bounding Box index created!'

    IF EXISTS (SELECT   1
                FROM    INFORMATION_SCHEMA.COLUMNS A
                WHERE   A.TABLE_CATALOG = @Catalog
                AND     A.TABLE_SCHEMA = @Schema
                AND     A.TABLE_NAME = @Table
                AND     A.COLUMN_NAME LIKE '%lat%')
    BEGIN
        IF EXISTS (SELECT   1
                    FROM    INFORMATION_SCHEMA.COLUMNS A
                    WHERE   A.TABLE_CATALOG = @Catalog
                    AND     A.TABLE_SCHEMA = @Schema
                    AND     A.TABLE_NAME = @Table
                    AND     A.COLUMN_NAME LIKE '%intptlon%')
        BEGIN
            PRINT CONVERT(VARCHAR, GETDATE()) + ': Latitude and Longitude columns detected!'
            PRINT CONVERT(VARCHAR, GETDATE()) + ': Creating PointGeom column on [' + @Table + ']'

            SET @LongColumn = (SELECT TOP 1 A.COLUMN_NAME
                                FROM    INFORMATION_SCHEMA.COLUMNS A
                                WHERE   A.TABLE_CATALOG = @Catalog
                                AND     A.TABLE_SCHEMA = @Schema
                                AND     A.TABLE_NAME = @Table
                                AND     A.COLUMN_NAME LIKE '%intptlon%')

            SET @LatColumn = (SELECT TOP 1 A.COLUMN_NAME
                                FROM    INFORMATION_SCHEMA.COLUMNS A
                                WHERE   A.TABLE_CATALOG = @Catalog
                                AND     A.TABLE_SCHEMA = @Schema
                                AND     A.TABLE_NAME = @Table
                                AND     A.COLUMN_NAME LIKE '%intptlat%')

            PRINT CONVERT(VARCHAR, GETDATE()) + ': Converting latitude and longitude columns to non-nullable float values on [' + @Table + ']'

            SET @Sql = 'ALTER TABLE [' + @Catalog + '].[' + @Schema + '].[' + @Table + ']' + @NewLineChar
                    + 'ALTER COLUMN [' + @LongColumn + '] float NOT NULL' + @NewLineChar
                    + 'ALTER TABLE [' + @Catalog + '].[' + @Schema + '].[' + @Table + ']' + @NewLineChar
                    + 'ALTER COLUMN [' + @LatColumn + '] float NOT NULL'

            EXEC(@Sql)

            PRINT CONVERT(VARCHAR, GETDATE()) + ': Creating index on latitude and longitude!'

            SET @Sql = 'CREATE INDEX idx_' + @Table + '_' + @LongColumn + '_' + @LatColumn + ' ON [' + @Table + ']([' + @LongColumn + '], [' + @LatColumn + '])'

            EXEC(@Sql)

            PRINT CONVERT(VARCHAR, GETDATE()) + ': Latitude and Longitude index created!'

            SET @Sql = 'ALTER TABLE [' + @Catalog + '].[' + @Schema + '].[' + @Table + '] ADD [PointGeom] AS GEOMETRY::Point([' + @LongColumn + '], [' + @LatColumn + '], [' + @GeomColumn + '].STSrid) PERSISTED NOT NULL'

            EXEC(@Sql)

            PRINT CONVERT(VARCHAR, GETDATE()) + ': PointGeom column created.'
            PRINT CONVERT(VARCHAR, GETDATE()) + ': Creating Spatial index on column!'

            SET @Sql = 'DECLARE @minlat FLOAT, @minlong FLOAT, @maxlat FLOAT, @maxlong FLOAT, @Sql VARCHAR(MAX)' + @NewLineChar
                        + 'SELECT @minlat = MIN([' + @LatColumn + ']), @minlong = MIN([' + @LongColumn + ']), @maxlat = MAX([' + @LatColumn + ']), @maxlong = MAX([' + @LongColumn + ']) FROM [' + @Catalog + '].[' + @Schema + '].[' + @Table + '] A' + @NewLineChar
                        + 'SET @Sql = ''CREATE SPATIAL INDEX idx_' + @Table + '_PointGeom ON [' + @Catalog + '].[' + @Schema + '].[' + @Table + '] ([PointGeom]) WITH ( BOUNDING_BOX = ( '' + CONVERT(VARCHAR, @minlong) + '', '' + CONVERT(VARCHAR, @minlat) + '', '' + CONVERT(VARCHAR, @maxlong) + '', '' + CONVERT(VARCHAR, @maxlat) + ''), GRIDS =(LEVEL_1 = HIGH, LEVEL_2 = HIGH, LEVEL_3 = HIGH, LEVEL_4 = HIGH), CELLS_PER_OBJECT = 16)''' + @NewLineChar
                        + 'EXEC(@Sql)'

            EXEC(@Sql)

            PRINT CONVERT(VARCHAR, GETDATE()) + ': Spatial Index Created on PointGeom column'
        END
    END

    PRINT CONVERT(VARCHAR, GETDATE()) + ': Creating Spatial index on [' + @GeomColumn + ']'

    SET @Sql = 'DECLARE @minlat FLOAT, @minlong FLOAT, @maxlat FLOAT, @maxlong FLOAT, @Sql VARCHAR(MAX)' + @NewLineChar
                + 'SELECT @minlat = MIN(A.MinLat), @minlong = MIN(A.MinLong), @maxlat = MAX(A.MaxLat), @maxlong = MAX(A.MaxLong) FROM [' + @Catalog + '].[' + @Schema + '].[' + @Table + '] A' + @NewLineChar
                + 'SET @Sql = ''CREATE SPATIAL INDEX idx_' + @Table + '_' + @GeomColumn + ' ON [' + @Catalog + '].[' + @Schema + '].[' + @Table + '] ([' + @GeomColumn + ']) WITH ( BOUNDING_BOX = ( '' + CONVERT(VARCHAR, @minlong) + '', '' + CONVERT(VARCHAR, @minlat) + '', '' + CONVERT(VARCHAR, @maxlong) + '', '' + CONVERT(VARCHAR, @maxlat) + ''), GRIDS =(LEVEL_1 = HIGH, LEVEL_2 = HIGH, LEVEL_3 = HIGH, LEVEL_4 = HIGH), CELLS_PER_OBJECT = 16)''' + @NewLineChar
                + 'EXEC(@Sql)'

    EXEC(@Sql)

    PRINT CONVERT(VARCHAR, GETDATE()) + ': Spatial Index Created on [' + @GeomColumn + '] column'
    PRINT CONVERT(VARCHAR, GETDATE()) + ': Consuming #Tables record'

    DELETE  #Tables
    WHERE   TABLE_CATALOG = @Catalog
    AND     TABLE_SCHEMA = @Schema
    AND     TABLE_NAME = @Table

    SELECT  @Catalog = A.TABLE_CATALOG,
            @Schema = A.TABLE_SCHEMA,
            @Table = A.TABLE_NAME
    FROM    #Tables A
END

DROP TABLE #Tables

PRINT CONVERT(VARCHAR, GETDATE()) + ': Script finished!'