ST_GEOHASHCOVEREXTEND table function

Edit online

The ST_GEOHASHCOVEREXTEND table function takes a geometry object, a depth, and a distance as input parameters and returns a table with one column containing the geohash cover of the specified geometry at the specified depth plus a buffer zone surrounding the specified geometry.

A geohash cover is the set of geohash cells that are needed to completely cover a given geometry. A larger depth corresponds to smaller cells, and therefore results in more exact coverage and less area of the geohash cover that is outside of the geometry. However, when the cells are smaller, more cells are needed.

The buffer zone is the geometry that surrounds the geohash cover by a specified distance.

Figure 1. Geohash extension where the distance is less than the length of the side of one cell
Geohash extension
Figure 2. Geohash extension where the distance is greater than the length of the side of one cell
Large Geohash extension
Read syntax diagramSkip visual syntax diagramST_GEOHASHCOVEREXTEND(geometry,depth,distance)
geometry
A value of type ST_GEOMETRY or one of its subtypes that represents the geometry for which the geohash cover values are to be calculated. If the specified geometry is NULL or empty, one row with a geohash value of NULL is returned.
depth
An integer value in the range 1 - 45 that determines the size of the geohash cell. The following table lists some of the most 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.

distance
The distance from the edge of the input cell that determines the size of the buffer zone. The value cannot be negative or null. The unit of measure is meters.
The result of the function is a table containing rows with the format shown in the following table. The column is nullable.
Table 1. Format of the resulting table for ST_GEOHASHCOVEREXTEND
Column name Data type Contains
GEOHASH BIGINT A geohash value of the specified point geometry at the specified depth after it has been extended in all dimensions by the specified distance.

Notes

The number of rows returned by this function cannot exceed 10,000. See Geohashes and geohash covers for more information on selecting an appropriate depth value.

Example

The following SQL statement returns the geohash values that cover the specified polygon geometry at depth 18 with a buffer of 25 kilometers:

SELECT geohash 
FROM TABLE(
  QSYS2.ST_GEOHASHCOVEREXTEND(QSYS2.ST_POLYGON('polygon((3 3, 3 5, 4 4, 3 3))'), 
                              18, 
                              25000));

Results:


GEOHASH
-------------------
6919393799361789952 
6920132671175655424 
6920906727361609728 
6920167855547744256 
6920941911733698560 
6921012280477876224 
6920273408664010752 
6921047464849965056 
6921117833594142720 
6920308593036099584 
6921082649222053888 
6921153017966231552 
6921364124198764544 
6920695621129076736 
6921469677315031040 
6921540046059208704 
6920730805501165568 
6921504861687119872 
6921575230431297536 
6920836358617432064 
6921610414803386368