Analytics Toolbox for Snowflake

Analytics Toolbox for Snowflake

Spatial indexes

Hierarchical grid systems, such as Quadbin, H3, and S2, are an essential tool for analysing large spatial datasets, especially when dealing with data sources in different spatial aggregations. These systems are based on geospatial indexes that provide a direct relationship between grid cells at different resolutions, enabling extremely performant spatial operations.

Microsoft Quadkeys
Quadbin (source)
Uber H3
H3 (source)
S2 indexes
S2 (source)

Go to the Reference for the full list of available functions and the Examples to access sample code. If you can’t find what you need, please let us know by opening an issue in our Github repository or become a contributor.

Quadbin

Quadbin is a hierarchical geospatial index based on the Bing Maps Tile System (Quadkey). Designed to be cluster-efficient, it stores in a 64-bit number the information to uniquely identify any of the grid cells that result from uniformly subdividing a map in Mercator projection into four squares at different resolution levels, from 0 to 26 (less than 1m² at the equator). The bit layout is inspired in the H3 design, and provides different modes to store not only cells, but edges, corners or vertices.

Quadbin
Multiple levels of quadbins (source)

H3

H3 is a multiresolution hexagonal global grid system with hierarchical indexing developed by Uber. It supports sixteen resolutions, each of them composed of cells with one-seventh the area of the lower resolution containing cell. Each hexagon cell at a particular resolution is uniquely identified, and these identifiers can be easily truncated to find the coarser-containing cell. However, since a hexagon cannot be exactly subdivided into seven hexagons, this process always results in a fixed amount of shape distortion.

H3
Multiple levels of H3 grids (source)

Instead of constructing a grid over a planar projection like Quadbin does, H3 projects from Earth like a sphere to a regular icosahedron, and then lays out hexagonal grid cells on each of its faces.

One of the most powerful properties of H3 is that all neighboring hexagons of a particular cell are at an equal distance. This enables fast computation of grid distances between hexagons and neighbouring areas around an index using the DISTANCE and KRING functions, respectively.

H3 KRing
kRings of distance 1, 2 and 3 around an H3 index of resolution 11.

S2

S2, developed by Google, works exclusively with spherical projections. By mapping points on the Earth’s surface to a sphere, S2 minimises distortion and avoids discontinuities such as the discontinuity along the 180-degree meridian present in the Mercator projection.

S2 enables the decomposition of the unit sphere into a hierarchy of cells, each of which is a quadrilateral formed by four geodesics. The cell hierarchy starts at level 0 and goes all the way down to 30, where cells represent approximately 1 squared cm. Level 0 is composed of six cells and lower levels are computed by subdividing each cell into four.

S2
S2 cell hierarchy (source)

S2 cells are uniquely identified by a 64-bit ID, following a numbering system optimized for spatial indexing: two S2 cells whose IDs are close together will also be close together in the space. To learn more about the properties of S2 cells, refer to the library’s official documentation.

Placekey

Placekey is a universal identifier for any physical place built upon H3. A placekey is composed of a What and a Where part (What@Where), the former capturing the descriptive element of a place and the latter its geospatial position.

Placekey
Placekey components (source)

The What part of a placekey is composed of two sets of three characters encoding the address and the POI, to account for the fact that multiple places can share the same address. For example, a Starbucks and a Subway in 555 Main Street will share the first three characters but differ on the other three, therefore having different What parts.

Finally, the Where part encodes the H3 hexagonal cell where the place is located. To learn more about Placekey, visit the project’s website.

EU flag

This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.