_ST_GeoHash

Description

GeoHash is a geocoding system that converts latitude and longitude coordinates into a string format (a combination of letters and numbers), providing a textual representation of geographical locations. Unlike pointing to an exact point, a GeoHash represents a rectangular area where all points within this area share the same GeoHash value. This characteristic allows GeoHash to describe geographical locations approximately while protecting personal location privacy and enabling quick processing. The core idea of GeoHash is interval binary division: it treats the Earth as a two-dimensional plane, recursively divides the longitude and latitude into smaller sub-blocks, and encodes each sub-block. The longer the GeoHash, the more times the longitude and latitude are divided, resulting in smaller sub-blocks and higher location accuracy.

In this topic, the _ST_GeoHash function is used to convert a geometry object into a string-based GeoHash value.

Syntax

_ST_GeoHash(geometry, [precision])

Parameter Description

• geometry: the geometry object to be converted into a GeoHash value.

• precision: an optional parameter that accepts an integer (int) input, representing the precision of the GeoHash calculation, i.e., the length of the returned GeoHash string. A larger value indicates a more precise geographical range.

  • If precision is not specified or set to 0, the precision of the returned GeoHash is calculated based on the input geometry object. It is the precision of the smallest sub-block that can contain the input geometry. For a point, the GeoHash will have a precision of 20 characters.
  • If precision is specified, the returned GeoHash will have the specified precision. For non-point geometries, the calculation starts from the center of the geometry's bounding box.

Examples

• Convert a point into a short string GeoHash value composed of letters and numbers with a precision of 20.

  SELECT _ST_GeoHash(Point(-126.002,48.348), 20) AS geohash;

  The result is as follows:

  +----------------------+
  | geohash              |
  +----------------------+
  | c0w7hc2ps87jfvwhesrb |
  +----------------------+
  1 row in set (0.001 sec)

• Convert a polygon into a short string GeoHash value composed of letters and numbers with a precision of 10.

  1. The ST_GEOMFROMTEXT function accepts a WKT (Well-Known Text) formatted string and returns a geometry object. In this example, POLYGON((10 10, 20 20, 10 30, 0 20, 10 10)) is used as the input parameter. This string defines the coordinates of the five vertices of a polygon. The last coordinate is the same as the first, making it a closed polygon.
  2. 10 is the precision parameter for GeoHash, indicating the length of the returned GeoHash string.
  3. The query result column is named geohash.

  SELECT _ST_GeoHash(ST_GEOMFROMTEXT('POLYGON((10 10, 20 20, 10 30, 0 20, 10 10))'), 10) AS geohash;

  The result is as follows:

  +------------+
  | geohash    |
  +------------+
  | s5x1g8cu2y |
  +------------+
  1 row in set (0.001 sec)

References

• For information about ST_GEOMFROMTEXT, see Create a geometry value function.

• For information about POLYGON, see Spatial data format.