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


下記のようなコピーをしたり

SELECT * INTO JPN71_COPY FROM JPN71;
GO

下記のように自分でテーブルを作成したり

メッシュ的な何かを作る方法

バルクコピーしてみたり

Insertの高速化を試みる

ArcGISの機能によって作成していないテーブルの場合空間インデックスがつかない。
とりあえず手動で付与する方法。


プロシージャ作成側
一度作っておくとあとは対象設定で使い回し。
ただしテーブルにデータが詰まってる想定なので、詰まってない場合は
CREATE SPATIAL INDEX 文のBOUNDING_BOXに予測される最大範囲(座標値)を設定
ArcGISはCHECK (([SHAPE].[STSrid]=(3857)))のようなSRID制約を付与するようだが
SQL Server側はSrid混在は許容される?めんどいので統一する事をおすすめするが。

USE データベース名
GO

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 @sqlstmt nvarchar(500);
  DECLARE @full_ext TABLE 
  (
    minx numeric(38,18) ,
    miny numeric(38,18) ,
    maxx numeric(38,18) ,
    maxy numeric(38,18) 
  );

  --最大範囲の取得
  SET @sqlstmt = N''; --インデント合わせ
  SET @sqlstmt = @sqlstmt + N'SELECT';
  SET @sqlstmt = @sqlstmt + N' MIN('+@shp_col_name+'.STEnvelope().STPointN(1).STX) ,';
  SET @sqlstmt = @sqlstmt + N' MIN('+@shp_col_name+'.STEnvelope().STPointN(1).STY) ,';
  SET @sqlstmt = @sqlstmt + N' MAX('+@shp_col_name+'.STEnvelope().STPointN(3).STX) ,';
  SET @sqlstmt = @sqlstmt + N' MAX('+@shp_col_name+'.STEnvelope().STPointN(3).STY)  ';
  SET @sqlstmt = @sqlstmt + 'FROM ';
  SET @sqlstmt = @sqlstmt +  @tab_name;
  SET @sqlstmt = @sqlstmt + ' WHERE';  --ジオメトリが存在するののみ対象
  SET @sqlstmt = @sqlstmt + ' (NOT '+@shp_col_name+' IS NULL) AND '+@shp_col_name+'.STIsEmpty() = 0';

  ----確認用
  --PRINT @sqlstmt;

  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

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

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

  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';
  SET @createInx = @createInx + ' GEOMETRY_GRID';
  SET @createInx = @createInx + ' WITH (';
  SET @createInx = @createInx + 'BOUNDING_BOX = (' + @minxSt + ',' + @minySt + ',' + @maxxSt + ',' + @maxySt + '),';
  SET @createInx = @createInx + 'GRIDS = (medium, medium, medium, medium), ';
  SET @createInx = @createInx + 'CELLS_PER_OBJECT = 16';
  SET @createInx = @createInx + ')';

  ----確認用
  --SELECT @createInx; --FROM DUAL;

  EXEC(@createInx);

  RETURN
GO

プロシージャ使用側

--ストアドプロシージャ実行
--@tab_name:対象テーブル名
--@shp_col_namec:ジオメトリカラム名
--@shp_inx_name:空間インデックス名
--既にインデックスがついている場合は作成不可
--クラスターインデックスが付与されていないと動かない
exec sp_full_extent_createInx @tab_name = N'JPN71_COPY', @shp_col_name=N'SHAPE' , @shp_inx_name = 'Shape_Index'
GO
カテゴリー: 運用, 開発 タグ: パーマリンク