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].
quadbin
:INT64
Quadbin to get the bbox from.
Return type
ARRAY<FLOAT64>
Example
SELECT `carto-un`.carto.QUADBIN_BBOX(5207251884775047167);
-- -22.5
-- 21.943045533438188
-- 0.0
-- 40.979898069620127
QUADBIN_BOUNDARY
QUADBIN_BOUNDARY(quadbin)
Description
Returns the boundary for a given Quadbin as a polygon GEOGRAPHY with the same coordinates as given by the QUADBIN_BBOX function.
quadbin
:INT64
Quadbin to get the boundary geography from.
Return type
GEOGRAPHY
Example
SELECT `carto-un`.carto.QUADBIN_BOUNDARY(5207251884775047167);
-- POLYGON((-22.5 40.9798980696201, -22.5 21.9430455334382, 0 21.9430455334382, 0 40.9798980696201, -22.5 40.9798980696201))
QUADBIN_CENTER
QUADBIN_CENTER(quadbin)
Description
Returns the center of a given Quadbin. The center is the intersection point of the four immediate children Quadbins.
quadbin
:INT64
Quadbin to get the center from.
Return type
GEOGRAPHY
Example
SELECT `carto-un`.carto.QUADBIN_CENTER(5207251884775047167);
-- POINT(-11.25 31.952162238025)
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.
origin
:INT64
origin quadbin index.destination
:INT64
destination quadbin index.
Return type
INT64
Example
SELECT `carto-un`.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
.
point
:GEOGRAPHY
point to get the Quadbin from.resolution
:INT64
level of detail or zoom.
Return type
INT64
Example
SELECT `carto-un`.carto.QUADBIN_FROMGEOGPOINT(ST_GEOGPOINT(-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.
longitude
:FLOAT64
longitude (WGS84) of the point.latitude
:FLOAT64
latitude (WGS84) of the point.resolution
:INT64
level of detail or zoom.
Return type
INT64
Example
SELECT `carto-un`.carto.QUADBIN_FROMLONGLAT(-3.7038, 40.4168, 4);
-- 5207251884775047167
QUADBIN_FROMQUADKEY
QUADBIN_FROMQUADKEY(quadkey)
Description
Compute a quadbin index from a quadkey.
quadkey
:STRING
Quadkey representation of the index.
Return type
INT64
Example
SELECT `carto-un`.carto.QUADBIN_FROMQUADKEY('0331110121');
-- 5234261499580514303
QUADBIN_FROMZXY
QUADBIN_FROMZXY(z, x, y)
Description
Returns a Quadbin from z
, x
, y
tile 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
).
Note that the y
coordinate increases from North to South, and the y
coordinate from West to East.
Return type
INT64
Example
SELECT `carto-un`.carto.QUADBIN_FROMZXY(4, 7, 6);
-- 5207251884775047167
QUADBIN_ISVALID
QUADBIN_ISVALID(quadbin)
Description
Returns true
when the given index is a valid Quadbin, false
otherwise.
quadbin
:INT64
Quadbin index.
Return type
BOOLEAN
Examples
SELECT `carto-un`.carto.QUADBIN_ISVALID(5207251884775047167);
-- true
SELECT `carto-un`.carto.QUADBIN_ISVALID(1234);
-- false
QUADBIN_KRING
QUADBIN_KRING(origin, size)
Description
Returns all Quadbin cell indexes in a filled square k-ring centered at the origin in no particular order.
origin
:INT64
Quadbin index of the origin.size
:INT64
size of the ring (distance from the origin).
Return type
ARRAY<INT64>
Example
SELECT `carto-un`.carto.QUADBIN_KRING(5207251884775047167, 1);
-- 5207128739472736255
-- 5207234292589002751
-- 5207269476961091583
-- 5207146331658780671
-- 5207251884775047167
-- 5207287069147135999
-- 5207902795658690559
-- 5208008348774957055
-- 5208043533147045887
QUADBIN_KRING_DISTANCES
QUADBIN_KRING_DISTANCES(origin, size)
Description
Returns all Quadbin cell indexes and their distances in a filled square k-ring centered at the origin in no particular order.
origin
:INT64
Quadbin index of the origin.size
:INT64
size of the ring (distance from the origin).
Return type
ARRAY<STRUCT<index INT64, distance INT64>>
Example
SELECT `carto-un`.carto.QUADBIN_KRING_DISTANCES(5207251884775047167, 1);
-- {"index": "5207128739472736255", "distance": "1"}
-- {"index": "5207234292589002751", "distance": "1"}
-- {"index": "5207269476961091583", "distance": "1"}
-- {"index": "5207146331658780671", "distance": "1"}
-- {"index": "5207251884775047167", "distance": "0"}
-- {"index": "5207287069147135999", "distance": "1"}
-- {"index": "5207902795658690559", "distance": "1"}
-- {"index": "5208008348774957055", "distance": "1"}
-- {"index": "5208043533147045887", "distance": "1"}
QUADBIN_POLYFILL
QUADBIN_POLYFILL(geog, resolution)
Description
Returns an array of quadbin cell indexes contained in the given geography (Polygon, MultiPolygon) at a requested resolution. Containment is determined by the cells' center. This function is equivalent to QUADBIN_POLYFILL_MODE
with mode center
.
geog
:GEOGRAPHY
representing the shape to cover.resolution
:INT64
level of detail. The value must be between 0 and 26.
Use QUADBIN_POLYFILL_MODE
with mode intersects
in the following cases:
You want to provide the minimum covering set of a Polygon, MultiPolygon.
The input geography type is Point, MultiPoint, LineString, MultiLineString.
Return type
ARRAY<INT64>
Examples
SELECT `carto-un`.carto.QUADBIN_POLYFILL(
ST_GEOGFROMTEXT('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-un`.carto.QUADBIN_POLYFILL(
ST_GEOGFROMTEXT('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 <project>.<dataset>.<table>,
UNNEST(`carto-un`.carto.QUADBIN_POLYFILL(geog, 17)) AS quadbin;
QUADBIN_POLYFILL_MODE
QUADBIN_POLYFILL_MODE(geog, resolution, mode)
Description
Returns an array of quadbin cell indexes contained in the given geography at a requested resolution. Containment is determined by the mode: center, intersects, contains.
geog
:GEOGRAPHY
representing the shape to cover.resolution
:INT64
level of detail. The value must be between 0 and 26.mode
:STRING
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 that the other modes. This mode is not compatible with points or lines. Equivalent toQUADBIN_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.
Mode center
:

Mode intersects
:

Mode contains
:

Return type
ARRAY<INT64>
Examples
SELECT `carto-un`.carto.QUADBIN_POLYFILL_MODE(
ST_GEOGFROMTEXT('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-un`.carto.QUADBIN_POLYFILL_MODE(
ST_GEOGFROMTEXT('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 <project>.<dataset>.<table>,
UNNEST(`carto-un`.carto.QUADBIN_POLYFILL_MODE(geog, 17, 'intersects')) AS quadbin;
QUADBIN_POLYFILL_TABLE (BETA)
QUADBIN_POLYFILL_TABLE(input_query, resolution, mode, output_table)
Description
Returns a table with the quadbin cell indexes contained in the given geography 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, clustered by the quadbin column.
input_query
:STRING
input data to polyfill. It must contain a columngeom
with the shape to cover. Additionally, other columns can be included.resolution
:INT64
level of detail. The value must be between 0 and 26.mode
:STRING
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 that the other modes. This mode is not compatible with points or lines. Equivalent toQUADBIN_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
:STRING
name of the output table to store the results of the polyfill.
Mode center
:

Mode intersects
:

Mode contains
:

Output
The results are stored in the table named <output_table>
, which contains the following columns:
quadbin
:INT64
the geometry of the considered point.The rest of columns included in
input_query
exceptgeom
.
Examples
CALL `carto-un`.carto.QUADBIN_POLYFILL_TABLE(
"SELECT ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))') AS geom",
12, 'intersects',
'<project>.<dataset>.<output_table>'
);
-- The table `<project>.<dataset>.<output_table>` will be created
-- with column: quadbin
CALL `carto-un`.carto.QUADBIN_POLYFILL_TABLE(
'SELECT geom, name, value FROM `<project>.<dataset>.<table>`',
12, 'center',
'<project>.<dataset>.<output_table>'
);
-- The table `<project>.<dataset>.<output_table>` will be created
-- with columns: quadbin, name, value
QUADBIN_RESOLUTION
QUADBIN_RESOLUTION(quadbin)
Description
Returns the resolution of the input Quadbin.
quadbin
:INT64
Quadbin from which to get the resolution.
Return type
INT64
Example
SELECT `carto-un`.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.
quadbin
:INT64
Quadbin to get the sibling from.direction
:STRING
'right'|'left'|'up'|'down'
direction to move in to extract the next sibling.
Return type
INT64
Example
SELECT `carto-un`.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).
quadbin
:INT64
Quadbin to get the children from.resolution
:INT64
resolution of the desired children.
Return type
ARRAY<INT64>
Example
SELECT `carto-un`.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.
quadbin
:INT64
Quadbin to get the parent from.resolution
:INT64
resolution of the desired parent.
Return type
INT64
Example
SELECT `carto-un`.carto.QUADBIN_TOPARENT(5207251884775047167, 3);
-- 5202783469519765503
QUADBIN_TOQUADKEY
QUADBIN_TOQUADKEY(quadbin)
Description
Compute a quadkey from a quadbin index.
quadbin
:INT64
Quadbin index.
Return type
STRING
Example
SELECT `carto-un`.carto.QUADBIN_TOQUADKEY(5234261499580514303);
-- '0331110121'
QUADBIN_TOZXY
QUADBIN_TOZXY(quadbin)
Description
Returns the zoom level z
and coordinates x
, y
for a given Quadbin.
quadbin
:INT64
Quadbin from which to obtain the coordinates.
Return type
STRUCT<INT64, INT64, INT64>
Example
SELECT `carto-un`.carto.QUADBIN_TOZXY(5207251884775047167);
-- z x y
-- 4 7 6
This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.
Last updated
Was this helpful?