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);