ST_GEOHASHVALUE scalar function

Edit online

The ST_GEOHASHVALUE scalar function takes an ST_POINT object and a depth as input parameters and returns a number representing the geohash of the specified point geometry at the specified depth.

A geohash is a number that uniquely identifies a specific region. The geohash algorithm divides the Earth into regions, called cells, and converts the latitude and longitude of the center of each cell into a number that uniquely identifies it. The size of each cell is determined by the depth value. The smaller the depth value, the larger the cell size.

Read syntax diagramSkip visual syntax diagram ST_GEOHASHVALUE (point_geometry,depth)
point_geometry
A value of type ST_POINT that represents the point geometry for which the geohash is to be calculated. If the specified geometry is NULL or empty, NULL is returned.
depth
An integer value in the range 1 - 45 that determines the size of the geohash cell. The following list contains some commonly used depths their corresponding approximate cell sizes.
Geohash Depth Approximate Cell Size Description Examples
45 .1 km2 Single point or address GPS-location or house
28 3 km2 Small region city block
23 100 km2 Medium-sized region forest or lake
18 3,000 km2 Large region county or postal code area
13 100,000 km2 Very large region state or country

Geohash values of two geometries that are to be compared should be computed using the same depth for a meaningful comparison.

The result of the function is BIGINT.

Example

The following SQL statement returns the geohash values for point geometries at depth 23:

CREATE TABLE EXAMPLE_POINTS (GEO_ID CHAR(5), GEO QSYS2.ST_POINT);

INSERT INTO EXAMPLE_POINTS VALUES 
  ('11111', QSYS2.ST_POINT(10, 10)),
  ('22222', QSYS2.ST_POINT(15, 15)),
  ('33333', QSYS2.ST_POINT(-10, -10));
                      
SELECT QSYS2.ST_GEOHASHVALUE(GEO, 23) AS GEOHASH FROM EXAMPLE_POINTS;

Results:


GEOHASH
-------------------
6935265249708736512
6975174223262121984
2288105687634411520