最近研究了一下GEOHASH，的確比較好用。具體的原理，就不多說了，網上資料很多。不過大多數都是java或者PHP實現的。下面提供一個mysql的function實現。有興趣的朋友可以用用。

使用效果：

encode

decode

?

function源碼：

CREATE DEFINER=`root`@`%` FUNCTION `geohash_base32`(_index TINYINT UNSIGNED) RETURNS char(1) CHARSET latin1NO SQLDETERMINISTICCOMMENT 'geohash_base32(0) => "0", geohash_base32(31) => "z"' BEGINDECLARE ch CHAR(1) DEFAULT NULL;CASE _indexWHEN 0 THEN SET ch = '0';WHEN 1 THEN SET ch = '1';WHEN 2 THEN SET ch = '2';WHEN 3 THEN SET ch = '3';WHEN 4 THEN SET ch = '4';WHEN 5 THEN SET ch = '5';WHEN 6 THEN SET ch = '6';WHEN 7 THEN SET ch = '7';WHEN 8 THEN SET ch = '8';WHEN 9 THEN SET ch = '9';WHEN 10 THEN SET ch = 'b';WHEN 11 THEN SET ch = 'c';WHEN 12 THEN SET ch = 'd';WHEN 13 THEN SET ch = 'e';WHEN 14 THEN SET ch = 'f';WHEN 15 THEN SET ch = 'g';WHEN 16 THEN SET ch = 'h';WHEN 17 THEN SET ch = 'j';WHEN 18 THEN SET ch = 'k';WHEN 19 THEN SET ch = 'm';WHEN 20 THEN SET ch = 'n';WHEN 21 THEN SET ch = 'p';WHEN 22 THEN SET ch = 'q';WHEN 23 THEN SET ch = 'r';WHEN 24 THEN SET ch = 's';WHEN 25 THEN SET ch = 't';WHEN 26 THEN SET ch = 'u';WHEN 27 THEN SET ch = 'v';WHEN 28 THEN SET ch = 'w';WHEN 29 THEN SET ch = 'x';WHEN 30 THEN SET ch = 'y';WHEN 31 THEN SET ch = 'z';END CASE;RETURN ch;ENDCREATE DEFINER=`root`@`%` FUNCTION `geohash_base32_index`(_ch CHAR(1)) RETURNS tinyint(3) unsignedNO SQLDETERMINISTICCOMMENT 'geohash_base32_index("b") => 10, geohash_base32_index("z") => 31' BEGINDECLARE idx TINYINT UNSIGNED DEFAULT NULL;CASE _chWHEN '0' THEN SET idx = 0;WHEN '1' THEN SET idx = 1;WHEN '2' THEN SET idx = 2;WHEN '3' THEN SET idx = 3;WHEN '4' THEN SET idx = 4;WHEN '5' THEN SET idx = 5;WHEN '6' THEN SET idx = 6;WHEN '7' THEN SET idx = 7;WHEN '8' THEN SET idx = 8;WHEN '9' THEN SET idx = 9;WHEN 'b' THEN SET idx = 10;WHEN 'c' THEN SET idx = 11;WHEN 'd' THEN SET idx = 12;WHEN 'e' THEN SET idx = 13;WHEN 'f' THEN SET idx = 14;WHEN 'g' THEN SET idx = 15;WHEN 'h' THEN SET idx = 16;WHEN 'j' THEN SET idx = 17;WHEN 'k' THEN SET idx = 18;WHEN 'm' THEN SET idx = 19;WHEN 'n' THEN SET idx = 20;WHEN 'p' THEN SET idx = 21;WHEN 'q' THEN SET idx = 22;WHEN 'r' THEN SET idx = 23;WHEN 's' THEN SET idx = 24;WHEN 't' THEN SET idx = 25;WHEN 'u' THEN SET idx = 26;WHEN 'v' THEN SET idx = 27;WHEN 'w' THEN SET idx = 28;WHEN 'x' THEN SET idx = 29;WHEN 'y' THEN SET idx = 30;WHEN 'z' THEN SET idx = 31;END CASE;RETURN idx;ENDCREATE DEFINER=`root`@`%` FUNCTION `geohash_bit`(_bit TINYINT UNSIGNED) RETURNS tinyint(3) unsignedNO SQLDETERMINISTICCOMMENT 'geohash_bit(0) => 16, geohash_bit(1) => 8' BEGINDECLARE bit TINYINT UNSIGNED DEFAULT NULL;CASE _bitWHEN 0 THEN SET bit = 16;WHEN 1 THEN SET bit = 8;WHEN 2 THEN SET bit = 4;WHEN 3 THEN SET bit = 2;WHEN 4 THEN SET bit = 1;END CASE;RETURN bit;ENDCREATE DEFINER=`root`@`%` FUNCTION `geohash_decode`(_geohash varchar(12)) RETURNS char(77) CHARSET latin1COMMENT 'geohash_decode(u4pru) => csv' BEGINDECLARE latL DOUBLE(10, 7) DEFAULT -90.0;DECLARE latR DOUBLE(10, 7) DEFAULT 90.0;DECLARE lonT DOUBLE(10, 7) DEFAULT -180.0;DECLARE lonB DOUBLE(10, 7) DEFAULT 180.0;DECLARE lat_err DOUBLE(10, 7) DEFAULT 90.0;DECLARE lon_err DOUBLE(10, 7) DEFAULT 180.0;DECLARE ch CHAR(1) DEFAULT '';DECLARE ch_pos INT UNSIGNED DEFAULT 0;DECLARE even TINYINT UNSIGNED DEFAULT 1;DECLARE geohash_length TINYINT UNSIGNED DEFAULT 0;DECLARE geohash_pos TINYINT UNSIGNED DEFAULT 0;DECLARE pos TINYINT UNSIGNED DEFAULT 0;DECLARE mask TINYINT UNSIGNED DEFAULT 0;DECLARE masked_val TINYINT UNSIGNED DEFAULT 0;DECLARE buf VARCHAR(77) DEFAULT '';SET geohash_length = LENGTH(_geohash);WHILE geohash_pos < geohash_lengthDOSET ch=substr(_geohash,geohash_pos+1,1);SET ch_pos = geohash_base32_index(ch);SET pos = 0;WHILE pos < 5DOSET mask = geohash_bit(pos);SET masked_val = ch_pos & mask;IF even = 1 THENSET lon_err = lon_err / 2;IF masked_val != 0 THENSET lonT = (lonT + lonB) / 2;ELSESET lonB = (lonT + lonB) / 2;END IF;ELSESET lat_err = lat_err / 2;IF masked_val != 0 THENSET latL = (latL + latR) / 2;ELSESET latR = (latL + latR) / 2;END IF;END IF;SET even = !even;SET pos = pos + 1;END WHILE;SET geohash_pos = geohash_pos + 1;END WHILE;SET lat_err = (latL + latR) / 2;SET lon_err = (lonT + lonB) / 2;SET buf = CONCAT(buf, latL, ',', lonT);SET buf = CONCAT(buf, ';');SET buf = CONCAT(buf, latR, ',', lonB);SET buf = CONCAT(buf, ';');SET buf = CONCAT(buf, lat_err, ',', lon_err);RETURN buf;ENDCREATE DEFINER=`root`@`%` FUNCTION `geohash_encode`(_latitude DOUBLE(10, 7),_longitude DOUBLE(10, 7),_precision TINYINT UNSIGNED) RETURNS varchar(12) CHARSET latin1NO SQLDETERMINISTICCOMMENT 'geohash_encode(57.64911, 10.40744, 12) => u4pruydqquvx' BEGINDECLARE latL DOUBLE(10, 7) DEFAULT -90.0;DECLARE latR DOUBLE(10, 7) DEFAULT 90.0;DECLARE lonT DOUBLE(10, 7) DEFAULT -180.0;DECLARE lonB DOUBLE(10, 7) DEFAULT 180.0;DECLARE bit TINYINT UNSIGNED DEFAULT 0;DECLARE bit_pos TINYINT UNSIGNED DEFAULT 0;DECLARE ch CHAR(1) DEFAULT '';DECLARE ch_pos INT UNSIGNED DEFAULT 0;DECLARE mid DOUBLE(10, 7) DEFAULT NULL;DECLARE even TINYINT UNSIGNED DEFAULT 1;DECLARE geohash VARCHAR(12) DEFAULT '';DECLARE geohash_length TINYINT UNSIGNED DEFAULT 0;IF _precision IS NULL THENSET _precision = 12;END IF;WHILE geohash_length < _precision DOIF even = 1 THEN---- is even--SET mid = (lonT + lonB) / 2;IF mid < _longitude THENSET bit = geohash_bit(bit_pos);SET ch_pos = ch_pos | bit;SET lonT = mid;ELSESET lonB = mid;END IF;ELSE---- not even--SET mid = (latL + latR) / 2;IF mid < _latitude THENSET bit = geohash_bit(bit_pos);SET ch_pos = ch_pos | bit;SET latL = mid;ELSESET latR = mid;END IF;END IF;-- toggle evenSET even = !even;IF bit_pos < 4 THENSET bit_pos = bit_pos + 1;ELSESET ch = geohash_base32(ch_pos);SET geohash = CONCAT(geohash, ch);SET bit_pos = 0;SET ch_pos = 0;END IF;SET geohash_length = LENGTH(geohash);END WHILE;RETURN geohash;END

該mysql存過，修改自GIthub開源項目。

總結

以上是生活随笔為你收集整理的Mysql GEOHASH function 实现的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。