Debian Codegraph @

mysql-test/suite/gis/t/geohash_functions.test

raw
1 #####################################################################
2 # #
3 # This test is for validating the functionality of the different #
4 # geohash functions. #
5 # #
6 # Creation Date: 2014-06-13 #
7 # Author : Erik Frøseth #
8 # #
9 #####################################################################
10
11 #####################################################################
12 # ST_LONGFROMGEOHASH()
13 #####################################################################
14
15 # Check for all valid characters and inputs
16 --echo # valid characters
17 SELECT ST_LONGFROMGEOHASH("0");
18
19 SELECT ST_LONGFROMGEOHASH("z");
20
21 SELECT ST_LONGFROMGEOHASH("0z");
22
23 SELECT ST_LONGFROMGEOHASH("upbp");
24
25 SELECT ST_LONGFROMGEOHASH("h000");
26
27 SELECT ST_LONGFROMGEOHASH("s000");
28
29 SELECT ST_LONGFROMGEOHASH("0123456789");
30
31 SELECT ST_LONGFROMGEOHASH("9876543210");
32
33 SELECT ST_LONGFROMGEOHASH("bcdefghjkmnpqrstuvwxyz");
34
35 SELECT ST_LONGFROMGEOHASH("zyxwvutsrqpnmkjhgfedcb");
36
37 SELECT ST_LONGFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz");
38
39 SELECT ST_LONGFROMGEOHASH("pbpbpbpbpbpbpbpbpbpb");
40
41 SELECT ST_LONGFROMGEOHASH("bpbpbpbpbpbpbpbpbpbp");
42
43 SELECT ST_LONGFROMGEOHASH("00000000000000000000");
44
45 SELECT ST_LONGFROMGEOHASH("rzzzzzzzzzzzzzzzzzzz");
46
47 SELECT ST_LONGFROMGEOHASH("2pbpbpbpbpbpbpbpbpbp");
48
49 SELECT ST_LONGFROMGEOHASH("7zzzzzzzzzzzzzzzzzzz");
50
51 SELECT ST_LONGFROMGEOHASH("gzzzzzzzzzzzzzzzzzzz");
52
53 SELECT ST_LONGFROMGEOHASH("5bpbpbpbpbpbpbpbpbpb");
54
55 SELECT ST_LONGFROMGEOHASH("0000000000zzzzzzzzzz");
56
57 SELECT ST_LONGFROMGEOHASH("zzzzzzzzzz0000000000");
58
59 SELECT ST_LONGFROMGEOHASH("s000000001z7wsg7zzm6");
60
61 SELECT ST_LONGFROMGEOHASH("kpbpbpbpbnpkqe5kpbtm");
62
63 SELECT ST_LONGFROMGEOHASH("ebpbpbpbpcbe9kuebp6d");
64
65 SELECT ST_LONGFROMGEOHASH("7zzzzzzzzy0s37hs00dt");
66
67 SELECT ST_LONGFROMGEOHASH("tzzzzzzzzzzzzzzzzzzz");
68
69 SELECT ST_LONGFROMGEOHASH("jzzzzzzzzzzzzzzzzzzz");
70
71 SELECT ST_LONGFROMGEOHASH("9zzzzzzzzzzzzzzzzzzz");
72
73 SELECT ST_LONGFROMGEOHASH("1zzzzzzzzzzzzzzzzzzz");
74
75 SELECT ST_LONGFROMGEOHASH("ypzpgxczbzurypzpgxcz");
76
77 SELECT ST_LONGFROMGEOHASH("czbzurypzpgxczbzuryp");
78
79 SELECT ST_LONGFROMGEOHASH("0z0z0z0z0z0z0z0z0z0z0z0z0z0z0z0z");
80
81 SELECT ST_LONGFROMGEOHASH("0123456789bcdefghjkmnpqrstuvwxyz");
82
83 SELECT ST_LONGFROMGEOHASH("0123456789BCDEFGHJKMNPQRSTUVWXYZ");
84
85 SELECT ST_LONGFROMGEOHASH("zyxwvutsrqpnmkjhgfedcb9876543210");
86
87 SELECT ST_LONGFROMGEOHASH("ZYXWVUTSRQPNMKJHGFEDCB9876543210");
88
89 SELECT ST_LONGFROMGEOHASH("1e1");
90
91 SELECT ST_LONGFROMGEOHASH("100");
92
93 SELECT ST_LONGFROMGEOHASH(CAST(100 AS CHAR));
94
95 SELECT ST_LONGFROMGEOHASH("10111000110001111001");
96
97 SELECT ST_LONGFROMGEOHASH("11111111111111111111");
98
99 SELECT ST_LONGFROMGEOHASH("99999999999999999999");
100
101 SELECT ST_LONGFROMGEOHASH(NULL);
102
103 SELECT ST_LONGFROMGEOHASH(null);
104
105 # Invalid characters and inputs
106 --echo # invalid characters and inputs
107 --error ER_WRONG_VALUE_FOR_TYPE
108 SELECT ST_LONGFROMGEOHASH("0123a45");
109
110 --error ER_WRONG_VALUE_FOR_TYPE
111 SELECT ST_LONGFROMGEOHASH("xyzi");
112
113 --error ER_WRONG_VALUE_FOR_TYPE
114 SELECT ST_LONGFROMGEOHASH("zyxLwv");
115
116 --error ER_WRONG_VALUE_FOR_TYPE
117 SELECT ST_LONGFROMGEOHASH("bcdjo");
118
119 --error ER_WRONG_VALUE_FOR_TYPE
120 SELECT ST_LONGFROMGEOHASH("zyx**wv");
121
122 --error ER_WRONG_VALUE_FOR_TYPE
123 SELECT ST_LONGFROMGEOHASH("1 2 3 4");
124
125 --error ER_WRONG_VALUE_FOR_TYPE
126 SELECT ST_LONGFROMGEOHASH("1''2345");
127
128 --error ER_WRONG_VALUE_FOR_TYPE
129 SELECT ST_LONGFROMGEOHASH("12.345");
130
131 --error ER_WRONG_VALUE_FOR_TYPE
132 SELECT ST_LONGFROMGEOHASH(" ");
133
134 --error ER_WRONG_VALUE_FOR_TYPE
135 SELECT ST_LONGFROMGEOHASH("NULL");
136
137 --error ER_WRONG_VALUE_FOR_TYPE
138 SELECT ST_LONGFROMGEOHASH("0ë 0Ñ");
139
140 --error ER_WRONG_VALUE_FOR_TYPE
141 SELECT ST_LONGFROMGEOHASH("-100");
142
143 --error ER_WRONG_VALUE_FOR_TYPE
144 SELECT ST_LONGFROMGEOHASH("");
145
146 --error ER_INCORRECT_TYPE
147 SELECT ST_LONGFROMGEOHASH(9876543210);
148
149 --error ER_INCORRECT_TYPE
150 SELECT ST_LONGFROMGEOHASH(0123456789);
151
152 --error ER_INCORRECT_TYPE
153 SELECT ST_LONGFROMGEOHASH(1e1);
154
155 --error ER_INCORRECT_TYPE
156 SELECT ST_LONGFROMGEOHASH(CAST("012" AS BINARY));
157
158 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
159 SELECT ST_LONGFROMGEOHASH();
160
161 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
162 SELECT ST_LONGFROMGEOHASH("123","456");
163
164 --error ER_PARSE_ERROR
165 SELECT ST_LONGFROMGEOHASH("123",);
166
167 --error ER_PARSE_ERROR
168 SELECT ST_LONGFROMGEOHASH(,"456");
169
170 --error ER_PARSE_ERROR
171 SELECT ST_LONGFROMGEOHASH(,);
172
173 --error ER_PARSE_ERROR
174 SELECT ST_LONGFROMGEOHASH("0123456"789);
175
176 --error ER_BAD_FIELD_ERROR
177 SELECT ST_LONGFROMGEOHASH(abcdef);
178
179 # Test geohashes that are long
180 --echo # very long geohash
181 SELECT ST_LONGFROMGEOHASH("0123456789bcdefghjkmnpqrstuvwxyz0123456789bcdefghjkm"
182 "npqrstuvwxyz0123456789bcdefghjkmnpqrstuvwxyz01234567"
183 "89bcdefghjkmnpqrstuvwxyz");
184
185 SELECT ST_LONGFROMGEOHASH("0123456789BCDEFGHJKMNPQRSTUVWXYZ0123456789BCDEFGHJKM"
186 "NPQRSTUVWXYZ0123456789BCDEFGHJKMNPQRSTUVWXYZ01234567"
187 "89BCDEFGHJKMNPQRSTUVWXYZ");
188
189 SELECT ST_LONGFROMGEOHASH("zyxwvutsrqpnmkjhgfedcb9876543210zyxwvutsrqpnmkjhgfed"
190 "cb9876543210zyxwvutsrqpnmkjhgfedcb9876543210zyxwvuts"
191 "rqpnmkjhgfedcb9876543210");
192
193 SELECT ST_LONGFROMGEOHASH("ZYXWVUTSRQPNMKJHGFEDCB9876543210ZYXWVUTSRQPNMKJHGFED"
194 "CB9876543210ZYXWVUTSRQPNMKJHGFEDCB9876543210ZYXWVUTS"
195 "RQPNMKJHGFEDCB9876543210");
196
197 # Table with different extreme values
198 CREATE TABLE geohashes (gid INT NOT NULL PRIMARY KEY, hash_value VARCHAR(255));
199 INSERT INTO geohashes VALUES
200 (1, "000000000000000000000"),
201 (2, "zzzzzzzzzzzzzzzzzzzzz"),
202 (3, NULL),
203 (4, "s00t"),
204 (5, "7zzzm"),
205 (6, "s00d"),
206 (7, "0"),
207 (8, "z"),
208 (9, "3ejh6z75ddt2d839zh2u"),
209 (10, "twtsuqg3q7vh3nrbt0nn"),
210 (11, "yw8s10dxddhe4s06nsph"),
211 (12, "h4g4h9yrjtgzvewxm0ru"),
212 (13, "9kqbredcnhq1b44ue48s"),
213 (14, "1pckwjkqw3km0v6ye5d2"),
214 (15, "wm313fnr92ggsysm64e6"),
215 (16, "vqghx20fx6d8r5vfkbgf"),
216 (17, "wvetm3u23kr9r6663k31"),
217 (18, "e5t2p7sk291vpyb08pwu");
218
219 --echo # different random geohash values
220 SELECT ST_LONGFROMGEOHASH(hash_value) FROM geohashes;
221
222 #####################################################################
223 # ST_LATFROMGEOHASH()
224 #####################################################################
225
226 # Check for all valid characters and inputs
227 --echo # valid characters
228 SELECT ST_LATFROMGEOHASH("0");
229
230 SELECT ST_LATFROMGEOHASH("z");
231
232 SELECT ST_LATFROMGEOHASH("0z");
233
234 SELECT ST_LATFROMGEOHASH("xbpb");
235
236 SELECT ST_LATFROMGEOHASH("8000");
237
238 SELECT ST_LATFROMGEOHASH("s000");
239
240 SELECT ST_LATFROMGEOHASH("0123456789");
241
242 SELECT ST_LATFROMGEOHASH("9876543210");
243
244 SELECT ST_LATFROMGEOHASH("bcdefghjkmnpqrstuvwxyz");
245
246 SELECT ST_LATFROMGEOHASH("zyxwvutsrqpnmkjhgfedcb");
247
248 SELECT ST_LATFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz");
249
250 SELECT ST_LATFROMGEOHASH("bpbpbpbpbpbpbpbpbpbp");
251
252 SELECT ST_LATFROMGEOHASH("pbpbpbpbpbpbpbpbpbpb");
253
254 SELECT ST_LATFROMGEOHASH("00000000000000000000");
255
256 SELECT ST_LATFROMGEOHASH("gzzzzzzzzzzzzzzzzzzz");
257
258 SELECT ST_LATFROMGEOHASH("5bpbpbpbpbpbpbpbpbpb");
259
260 SELECT ST_LATFROMGEOHASH("7zzzzzzzzzzzzzzzzzzz");
261
262 SELECT ST_LATFROMGEOHASH("rzzzzzzzzzzzzzzzzzzz");
263
264 SELECT ST_LATFROMGEOHASH("2pbpbpbpbpbpbpbpbpbp");
265
266 SELECT ST_LATFROMGEOHASH("0000000000zzzzzzzzzz");
267
268 SELECT ST_LATFROMGEOHASH("zzzzzzzzzz0000000000");
269
270 SELECT ST_LATFROMGEOHASH("s000000001z7wsg7zzm6");
271
272 SELECT ST_LATFROMGEOHASH("ebpbpbpbpcbe9kuebp6d");
273
274 SELECT ST_LATFROMGEOHASH("kpbpbpbpbnpkqe5kpbtm");
275
276 SELECT ST_LATFROMGEOHASH("7zzzzzzzzy0s37hs00dt");
277
278 SELECT ST_LATFROMGEOHASH("tzzzzzzzzzzzzzzzzzzz");
279
280 SELECT ST_LATFROMGEOHASH("9zzzzzzzzzzzzzzzzzzz");
281
282 SELECT ST_LATFROMGEOHASH("jzzzzzzzzzzzzzzzzzzz");
283
284 SELECT ST_LATFROMGEOHASH("1zzzzzzzzzzzzzzzzzzz");
285
286 SELECT ST_LATFROMGEOHASH("zbzurypzpgxczbzurypz");
287
288 SELECT ST_LATFROMGEOHASH("5zpgxczbzurypzpgxczb");
289
290 SELECT ST_LATFROMGEOHASH("0z0z0z0z0z0z0z0z0z0z0z0z0z0z0z0z");
291
292 SELECT ST_LATFROMGEOHASH("0123456789bcdefghjkmnpqrstuvwxyz");
293
294 SELECT ST_LATFROMGEOHASH("0123456789BCDEFGHJKMNPQRSTUVWXYZ");
295
296 SELECT ST_LATFROMGEOHASH("zyxwvutsrqpnmkjhgfedcb9876543210");
297
298 SELECT ST_LATFROMGEOHASH("ZYXWVUTSRQPNMKJHGFEDCB9876543210");
299
300 SELECT ST_LATFROMGEOHASH("1e1");
301
302 SELECT ST_LATFROMGEOHASH("100");
303
304 SELECT ST_LATFROMGEOHASH(CAST(100 AS CHAR));
305
306 SELECT ST_LATFROMGEOHASH("10111000110001111001");
307
308 SELECT ST_LATFROMGEOHASH("11111111111111111111");
309
310 SELECT ST_LATFROMGEOHASH("99999999999999999999");
311
312 SELECT ST_LATFROMGEOHASH(NULL);
313
314 SELECT ST_LATFROMGEOHASH(null);
315
316 # Invalid characters and inputs
317 --echo # invalid characters and inputs
318 --error ER_WRONG_VALUE_FOR_TYPE
319 SELECT ST_LATFROMGEOHASH("0123a45");
320
321 --error ER_WRONG_VALUE_FOR_TYPE
322 SELECT ST_LATFROMGEOHASH("xyzi");
323
324 --error ER_WRONG_VALUE_FOR_TYPE
325 SELECT ST_LATFROMGEOHASH("zyxLwv");
326
327 --error ER_WRONG_VALUE_FOR_TYPE
328 SELECT ST_LATFROMGEOHASH("bcdjo");
329
330 --error ER_WRONG_VALUE_FOR_TYPE
331 SELECT ST_LATFROMGEOHASH("zyx**wv");
332
333 --error ER_WRONG_VALUE_FOR_TYPE
334 SELECT ST_LATFROMGEOHASH("1 2 3 4");
335
336 --error ER_WRONG_VALUE_FOR_TYPE
337 SELECT ST_LATFROMGEOHASH("1''2345");
338
339 --error ER_WRONG_VALUE_FOR_TYPE
340 SELECT ST_LATFROMGEOHASH("12.345");
341
342 --error ER_WRONG_VALUE_FOR_TYPE
343 SELECT ST_LATFROMGEOHASH(" ");
344
345 --error ER_WRONG_VALUE_FOR_TYPE
346 SELECT ST_LATFROMGEOHASH("NULL");
347
348 --error ER_WRONG_VALUE_FOR_TYPE
349 SELECT ST_LATFROMGEOHASH("0ë 0Ñ");
350
351 --error ER_WRONG_VALUE_FOR_TYPE
352 SELECT ST_LATFROMGEOHASH("-100");
353
354 --error ER_WRONG_VALUE_FOR_TYPE
355 SELECT ST_LATFROMGEOHASH("");
356
357 --error ER_INCORRECT_TYPE
358 SELECT ST_LATFROMGEOHASH(9876543210);
359
360 --error ER_INCORRECT_TYPE
361 SELECT ST_LATFROMGEOHASH(0123456789);
362
363 --error ER_INCORRECT_TYPE
364 SELECT ST_LATFROMGEOHASH(1e1);
365
366 --error ER_INCORRECT_TYPE
367 SELECT ST_LATFROMGEOHASH(CAST("012" AS BINARY));
368
369 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
370 SELECT ST_LATFROMGEOHASH();
371
372 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
373 SELECT ST_LATFROMGEOHASH("123","456");
374
375 --error ER_PARSE_ERROR
376 SELECT ST_LATFROMGEOHASH("123",);
377
378 --error ER_PARSE_ERROR
379 SELECT ST_LATFROMGEOHASH(,"456");
380
381 --error ER_PARSE_ERROR
382 SELECT ST_LATFROMGEOHASH(,);
383
384 --error ER_PARSE_ERROR
385 SELECT ST_LATFROMGEOHASH("0123456"789);
386
387 --error ER_BAD_FIELD_ERROR
388 SELECT ST_LATFROMGEOHASH(abcdef);
389
390 # Test geohashes that are long
391 --echo # very long geohash
392 SELECT ST_LATFROMGEOHASH("0123456789bcdefghjkmnpqrstuvwxyz0123456789bcdefghjkmn"
393 "pqrstuvwxyz0123456789bcdefghjkmnpqrstuvwxyz0123456789"
394 "bcdefghjkmnpqrstuvwxyz");
395
396 SELECT ST_LATFROMGEOHASH("0123456789BCDEFGHJKMNPQRSTUVWXYZ0123456789BCDEFGHJKMN"
397 "PQRSTUVWXYZ0123456789BCDEFGHJKMNPQRSTUVWXYZ0123456789"
398 "BCDEFGHJKMNPQRSTUVWXYZ");
399
400 SELECT ST_LATFROMGEOHASH("zyxwvutsrqpnmkjhgfedcb9876543210zyxwvutsrqpnmkjhgfedc"
401 "b9876543210zyxwvutsrqpnmkjhgfedcb9876543210zyxwvutsrq"
402 "pnmkjhgfedcb9876543210");
403
404 SELECT ST_LATFROMGEOHASH("ZYXWVUTSRQPNMKJHGFEDCB9876543210ZYXWVUTSRQPNMKJHGFEDC"
405 "B9876543210ZYXWVUTSRQPNMKJHGFEDCB9876543210ZYXWVUTSRQ"
406 "PNMKJHGFEDCB9876543210");
407
408 --echo # different random geohash values
409 SELECT ST_LATFROMGEOHASH(hash_value) FROM geohashes;
410
411 #####################################################################
412 # ST_POINTFROMGEOHASH()
413 #####################################################################
414
415 # Check for all valid characters and inputs
416 --echo # valid characters
417 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0", 0));
418
419 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("z", 0));
420
421 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0z", 0));
422
423 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("xbpb", 0));
424
425 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("8000", 0));
426
427 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("s000", 0));
428
429 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0123456789", 0));
430
431 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("9876543210", 0));
432
433 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("bcdefghjkmnpqrstuvwxyz", 0));
434
435 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zyxwvutsrqpnmkjhgfedcb", 0));
436
437 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz", 0));
438
439 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("bpbpbpbpbpbpbpbpbpbp", 0));
440
441 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("pbpbpbpbpbpbpbpbpbpb", 0));
442
443 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("00000000000000000000", 0));
444
445 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("gzzzzzzzzzzzzzzzzzzz", 0));
446
447 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("5bpbpbpbpbpbpbpbpbpb", 0));
448
449 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("7zzzzzzzzzzzzzzzzzzz", 0));
450
451 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("rzzzzzzzzzzzzzzzzzzz", 0));
452
453 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("2pbpbpbpbpbpbpbpbpbp", 0));
454
455 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0000000000zzzzzzzzzz", 0));
456
457 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zzzzzzzzzz0000000000", 0));
458
459 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("s000000001z7wsg7zzm6", 0));
460
461 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("ebpbpbpbpcbe9kuebp6d", 0));
462
463 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("kpbpbpbpbnpkqe5kpbtm", 0));
464
465 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("7zzzzzzzzy0s37hs00dt", 0));
466
467 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("tzzzzzzzzzzzzzzzzzzz", 0));
468
469 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("9zzzzzzzzzzzzzzzzzzz", 0));
470
471 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("jzzzzzzzzzzzzzzzzzzz", 0));
472
473 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("1zzzzzzzzzzzzzzzzzzz", 0));
474
475 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zbzurypzpgxczbzurypz", 0));
476
477 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("5zpgxczbzurypzpgxczb", 0));
478
479 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0z0z0z0z0z0z0z0z0z0z0z0z0z0z0z0z", 0));
480
481 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0123456789bcdefghjkmnpqrstuvwxyz", 0));
482
483 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0123456789BCDEFGHJKMNPQRSTUVWXYZ", 0));
484
485 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zyxwvutsrqpnmkjhgfedcb9876543210", 0));
486
487 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("ZYXWVUTSRQPNMKJHGFEDCB9876543210", 0));
488
489 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("1e1", " "));
490
491 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("100", " 0"));
492
493 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(CAST(100 AS CHAR), 0));
494
495 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("10111000110001111001", "0"));
496
497 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("11111111111111111111", 0));
498
499 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("99999999999999999999", 0));
500
501 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("00000000000000000000", " ***** "));
502
503 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("00000000000000000000", " 0 "));
504
505 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz", (CAST(0 AS CHAR))));
506
507 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(NULL, 0));
508
509 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(null, 0));
510
511 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("tzzzzzzzzzzzzzzzzzzz", NULL));
512
513 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("9zzzzzzzzzzzzzzzzzzz", null));
514
515 # Invalid characters and inputs
516 --echo # invalid characters and inputs
517 --error ER_WRONG_VALUE_FOR_TYPE
518 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0123a45", 0));
519
520 --error ER_WRONG_VALUE_FOR_TYPE
521 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("xyzi", 0));
522
523 --error ER_WRONG_VALUE_FOR_TYPE
524 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zyxLwv", 0));
525
526 --error ER_WRONG_VALUE_FOR_TYPE
527 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("bcdjo", 0));
528
529 --error ER_WRONG_VALUE_FOR_TYPE
530 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zyx**wv", 0));
531
532 --error ER_WRONG_VALUE_FOR_TYPE
533 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("1 2 3 4", 0));
534
535 --error ER_WRONG_VALUE_FOR_TYPE
536 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("1''2345", 0));
537
538 --error ER_WRONG_VALUE_FOR_TYPE
539 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("12.345", 0));
540
541 --error ER_WRONG_VALUE_FOR_TYPE
542 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(" ", 0));
543
544 --error ER_WRONG_VALUE_FOR_TYPE
545 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("NULL", 0));
546
547 --error ER_WRONG_VALUE_FOR_TYPE
548 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0ë 0Ñ", 0));
549
550 --error ER_WRONG_VALUE_FOR_TYPE
551 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("-100", 0));
552
553 --error ER_WRONG_VALUE_FOR_TYPE
554 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("", 2000));
555
556 --error ER_INCORRECT_TYPE
557 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(9876543210, 0));
558
559 --error ER_INCORRECT_TYPE
560 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(0123456789, 0));
561
562 --error ER_INCORRECT_TYPE
563 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(1e1, 0));
564
565 --error ER_INCORRECT_TYPE
566 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(CAST("012" AS BINARY), 0));
567
568 --error ER_INCORRECT_TYPE
569 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz", (CAST(0 AS BINARY))));
570
571 --error ER_DATA_OUT_OF_RANGE
572 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz", 4294967296));
573
574 --error ER_DATA_OUT_OF_RANGE
575 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("bpbpbpbpbpbpbpbpbpbp", 10000000000));
576
577 --error ER_DATA_OUT_OF_RANGE
578 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("pbpbpbpbpbpbpbpbpbpb", 4294967295000));
579
580 --error ER_DATA_OUT_OF_RANGE
581 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("gzzzzzzzzzzzzzzzzzzz", -1));
582
583 --error ER_DATA_OUT_OF_RANGE
584 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("5bpbpbpbpbpbpbpbpbpb", -1024));
585
586 --error ER_DATA_OUT_OF_RANGE
587 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("7zzzzzzzzzzzzzzzzzzz", -04294967295));
588
589 --error ER_DATA_OUT_OF_RANGE
590 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("00000000000000000000", "4294967296"));
591
592 --error ER_DATA_OUT_OF_RANGE
593 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("00000000000000000000", "10000000000"));
594
595 --error ER_DATA_OUT_OF_RANGE
596 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("00000000000000000000", "-1"));
597
598 --error ER_DATA_OUT_OF_RANGE
599 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("00000000000000000000", "-1024"));
600
601 --error ER_PARSE_ERROR
602 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("123",));
603
604 --error ER_PARSE_ERROR
605 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(,"456"));
606
607 --error ER_PARSE_ERROR
608 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(,));
609
610 --error ER_PARSE_ERROR
611 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0123456"789));
612
613 --error ER_PARSE_ERROR
614 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("7zzzzzzzzzzzzzzzzzzz", ****));
615
616 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
617 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz"));
618
619 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
620 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH());
621
622 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
623 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(abcdef));
624
625 --error ER_OPERAND_COLUMNS
626 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH((1, 1), 1));
627
628 # Test geohashes that are long
629 --echo # very long geohash
630 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0123456789bcdefghjkmnpqrstuvwxyz0123456789bc"
631 "defghjkmnpqrstuvwxyz0123456789bcdefghjkmnpqr"
632 "stuvwxyz0123456789bcdefghjkmnpqrstuvwxyz", 0));
633
634 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0123456789BCDEFGHJKMNPQRSTUVWXYZ0123456789BC"
635 "DEFGHJKMNPQRSTUVWXYZ0123456789BCDEFGHJKMNPQR"
636 "STUVWXYZ0123456789BCDEFGHJKMNPQRSTUVWXYZ", 0));
637
638 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("zyxwvutsrqpnmkjhgfedcb9876543210zyxwvutsrqpn"
639 "mkjhgfedcb9876543210zyxwvutsrqpnmkjhgfedcb98"
640 "76543210zyxwvutsrqpnmkjhgfedcb9876543210", 0));
641
642 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("ZYXWVUTSRQPNMKJHGFEDCB9876543210ZYXWVUTSRQPN"
643 "MKJHGFEDCB9876543210ZYXWVUTSRQPNMKJHGFEDCB98"
644 "76543210ZYXWVUTSRQPNMKJHGFEDCB9876543210", 0));
645
646 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("0123456789bcdefghjkmnpqrstuvwxyz0123456789bc"
647 "defghjkmnpqrstuvwxyz0123456789bcdefghjkmnpqr"
648 "stuvwxyz0123456789bcdefghjkmnpqrstuvwxyz0123"
649 "456789bcdefghjkmnpqrstuvwxyz0123456789bcdefg"
650 "hjkmnpqrstuvwxyz0123456789bcdefghjkmnpqrstuv"
651 "wxyz01234567890123456789bcdefghjkmnpqrstuvwx"
652 "yz0123456789bcdefghjkmnpqrstuvwxyz0123456789"
653 "bcdefghjkmnpqrstuvwxyz0123456789bcdefghjkmnp"
654 "qrstuvwxyz0123456789bcdefghjkmnpqrstuvwxyz01"
655 "23456789bcdefghjkmnpqrstuvwxyz0123456789bcde"
656 "fghjkmnpqrstuvwxyz", 4326));
657
658 --echo # different random geohash values
659 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(hash_value,0)) FROM geohashes;
660
661 --echo # Test create table from SELECT statement
662 CREATE TABLE t1 AS SELECT ST_POINTFROMGEOHASH("0123", 4326);
663 EXPLAIN t1;
664 DROP TABLE t1;
665
666 #####################################################################
667 # ST_GEOHASH()
668 #####################################################################
669
670 # Check for all valid inputs
671 --echo # valid inputs
672 SELECT ST_GEOHASH(0,0,1);
673
674 SELECT ST_GEOHASH(180,0,20);
675
676 SELECT ST_GEOHASH(-180,0,20);
677
678 SELECT ST_GEOHASH(0,90,20);
679
680 SELECT ST_GEOHASH(0,-90,20);
681
682 SELECT ST_GEOHASH(180,90,20);
683
684 SELECT ST_GEOHASH(180,-90,20);
685
686 SELECT ST_GEOHASH(-180,90,20);
687
688 SELECT ST_GEOHASH(-180,-90,20);
689
690 SELECT ST_GEOHASH(100,45,5);
691
692 SELECT ST_GEOHASH(100,-45,10);
693
694 SELECT ST_GEOHASH(-100,45,+50);
695
696 SELECT ST_GEOHASH(0.0001,0.0001,10);
697
698 SELECT ST_GEOHASH(0.0001,-0.0001,30);
699
700 SELECT ST_GEOHASH(-0.0001,0.0001,90);
701
702 SELECT ST_GEOHASH(-0.0001,-0.0001,100);
703
704 SELECT ST_GEOHASH("",90,10);
705
706 SELECT ST_GEOHASH(0,"90",10);
707
708 SELECT ST_GEOHASH("0","0",10);
709
710 SELECT ST_GEOHASH(180,90,"20");
711
712 SELECT ST_GEOHASH("180","90","20");
713
714 SELECT ST_GEOHASH("***",90,"20");
715
716 SELECT ST_GEOHASH(180,"***",20);
717
718 SELECT ST_GEOHASH("abcd",90,20);
719
720 SELECT ST_GEOHASH(180,"abcd",25-5);
721
722 SELECT ST_GEOHASH(NULL,90,10);
723
724 SELECT ST_GEOHASH(180,NULL,10);
725
726 SELECT ST_GEOHASH(180,90,NULL);
727
728 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)'),10);
729
730 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 0)'),20);
731
732 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-180 0)'),25);
733
734 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 90)'),30);
735
736 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 -90)'),35);
737
738 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 90)'),40);
739
740 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 -90)'),45);
741
742 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-180 90)'),50);
743
744 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-180 -90)'),55);
745
746 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(100 45)'),60);
747
748 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(100 -45)'),65);
749
750 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-100 45)'),70);
751
752 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-100 -45)'),75);
753
754 SELECT ST_GEOHASH(ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('POINT(0.0001 0.0001)'))),80);
755
756 SELECT ST_GEOHASH(ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('POINT(0.0001 -0.0001)'))),85);
757
758 SELECT ST_GEOHASH(ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('POINT(-0.0001 0.0001)'))),90);
759
760 SELECT ST_GEOHASH(ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('POINT(-0.0001 -0.0001)'))),100);
761
762 SELECT ST_GEOHASH(ST_GEOMFROMWKB(0x010100000000000000008066400000000000805640),20);
763
764 SELECT ST_GEOHASH(ST_GEOMFROMWKB(0x010100000000000000008066C00000000000805640),20);
765
766 SELECT ST_GEOHASH(ST_GEOMFROMWKB(0x00000000014066800000000000C056800000000000),20);
767
768 SELECT ST_GEOHASH(ST_GEOMFROMWKB(0x0000000001C066800000000000C056800000000000),20);
769
770 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)'),"1");
771
772 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)')," 10 ");
773
774 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)'),"+100");
775
776 SELECT ST_GEOHASH(NULL,100);
777
778 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-0.0001 -0.0001)'),NULL);
779
780 SELECT ST_GEOHASH(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(180 90)'),ST_GEOMFROMTEXT('POINT(0 0)')),20);
781
782 SELECT ST_GEOHASH(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(-180 90)'),ST_GEOMFROMTEXT('MULTIPOINT(0 0,100 100)')),20);
783
784 SELECT ST_GEOHASH(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(180 -90)'),ST_GEOMFROMTEXT('POINT(180 -90)')),20);
785
786 SELECT ST_GEOHASH(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(-180 -90)'),ST_GEOMFROMTEXT('POINT(-180 -90)')),20);
787
788 # Invalid inputs
789 --echo # invalid inputs
790 --error ER_DATA_OUT_OF_RANGE
791 SELECT ST_GEOHASH(181,0,10);
792
793 --error ER_DATA_OUT_OF_RANGE
794 SELECT ST_GEOHASH(1000,90,10);
795
796 --error ER_DATA_OUT_OF_RANGE
797 SELECT ST_GEOHASH(180.0000000000001,-90,10);
798
799 --error ER_DATA_OUT_OF_RANGE
800 SELECT ST_GEOHASH(-181,0,10);
801
802 --error ER_DATA_OUT_OF_RANGE
803 SELECT ST_GEOHASH(-1000,90,10);
804
805 --error ER_DATA_OUT_OF_RANGE
806 SELECT ST_GEOHASH(-180.0000000000001,-90,10);
807
808 --error ER_DATA_OUT_OF_RANGE
809 SELECT ST_GEOHASH(0,91,10);
810
811 --error ER_DATA_OUT_OF_RANGE
812 SELECT ST_GEOHASH(180,500,10);
813
814 --error ER_DATA_OUT_OF_RANGE
815 SELECT ST_GEOHASH(-180,90.0000000000001,10);
816
817 --error ER_DATA_OUT_OF_RANGE
818 SELECT ST_GEOHASH(0,-91,10);
819
820 --error ER_DATA_OUT_OF_RANGE
821 SELECT ST_GEOHASH(180,-500,10);
822
823 --error ER_DATA_OUT_OF_RANGE
824 SELECT ST_GEOHASH(-180,-90.0000000000001,10);
825
826 --error ER_DATA_OUT_OF_RANGE
827 SELECT ST_GEOHASH(180,90,0);
828
829 --error ER_DATA_OUT_OF_RANGE
830 SELECT ST_GEOHASH(-180,90,-1);
831
832 --error ER_DATA_OUT_OF_RANGE
833 SELECT ST_GEOHASH(180,-90,-100);
834
835 --error ER_DATA_OUT_OF_RANGE
836 SELECT ST_GEOHASH(-180,-90,101);
837
838 --error ER_DATA_OUT_OF_RANGE
839 SELECT ST_GEOHASH(0,90,1000);
840
841 --error ER_DATA_OUT_OF_RANGE
842 SELECT ST_GEOHASH("181",90,20);
843
844 --error ER_DATA_OUT_OF_RANGE
845 SELECT ST_GEOHASH("-181",90,20);
846
847 --error ER_DATA_OUT_OF_RANGE
848 SELECT ST_GEOHASH(180,"91",20);
849
850 --error ER_DATA_OUT_OF_RANGE
851 SELECT ST_GEOHASH(180,"-91",20);
852
853 --error ER_DATA_OUT_OF_RANGE
854 SELECT ST_GEOHASH(180,90,"0");
855
856 --error ER_DATA_OUT_OF_RANGE
857 SELECT ST_GEOHASH(180,90,"-1");
858
859 --error ER_DATA_OUT_OF_RANGE
860 SELECT ST_GEOHASH(180,90,"-100");
861
862 --error ER_DATA_OUT_OF_RANGE
863 SELECT ST_GEOHASH(180,90,"101");
864
865 --error ER_DATA_OUT_OF_RANGE
866 SELECT ST_GEOHASH(180,90,"1000");
867
868 --error ER_DATA_OUT_OF_RANGE
869 SELECT ST_GEOHASH(180,90,"");
870
871 --error ER_DATA_OUT_OF_RANGE
872 SELECT ST_GEOHASH(180,90,"****");
873
874 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
875 SELECT ST_GEOHASH();
876
877 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
878 SELECT ST_GEOHASH(1);
879
880 --error ER_INCORRECT_TYPE
881 SELECT ST_GEOHASH(1,2);
882
883 --error ER_INCORRECT_TYPE
884 SELECT ST_GEOHASH(-100,-45,100.1);
885
886 --error ER_PARSE_ERROR
887 SELECT ST_GEOHASH(, ,);
888
889 --error ER_PARSE_ERROR
890 SELECT ST_GEOHASH(1,2,);
891
892 --error ER_PARSE_ERROR
893 SELECT ST_GEOHASH(*,0,10);
894
895 --error ER_PARSE_ERROR
896 SELECT ST_GEOHASH(0,*,10);
897
898 --error ER_PARSE_ERROR
899 SELECT ST_GEOHASH(0,0,*);
900
901 --error ER_BAD_FIELD_ERROR
902 SELECT ST_GEOHASH(a,90,10);
903
904 --error ER_BAD_FIELD_ERROR
905 SELECT ST_GEOHASH(180,p,10);
906
907 --error ER_BAD_FIELD_ERROR
908 SELECT ST_GEOHASH(180,90,z);
909
910 --error ER_INCORRECT_TYPE
911 SELECT ST_GEOHASH((CAST(180 AS DATE)),90,10);
912
913 --error ER_INCORRECT_TYPE
914 SELECT ST_GEOHASH(180,(CAST(90 AS DATE)),10);
915
916 --error ER_DATA_OUT_OF_RANGE
917 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(181 0)'),10);
918
919 --error ER_DATA_OUT_OF_RANGE
920 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(1000 90)'),10);
921
922 --error ER_DATA_OUT_OF_RANGE
923 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180.0000000000001 -90)'),10);
924
925 --error ER_DATA_OUT_OF_RANGE
926 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-181 0)'),10);
927
928 --error ER_DATA_OUT_OF_RANGE
929 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-1000 90)'),10);
930
931 --error ER_DATA_OUT_OF_RANGE
932 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-180.0000000000001 -90)'),10);
933
934 --error ER_DATA_OUT_OF_RANGE
935 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 91)'),10);
936
937 --error ER_DATA_OUT_OF_RANGE
938 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 500)'),10);
939
940 --error ER_DATA_OUT_OF_RANGE
941 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-180 90.0000000000001)'),10);
942
943 --error ER_DATA_OUT_OF_RANGE
944 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 -91)'),10);
945
946 --error ER_DATA_OUT_OF_RANGE
947 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 -500)'),10);
948
949 --error ER_DATA_OUT_OF_RANGE
950 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-180 -90.0000000000001)'),10);
951
952 --error ER_DATA_OUT_OF_RANGE
953 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 90)'),0);
954
955 --error ER_DATA_OUT_OF_RANGE
956 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 -90)'),-1);
957
958 --error ER_DATA_OUT_OF_RANGE
959 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-180 90)'),-100);
960
961 --error ER_DATA_OUT_OF_RANGE
962 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-180 -90)'),101);
963
964 --error ER_DATA_OUT_OF_RANGE
965 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)'),10000);
966
967 --error ER_DATA_OUT_OF_RANGE
968 SELECT ST_GEOHASH(ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('POINT(180 90)'))),"0");
969
970 --error ER_DATA_OUT_OF_RANGE
971 SELECT ST_GEOHASH(ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('POINT(180 -90)'))),"-1");
972
973 --error ER_DATA_OUT_OF_RANGE
974 SELECT ST_GEOHASH(ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('POINT(-180 90)'))),"-100");
975
976 --error ER_DATA_OUT_OF_RANGE
977 SELECT ST_GEOHASH(ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('POINT(-180 -90)'))),"101");
978
979 --error ER_DATA_OUT_OF_RANGE
980 SELECT ST_GEOHASH(ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('POINT(0 0)'))),"10000");
981
982 --error ER_DATA_OUT_OF_RANGE
983 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)')," ");
984
985 --error ER_DATA_OUT_OF_RANGE
986 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)'),"***");
987
988 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
989 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)'));
990
991 --error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
992 SELECT ST_GEOHASH(ST_GEOMFROMTEXT(),20);
993
994 --error ER_PARSE_ERROR
995 SELECT ST_GEOHASH((ST_GEOMFROMTEXT('POINT(0 0)'),);
996
997 --error ER_PARSE_ERROR
998 SELECT ST_GEOHASH(,10);
999
1000 --error ER_BAD_FIELD_ERROR
1001 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)'),a);
1002
1003 --error ER_INCORRECT_TYPE
1004 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('LINESTRING(0 0,10 10)'),10);
1005
1006 --error ER_INCORRECT_TYPE
1007 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POLYGON((0 0,10 0,10 10,0 10,0 0))'),10);
1008
1009 --error ER_INCORRECT_TYPE
1010 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('MULTIPOINT(0 0,10 10)'),10);
1011
1012 --error ER_INCORRECT_TYPE
1013 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('MULTILINESTRING((0 0,10 10),(20 20,30 30))'),10);
1014
1015 --error ER_INCORRECT_TYPE
1016 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4)))'),10);
1017
1018 --error ER_INCORRECT_TYPE
1019 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))'),10);
1020
1021 --error ER_INCORRECT_TYPE
1022 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 90)'),20.0001);
1023
1024 --error ER_INCORRECT_TYPE
1025 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 90)'),(CAST(10 AS DATE)));
1026
1027 --error ER_INCORRECT_TYPE
1028 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 90)'),(CAST(10 AS BINARY)));
1029
1030 --error ER_INCORRECT_TYPE
1031 SELECT ST_GEOHASH((CAST(10 AS BINARY)),20,1);
1032
1033 --error ER_INCORRECT_TYPE
1034 SELECT ST_GEOHASH(10,(CAST(20 AS BINARY)),1);
1035
1036 --error ER_INCORRECT_TYPE
1037 SELECT ST_GEOHASH((CAST(10 AS BINARY)),(CAST(20 AS BINARY)),1);
1038
1039 --error ER_INCORRECT_TYPE
1040 SELECT ST_GEOHASH(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(180 90)'),ST_GEOMFROMTEXT('POINT(1 1)')),20);
1041
1042 --error ER_INCORRECT_TYPE
1043 SELECT ST_GEOHASH(ST_UNION(ST_GEOMFROMTEXT('POINT(180 90)'),ST_GEOMFROMTEXT('POINT(-180 -90)')),20);
1044
1045 --error ER_INCORRECT_TYPE
1046 SELECT ST_GEOHASH(ST_DIFFERENCE(ST_GEOMFROMTEXT('POINT(180 90)'),ST_GEOMFROMTEXT('MULTIPOINT(0 0,180 90)')),20);
1047
1048 --error ER_INCORRECT_TYPE
1049 SELECT ST_GEOHASH(ST_SYMDIFFERENCE(ST_GEOMFROMTEXT('POINT(180 90)'),ST_GEOMFROMTEXT('POINT(0 0)')),20);
1050
1051 --error ER_GIS_INVALID_DATA
1052 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT()'),20);
1053
1054 --error ER_GIS_INVALID_DATA
1055 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(1)'),20);
1056
1057 --error ER_GIS_INVALID_DATA
1058 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(1 a)'),20);
1059
1060 --error ER_GIS_INVALID_DATA
1061 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180,90)'),20);
1062
1063 #####################################################################
1064 # Nested geohash functions
1065 #####################################################################
1066
1067 --echo #=============================================================
1068 --echo # ST_LONGFROMGEOHASH()
1069 --echo #=============================================================
1070 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(180,90,20));
1071
1072 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(180,-90,20));
1073
1074 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(-180,90,20));
1075
1076 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(-180,-90,20));
1077
1078 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(180,0,20));
1079
1080 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(-180,0,20));
1081
1082 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(-0,0,20));
1083
1084 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(0.10,90,20));
1085
1086 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(100,45,20));
1087
1088 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(179.999999,90,20));
1089
1090 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(-179.999999,0,20));
1091
1092 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(18*10,900/10,20));
1093
1094 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(100 10)'),10));
1095
1096 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(10 10)'),10));
1097
1098 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-10 10)'),10));
1099
1100 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(1.000 10)'),10));
1101
1102 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-1.000 10)'),10));
1103
1104 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(1.00101 90)'),20));
1105
1106 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-1.00101 90)'),20));
1107
1108 SELECT ST_LONGFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(20 20)'),10));
1109
1110 --echo #=============================================================
1111 --echo # ST_LATFROMGEOHASH()
1112 --echo #=============================================================
1113 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(180,90,20));
1114
1115 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(180,-90,20));
1116
1117 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(-180,90,20));
1118
1119 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(-180,-90,20));
1120
1121 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(0,90,20));
1122
1123 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(0,-90,20));
1124
1125 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(0,-0,20));
1126
1127 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(180,0.101,20));
1128
1129 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(100,45,20));
1130
1131 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(180,89.99999990,20));
1132
1133 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(0,89.999999,20));
1134
1135 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(18*10,900/10,20));
1136
1137 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(10 50)'),10));
1138
1139 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(10 10)'),10));
1140
1141 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(10 -10)'),10));
1142
1143 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(10 1.000)'),10));
1144
1145 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(10 -1.0000)'),10));
1146
1147 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(180 1.00101)'),20));
1148
1149 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-100 -1.00101)'),20));
1150
1151 SELECT ST_LATFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(20 -20)'),10));
1152
1153 --echo #=============================================================
1154 --echo # ST_POINTFROMGEOHASH()
1155 --echo #=============================================================
1156 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(180,90,20),0));
1157
1158 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(180,-90,20),0));
1159
1160 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(-180,90,20),0));
1161
1162 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(-180,-90,20),0));
1163
1164 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(180,0,20),0));
1165
1166 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(-180,0,20),0));
1167
1168 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(0,90,20),0));
1169
1170 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(0,-90,20),0));
1171
1172 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(0,0,20),0));
1173
1174 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(-0,0,20),0));
1175
1176 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(0,-0,20),0));
1177
1178 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(0.10,90,20),0));
1179
1180 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(100,45,20),0));
1181
1182 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(179.999999,90,20),0));
1183
1184 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(-179.999999,0,20),0));
1185
1186 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(180,89.999999,20),0));
1187
1188 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(0,-89.999999,20),0));
1189
1190 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(18*10,900/10,20),0));
1191
1192 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(100 50)'),10),0));
1193
1194 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(10 10)'),10),0));
1195
1196 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-10 10)'),10),0));
1197
1198 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(1.000 1.00010)'),10),0));
1199
1200 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-1.000 -1.1010)'),10),0));
1201
1202 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(1.00101 1.000)'),10),0));
1203
1204 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(-1.00101 -1.0000)'),20),0));
1205
1206 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(ST_GEOMFROMTEXT('POINT(20 20)'),10),0));
1207
1208 --echo #=============================================================
1209 --echo # ST_GEOHASH()
1210 --echo #=============================================================
1211 SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("upbp",0),4);
1212
1213 SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("h000",0),8);
1214
1215 SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("s000",0),10);
1216
1217 SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("zzzzm",0),10);
1218
1219 SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("pbpbt",0),10);
1220
1221 SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("bpbp6",0),10);
1222
1223 SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("0000d",0),10);
1224
1225 SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("xbpbpbpbp",0),9);
1226
1227 SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("800000000",0),12);
1228
1229 SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz",0),20);
1230
1231 SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("00000000000000000000",0),20);
1232
1233 SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("pbpbpbpbpbpbpbpbpbpb",0),20);
1234
1235 SELECT ST_GEOHASH(ST_POINTFROMGEOHASH("bpbpbpbpbpbpbpbpbpbp",0),20);
1236
1237 SELECT ST_GEOHASH(ST_LONGFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz"),ST_LATFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz"),20);
1238
1239 SELECT ST_GEOHASH(ST_LONGFROMGEOHASH("00000000000000000000"),ST_LATFROMGEOHASH("00000000000000000000"),20);
1240
1241 SELECT ST_GEOHASH(ST_LONGFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz"),ST_LATFROMGEOHASH("00000000000000000000"),20);
1242
1243 SELECT ST_GEOHASH(ST_LONGFROMGEOHASH("00000000000000000000"),ST_LATFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz"),20);
1244
1245 SELECT ST_GEOHASH(ST_LONGFROMGEOHASH("pbpbpbpbpbpbpbpbpbpb"),ST_LATFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz"),20);
1246
1247 SELECT ST_GEOHASH(ST_LONGFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz"),ST_LATFROMGEOHASH("pbpbpbpbpbpbpbpbpbpb"),20);
1248
1249 SELECT ST_GEOHASH(ST_LONGFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz"),ST_LATFROMGEOHASH("bpbpbpbpbpbpbpbpbpbp"),20);
1250
1251 SELECT ST_GEOHASH(ST_LONGFROMGEOHASH("bpbpbpbpbpbpbpbpbpbp"),ST_LATFROMGEOHASH("zzzzzzzzzzzzzzzzzzzz"),20);
1252
1253 SELECT ST_GEOHASH(ST_LONGFROMGEOHASH("bpbpbpbpbpbpbpbpbpbp"),ST_LATFROMGEOHASH("pbpbpbpbpbpbpbpbpbpb"),20);
1254
1255 #####################################################################
1256 # Bug#19657725
1257 #####################################################################
1258 SELECT
1259 ST_LONGFROMGEOHASH(
1260 CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
1261 AS CHAR CHARACTER SET utf8) COLLATE utf8_persian_ci);
1262
1263 SELECT
1264 ST_LONGFROMGEOHASH(
1265 CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
1266 AS CHAR CHARACTER SET utf8) COLLATE utf8_czech_ci);
1267
1268 SELECT
1269 ST_LONGFROMGEOHASH(
1270 CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
1271 AS CHAR CHARACTER SET utf8) COLLATE utf8_unicode_ci);
1272
1273 SELECT
1274 ST_LONGFROMGEOHASH(
1275 CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
1276 AS CHAR CHARACTER SET utf8) COLLATE utf8_hungarian_ci);
1277
1278 SELECT
1279 ST_LONGFROMGEOHASH(
1280 CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
1281 AS CHAR CHARACTER SET gb18030) COLLATE gb18030_unicode_520_ci);
1282
1283 SELECT
1284 ST_LATFROMGEOHASH(
1285 CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
1286 AS CHAR CHARACTER SET utf8) COLLATE utf8_persian_ci);
1287
1288 SELECT
1289 ST_LATFROMGEOHASH(
1290 CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
1291 AS CHAR CHARACTER SET utf8) COLLATE utf8_czech_ci);
1292
1293 SELECT
1294 ST_LATFROMGEOHASH(
1295 CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
1296 AS CHAR CHARACTER SET utf8) COLLATE utf8_unicode_ci);
1297
1298 SELECT
1299 ST_LATFROMGEOHASH(
1300 CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
1301 AS CHAR CHARACTER SET utf8) COLLATE utf8_hungarian_ci);
1302
1303 SELECT
1304 ST_LATFROMGEOHASH(
1305 CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
1306 AS CHAR CHARACTER SET gb18030) COLLATE gb18030_unicode_520_ci);
1307
1308 SELECT
1309 ST_ASTEXT(
1310 ST_POINTFROMGEOHASH(
1311 CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
1312 AS CHAR CHARACTER SET utf8) COLLATE utf8_persian_ci,
1313 4326
1314 )
1315 );
1316
1317 SELECT
1318 ST_ASTEXT(
1319 ST_POINTFROMGEOHASH(
1320 CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
1321 AS CHAR CHARACTER SET utf8) COLLATE utf8_czech_ci,
1322 4326
1323 )
1324 );
1325
1326 SELECT
1327 ST_ASTEXT(
1328 ST_POINTFROMGEOHASH(
1329 CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
1330 AS CHAR CHARACTER SET utf8) COLLATE utf8_unicode_ci,
1331 4326
1332 )
1333 );
1334
1335 SELECT
1336 ST_ASTEXT(
1337 ST_POINTFROMGEOHASH(
1338 CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
1339 AS CHAR CHARACTER SET utf8) COLLATE utf8_hungarian_ci,
1340 4326
1341 )
1342 );
1343
1344 SELECT
1345 ST_ASTEXT(
1346 ST_POINTFROMGEOHASH(
1347 CAST("0123456789bcdefghjkmnpqrstuvwxyz0123456789BCDEFGHJKMNPQRSTUVWXYZ"
1348 AS CHAR CHARACTER SET gb18030) COLLATE gb18030_unicode_520_ci,
1349 4326
1350 )
1351 );
1352
1353 #####################################################################
1354 # Bug#19383904
1355 #####################################################################
1356 # This table is for testing functions with DOUBLE values. Literals like 10.2 are
1357 # recognized as DECIMAL.
1358 CREATE TABLE t1 (val DOUBLE);
1359 INSERT INTO t1 (val) VALUES (12.2);
1360 SELECT val INTO @double FROM t1;
1361 DROP TABLE t1;
1362
1363 SET @null = NULL;
1364 SET @geohash = "01bbgcee";
1365 PREPARE stmt FROM "SELECT ST_LONGFROMGEOHASH(?)";
1366 EXECUTE stmt USING @geohash;
1367 DEALLOCATE PREPARE stmt;
1368 SELECT ST_LONGFROMGEOHASH(@geohash);
1369 SELECT ST_LONGFROMGEOHASH(@null);
1370
1371 PREPARE stmt FROM "SELECT ST_LATFROMGEOHASH(?)";
1372 EXECUTE stmt USING @geohash;
1373 DEALLOCATE PREPARE stmt;
1374 SELECT ST_LATFROMGEOHASH(@geohash);
1375 SELECT ST_LATFROMGEOHASH(@null);
1376
1377 PREPARE stmt FROM "SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(?, 0))";
1378 EXECUTE stmt USING @geohash;
1379 DEALLOCATE PREPARE stmt;
1380 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(@geohash, 0));
1381
1382 SET @srid = 4326;
1383 PREPARE stmt FROM "SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(\"00\", ?))";
1384 EXECUTE stmt USING @srid;
1385 DEALLOCATE PREPARE stmt;
1386 SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("00", @srid));
1387
1388 SET @maxlen = 3;
1389 PREPARE stmt FROM "SELECT ST_GeoHash(POINT(10, 10), ?)";
1390 EXECUTE stmt USING @maxlen;
1391 DEALLOCATE PREPARE stmt;
1392 SELECT ST_GeoHash(POINT(10, 10), @maxlen);
1393 SELECT ST_GeoHash(POINT(10, 10), @null);
1394
1395 SET @point = POINT(10, 10);
1396 PREPARE stmt FROM "SELECT ST_GeoHash(?, 10)";
1397 EXECUTE stmt USING @point;
1398 DEALLOCATE PREPARE stmt;
1399 SELECT ST_GeoHash(@point, 10);
1400 SELECT ST_GeoHash(@null, 10);
1401
1402 SET @long = 22.0311;
1403 PREPARE stmt FROM "SELECT ST_GeoHash(?, 10, 10)";
1404 EXECUTE stmt USING @long;
1405 DEALLOCATE PREPARE stmt;
1406 SELECT ST_GeoHash(@long, 10, 10);
1407 SELECT ST_GeoHash(@null, 10, 10);
1408
1409 SET @lat = -54.123;
1410 PREPARE stmt FROM "SELECT ST_GeoHash(10, ?, 10)";
1411 EXECUTE stmt USING @lat;
1412 DEALLOCATE PREPARE stmt;
1413 SELECT ST_GeoHash(10, @lat, 10);
1414 SELECT ST_GeoHash(10, @null, 10);
1415
1416 PREPARE stmt FROM "SELECT ST_GeoHash(10, 10, ?)";
1417 EXECUTE stmt USING @maxlen;
1418 DEALLOCATE PREPARE stmt;
1419 SELECT ST_GeoHash(10, 10, @maxlen);
1420 SELECT ST_GeoHash(@double, @double, 10);
1421
1422 --error ER_INCORRECT_TYPE
1423 SELECT ST_GeoHash(POINT(10, 10), @double);
1424 --error ER_INCORRECT_TYPE
1425 SELECT ST_POINTFROMGEOHASH("00", @double);
1426
1427 #####################################################################
1428 # Bug#20293609
1429 #####################################################################
1430 DROP TABLE IF EXISTS t1;
1431 CREATE TABLE t1 (a VARCHAR(255));
1432 INSERT INTO t1 (a) VALUES (' ');
1433 --error ER_WRONG_VALUE_FOR_TYPE
1434 SELECT ST_LONGFROMGEOHASH(a) FROM t1;
1435 --error ER_WRONG_VALUE_FOR_TYPE
1436 SELECT ST_LATFROMGEOHASH(a) FROM t1;
1437 --error ER_WRONG_VALUE_FOR_TYPE
1438 SELECT ST_POINTFROMGEOHASH(a, 0) FROM t1;
1439
1440 DELETE FROM t1;
1441 INSERT INTO t1 VALUES ('swpwwwwww'), ('guqtjvooguqtjvoo');
1442 --error ER_WRONG_VALUE_FOR_TYPE
1443 SELECT ST_LONGFROMGEOHASH(a) FROM t1;
1444 --error ER_WRONG_VALUE_FOR_TYPE
1445 SELECT ST_LATFROMGEOHASH(a) FROM t1;
1446 --error ER_WRONG_VALUE_FOR_TYPE
1447 SELECT ST_POINTFROMGEOHASH(a, 0) FROM t1;
1448
1449 --echo # Clean up
1450 DROP TABLE geohashes;
1451
1452 DROP TABLE t1;
1453
1454
1455 --echo #
1456 --echo # Bug#22165582 ST_*FROMGEOHASH ROUNDS INCORRECTLY
1457 --echo #
1458 SELECT ST_GeoHash(ST_PointFromGeoHash('xkcd', 0), 4);
1459 SELECT ST_LongFromGeoHash('xkcd');
1460 SELECT ST_LatFromGeoHash('xkcd');
1461 SELECT ST_GeoHash(ST_PointFromGeoHash('ebrb', 0), 4);
1462 SELECT ST_LatFromGeoHash('m7s9pyctu9bbwqkgbw5x6vutzkztd9szjh86gmz9w9nsz6792d');
1463
1464 --echo #
1465 --echo # Bug#22838668 CRASH IN ITEM_FUNC_LATLONGFROMGEOHASH::VAL_REAL
1466 --echo # WITH BAD PARAMETERS
1467 --echo #
1468 --error ER_GIS_INVALID_DATA
1469 DO ST_Disjoint(
1470 POLYGON(
1471 LINESTRING(POINT(1, 1))
1472 ),
1473 ST_LatFromGeoHash(ExtractValue(1, NULL))
1474 );
1475
1476 --error ER_GIS_INVALID_DATA
1477 DO ST_Disjoint(
1478 POLYGON(
1479 LINESTRING(POINT(1, 1))
1480 ),
1481 ST_LongFromGeoHash(ExtractValue(1, NULL))
1482 );
1483
1484 --error ER_GIS_INVALID_DATA
1485 DO ST_Disjoint(
1486 POLYGON(
1487 LINESTRING(POINT(1, 1))
1488 ),
1489 ST_LatFromGeoHash(UpdateXML(1, NULL, NULL))
1490 );
1491
1492 --error ER_GIS_INVALID_DATA
1493 DO ST_Disjoint(
1494 POLYGON(
1495 LINESTRING(POINT(1, 1))
1496 ),
1497 ST_LongFromGeoHash(UpdateXML(1, NULL, NULL))
1498 );
1499
1500 --echo #
1501 --echo # WL#8579 Spatial Reference Systems
1502 --echo #
1503
1504 --echo # SRID 0 (should pass)
1505 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)', 0), 10);
1506
1507 --echo # Projected SRS (should fail)
1508 --error ER_ONLY_IMPLEMENTED_FOR_SRID_0_AND_4326
1509 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)', 2000), 10);
1510
1511 --echo # Geographic SRS (should pass)
1512 SELECT ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 10);
1513
1514 --echo #
1515 --echo # Bug#25912557 ST_LATFROMGEOHASH/ST_LONGFROMGEOHASH ODD INPUT TYPE
1516 --echo # REQUIREMENT
1517 --echo #
1518 CREATE TABLE t1 (col1 CHAR(4));
1519 INSERT INTO t1 VALUES ('bbbb');
1520 SELECT ST_LatFromGeohash(col1) FROM t1;
1521 SELECT ST_LongFromGeohash(col1) FROM t1;
1522 SELECT ST_AsText(ST_PointFromGeohash(col1, 4326)) FROM t1;
1523 DROP TABLE t1;
1524
1525 --echo #
1526 --echo # WL#11096 Don't do Cartesian computations on geographic geometries
1527 --echo #
1528
1529 --echo # Assume SRID 10 is not defined.
1530 --error ER_SRS_NOT_FOUND
1531 DO ST_GEOHASH(x'0A000000010100000000000000000000000000000000000000', 10);
1532 --error ER_SRS_NOT_FOUND
1533 DO ST_POINTFROMGEOHASH('0', 10);
1534
1535 --echo # The only geographic SRS allowed is 4326.
1536 --error ER_ONLY_IMPLEMENTED_FOR_SRID_0_AND_4326
1537 DO ST_GEOHASH(ST_GEOMFROMTEXT('POINT(0 0)', 3819), 10);