Skip to content

Instantly share code, notes, and snippets.

@geozelot
Last active March 25, 2024 15:13
Show Gist options
  • Save geozelot/df5ab90ccaa544da188c35d1097bd241 to your computer and use it in GitHub Desktop.
Save geozelot/df5ab90ccaa544da188c35d1097bd241 to your computer and use it in GitHub Desktop.
PostgreSQL/PostGIS - Grid-based derivation of (8) neighborhood cells for a given GeoHash identifier.
/*
* @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$
;
@geozelot
Copy link
Author

geozelot commented Oct 5, 2022

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}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment