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
	  neighbors := neighbors || 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$
;