ジオハッシュ インデックスをテーブルに追加する

クラスタリング (集計テーブル) を可能にして、大規模なデータ視覚化を容易にするには、ジオメトリ (または XY) から geohash インデックスを生成します。

以下は、ジオハッシュ ID を作成するサンプルのユーザ定義関数 (UDF) です。この UDF は、経度と緯度およびその精度を表す数値パラメータを受け取ります。この関数は、経度と緯度の値が WGS 84 座標系に基づくものと仮定しています。

注:
  • この関数を作成するためには DB 上で UDF を作成する権限が必要です。
  • 関数には一意の名前を付け、他に同じ名前の関数が存在しないことを確認してください。
CREATE FUNCTION [dbo].[createGeohash] (@LONGITUDE  NUMERIC(10, 6),
   @LATITUDE NUMERIC(10, 6), @PRECISION INT) RETURNS VARCHAR(12) AS
BEGIN	
	DECLARE @GEOHASH_CHARSET CHAR(32)
	SET @GEOHASH_CHARSET = '0123456789bcdefghjkmnpqrstuvwxyz'
	
	DECLARE @MIN_LAT AS NUMERIC(12,8) = -90.0
	DECLARE @MAX_LAT AS NUMERIC(12,8)  = 90.0
	DECLARE @MIN_LNG AS NUMERIC(12,8)  = -180.0
	DECLARE @MAX_LNG AS NUMERIC(12,8)  = 180.0
		
	DECLARE @IDX AS INT = 0
	DECLARE @BIT AS INT = 0
	DECLARE @GEOHASH AS VARCHAR(12)
	DECLARE @GEOHASH_SUFFIX AS CHAR(1)
	DECLARE @MID_POINT AS NUMERIC(12,8)
	
	DECLARE @LOOPCOUNTER AS INT = 0
	DECLARE @EVEN AS BIT = 1

	SET @LOOPCOUNTER = 0
	WHILE (@LOOPCOUNTER < @PRECISION)
		BEGIN
		    IF(@EVEN > 0) 
				BEGIN 
					SET @MID_POINT = (@MIN_LNG + @MAX_LNG) / 2
					IF(@LONGITUDE >= @MID_POINT)
						BEGIN  
		       				SET @MIN_LNG = @MID_POINT
							SET @IDX = (@IDX * 2) + 1	
						END
		     		ELSE
						BEGIN
		       				SET @MAX_LNG = @MID_POINT
							SET @IDX = (@IDX * 2)
						END
				END  
			ELSE 
				BEGIN	
					SET @MID_POINT = (@MIN_LAT + @MAX_LAT) / 2
					IF(@LATITUDE >= @MID_POINT) 
						BEGIN
		       				SET @MIN_LAT = @MID_POINT
							SET @IDX = (@IDX * 2) + 1		
						END
					ELSE 
						BEGIN
		      				SET @MAX_LAT = @MID_POINT
							SET @IDX = (@IDX * 2)
                        					END
				END	
			
			SET @EVEN = ~@EVEN				
			SET @BIT = @BIT + 1
			
			IF(@BIT = 5)
				BEGIN
					SET @GEOHASH_SUFFIX = SubString(@GEOHASH_CHARSET, @IDX + 1, 1)
					SET @GEOHASH = CONCAT(@GEOHASH, @GEOHASH_SUFFIX)
					SET @LOOPCOUNTER = @LOOPCOUNTER + 1
 					SET @BIT = 0
					SET @IDX = 0
				END
		END
 	RETURN @GEOHASH
END

DB テーブルをジオハッシュで更新するクエリ

ジオハッシュ ID を格納するテーブルには Varchar 型の列が必要です。null 値があっても更新が正常に行われるようにするには、WHERE 句で "NOT NULL" をチェックします。

テーブルをポイント ジオメトリで検索するサンプル クエリ

UPDATE dbo.POI SET GEOHASH = dbo.createGeohash(CAST(SP_GEOMETRY.STX as numeric(16,8)), CAST(SP_GEOMETRY.STY as numeric(16,8)), 12);

テーブルを X(経度) および Y(緯度) 列で検索するサンプル クエリ

UPDATE dbo.POI SET GEOHASH = dbo.createGeohash(LONGITUDE, LATITUDE, 12);