--===========================================================================--
-- BOUNDING BOX, calcul automatique pour index spatial géométrique           --
--===========================================================================--
-- Fredéric Brouard alias SQLpro                http://sqlpro.developpez.com --
-- Société SQL SPOT - http://www.sqlspot.com        2017-01-09 - version 1.0 --
--===========================================================================--


--▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄--
-- PHASE 1 : création de la procédure                                        --
--▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀--
CREATE PROCEDURE dbo.P_AUTOBOUND_SPATIAL_INDEX @DELTA FLOAT
AS
/******************************************************************************
* Calcul automatique de la "bounding box" pour les index spatiaux géométriqes *
* Procédure dbo.P_AUTOBOUND_SPATIAL_INDEX :                                   *
* Retourne une liste pour créer tous les index spatiaux pour toutes les       *
* colonnes de type "geometry" des tables de la base                           *
*******************************************************************************   
* Frédéric Brouard - SQLpro@SQLspot.com - Sté SQL SPOT http://www.sqlspot.com *
* Plus d'info. sur http://sqlpro.developpez.com   -  2017-01-09 - version 1.0 *   
*******************************************************************************   
* Cette procédure prend en argment en entrée un paramètre pour "élargir"      *
* de manière relative la bounding box                                         *
*******************************************************************************
* Paramètre en entrée : facteur élargissant la bounding box @DELTA FLOAT      *
* Type en sortie : (dataset) liste des index spatiaux à créer                 *
*                                                                             *
* EXEMPLE :                                                                   *
* Execution                                                                   *
*    EXEC dbo.P_AUTOBOUND_SPATIAL_INDEX 1.1;                                  *
* Resultats                                                                   *
* Génère les lignes suivantes :                                               *
*    CREATE SPATIAL INDEX XS_... ON ... (...)                                 *
*        WITH ( BOUNDING_BOX = (...) );                                       *
******************************************************************************/
SET NOCOUNT ON;
 
DECLARE @T TABLE (SCH sysname, TBL sysname, COL sysname, 
                  XMIN FLOAT, XMAX FLOAT, YMIN FLOAT, YMAX FLOAT);
-- le delta doit être 1 (bornes strictes) ou supérieur à 1
IF @DELTA < 1 OR @DELTA IS NULL
   SET @DELTA = 1;

-- recherches des colonnes spatiales non indexées de type GEOMETRY
INSERT INTO @T (SCH, TBL, COL)
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  DATA_TYPE = 'geometry'
EXCEPT
SELECT s.name, o.name, c.name
FROM   sys.objects AS o
       JOIN sys.indexes AS i
            ON o.object_id = i.object_id
       JOIN sys.schemas AS s
            ON o.schema_id = s.schema_id
       JOIN sys.spatial_indexes AS si
            ON i.object_id = si.object_id
            AND i.index_id = si.index_id
       JOIN sys.index_columns AS ic
            ON i.object_id = ic.object_id
            AND i.index_id = ic.index_id
       JOIN sys.columns AS c
            ON ic.object_id = c.object_id
            AND ic.column_id = c.column_id
       JOIN sys.systypes AS t
            ON c.system_type_id = t.type 
WHERE  t.name = 'geometry';

DECLARE @TXY TABLE (XMIN FLOAT, XMAX FLOAT, YMIN FLOAT, YMAX FLOAT);
DECLARE @TBL sysname, @SCH sysname, @COL sysname, 
        @XMIN FLOAT, @XMAX FLOAT, @YMIN FLOAT, @YMAX FLOAT, 
        @SQL NVARCHAR(max);

DECLARE C CURSOR 
   LOCAL
   FORWARD_ONLY
   STATIC
   READ_ONLY
FOR
   SELECT SCH, TBL, COL
   FROM   @T;

OPEN C;

FETCH C INTO @SCH, @TBL, @COL;

WHILE @@FETCH_STATUS = 0
BEGIN
   DELETE FROM @TXY;
   SET @SQL = N'WITH T AS (SELECT geometry::EnvelopeAggregate([' 
            + @COL + N']) AS GEO FROM [' + @SCH  + '].[' + @TBL + ']) ' 
            + N'SELECT geometry::EnvelopeAggregate(GEO).STPointN(1).STX / ' 
            + CAST(@DELTA AS VARCHAR(16)) + N' AS MIN_X, '
            + N'geometry::EnvelopeAggregate(GEO).STPointN(3).STX * ' 
            + CAST(@DELTA AS VARCHAR(16)) + N' AS MAX_X, '
            + N'geometry::EnvelopeAggregate(GEO).STPointN(1).STY / ' 
            + CAST(@DELTA AS VARCHAR(16)) + N' AS MIN_Y, '
            + N'geometry::EnvelopeAggregate(GEO).STPointN(3).STY * ' 
            + CAST(@DELTA AS VARCHAR(16)) + N' AS MAX_Y '
            + N'FROM T;'
   INSERT INTO @TXY
   EXEC (@SQL);
   UPDATE T
   SET    XMIN = TT.XMIN, XMAX = TT.XMAX, YMIN = TT.YMIN, YMAX = TT.YMAX
   FROM   @T AS T
          CROSS JOIN @TXY AS TT
   WHERE  SCH = @SCH
     AND  TBL = @TBL
     AND  COL = @COL;
   FETCH C INTO @SCH, @TBL, @COL;
END

CLOSE C;

DEALLOCATE C;

SELECT N'CREATE SPATIAL INDEX XS_' + LEFT(SCH, 16) + N'_' + LEFT(TBL, 30) + N'_' + LEFT(COL, 30)  + N'_'
       + REPLACE(CAST(NEWID() AS NVARCHAR(38)), N'-', N'_') + '_' + CONVERT(CHAR(8), GETDATE(), 112)
       + NCHAR(13) + NCHAR(10)
       + N'   ON [' + SCH + N'].[' + TBL + '] ([' + COL + N']) ' 
       + NCHAR(13) + NCHAR(10)
       + N'   WITH ( BOUNDING_BOX = (' 
       + CAST( XMIN AS NVARCHAR(32)) + N', '
       + CAST( YMIN AS NVARCHAR(32)) + N', '
       + CAST( XMAX AS NVARCHAR(32)) + N', '
       + CAST( YMAX AS NVARCHAR(32)) + N') );'
FROM   @T;  

GO