quadbin

You can learn more about Quadbins in the Spatial Indexes section of the documentation.

QUADBIN_BBOX

QUADBIN_BBOX(quadbin)

Description

Returns an array with the boundary box of a given Quadbin. 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].

Input parameters

• quadbin: BIGINT Quadbin to get the bbox from.

Return type

ARRAY<FLOAT64>

Example

SELECT carto.QUADBIN_BBOX(5207251884775047167);
-- {-22.5,21.943045533438188,0.0,40.97989806962013}

QUADBIN_BOUNDARY

QUADBIN_BOUNDARY(quadbin)

Description

Returns the boundary for a given Quadbin as a polygon GEOMETRY with the same coordinates as given by the QUADBIN_BBOX function.

Input parameters

• quadbin: BIGINT Quadbin to get the boundary geometry from.

Return type

GEOGRAPHY

Example

SELECT carto.QUADBIN_BOUNDARY(5207251884775047167);
-- POLYGON ((-22.5 21.943045533438188, -22.5 40.97989806962013, 0 40.97989806962013, 0 21.943045533438188, -22.5 21.943045533438188))

QUADBIN_CENTER

QUADBIN_CENTER(quadbin)

Description

Returns the center for a given Quadbin. The center is the intersection point of the four immediate children Quadbin.

Input parameters

• quadbin: BIGINT Quadbin to get the center from.

Return type

GEOMETRY

Example

SELECT carto.QUADBIN_CENTER(5207251884775047167);
-- POINT (-11.25 31.952162238024965)

QUADBIN_DISTANCE

QUADBIN_DISTANCE(origin, destination)

Description

Returns the Chebyshev distance between two quadbin indexes. The origin and destination indices must have the same resolution. Otherwise NULL will be returned.

Input parameters

• origin: BIGINT origin quadbin index.

• destination: BIGINT destination quadbin index.

Return type

BIGINT

Example

SELECT carto.QUADBIN_DISTANCE(5207251884775047167, 5207128739472736255);
-- 1

QUADBIN_FROMGEOGPOINT

QUADBIN_FROMGEOGPOINT(point, resolution)

Description

Returns the Quadbin of a given point at a requested resolution. This function is an alias for QUADBIN_FROMGEOPOINT.

Input parameters

• point: GEOMETRY point to get the Quadbin from.

• resolution: BIGINT level of detail or zoom.

Return type

BIGINT

Example

SELECT carto.QUADBIN_FROMGEOGPOINT(ST_MAKEPOINT(-3.7038, 40.4168), 4);
-- 5207251884775047167

QUADBIN_FROMLONGLAT

QUADBIN_FROMLONGLAT(longitude, latitude, resolution)

Description

Returns the Quadbin representation of a point for a requested resolution and geographic coordinates.

Input parameters

• longitude: DOUBLE PRECISION longitude (WGS84) of the point.

• latitude: DOUBLE PRECISION latitude (WGS84) of the point.

• resolution: INT level of detail or zoom.

Return type

BIGINT

Example

SELECT carto.QUADBIN_FROMLONGLAT(-3.7038, 40.4168, 4);
-- 5207251884775047167

QUADBIN_FROMQUADKEY

QUADBIN_FROMQUADKEY(quadkey)

Description

Compute a quadbin index from a quadkey.

Input parameters

• quadkey: TEXT Quadkey representation of the index.

Return type

BIGINT

Example

SELECT carto.QUADBIN_FROMQUADKEY('0331110121');
-- 5234261499580514303

QUADBIN_FROMZXY

QUADBIN_FROMZXY(z, x, y)

Description

Returns a Quadbin from z, x, y tile coordinates.

Input parameters

• z: INT zoom level.

• x: INT horizontal position of a tile.

• y: INT 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

BIGINT

Example

SELECT carto.QUADBIN_FROMZXY(4, 7, 6);
-- 5207251884775047167

QUADBIN_ISVALID

QUADBIN_ISVALID(quadbin)

Description

Returns true when the given index is valid, false otherwise.

Input parameters

• quadbin: BIGINT Quadbin index.

Return type

BOOLEAN

Examples

SELECT carto.QUADBIN_ISVALID(5207251884775047167);
-- TRUE

SELECT carto.QUADBIN_ISVALID(1234);
-- FALSE

QUADBIN_KRING

QUADBIN_KRING(origin, size)

Description

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

Input parameters

• origin: BIGINT Quadbin index of the origin.

• size: INT size of the ring (distance from the origin).

Return type

BIGINT[]

Example

SELECT carto.QUADBIN_KRING(5207251884775047167, 1);
-- { 5207128739472736255
-- 5207146331658780671
-- 5207234292589002751
-- 5207251884775047167
-- 5207269476961091583
-- 5207287069147135999
-- 5207902795658690559
-- 5208008348774957055
-- 5208043533147045887 }

QUADBIN_KRING_DISTANCES

QUADBIN_KRING_DISTANCES(origin, size)

Description

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

Input parameters

• origin: BIGINT Quadbin index of the origin.

• size: INT size of the ring (distance from the origin).

Return type

JSON[]

Example

SELECT carto.QUADBIN_KRING_DISTANCES(5207251884775047167, 1);
-- {{"index" : 5207269476961091583, "distance" : "1"}
--  {"index" : 5207251884775047167, "distance" : "0"}
--  {"index" : 5207902795658690559, "distance" : "1"}
--  {"index" : 5208008348774957055, "distance" : "1"}
--  {"index" : 5207234292589002751, "distance" : "1"}
--  {"index" : 5207287069147135999, "distance" : "1"}
--  {"index" : 5207128739472736255, "distance" : "1"}
--  {"index" : 5208043533147045887, "distance" : "1"}
--  {"index" : 5207146331658780671, "distance" : "1"}}

tip

The distance of the rings is computed as the Chebyshev distance.

QUADBIN_POLYFILL

QUADBIN_POLYFILL(geom, resolution [, mode])

Description

Returns an array of quadbin cell indexes contained in the given geometry at a requested resolution. Containment is determined by the mode: center, intersects, contains.

Input parameters

• geom: GEOMETRY representing the shape to cover.

• resolution: INT level of detail. The value must be between 0 and 26.

• mode (optional): VARCHAR

  • center (default) returns the indexes of the quadbin cells which centers intersect the input geometry (polygon). The resulting quadbin set does not fully cover the input geometry, however, this is significantly faster that the other modes. This mode is not compatible with points or lines.

  • intersects returns the indexes of the quadbin cells that intersect the input geometry. The resulting quadbin set will completely cover the input geometry (point, line, polygon).

  • contains returns the indexes of the quadbin cells that are entirely contained inside the input geometry (polygon). This mode is not compatible with points or lines.

Mode center:

Mode intersects:

Mode contains:

Return type

BIGINT[]

Examples

SELECT carto.QUADBIN_POLYFILL(
  ST_GEOMFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  17
);
-- [5265786693163941887, 5265786693164466175, 5265786693164728319]

SELECT quadbin
FROM UNNEST(carto.QUADBIN_POLYFILL(
  ST_GEOMFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  17
)) AS quadbin;
-- 5265786693163941887
-- 5265786693164466175
-- 5265786693164728319

SELECT quadbin
FROM <my-schema>.<my-table>,
  UNNEST(carto.QUADBIN_POLYFILL(geom, 17)) AS quadbin;

SELECT carto.QUADBIN_POLYFILL(
  ST_GEOMFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  17, 'intersects'
);
-- [5265786693153193983, 5265786693163941887, 5265786693164466175, 5265786693164204031, 5265786693164728319, 5265786693165514751]

SELECT quadbin
FROM UNNEST(carto.QUADBIN_POLYFILL(
  ST_GEOMFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  17, 'intersects'
)) AS quadbin;
-- 5265786693153193983
-- 5265786693163941887
-- 5265786693164466175
-- 5265786693164204031
-- 5265786693164728319
-- 5265786693165514751

SELECT quadbin
FROM <my-schema>.<my-table>,
  UNNEST(carto.QUADBIN_POLYFILL(geom, 17, 'intersects')) AS quadbin;

QUADBIN_POLYFILL_TABLE

QUADBIN_POLYFILL_TABLE(input_query, resolution, mode, output_table)

Description

Creates a table with the quadbin cell indexes contained in the geographies of the input query at a requested resolution. Containment is determined by the mode: center, intersects, contains. All the attributes except the geography will be included in the output table.

Unlike QUADBIN_POLYFILL, which returns the cells as an array, this procedure writes the result directly to a table. This avoids the array size limits that can be reached when polyfilling large geographies at high resolutions.

Input parameters

• input_query: TEXT input data to polyfill. It must contain a column geom with the shape to cover. Additionally, other columns can be included.

• resolution: INT level of detail. The value must be between 0 and 26.

• mode: TEXT.

  • center returns the indexes of the quadbin cells which centers intersect the input geography (polygon). The resulting quadbin set does not fully cover the input geography, however, this is significantly faster than the other modes. This mode is not compatible with points or lines. Equivalent to QUADBIN_POLYFILL.

  • intersects returns the indexes of the quadbin cells that intersect the input geography. The resulting quadbin set will completely cover the input geography (point, line, polygon).

  • contains returns the indexes of the quadbin cells that are entirely contained inside the input geography (polygon). This mode is not compatible with points or lines.

• output_table: TEXT qualified name of the output table, e.g. <my-schema>.<my-output-table>.

Output

The results are stored in the table named <my-output-table>, which contains the following columns:

• quadbin: BIGINT the quadbin cell index.

• The rest of columns included in input_query except geom.

Examples

CALL carto.QUADBIN_POLYFILL_TABLE(
  'SELECT ST_GEOMFROMTEXT(''POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'') AS geom',
  17, 'intersects',
  '<my-schema>.<my-output-table>'
);
-- The table `<my-schema>.<my-output-table>` will be created
-- with column: quadbin

CALL carto.QUADBIN_POLYFILL_TABLE(
  'SELECT geom, name, value FROM <my-schema>.<my-table>',
  17, 'center',
  '<my-schema>.<my-output-table>'
);
-- The table `<my-schema>.<my-output-table>` will be created
-- with columns: quadbin, name, value

QUADBIN_RESOLUTION

QUADBIN_RESOLUTION(quadbin)

Description

Returns the resolution of the input Quadbin.

Input parameters

• quadbin: BIGINT Quadbin from which to get the resolution.

Return type

BIGINT

Example

SELECT carto.QUADBIN_RESOLUTION(5207251884775047167);
-- 4

QUADBIN_SIBLING

QUADBIN_SIBLING(quadbin, direction)

Description

Returns the Quadbin directly next to the given Quadbin at the same resolution. The direction must be set in the corresponding argument and currently only horizontal/vertical neigbours are supported. It will return NULL if the sibling does not exist.

Input parameters

• quadbin: BIGINT Quadbin to get the sibling from.

• direction: TEXT 'right'|'left'|'up'|'down' direction to move in to extract the next sibling.

Return type

BIGINT

Example

SELECT carto.QUADBIN_SIBLING(5207251884775047167, 'up');
-- 5207146331658780671

QUADBIN_TOCHILDREN

QUADBIN_TOCHILDREN(quadbin, resolution)

Description

Returns an array with the children Quadbins of a given Quadbin for a specific resolution. A children Quadbin is a Quadbin of higher level of detail that is contained by the current Quadbin. Each Quadbin has four direct children (at the next higher resolution).

Input parameters

• quadbin: BIGINT Quadbin to get the children from.

• resolution: INT resolution of the desired children.

Return type

BIGINT[]

Example

SELECT carto.QUADBIN_TOCHILDREN(5207251884775047167, 5);
-- { 5211742290262884351
-- 5211751086355906559
-- 5211746688309395455
-- 5211755484402417663 }

QUADBIN_TOPARENT

QUADBIN_TOPARENT(quadbin, resolution)

Description

Returns the parent (ancestor) Quadbin of a given Quadbin for a specific resolution. An ancestor of a given Quadbin is a Quadbin of smaller resolution that spatially contains it.

Input parameters

• quadbin: BIGINT Quadbin to get the parent from.

• resolution: INT resolution of the desired parent.

Return type

BIGINT

Example

SELECT carto.QUADBIN_TOPARENT(5207251884775047167, 3);
-- 5202783469519765503

QUADBIN_TOQUADKEY

QUADBIN_TOQUADKEY(quadbin)

Description

Compute a quadkey from a quadbin index.

Input parameters

• quadbin: BIGINT Quadbin index.

Return type

TEXT

Example

SELECT carto.QUADBIN_TOQUADKEY(5234261499580514303);
-- '0331110121'

QUADBIN_TOZXY

QUADBIN_TOZXY(quadbin)

Description

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

Input parameters

• quadbin: BIGINT Quadbin from which to obtain the coordinates.

Return type

JSON

Example

SELECT carto.QUADBIN_TOZXY(5207251884775047167);
-- {"z" : 4, "x" : 7, "y" : 6}