To enable clustering (aggregated tables) and facilitate large data visualization,
generate the geohash index from the geometry (or the XY).
Below is a sample user defined function (UDF) for creating a geohash ID in PostGIS/PostGres table.
Note:
- To create the function make sure you have the rights for creating UDF on the DB.
- Ensure that you give function a unique name and that no other function exists with the same name.
CREATE OR REPLACE FUNCTION public.togeohash(long numeric,
lat numeric)
RETURNS character varying
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS '
declare
ghash varchar;
begin
SELECT substring(ST_GeoHash(ST_SetSRID(ST_Point(long,lat),4326)),1,12) into ghash;
return ghash;
end;
'Example - To populate the DB Table with Geohash ID
Example Query for a Table with a Point Geometry
UPDATEpublic.geohash_table SET geohash =
togeohash(cast (ST_X("SP_GEOMETRY") ASNUMERIC), cast
(ST_Y("SP_GEOMETRY") ASNUMERIC))Example Query for a Table with X(Longitude) and Y(Latitude) columns
UPDATEpublic.geohash_table SET geohash =
togeohash(cast ("LONGITUDE" ASNUMERIC), cast ("LATITUDE"
ASNUMERIC))