_ST_GeoHash

Purpose

Geohash is a geocoding system that converts latitude and longitude coordinates into a string format (a combination of letters and numbers) to provide a textual representation of geographic locations. Geohash does not point to an exact point but represents a rectangular area where all points within the area share the same geohash value. This feature allows geohash to support approximate descriptions and fast processing of geographic locations while protecting personal location privacy. The core idea of geohash is interval binary division: it treats the Earth as a two-dimensional plane and recursively divides the longitude and latitude into smaller subblocks, encoding each subblock. The longer the geohash, the more times the longitude and latitude are divided, resulting in smaller subblocks 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])

Parameters

• 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 geographic range.

  • If precision is not specified or set to 0, the precision of the returned geohash will be calculated based on the input geometry object. The precision is the smallest subblock that can contain the input geometry. A point will return a geohash with 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.

  obclient [test]> SELECT _ST_GeoHash(Point(-126.002,48.348), 20) AS geohash;
  

  The result is as follows:

  +----------------------+
  | geohash              |
  +----------------------+
  | c0w7hc2ps87jfvwhesrb |
  +----------------------+
  1 row in set
  

• 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 Well-Known Text (WKT) 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.

  obclient [test]> 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
  

References

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

• For more information about the POLYGON data type, see Spatial data format.