MySQL Queries on GeoHash with arbitrary Base32 encoding table.

geohash.sql

--
-- Query unique GeoHash prefixes
--
SELECT
  SUBSTR(p.last_location, 1, :length) prefix,
  COUNT(p.id) count
FROM
  user_profile p
GROUP BY prefix;

--
-- Create Number Sequence Table
--

DROP TABLE IF EXISTS sequence;

CREATE TABLE sequence (value int NOT NULL UNIQUE AUTO_INCREMENT);

INSERT INTO sequence SELECT @row := @row + 1 as value FROM
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(SELECT @row:=0) r;

SELECT * FROM sequence; -- 10000

--
-- Create GeoHash Encoding Map Table
--

DROP TABLE IF EXISTS geohash_table;

CREATE TABLE geohash_table (
  value INT UNIQUE NOT NULL,
  code CHAR(1) UNIQUE NOT NULL,
  KEY (value), KEY (code)
) CHARACTER SET utf8;

INSERT INTO geohash_table (value, code)
  SELECT n.row - 1, SUBSTR(p.v,n.row, 1) AS geohash
  FROM (SELECT `value` AS row FROM sequence LIMIT 32) n
    INNER JOIN (SELECT '0123456789bcdefghjkmnpqrstuvwxyz' v) p
      ON n.row <= LENGTH(p.v) + 1;

--
-- Find nearest location, based on the similarity of b32 encoded GeoHash string
--
SELECT p.id, count(p.geohash) AS score, p.last_active_at
FROM (
       SELECT substr(p.last_location, 1, n.idx) AS geohash
       FROM
         (SELECT value AS idx FROM sequence LIMIT 12) n
         INNER JOIN
         (SELECT 'wx4gd2czvktp' last_location) p ON n.idx <= length(p.last_location)
     ) sample
  INNER JOIN (
               SELECT p.id, substr(p.last_location,1, n.idx) AS geohash, p.last_active_at
               FROM
                 (SELECT value AS idx FROM sequence LIMIT 12) n
                 INNER JOIN
                 (SELECT * FROM user_profile) p ON n.idx <= length(p.last_location)
             ) p
    ON p.geohash = sample.geohash
GROUP BY id
ORDER BY score DESC, last_active_at DESC;

--
-- Convert b32 encoded GeoHash into binary
--
SELECT p.id, p.last_active_at, GROUP_CONCAT(p.geohash_b ORDER BY idx SEPARATOR '') geohash_b
FROM (
       SELECT x.*, substr(concat('0000', conv(c.value, 10, 2)), -5) AS geohash_b
       FROM (
              SELECT p.id, n.idx, substr(p.last_location,n.idx, 1) AS geohash, p.last_active_at
              FROM (SELECT value as idx FROM sequence LIMIT 12) n
                INNER JOIN (SELECT * FROM user_profile) p
                  ON n.idx <= length(p.last_location)
            ) x
         LEFT JOIN geohash_table c ON c.code = geohash
     ) p GROUP BY id;

--
-- Find nearest location, based on the similarity of binary GeoHash string (better accuracy, costs more resources)
--
SELECT p.id, count(p.geohash) AS score, p.last_active_at
FROM (
       SELECT substr(p.last_location, 1, n.idx) AS geohash
       FROM
         (SELECT value AS idx FROM sequence LIMIT 60) n
         INNER JOIN
         (SELECT 0 AS id, GROUP_CONCAT(p.geohash_b ORDER BY idx SEPARATOR '') last_location
          FROM (
                 SELECT x.*, c.value, substr(concat('0000', conv(c.value, 10, 2)), -5) AS geohash_b
                 FROM (
                        SELECT n.idx, substr(p.last_location,n.idx, 1) AS geohash
                        FROM (SELECT value as idx FROM sequence LIMIT 12) n
                          INNER JOIN (SELECT 'wx4gd2czvktp' last_location) p
                            ON n.idx <= length(p.last_location)
                      ) x
                   LEFT JOIN geohash_table c ON c.code = geohash
               ) p GROUP BY id) p ON n.idx <= length(p.last_location)
     ) sample
  INNER JOIN (
               SELECT p.id, substr(p.last_location,1, n.idx) AS geohash, p.last_active_at
               FROM
                 (SELECT value AS idx FROM sequence LIMIT 60) n
                 INNER JOIN
                 (SELECT p.id, p.last_active_at, GROUP_CONCAT(p.geohash_b ORDER BY idx SEPARATOR '') last_location
                  FROM (
                         SELECT x.*, c.value, substr(concat('0000', conv(c.value, 10, 2)), -5) AS geohash_b
                         FROM (
                                SELECT p.id, n.idx, substr(p.last_location,n.idx, 1) AS geohash, p.last_active_at
                                FROM (SELECT value as idx FROM sequence LIMIT 12) n
                                  INNER JOIN (SELECT * FROM user_profile) p
                                    ON n.idx <= length(p.last_location)
                              ) x
                           LEFT JOIN geohash_table c ON c.code = geohash
                       ) p GROUP BY id) p ON n.idx <= length(p.last_location)
             ) p
    ON p.geohash = sample.geohash
GROUP BY id
ORDER BY score DESC, last_active_at DESC;