Get geohash neighbors with PostGIS

Question

I’d like to get the 8 neighbors of the geohash grid. For example, the neighbors of the hashcode dqcjqc are dqcjqf,dqcjqb,dqcjr1,dqcjq9,dqcjqd,dqcjr4,dqcjr0,dqcjq8.

I wonder how to do it in PostGIS?

---

PS: I know there's an implementation in R: https://rdrr.io/cran/geohash/man/neighbours.html

and an implmentation in Javascript: https://github.com/davetroy/geohash-js

Answer 1

I had have to do this a while ago, but I had to recreate from the top of my head here; this should be correct for all but the top-level 1-digit hashes¹.

Edit 1:

I looked up my former implementation; needed to add conditional rounding directions (FLOOR vs CEIL). Also added a safeguard against out-of-bounds coordinates, necessary to allow top-level 1-digit hashes. Results are, as far as I could test, consistent with the mentioned implementations.

WITH
    geo AS (
      SELECT
        'dqcjqc' AS hash
    )
SELECT
  ST_GeoHash(
    ST_MakePoint(px, py),
    len
  ) AS n_hash
FROM
  geo,
  LATERAL LENGTH(hash) AS len,
  LATERAL ST_PointFromGeoHash(hash, len) AS pt,
  Generate_Series(-1, 1, 1) AS x,
  Generate_Series(-1, 1, 1) AS y,
  LATERAL CAST(ST_X(pt) + x*360.0/(2^CEIL(len / 2.0)*4^len) AS FLOAT) AS px,
  LATERAL CAST(ST_Y(pt) + y*180.0/(2^FLOOR(len / 2.0)*4^len) AS FLOAT) AS py
WHERE
  NOT (x = 0 AND y = 0)
  AND
  NOT (px < -180.0 OR px > 180.0)
  AND
  NOT (py < -90.0 OR py > 90.0)
;

---

Edit 2:

See here for two more sophisticated and efficient PL/pgSQL function implementations.

---

¹ You'd need some more complex conditional logic which would probably better fit into a function.

Answer 2

Sorry, I'm 5 years late... A reliable solution. Based on this old Javascript of movable-type.co.uk/scripts, that was based this oldest (2008) library, https://github.com/davetroy/geohash-js

Pure string solution

All SQL IMMUTABLE functions, with good optimization in any context. The main one is geohash_neighbours(geohash) that returns the array of neighbours Geohashes.

CREATE FUNCTION geohash_adjacent(
  geohash text,  -- Cell to which adjacent cell is required.
  direction text --  Direction from geohash (N/S/E/W).
) RETURNS text AS $f$
    WITH prepare AS (
      SELECT *, CASE WHEN length(geohash)=0 OR direction !~ '^[nsew]$' THEN true ELSE false END AS error
      FROM (
        SELECT lower(geohash) AS geohash,
               lower(direction) AS direction,
               json_build_object(
                'n', '["p0r21436x8zb9dcf5h7kjnmqesgutwvy","bc01fg45238967deuvhjyznpkmstqrwx"]'::jsonb,
                's', '["14365h7k9dcfesgujnmqp0r2twvyx8zb","238967debc01fg45kmstqrwxuvhjyznp"]'::jsonb,
                'e', '["bc01fg45238967deuvhjyznpkmstqrwx","p0r21436x8zb9dcf5h7kjnmqesgutwvy"]'::jsonb,
                'w', '["238967debc01fg45kmstqrwxuvhjyznp","14365h7k9dcfesgujnmqp0r2twvyx8zb"]'::jsonb
               ) AS neighbour,
               json_build_object(
                'n', '[ "prxz",     "bcfguvyz" ]'::jsonb,
                's', '[ "028b",     "0145hjnp" ]'::jsonb,
                'e', '[ "bcfguvyz", "prxz"     ]'::jsonb,
                'w', '[ "0145hjnp", "028b"     ]'::jsonb
             ) AS border,
             right(geohash,1) AS lastCh,               -- last character of hash. '4'
             left(geohash,length(geohash)-1) AS parent, -- hash without last character. '123'... Or NULL
             length(geohash) % 2 AS type,
             '0123456789bcdefghjkmnpqrstuvwxyz' AS base32_alphabet
        ) t0
    )
            --  append letter for direction to parent:
    SELECT parent || substring(base32_alphabet, position(lastCh IN neighbour->direction->>type), 1)
    FROM (
       SELECT geohash,direction,neighbour,border,lastch,type, error,base32_alphabet,
           CASE -- check for edge-cases which don't share common prefix:
              WHEN error THEN NULL
              WHEN position(lastCh IN border->direction->>type)>0 AND parent is not NULL THEN geohash_adjacent(parent, direction)
           ELSE parent
         END AS parent
       FROM prepare
    ) t2
$f$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION geohash_neighbours_directions(geohash text) RETURNS jsonb AS $f$
/**
 * Returns all 8 adjacent cells to specified geohash.
 *
 * @param   {string} geohash - Geohash neighbours are required of.
 * @returns {{n,ne,e,se,s,sw,w,nw: string}}
 * @throws  Invalid geohash.
 */
  SELECT json_build_object(
        'n',  geohash_adjacent(geohash, 'n'),
        'ne', geohash_adjacent(geohash_adjacent(geohash, 'n'), 'e'),
        'e',  geohash_adjacent(geohash, 'e'),
        'se', geohash_adjacent(geohash_adjacent(geohash, 's'), 'e'),
        's',  geohash_adjacent(geohash, 's'),
        'sw', geohash_adjacent(geohash_adjacent(geohash, 's'), 'w'),
        'w',  geohash_adjacent(geohash, 'w'),
        'nw', geohash_adjacent(geohash_adjacent(geohash, 'n'), 'w')
    )
$f$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION geohash_neighbours(geohash text) RETURNS text[] AS $wrap$
  SELECT array[n->>'n', n->>'ne', n->>'e', n->>'se', n->>'s', n->>'sw', n->>'w', n->>'nw']
  FROM (SELECT geohash_neighbours_directions(geohash)) t(n)
$wrap$ LANGUAGE SQL IMMUTABLE;

---

Compare with the brute-force function

Try your self, checking errors and performance.

CREATE FUNCTION array_sort ( ANYARRAY ) RETURNS ANYARRAY AS $f$
  SELECT ARRAY(SELECT unnest($1) ORDER BY 1)
$f$ language SQL strict IMMUTABLE;

CREATE FUNCTION geohash_neighbours_brute(hash text) RETURNS text[] AS $f$
  -- @geozelot's solution
 SELECT
  array_agg(ST_GeoHash(
    ST_MakePoint(ST_X(pt) + x*360.0/d, ST_Y(pt) + y*180.0/d),
    len
  ))
 FROM
  (SELECT LENGTH(hash) AS len) t,
  LATERAL CAST(2^CEIL(len / 2.0)*4^len AS FLOAT) AS d,
  LATERAL ST_PointFromGeoHash(hash, len) AS pt,
  Generate_Series(-1, 1) AS x,
  Generate_Series(-1, 1) AS y
 WHERE NOT (x = 0 AND y = 0)
$f$ LANGUAGE SQL IMMUTABLE;

Example shoing errors on "brute":

SELECT
    array_sort(  geohash_neighbours(ghs)  ) AS good,
    array_sort(  geohash_neighbours_brute(ghs)  ) AS brute
FROM (SELECT unnest('{6gf,6ge,6u6,75b}'::text[])) t(ghs);

good	brute (with ERRORS!)
{6g9,6gc,6gd,6ge,6gg,6u1,6u4,6u5}	{6gc,6gc,6gf,6gg,6gg,6u1,6u4,6u5}
{6g6,6g7,6gd,6gf,6gg,6gk,6gs,6gu}	{6gd,6gd,6ge,6gf,6gg,6gs,6gs,6gu}
{6u1,6u3,6u4,6u5,6u7,6u9,6ud,6ue}	{6u3,6u3,6u6,6u7,6u7,6u9,6ud,6ue}
{6gx,6gz,6up,758,759,75c,7h0,7h1}	{6gz,6gz,6up,75b,75c,75c,7h0,7h1}

Performance: why (!?) string function consuming a lot more? ... Perhaps needs to optimize, a "no JSON" and "no recursion" solution.

EXPLAIN  ANALYZE SELECT array_sort(  geohash_neighbours_brute(ghs)  ) AS brute
FROM (SELECT unnest('{6gf,6ge,6u6,75b,06,sj8101b0,s012}'::text[])) t(ghs),
     generate_series(1, 10) as a(n);
 -- Planning Time: 0.227 ms
 -- Execution Time: 4.137 ms

EXPLAIN  ANALYZE SELECT array_sort(  geohash_neighbours(ghs)  ) AS good
FROM (SELECT unnest('{6gf,6ge,6u6,75b,06,sj8101b0,s012}'::text[])) t(ghs),
     generate_series(1, 10) as a(n);

 -- Planning Time: 0.099 ms
 -- Execution Time: 5396.184 ms

---

Edit by @geozelot:

An improved set of functions replicating the string-based approach mentioned in this answer:

CREATE OR REPLACE FUNCTION ST_GeoHashAdjacent(
  IN  ref_hash  TEXT,
  IN  direction TEXT,
  OUT neighbor  TEXT
) LANGUAGE 'plpgsql' IMMUTABLE STRICT AS
  $FUNCTION$
    DECLARE
      _lu_d TEXT   := 'n s e w';
      _b_32 TEXT[] := ARRAY[
        '0','1','2','3','4','5','6','7','8','9','b','c','d','e','f','g','h','j','k','m','n','p','q','r','s','t','u','v','w','x','y','z'
      ];
      _g_nb TEXT[] := ARRAY[
        'p0r21436x8zb9dcf5h7kjnmqesgutwvy', 'bc01fg45238967deuvhjyznpkmstqrwx',
        '14365h7k9dcfesgujnmqp0r2twvyx8zb', '238967debc01fg45kmstqrwxuvhjyznp',
        'bc01fg45238967deuvhjyznpkmstqrwx', 'p0r21436x8zb9dcf5h7kjnmqesgutwvy',
        '238967debc01fg45kmstqrwxuvhjyznp', '14365h7k9dcfesgujnmqp0r2twvyx8zb'
      ];
      _g_bd TEXT[] := ARRAY[
        'prxz',     'bcfguvyz',
        '028b',     '0145hjnp',
        'bcfguvyz', 'prxz',
        '0145hjnp', '028b'
      ];
                      
      _h_pf TEXT   := LEFT($1, -1);
      _h_lc TEXT   := RIGHT($1, 1);
    
      _h_tp INT    := LENGTH($1) % 2;
      
      __ai  INT    := STRPOS(_lu_d, $2) + _h_tp;
      
    BEGIN
      If STRPOS(_g_bd[__ai], _h_lc) > 0 AND _h_pf <> ''
        THEN _h_pf := ST_GeoHashAdjacent(_h_pf, $2);
      END IF;
          
      neighbor := _h_pf || _b_32[STRPOS(_g_nb[__ai], _h_lc)];
    END;
  $FUNCTION$
;

CREATE OR REPLACE FUNCTION ST_GeoHashNeighbors(
  IN  center_hash TEXT,
  OUT neighbors   TEXT[]
) LANGUAGE 'plpgsql' IMMUTABLE STRICT AS
  $FUNCTION$
    BEGIN
      neighbors := ARRAY[
        ST_GeoHashAdjacent(ST_GeoHashAdjacent($1, 'n'), 'w'),
        ST_GeoHashAdjacent($1, 'n'),
        ST_GeoHashAdjacent(ST_GeoHashAdjacent($1, 'n'), 'e'),
        ST_GeoHashAdjacent($1, 'e'),
        ST_GeoHashAdjacent(ST_GeoHashAdjacent($1, 's'), 'e'),
        ST_GeoHashAdjacent($1, 's'),
        ST_GeoHashAdjacent(ST_GeoHashAdjacent($1, 's'), 'w'),
        ST_GeoHashAdjacent($1, 'w')
      ];
    END;
  $FUNCTION$
;

They now perform only slightly worse than the optimized grid-based approach.