SQL-Server-Geohash/script.sql at master · Broham/SQL-Server-Geohash · GitHub

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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
----- bit mapping function ---------------------------------------------------------
CREATEFUNCTION [dbo].[geohash_bit] (
 @_bit TINYINT
)
RETURNSTINYINT
AS
BEGIN
DECLARE @bit TINYINT

SET @bit =CASE @_bit
WHEN0THEN16
WHEN1THEN8
WHEN2THEN4
WHEN3THEN2
WHEN4THEN1
END

RETURN @bit;
end

-----base 32 reverse mapping function ------------------------------------------------
CREATEFUNCTION [dbo].[geohash_base32_index] (
 @ch CHAR(1)
)
RETURNSTINYINT
AS
BEGIN
DECLARE @idx TINYINT

SET @idx =CASE @ch
WHEN'0'THEN0
WHEN'1'THEN1
WHEN'2'THEN2
WHEN'3'THEN3
WHEN'4'THEN4
WHEN'5'THEN5
WHEN'6'THEN6
WHEN'7'THEN7
WHEN'8'THEN8
WHEN'9'THEN9
WHEN'b'THEN10
WHEN'c'THEN11
WHEN'd'THEN12
WHEN'e'THEN13
WHEN'f'THEN14
WHEN'g'THEN15
WHEN'h'THEN16
WHEN'j'THEN17
WHEN'k'THEN18
WHEN'm'THEN19
WHEN'n'THEN20
WHEN'p'THEN21
WHEN'q'THEN22
WHEN'r'THEN23
WHEN's'THEN24
WHEN't'THEN25
WHEN'u'THEN26
WHEN'v'THEN27
WHEN'w'THEN28
WHEN'x'THEN29
WHEN'y'THEN30
WHEN'z'THEN31
end
RETURN @idx;
END

-----base 32 mapping function ---------------------------------------------------------
CREATEFUNCTION [dbo].[geohash_base32] (
 @_index TINYINT
)
RETURNSCHAR(1)
AS
BEGIN
DECLARE @ch CHAR(1)

set @ch =CASE @_index
WHEN0THEN'0'
WHEN1THEN'1'
WHEN2THEN'2'
WHEN3THEN'3'
WHEN4THEN'4'
WHEN5THEN'5'
WHEN6THEN'6'
WHEN7THEN'7'
WHEN8THEN'8'
WHEN9THEN'9'
WHEN10THEN'b'
WHEN11THEN'c'
WHEN12THEN'd'
WHEN13THEN'e'
WHEN14THEN'f'
WHEN15THEN'g'
WHEN16THEN'h'
WHEN17THEN'j'
WHEN18THEN'k'
WHEN19THEN'm'
WHEN20THEN'n'
WHEN21THEN'p'
WHEN22THEN'q'
WHEN23THEN'r'
WHEN24THEN's'
WHEN25THEN't'
WHEN26THEN'u'
WHEN27THEN'v'
WHEN28THEN'w'
WHEN29THEN'x'
WHEN30THEN'y'
WHEN31THEN'z'
END

RETURN @ch;
END
-----Shift Right funciton needed to decode----------------------------------------------
CREATEFUNCTION [dbo].[shiftRight] (
	@x	INT,
	@s	INT
)
RETURNSint
AS
begin
declare @y int
declare @pow int
set @pow =POWER(CAST(2ASBIGINT), @s & 0x1F)

if @x >=0
set @y =CAST(@x / @pow ASINT)
else
set @y =CAST(~@x / @pow ASINT)
return @y;
end
GO


----Actual decode function---------------------------------------------------------------
CREATEFUNCTION [dbo].[decode_geohash] (
 @_geohash VARCHAR(12)
)
RETURNSVARCHAR(256)
AS
BEGIN
DECLARE @latMin decimal(10, 7)
DECLARE @latMax decimal(10, 7)
SET @latMin =-90.0
SET @latMax =90.00

DECLARE @lonMin decimal(10, 7)
DECLARE @lonMax decimal(10, 7)
SET @lonMin =-180.0
SET @lonMax =180.0

DECLARE @chr CHAR(1)
DECLARE @idx INT
set @idx =0

DECLARE @even BIT
set @even =1
DECLARE @geohash_length TINYINT
set @geohash_length =0
DECLARE @geohash_pos TINYINT
SET @geohash_pos =0
DECLARE @n int

DECLARE @buf VARCHAR(77)

SET @geohash_length =LEN(@_geohash);
WHILE @geohash_pos < @geohash_length
begin
SET @chr =SUBSTRING(@_geohash, @geohash_pos +1, 1)
SET @idx =dbo.geohash_base32_index(@chr);
SET @n =4;
WHILE @n >=0
begin
DECLARE @bitN tinyint
set @bitN =dbo.shiftRight(@idx, @n) & 1;
IF @even =1
begin
DECLARE @lonMid decimal(10, 7)
SET @lonMid = (@lonMin + @lonMax) /2;
IF @bitN =1
SET @lonMin = @lonMid;
ELSE
SET @lonMax = @lonMid;
end
ELSE
begin
DECLARE @latMid decimal(10, 7)
SET @latMid = (@latMin + @latMax) /2;
IF @bitN =1
SET @latMin = @latMid;
ELSE
SET @latMax = @latMid;
end
SET @even = ~@even;
SET @n = @n -1;
end
SET @geohash_pos = @geohash_pos +1;
end

declare @latAvg decimal(10,7)
set @latAvg = (@latMax + @latMin) /2

declare @lonAvg decimal(10,7)
set @lonAvg = (@lonMax + @lonMin) /2
return'POINT('+CONVERT(VARCHAR(30), @lonAvg) +' '+CONVERT(VARCHAR(30), @latAvg) +')'
END

GO