Adding a Geohash Index to a Table

To enable clustering (aggregated tables) and facilitate large data visualization, generate the geohash index from the geometry (or the XY).

Below is a sample user defined function (UDF) for creating a geohash ID. This UDF takes numeric value parameters for longitude and latitude along with precision. This function assumes that the longitude and latitude values are in WGS 84 Coordinate System.

Note:

To create the function make sure you have the rights for creating UDF on the DB.
Ensure that you give function a unique name and that no other function exists with the same name.

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

Queries to UPDATE the DB Table with Geohash

A column of type Varchar is required in the table for storing the geohash ID. To ensure that the update works correctly when there are null values, use the WHERE clause to check for "not null."

Example Query for a Table with a Point Geometry

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

Example Query for a Table with X(Longitude) and Y(Latitude) columns

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