ST_FUZZYGEOHASHCOVEREXTEND table function

Edit online

The ST_FUZZYGEOHASHCOVEREXTEND 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 minimum bounding rectangle 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.

The minimum bounding rectangle of a geometry is the bounding geometry formed by the minimum and maximum (X,Y) coordinates. See Minimum bounding rectangle for more information.

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_FUZZYGEOHASHCOVEREXTEND(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 and the 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 must 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_FUZZYGEOHASHCOVEREXTEND
Column name Data type Contains
GEOHASH BIGINT A geohash value of the minimum bounding rectangle 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 minimum bounding box of the specified polygon geometry at depth 18 with a buffer of 25 kilometers:

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

Results:


GEOHASH
-------------------
6918619743175835648 
6919393799361789952 
6919464168105967616 
6919675274338500608 
6920132671175655424 
6920906727361609728 
6920977096105787392 
6921188202338320384 
6920167855547744256 
6920941911733698560 
6921012280477876224 
6921223386710409216 
6920273408664010752 
6921047464849965056 
6921117833594142720 
6921328939826675712 
6920308593036099584 
6921082649222053888 
6921153017966231552 
6921364124198764544 
6920695621129076736 
6921469677315031040 
6921540046059208704 
6921751152291741696 
6920730805501165568 
6921504861687119872 
6921575230431297536 
6921786336663830528 
6920836358617432064 
6921610414803386368 
6921680783547564032 
6921891889780097024