Filtering using geohash covers

You can use geohash covers to filter objects for spatial processing, thereby greatly improving query performance.

For example, you can use geohash covers to quickly determine whether it is possible that two geometries intersect:

If their geohash covers share at least one cell, their maximum separation is the length of one cell diagonal. They might intersect (see Figure 1), or they might not (see Figure 2).
However, if their geohash covers do not share any cells, you can be certain that they do not intersect (see Figure 3).

Figure 1. The geohash covers of the two geometries share a common cell, and the geometries intersect

Figure 2. The geohash covers of the two geometries share a common cell, but the geometries do not intersect

Figure 3. The geohash covers of the two geometries do not share any cells

To filter geometries using geohash covers:

Identify the base tables involved in the spatial relationship function. For example, the base tables involved in the following st_intersects function are SPX.TABLE2011 and SPX.TABLE2019:
```
SELECT a.*, b.TERR_FCS 
from SPX.TABLE2011 as a, 
     SPX.TABLE2019 as b 
WHERE a.SST_DC = 'CA' and 
      a.DC_ZIP = '95136' and 
      st_intersects(a.SHAPE,b.SHAPE);
```
Note the primary key and geometry column of each of the base tablesthat are involved in the spatial relationship function. For example:
- In SPX.TABLE2011, a.shape2011_key and a.shape
- In SPX.TABLE2019, b.shape2019_key and b.shape
Determine the size of the geometries to be covered, and use this information to decide which depth to use. The geohash covers of two geometries that are to be compared must be computed using the same depth. An attempt to compare geohash values computed using different depths is invalid. The following examples use a depth of 23.

Create a geohash-filter table for each base table, and use the st_geohashcover function to populate each geohash-filter table with the geohash values that correspond to the geohash covers of the geometries. For example:

Create table SPX.FILTERTABLE2011 (shape2011_key integer, geohash BIGINT)
INSERT into SPX.FILTERTABLE2011 (select g.shape2011_key, t.geohash 
   from SPX.TABLE2011 as g, table(st_geohashcover_medium(g.shape)) as t)

Create table SPX.FILTERTABLE2019 (shape2019_key integer, geohash BIGINT)
INSERT into SPX.FILTERTABLE2019 (select g.shape2019_key, t.geohash 
   from SPX.TABLE2019 as g, table(st_geohashcover_medium(g.shape)) as t)

Rewrite the original query to use the filter tables to reduce the amount of data that needs to be compared. For example:

SELECT a.*,  b.TERR_FCS from   
   SPX.TABLE2011   a,
   SPX.TABLE2019  b, 
   SPX.FILTERTABLE2011 as c, 
   SPX.FILTERTABLE2019 as d 
WHERE a.SST_DC = 'CA’ and
   a.DC_ZIP = '95136’ and
   c.shape2011_key = a.shape2011_key and
   d.shape2019_key = b.shape2019_key and
   c.geohash = d.geohash and
   st_intersects(a.SHAPE,b.SHAPE);