SQL Server 2012 空間Index付与Geography


下記は2008 R2でも動くがGeography型非対応だったので2012最適化と一緒に書き直してみる。

ArcGISなしで適当に空間インデックスを付与

if exists (SELECT * FROM sysobjects WHERE id = object_id('スキーマ名.sp_full_extent_createInx') AND sysstat & 0xf = 4)
	drop procedure スキーマ名.sp_full_extent_createInx;
GO

CREATE PROCEDURE スキーマ名.sp_full_extent_createInx
 @tab_name nvarchar(100),
 @shp_col_name nvarchar(50),
 @shp_inx_name varchar(50)
AS

DECLARE @shptype AS VARCHAR(10)

SELECT TOP 1
 @shptype = tp.name
FROM
 sys.tables AS tab
INNER JOIN
 syscolumns AS col
ON
 tab.object_id = col.id
 INNER JOIN
  sys.types AS tp
ON
 col.xtype = tp.system_type_id AND
 col.xusertype = tp.user_type_id
WHERE
 tab.type_desc = 'USER_TABLE' AND
 (tp.name = 'geometry' OR tp.name = 'geography')
  AND
 tab.name = @tab_name 

----確認用
--SELECT @shptype;

DECLARE @sqlstmt nvarchar(500);
SET @sqlstmt = NULL;

--最大範囲の取得
IF @shptype = 'geography'
 BEGIN

  PRINT 'geography';
  /*SET @sqlstmt = @sqlstmt + N' GEOGRAPHY::EnvelopeAggregate(' + @shp_col_name + N').STPointN(1).Lat';
  SET @sqlstmt = @sqlstmt + N',GEOGRAPHY::EnvelopeAggregate(' + @shp_col_name + N').STPointN(1).Long';
  SET @sqlstmt = @sqlstmt + N',GEOGRAPHY::EnvelopeAggregate(' + @shp_col_name + N').STPointN(3).Lat';
  SET @sqlstmt = @sqlstmt + N',GEOGRAPHY::EnvelopeAggregate(' + @shp_col_name + N').STPointN(3).Long';*/
 END
ELSE
 BEGIN
  SET @sqlstmt = N''; 
  SET @sqlstmt = N'SELECT';
  SET @sqlstmt = @sqlstmt + N' GEOMETRY::EnvelopeAggregate(Shape).STPointN(1).STX';
  SET @sqlstmt = @sqlstmt + N',GEOMETRY::EnvelopeAggregate(Shape).STPointN(1).STY';
  SET @sqlstmt = @sqlstmt + N',GEOMETRY::EnvelopeAggregate(Shape).STPointN(3).STX';
  SET @sqlstmt = @sqlstmt + N',GEOMETRY::EnvelopeAggregate(Shape).STPointN(3).STY';
  SET @sqlstmt = @sqlstmt + ' FROM ' + @tab_name;
 END

----確認用
--SELECT @sqlstmt;

DECLARE @minxSt VARCHAR(max);
DECLARE @minySt VARCHAR(max);
DECLARE @maxxSt VARCHAR(max);
DECLARE @maxySt VARCHAR(max);

IF NOT @sqlstmt IS NULL
 BEGIN
	DECLARE @full_ext TABLE 
	  (
		minx numeric(38,18) ,
		miny numeric(38,18) ,
		maxx numeric(38,18) ,
		maxy numeric(38,18) 
	  );

	INSERT INTO @full_ext EXECUTE sp_executesql @sqlstmt;

	----確認用
	--SELECT * FROM @full_ext;

	DECLARE @minx float;
	DECLARE @miny float;
	DECLARE @maxx float;
	DECLARE @maxy float;

	SELECT 
	@minx = MIN(minx),
	@miny = MIN(miny),
	@maxx = MAX(maxx),
	@maxy = MAX(maxy)
	FROM
	@full_ext

	SET @minxSt = CONVERT(VARCHAR(max), @minx);
	SET @minySt = CONVERT(VARCHAR(max), @miny);
	SET @maxxSt = CONVERT(VARCHAR(max), @maxx);
	SET @maxySt = CONVERT(VARCHAR(max), @maxy);
 END

DECLARE @createInx varchar(500);
SET @createInx = '';  --インデント合わせ
SET @createInx = @createInx + 'CREATE SPATIAL INDEX ';
SET @createInx = @createInx + @shp_inx_name;
SET @createInx = @createInx + ' ON ';
SET @createInx = @createInx + @tab_name + ' ('+@shp_col_name+') ';
SET @createInx = @createInx + 'USING';

IF @shptype = 'geography'
 BEGIN
  SET @createInx = @createInx + ' GEOGRAPHY_GRID';
 END
ELSE
 BEGIN
  SET @createInx = @createInx + ' GEOMETRY_GRID';
 END

SET @createInx = @createInx + ' WITH (';

IF @shptype <> 'geography'
 BEGIN
  SET @createInx = @createInx + 'BOUNDING_BOX = (' + @minxSt + ',' + @minySt + ',' + @maxxSt + ',' + @maxySt + '),';
 END

SET @createInx = @createInx + 'GRIDS = (medium, medium, medium, medium), ';
SET @createInx = @createInx + 'CELLS_PER_OBJECT = 16';
SET @createInx = @createInx + ')';

--確認用
PRINT @createInx;

EXEC(@createInx);

RETURN
GO
カテゴリー: 運用, 開発 タグ: パーマリンク