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¹:

WITH
    geo AS (
      SELECT
        'dqcjqc' AS hash
    )
SELECT
  ST_GeoHash(
    ST_MakePoint(ST_X(pt) + x*360.0/d, ST_Y(pt) + y*180.0/d),
    len
  ) AS n_hash
FROM
  geo,
  LATERAL LENGTH(hash) AS len,
  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)
;

Please verify by testing before using it in production!

---

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