Analytics Toolbox for BigQuery

Analytics Toolbox for BigQuery

Github Code

quadkey

You can learn more about quadkeys and quandints in the Overview section of the documentation.

QUADINT_BBOX

Description

Returns an array with the boundary box of a given quadint. This boundary box contains the minimum and maximum longitude and latitude. The output format is [West-South, East-North] or [min long, min lat, max long, max lat].

  • quadint: INT64 quadint to get the bbox from.

Return type

ARRAY<FLOAT64>

Example

1
2
3
4
5

SELECT `carto-os`.carto.QUADINT_BBOX(4388);
-- 22.5
-- -21.943045533438177
-- 45.0
-- 0.0

QUADINT_BOUNDARY

Description

Returns the boundary for a given quadint. We extract the boundary in the same way as when we calculate its QUADINT_BBOX, then enclose it in a GeoJSON and finally transform it into a geography.

  • quadint: INT64 quadint to get the boundary geography from.

Return type

GEOGRAPHY

Example

1
2

SELECT `carto-os`.carto.QUADINT_BOUNDARY(4388);
-- POLYGON((22.5 0, 22.5 -21.9430455334382, 22.67578125 ...

QUADINT_FROMGEOGPOINT

Description

Returns the quadint of a given point at a given level of detail.

  • point: GEOGRAPHY point to get the quadint from.
  • resolution: INT64 level of detail or zoom.

Return type

INT64

Example

1
2

SELECT `carto-os`.carto.QUADINT_FROMGEOGPOINT(ST_GEOGPOINT(40.4168, -3.7038), 4);
-- 4388

QUADINT_FROMLONGLAT

Description

Returns the quadint representation for a given level of detail and geographic coordinates.

  • longitude: FLOAT64 horizontal coordinate of the map.
  • latitude: FLOAT64 vertical coordinate of the map.
  • resolution: INT64 level of detail or zoom.

Return type

INT64

Example

1
2

SELECT `carto-os`.carto.QUADINT_FROMLONGLAT(40.4168, -3.7038, 4);
-- 4388

QUADINT_FROMLONGLAT_ZOOMRANGE

Description

Returns the quadint index for the given point for each zoom level requested, at the specified resolution (computed as the current zoom level + the value of resolution). The output is an array of structs with the following elements: quadint id, zoom level (z), and horizontal (x) and vertical (y) position of the tile. These quadint indexes can be used for grouping and generating aggregations of points throughout the zoom range requested. Notice the use of an additional variable resolution for adjusting the desired level of granularity.

  • longitude: FLOAT64 horizontal coordinate of the map.
  • latitude: FLOAT64 vertical coordinate of the map.
  • zoom_min: INT64 minimum zoom to get the quadints from.
  • zoom_max: INT64 maximum zoom to get the quadints from.
  • zoom_step: INT64 used for skipping levels of zoom.
  • resolution: INT64 resolution added to the current zoom to extract the quadints.

Return type

ARRAY<STRUCT<INT64, INT64, INT64>>

Example

1
2
3
4
5
6

SELECT `carto-os`.carto.QUADINT_FROMLONGLAT_ZOOMRANGE(40.4168, -3.7038, 3, 6, 1, 4);
-- id        z  x   y
-- 268743    3  4   4
-- 1069960   4  9   8
-- 4286249   5  19  16
-- 17124938  6  39  32

QUADINT_FROMQUADKEY

Description

Returns the quadint equivalent to the input quadkey.

  • quadkey: STRING quadkey to be converted to quadint.

Return type

INT64

Example

1
2

SELECT `carto-os`.carto.QUADINT_FROMQUADKEY("3001");
-- 4388

QUADINT_FROMZXY

Description

Returns a quadint from z, x, y coordinates.

  • z: INT64 zoom level.
  • x: INT64 horizontal position of a tile.
  • y: INT64 vertical position of a tile.

Constraints

Tile coordinates x and y depend on the zoom level z. For both coordinates, the minimum value is 0, and the maximum value is two to the power of z, minus one (2^z - 1).

Return type

INT64

Example

1
2

SELECT `carto-os`.carto.QUADINT_FROMZXY(4, 9, 8);
-- 4388

QUADINT_KRING

Description

Returns all cell indexes in a filled square k-ring centered at the origin in no particular order.

  • origin: INT64 quadint index of the origin.
  • size: INT64 size of the ring (distance from the origin).

Return type

ARRAY<INT64>

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT `carto-os`.carto.QUADINT_KRING(4388, 1);
-- 3844
-- 3876
-- 3908
-- 4356
-- 4388
-- 4420
-- 4868
-- 4900
-- 4932

QUADINT_KRING_DISTANCES

Description

Returns all cell indexes and their distances in a filled square k-ring centered at the origin in no particular order.

  • origin: INT64 quadint index of the origin.
  • size: INT64 size of the ring (distance from the origin).

Return type

ARRAY<STRUCT<index INT64, distance INT64>>

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT `carto-os`.carto.QUADINT_KRING_DISTANCES(4388, 1);
-- {"index": "4388", "distance": "0"}
-- {"index": "4932", "distance": "1"}
-- {"index": "4900", "distance": "1"}
-- {"index": "4868", "distance": "1"}
-- {"index": "4420", "distance": "1"}
-- {"index": "4356", "distance": "1"}
-- {"index": "3908", "distance": "1"}
-- {"index": "3876", "distance": "1"}
-- {"index": "3844", "distance": "1"}

QUADINT_POLYFILL

Description

Returns an array of quadints that intersect with the given geography at a given level of detail.

  • geography: GEOGRAPHY geography to extract the quadints from.
  • resolution: INT64 level of detail or zoom.

Return type

ARRAY<INT64>

Example

1
2
3
4
5
6
7
8
9

SELECT `carto-os`.carto.QUADINT_POLYFILL(
    ST_MAKEPOLYGON(ST_MAKELINE([ST_GEOGPOINT(-363.71219873428345, 40.413365349070865), ST_GEOGPOINT(-363.7144088745117, 40.40965661286395), ST_GEOGPOINT(-363.70659828186035, 40.409525904775634), ST_GEOGPOINT(-363.71219873428345, 40.413365349070865)])), 
    17);
-- 207301334801
-- 207305529105
-- 207305529073
-- 207305529137
-- 207305529169
-- 207301334833

QUADINT_SIBLING

Description

Returns the quadint directly next to the given quadint at the same zoom level. The direction must be sent as argument and currently only horizontal/vertical movements are allowed.

  • quadint: INT64 quadint to get the sibling from.
  • direction: STRING ‘right’|‘left’|‘up’|‘down’ direction to move in to extract the next sibling.

Return type

INT64

Example

1
2

SELECT `carto-os`.carto.QUADINT_SIBLING(4388, 'up');
-- 3876

QUADINT_TOCHILDREN

Description

Returns an array with the children quadints of a given quadint for a specific resolution. A children quadint is a quadint of higher level of detail that is contained by the current quadint. Each quadint has four children by definition.

  • quadint: INT64 quadint to get the children from.
  • resolution: INT64 resolution of the desired children.

Return type

ARRAY<INT64>

Example

1
2
3
4
5

SELECT `carto-os`.carto.QUADINT_TOCHILDREN(1155, 4);
-- 4356
-- 4868
-- 4388
-- 4900

QUADINT_TOGEOGPOINT

Description

Returns the centroid for a given quadint.

  • quadint: INT64 quadint to get the centroid geography from.

Return type

GEOGRAPHY

Example

1
2

SELECT `carto-os`.carto.QUADINT_TOGEOGPOINT(4388);
-- 	POINT(33.75 22.2982994295938)

QUADINT_TOPARENT

Description

Returns the parent quadint of a given quadint for a specific resolution. A parent quadint is the smaller resolution containing quadint.

  • quadint: INT64 quadint to get the parent from.
  • resolution: INT64 resolution of the desired parent.

Return type

INT64

Example

1
2

SELECT `carto-os`.carto.QUADINT_TOPARENT(4388, 3);
-- 1155

QUADINT_TOQUADKEY

Description

Returns the quadkey equivalent to the input quadint.

  • quadint: INT64 quadint to be converted to quadkey.

Return type

STRING

Example

1
2

SELECT `carto-os`.carto.QUADINT_TOQUADKEY(4388);
-- 3001

QUADINT_TOZXY

Description

Returns the zoom level z and coordinates x, y for a given quadint.

  • quadint: INT64 quadint we want to extract tile information from.

Return type

STRUCT<INT64, INT64, INT64>

Example

1
2
3

SELECT `carto-os`.carto.QUADINT_TOZXY(4388);
-- z  x  y
-- 4  9  8