Converts geohash between text and integer formats in Postgres

geohash12_int8.sql

-- Converts geohash (up to 12 characters) between text and bigint formats in Postgres

-- https://dbfiddle.uk/YNXSfa3r

-- The geohash length is stored in the 4 least significant bits,
-- so that the relative order of the resulting int8 is the same
-- as that of the the original text string:
--   gzzzzzzzzzzz < h < h0 < h00 < h01             < h1
--   -4           < 1 < 2  < 3   < 562949953421315 < 18014398509481986

-- bits 0..3 store the geohash length (0 to 12)
-- bits 4..8 store the character in position 12
-- bits 9..13 store the character in position 11
-- ...
-- bits 54..58 store the character in position 2
-- bits 59..63 store the character in position 1
-- 2^63 is subtracted from the whole number so that it fits into a signed int8


CREATE OR REPLACE FUNCTION geohash12_to_int8(text)
	RETURNS int8
	LANGUAGE SQL
	IMMUTABLE
	STRICT
	PARALLEL SAFE
RETURN (
	SELECT length($1) + (
		(1::int8 << 59) + coalesce(
			sum((strpos(base32, ch) - 1)::int8 << (60 - pos * 5)::int4)::int8,
			0
		) << 4
	)
	FROM string_to_table($1, NULL) WITH ORDINALITY AS s (ch, pos),
		(SELECT '0123456789bcdefghjkmnpqrstuvwxyz' AS base32)
);


CREATE OR REPLACE FUNCTION int8_to_geohash12(int8)
	RETURNS text
	LANGUAGE SQL
	IMMUTABLE
	STRICT
	PARALLEL SAFE
RETURN (
	SELECT coalesce(
		string_agg(
			base32[(most_significant_60 >> (60 - pos * 5)::int4 & 31) + 1],
			''
			ORDER BY pos
		),
		''
	)
	FROM generate_series(1, $1 & 15) AS pos, (
		SELECT 
			($1 >> 4) - (1::int8 << 59) AS most_significant_60,
			'{0,1,2,3,4,5,6,7,8,9,b,c,d,e,f,g,h,j,k,m,n,p,q,r,s,t,u,v,w,x,y,z}'::text[] AS base32
	)
);


-- Test

SELECT
	geohash12_to_int8('geek') AS geek,
	geohash12_to_int8('reef') AS reef,
	geohash12_to_int8('sunny') AS sunny,
	geohash12_to_int8('h') AS h,
	geohash12_to_int8('') AS empty_str,
	geohash12_to_int8('0') AS "0",
	geohash12_to_int8('000000000000') AS "000000000000",
	geohash12_to_int8('zzzzzzzzzzzz') AS zzzzzzzzzzzz;

SELECT
	int8_to_geohash12(-334638562936881148) AS "-334638562936881148",
	int8_to_geohash12(4276977086746329092) AS "4276977086746329092",
	int8_to_geohash12(5091687715137650693) AS "5091687715137650693",
	int8_to_geohash12(1) AS "1",
	int8_to_geohash12(-9223372036854775808) AS "-9223372036854775808",
	int8_to_geohash12(-9223372036854775807) AS "-9223372036854775807",
	int8_to_geohash12(-9223372036854775796) AS "-9223372036854775796",
	int8_to_geohash12(9223372036854775804) AS "9223372036854775804";

geohash13_int8.sql

-- Converts 13-digit geohash between text and bigint formats in Postgres

-- https://dbfiddle.uk/P7KkRqlB

-- Since 65 bits would be necessary to store 13 full base32 digits,
-- the first digit is restricted to 16 possible values only, saving 1 bit.
-- The default set for the first digit is '0123456789bcdefg' (western hemisphere),
-- but any other 16-digit combination can be used to fit the working region.
-- For example, 'hjkmnpqrstuvwxyz' can be used for the eastern hemisphere.

-- bits 0..4 store the character in position 13
-- bits 5..9 store the character in position 12
-- ...
-- bits 55..59 store the character in position 2
-- bits 60..63 store the character in position 1 (only 16 possible values)
-- 2^63 is subtracted from the whole number so that it fits into a signed int8


CREATE OR REPLACE FUNCTION geohash13_to_int8(
	geohash text,
	valid_first_digits text DEFAULT '0123456789bcdefg'
)
	RETURNS int8
	LANGUAGE SQL
	IMMUTABLE
	STRICT
	PARALLEL SAFE
RETURN (
	SELECT (strpos(valid_first_digits, left(geohash, 1))::int8 + 7 << 60)
		+ sum((strpos(base32, ch) - 1)::int8 << (60 - pos * 5)::int4)
	FROM string_to_table(right(geohash, -1), NULL) WITH ORDINALITY AS s (ch, pos),
		(SELECT '0123456789bcdefghjkmnpqrstuvwxyz' AS base32)
);


CREATE OR REPLACE FUNCTION int8_to_geohash13(
	int8_hash int8,
	valid_first_digits text DEFAULT '0123456789bcdefg'
)
	RETURNS text
	LANGUAGE SQL
	IMMUTABLE
	STRICT
	PARALLEL SAFE
RETURN (
	SELECT substr(valid_first_digits, (int8_hash >> 60)::int4 + 9, 1)
		|| string_agg(
			base32[(int8_hash >> 60 - pos * 5 & 31) + 1],
			''
			ORDER BY pos
		)
	FROM generate_series(1, 12) AS pos,
		(SELECT '{0,1,2,3,4,5,6,7,8,9,b,c,d,e,f,g,h,j,k,m,n,p,q,r,s,t,u,v,w,x,y,z}'::text[] AS base32)
);


-- Test

SELECT
	geohash13_to_int8('0000000000000') AS "0000000000000",
	geohash13_to_int8('0bscurenesses') AS "0bscurenesses",
	geohash13_to_int8('7zzzzzzzzzzzz') AS "7zzzzzzzzzzzz",
	geohash13_to_int8('8000000000000') AS "8000000000000",
	geohash13_to_int8('berserknesses') AS berserknesses,
	geohash13_to_int8('greenskeepers') AS greenskeepers,
	geohash13_to_int8('gzzzzzzzzzzzz') AS gzzzzzzzzzzzz,
	geohash13_to_int8('zzzzzzzzzzzzz', 'hjkmnpqrstuvwxyz') AS zzzzzzzzzzzzz;

SELECT
	int8_to_geohash13(-9223372036854775808) AS "-9223372036854775808",
	int8_to_geohash13(-8835646048587456072) AS "-8835646048587456072",
	int8_to_geohash13(-1) AS "-1",
	int8_to_geohash13(0) AS "0",
	int8_to_geohash13(2800972597185765816) AS "2800972597185765816",
	int8_to_geohash13(8914229793954379512) AS "8914229793954379512",
	int8_to_geohash13(9223372036854775807) AS "9223372036854775807",
	int8_to_geohash13(9223372036854775807, 'hjkmnpqrstuvwxyz') AS "9223372036854775807z";

geohash7_to_int4.sql

-- Converts 7-digit geohash between text and integer formats in Postgres

-- https://dbfiddle.uk/AP2yckSI

-- Since 35 bits would be necessary to store 7 full base32 digits,
-- the first digit is restricted to 4 possible values only, saving 3 bits.
-- The default set for the first digit is '67de' (hi from Brazil),
-- but any other 4-digit combination can be used to fit the working region.
-- For example, 'egsu' can be used for northern Africa and most of Europe.

-- bits 0..4 store the character in position 7
-- bits 5..9 store the character in position 6
-- ...
-- bits 25..29 store the character in position 2
-- bits 30 and 31 store the character in position 1 (only 4 possible values)
-- 2^31 is subtracted from the whole number so that it fits into a signed int4


CREATE OR REPLACE FUNCTION geohash7_to_int4(
	geohash text,
	valid_first_digits text DEFAULT '67de'
)
	RETURNS int4
	LANGUAGE SQL
	IMMUTABLE
	STRICT
	PARALLEL SAFE
RETURN (
	SELECT (strpos(valid_first_digits, left(geohash, 1)) + 1 << 30)
		+ sum(strpos(base32, ch) - 1 << (30 - pos * 5)::int4)
	FROM string_to_table(right(geohash, -1), NULL) WITH ORDINALITY AS s (ch, pos),
		(SELECT '0123456789bcdefghjkmnpqrstuvwxyz' AS base32)
);


CREATE OR REPLACE FUNCTION int4_to_geohash7(
	int4_hash int4,
	valid_first_digits text DEFAULT '67de'
)
	RETURNS text
	LANGUAGE SQL
	IMMUTABLE
	STRICT
	PARALLEL SAFE
RETURN (
	SELECT substr(valid_first_digits, (int4_hash >> 30) + 3, 1)
		|| string_agg(
			base32[(int4_hash >> 30 - pos * 5 & 31) + 1],
			''
			ORDER BY pos
		)
	FROM generate_series(1, 6) AS pos,
		(SELECT '{0,1,2,3,4,5,6,7,8,9,b,c,d,e,f,g,h,j,k,m,n,p,q,r,s,t,u,v,w,x,y,z}'::text[] AS base32)
);


-- Test

SELECT
	geohash7_to_int4('6000000') AS "6000000",
	geohash7_to_int4('6zzzzzz') AS "6zzzzzz",
	geohash7_to_int4('7000000') AS "7000000",
	geohash7_to_int4('7zzzzzz') AS "7zzzzzz",
	geohash7_to_int4('d000000') AS d000000,
	geohash7_to_int4('dzzzzzz') AS dzzzzzz,
	geohash7_to_int4('e000000') AS e000000,
	geohash7_to_int4('ezzzzzz') AS ezzzzzz,
	geohash7_to_int4('uzzzzzz', 'egsu') AS uzzzzzz;

SELECT
	int4_to_geohash7(-2147483648) AS "-2147483648",
	int4_to_geohash7(-1073741825) AS "-1073741825",
	int4_to_geohash7(-1073741824) AS "-1073741824",
	int4_to_geohash7(-1) AS "-1",
	int4_to_geohash7(0) AS "0",
	int4_to_geohash7(1073741823) AS "1073741823",
	int4_to_geohash7(1073741824) AS "1073741824",
	int4_to_geohash7(2147483647) AS "2147483647",
	int4_to_geohash7(2147483647, 'egsu') AS "2147483647u";