PostgreSQL/PostGIS - Grid-based derivation of (8) neighborhood cells for a given GeoHash identifier.

ST_GeoHashNeighbors.sql

/*
 * @in_params
 * center_hash - GeoHash identifier to find adjacent cells for
 *
 * @out_params (column description)
 * hash        - GeoHash identifier for this adjacent cell
 * centroid    - Centroid geometry (POINT) for this adjacent cell
 * bound       - Cell bounds geometry (POLYGON) for this adjacent cell
 *
 * The function returns a SETOF RECORD containing the 8 direct
 * neighboring cells of the input GeoHash at the given resolution.
 * The result set is ordered top-left to bottom-right.
 */
 
CREATE OR REPLACE FUNCTION ST_GeoHashNeighbors (
  IN  center_hash TEXT,
  OUT hash        TEXT,
  OUT centroid    GEOMETRY(POINT, 4326),
  OUT bounds      GEOMETRY(POLYGON, 4326)
) RETURNS SETOF RECORD LANGUAGE 'plpgsql' IMMUTABLE STRICT AS
$FUNCTION$
  DECLARE
    _h_len INT 	    := LENGTH($1);
    _h_ctr GEOMETRY := ST_PointFromGeoHash($1);
	
    _ctr_x FLOAT8   := ST_X(_h_ctr);
    _ctr_y FLOAT8   := ST_Y(_h_ctr);
	
    __hh_len FLOAT  := _h_len/2.0;
    __qh_len FLOAT  := 4^_h_len;
	
    _dx FLOAT 	    := 360.0/(2^CEIL(__hh_len)*__qh_len);
    _dy FLOAT 	    := 180.0/(2^FLOOR(__hh_len)*__qh_len);
    
    __h_dx FLOAT    := _dx/2.0;
    __h_dy FLOAT    := _dy/2.0;
	
    _x INT;
    _y INT;
		
  BEGIN
    FOREACH _x IN ARRAY ARRAY[1, 0, -1] LOOP
      FOREACH _y IN ARRAY ARRAY[-1, 0, 1] LOOP
	IF NOT (_x = 0 AND _y = 0) THEN
	  centroid := ST_MakePoint(
	      (_ctr_x + _x * _dx) + ( (_ctr_x + _x * _dx)::INT / 180 ) * -360,
	      (_ctr_y + _y * _dy) + ( (_ctr_y + _y * _dy)::INT / 90 ) * -180
	  );
		
	  hash   := ST_GeoHash(centroid, _h_len);
	  bounds := ST_Expand(centroid, __h_dx, __h_dy);
		  
	  RETURN NEXT;
	END IF;
      END LOOP;
    END LOOP;
  END;
$FUNCTION$
;


/*
 * @in_params
 * center_hash - GeoHash identifier to find neighborhood for
 *
 * @out_params
 * nieghbors - Array of GeoHash identifiers of the 8 neighboring cells
 *
 * The function returns a TEXT[] containing the 8 direct
 * neighboring cell GeoHash identifiers.
 * The result set is ordered top-left to bottom-right.
 */
 
CREATE OR REPLACE FUNCTION ST_GeoHashNeighborhood (
  IN  center_hash TEXT,
  OUT neighbors   TEXT[]
) LANGUAGE 'plpgsql' IMMUTABLE STRICT AS
$FUNCTION$
  DECLARE
    _h_len INT 	    := LENGTH($1);
    _h_ctr GEOMETRY := ST_PointFromGeoHash($1);
	
    _ctr_x FLOAT8   := ST_X(_h_ctr);
    _ctr_y FLOAT8   := ST_Y(_h_ctr);
	
    __hh_len FLOAT  := _h_len/2.0;
    __qh_len FLOAT  := 4^_h_len;
	
    _dx FLOAT 	    := 360.0/(2^CEIL(__hh_len)*__qh_len);
    _dy FLOAT 	    := 180.0/(2^FLOOR(__hh_len)*__qh_len);
    
    __h_dx FLOAT    := _dx/2.0;
    __h_dy FLOAT    := _dy/2.0;
	
    _x INT;
    _y INT;
		
  BEGIN
    FOREACH _x IN ARRAY ARRAY[1, 0, -1] LOOP
      FOREACH _y IN ARRAY ARRAY[-1, 0, 1] LOOP
	IF NOT (_x = 0 AND _y = 0) THEN
	  neighborhood := neighborhood || ST_GeoHash(
	    ST_MakePoint(
	      (_ctr_x + _x * _dx) + ( (_ctr_x + _x * _dx)::INT / 180 ) * -360,
	      (_ctr_y + _y * _dy) + ( (_ctr_y + _y * _dy)::INT / 90 ) * -180
	    ),
	    _h_len
	  );
		
	END IF;
      END LOOP;
    END LOOP;
  END;
$FUNCTION$
;

Example:

SELECT
  ghn.hash,
  ghn.centroid   -- ghn.bounds
FROM
  ( VALUES ('6gf') ) AS input(hash),
  LATERAL ST_GeoHashNeighbors(input.hash) AS ghn
;

  hash   |                  centroid                  
---------+--------------------------------------------
 6u1     | 01010000000000000000124BC00000000000CC35C0
 6u4     | 010100000000000000005E4AC00000000000CC35C0
 6u5     | 01010000000000000000AA49C00000000000CC35C0
 6gc     | 01010000000000000000124BC000000000003437C0
 6gg     | 01010000000000000000AA49C000000000003437C0
 6g9     | 01010000000000000000124BC000000000009C38C0
 6gd     | 010100000000000000005E4AC000000000009C38C0
 6ge     | 01010000000000000000AA49C000000000009C38C0

---

SELECT
  ghn.neighbors
FROM
  ( VALUES ('6gf') ) AS input(hash),
  LATERAL ST_GeoHashNeighborhood(input.hash) AS ghn

             neighbors             
-----------------------------------
 {6u1,6u4,6u5,6gc,6gg,6g9,6gd,6ge}