Returns a grid of all rectangles represented by geohashes of a given length that intersect a given geometry

geohash_grid.sql

-- https://dbfiddle.uk/3tf4mT2H

CREATE OR REPLACE FUNCTION geohash_grid(original_geometry geometry, geohash_length integer)
-- Given a geometry and a geohash length, returns a grid of all rectangles
-- represented by geohashes of that length that intersect the geometry.
	RETURNS TABLE (geohash text, geom geometry)
	LANGUAGE SQL
	IMMUTABLE
	STRICT
	PARALLEL SAFE
BEGIN ATOMIC
	WITH RECURSIVE grid AS (
			-- Base case: Generate the 1-character geohashes
			SELECT
				geohash,
				1 AS geohash_length,
				geohash_geom
			FROM generate_series(1, 32) AS n,
				substr('0123456789bcdefghjkmnpqrstuvwxyz', n, 1) AS geohash,
				ST_SetSRID(ST_GeomFromGeoHash(geohash), ST_SRID($1)) AS geohash_geom
			WHERE ST_Intersects($1, geohash_geom)
		UNION ALL
			-- Recursive step: Increase number of characters and filter by intersection
			SELECT
				hash,
				g.geohash_length + 1,
				geom
			FROM grid AS g,
				generate_series(1, 32) AS n,
				concat(
					g.geohash,
					substr('0123456789bcdefghjkmnpqrstuvwxyz', n, 1)
				) AS hash,
				ST_SetSRID(ST_GeomFromGeoHash(hash), ST_SRID($1)) AS geom
			WHERE g.geohash_length < $2
				AND ST_Intersects($1, geom)
	)
	SELECT geohash, geohash_geom
	FROM grid
	WHERE geohash_length = $2;
END;


-- Test
SELECT geohash, geom
FROM geohash_grid(
	'SRID=4674;POLYGON((-46.3 -23, -44.3 -22.68, -48.3 -25, -51.4 -20.5, -46.3 -23))',
	3
);

geohash_grid_compact.sql

-- https://dbfiddle.uk/3tf4mT2H

CREATE OR REPLACE FUNCTION geohash_grid_compact(original_geometry geometry, geohash_length integer)
-- Given a geometry and a geohash length, returns a set of non-overlapping rectangles represented
-- by geohashes of that length or lower which, together, contain the geometry, aiming to minimize
-- both the number of rectangles inside the geometry and the area outside it.
	RETURNS TABLE (geohash text, geom geometry)
	LANGUAGE SQL
	IMMUTABLE
	STRICT
	PARALLEL SAFE
BEGIN ATOMIC
	WITH RECURSIVE grid AS (
			-- Base case: Generate the 1-character geohashes
			SELECT
				geohash,
				1 AS geohash_length,
				FALSE AS contained,
				geohash_geom
			FROM generate_series(1, 32) AS n,
				substr('0123456789bcdefghjkmnpqrstuvwxyz', n, 1) AS geohash,
				ST_SetSRID(ST_GeomFromGeoHash(geohash), ST_SRID($1)) AS geohash_geom
			WHERE ST_Intersects($1, geohash_geom)
		UNION ALL
			-- Recursive step: Increase number of characters and filter by intersection
			SELECT
				hash,
				g.geohash_length + 1,
				ST_Contains($1, geom),
				geom
			FROM grid AS g,
				generate_series(1, 32) AS n,
				concat(
					g.geohash,
					substr('0123456789bcdefghjkmnpqrstuvwxyz', n, 1)
				) AS hash,
				ST_SetSRID(ST_GeomFromGeoHash(hash), ST_SRID($1)) AS geom
			WHERE g.geohash_length < $2
				AND NOT g.contained
				AND ST_Intersects($1, geom)
	)
	SELECT geohash, geohash_geom
	FROM grid
	WHERE contained OR geohash_length = $2;
END;


-- Test
SELECT geohash, geom
FROM geohash_grid_compact(
	'SRID=4674;POLYGON((-46.3 -23, -44.3 -22.68, -48.3 -25, -51.4 -20.5, -46.3 -23))',
	5
);