# Get geohash neighbors with PostGIS

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

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.

• Thank you! Accepting this answer! Commented May 18, 2022 at 1:39
• This is a "brute force" algorithm, there is an faster (string-only analysis) alternative, see Javascript implementation at movable-type.co.uk/scripts/geohash.html Commented Oct 2, 2022 at 12:44
• ERROR: repeat Geohashes sometimes and add `hash` it self. Commented Oct 2, 2022 at 14:38
• @PeterKrauss Thanks for validating. I looked up my earlier implementation and fixed it, see my edit. Just FYI: this is probably the exact opposite of a brute force algorithm, in that it specifically locates the neighbors with minimal theoretical complexity. While a string based approach may be faster than this when compiled as core C function, it would only ever be faster because it is low-level C vs. SQL - you will not find a faster solution than simple grid math plus low-level GeoHash encoding in SQL - and I suspect even in other languages. Commented Oct 3, 2022 at 12:35
• Hi @geozelot, you make a good job, and yes your solution is fast (!). Another advantage, it is general, it runs with other geocodes, for example on Hilbert Curve of S2 Gemetry. Commented Oct 4, 2022 at 23:37

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(
)
\$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 <> ''
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[
• Thanks @geozelot! Can you check my recursion bug? It is a final optimization before a `C++` translation Commented Oct 9, 2022 at 14:23