Mysql GEOHASH function 实现

最近研究了一下GEOHASH，的确比较好用。具体的原理，就不多说了，网上资料很多。不过大多数都是java或者PHP实现的。下面提供一个mysql的function实现。有兴趣的朋友可以用用。

使用效果：

encode

decode

 

function源码：

CREATE DEFINER=`root`@`%` FUNCTION `geohash_base32`(

_index TINYINT UNSIGNED

) RETURNS char(1) CHARSET latin1
    NO SQL
    DETERMINISTIC
    COMMENT 'geohash_base32(0) => "0", geohash_base32(31) => "z"'
BEGIN

DECLARE ch CHAR(1) DEFAULT NULL;



CASE _index

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

END

CREATE DEFINER=`root`@`%` FUNCTION `geohash_base32_index`(

_ch CHAR(1)

) RETURNS tinyint(3) unsigned
    NO SQL
    DETERMINISTIC
    COMMENT 'geohash_base32_index("b") => 10, geohash_base32_index("z") => 31'
BEGIN

DECLARE idx TINYINT UNSIGNED DEFAULT NULL;



CASE _ch

WHEN '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;

END

CREATE DEFINER=`root`@`%` FUNCTION `geohash_bit`(

_bit TINYINT UNSIGNED

) RETURNS tinyint(3) unsigned
    NO SQL
    DETERMINISTIC
    COMMENT 'geohash_bit(0) => 16, geohash_bit(1) => 8'
BEGIN

DECLARE bit TINYINT UNSIGNED DEFAULT NULL;



CASE _bit

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

END

CREATE DEFINER=`root`@`%` FUNCTION `geohash_decode`(_geohash varchar(12)) RETURNS char(77) CHARSET latin1
    COMMENT 'geohash_decode(u4pru) => csv'
BEGIN

    DECLARE 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_length
        DO

            SET ch=substr(_geohash,geohash_pos+1,1);
            SET ch_pos = geohash_base32_index(ch);


            SET pos = 0;

            WHILE pos < 5
                DO

                    SET mask = geohash_bit(pos);

                    SET masked_val = ch_pos & mask;


                    IF even = 1 THEN

                        SET lon_err = lon_err / 2;


                        IF masked_val != 0 THEN

                            SET lonT = (lonT + lonB) / 2;

                        ELSE

                            SET lonB = (lonT + lonB) / 2;

                        END IF;

                    ELSE

                        SET lat_err = lat_err / 2;


                        IF masked_val != 0 THEN

                            SET latL = (latL + latR) / 2;

                        ELSE

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

END

CREATE DEFINER=`root`@`%` FUNCTION `geohash_encode`(

_latitude DOUBLE(10, 7),

_longitude DOUBLE(10, 7),

_precision TINYINT UNSIGNED

) RETURNS varchar(12) CHARSET latin1
    NO SQL
    DETERMINISTIC
    COMMENT 'geohash_encode(57.64911, 10.40744, 12) => u4pruydqquvx'
BEGIN

DECLARE 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 THEN

SET _precision = 12;

END IF;



WHILE geohash_length < _precision DO

IF even = 1 THEN

--

-- is even

--



SET mid = (lonT + lonB) / 2;

IF mid < _longitude THEN

SET bit = geohash_bit(bit_pos);



SET ch_pos = ch_pos | bit;

SET lonT = mid;

ELSE

SET lonB = mid;

END IF;

ELSE

--

-- not even

--



SET mid = (latL + latR) / 2;

IF mid < _latitude THEN

SET bit = geohash_bit(bit_pos);



SET ch_pos = ch_pos | bit;

SET latL = mid;

ELSE

SET latR = mid;

END IF;

END IF;



-- toggle even

SET even = !even;



IF bit_pos < 4 THEN

SET bit_pos = bit_pos + 1;

ELSE

SET 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开源项目。