-
Notifications
You must be signed in to change notification settings - Fork 0
/
GoehashUDF
73 lines (66 loc) · 1.71 KB
/
GoehashUDF
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
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