quadbin

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

QUADBIN_POLYFILL is a pipelined function that can be used directly inside INSERT … SELECT to materialize a table.

QUADBIN_BBOX

QUADBIN_BBOX(quadbin)

Description

Returns an object with the boundary box of a given Quadbin. This boundary box contains the minimum and maximum longitude and latitude. The output object exposes the named fields west, south, east, north (i.e. [West-South, East-North], or [min long, min lat, max long, max lat]).

Input parameters

• quadbin: NUMBER Quadbin to get the bbox from.

Return type

QUADBIN_BBOX_OBJ (object with west, south, east, north of type BINARY_DOUBLE)

Example

SELECT t.bbox.west, t.bbox.south, t.bbox.east, t.bbox.north
FROM (
    SELECT carto.QUADBIN_BBOX(5207251884775047167) AS bbox FROM DUAL
) t;
-- WEST                    SOUTH                  EAST  NORTH
-- -2.250000000000000e+01  2.194304553343818e+01  0     4.097989806962013e+01

QUADBIN_BOUNDARY

QUADBIN_BOUNDARY(quadbin)

Description

Returns the boundary for a given Quadbin as a polygon SDO_GEOMETRY (SRID 4326) with the same coordinates as given by the QUADBIN_BBOX function.

Input parameters

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

Return type

SDO_GEOMETRY

Example

SELECT carto.QUADBIN_BOUNDARY(5207251884775047167) FROM DUAL;
-- SDO_GEOMETRY(2003, 4326, NULL,
--   SDO_ELEM_INFO_ARRAY(1, 1003, 1),
--   SDO_ORDINATE_ARRAY(0, 40.97989806962013, 0, 21.94304553343818, ...))

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: NUMBER Quadbin to get the center from.

Return type

SDO_GEOMETRY

Example

SELECT carto.QUADBIN_CENTER(5207251884775047167) FROM DUAL;
-- SDO_GEOMETRY(2001, 4326,
--   SDO_POINT_TYPE(-1.125000000000000e+01, 3.195216223802496e+01, NULL),
--   NULL, NULL)

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: NUMBER origin quadbin index.

• destination: NUMBER destination quadbin index.

Return type

NUMBER

Example

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

QUADBIN_FROMGEOGPOINT

QUADBIN_FROMGEOGPOINT(point, resolution)

Description

Returns the Quadbin of a given point at a requested resolution. The input point is interpreted as WGS84 (EPSG:4326).

Input parameters

• point: SDO_GEOMETRY point to get the Quadbin from.

• resolution: NUMBER level of detail or zoom.

Return type

NUMBER

Example

SELECT carto.QUADBIN_FROMGEOGPOINT(
    SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-3.7038, 40.4168, NULL), NULL, NULL),
    4
) FROM DUAL;
-- 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: NUMBER longitude (WGS84) of the point.

• latitude: NUMBER latitude (WGS84) of the point.

• resolution: NUMBER level of detail or zoom.

Return type

NUMBER

Example

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

QUADBIN_FROMQUADKEY

QUADBIN_FROMQUADKEY(quadkey)

Description

Compute a quadbin index from a quadkey.

Input parameters

• quadkey: VARCHAR2 Quadkey representation of the index.

Return type

NUMBER

Example

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

Note

In Oracle '' is NULL, so the empty-string quadkey for zoom 0 cannot be passed directly. Use QUADBIN_FROMZXY(0, 0, 0) for zoom level 0.

QUADBIN_FROMZXY

QUADBIN_FROMZXY(z, x, y)

Description

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

Input parameters

• z: NUMBER zoom level.

• x: NUMBER horizontal position of a tile.

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

NUMBER

Example

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

QUADBIN_ISVALID

QUADBIN_ISVALID(quadbin)

Description

Returns 1 when the given index is a valid Quadbin, 0 otherwise. Oracle has no native SQL BOOLEAN, so the function uses the conventional 1/0 shape — callers test with = 1.

Input parameters

• quadbin: NUMBER Quadbin index.

Return type

NUMBER (1 or 0)

Examples

SELECT carto.QUADBIN_ISVALID(5207251884775047167) FROM DUAL;
-- 1

SELECT carto.QUADBIN_ISVALID(1234) FROM DUAL;
-- 0

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: NUMBER Quadbin index of the origin.

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

Return type

QUADBIN_INDEX_ARRAY (pipelined TABLE OF NUMBER)

Example

SELECT COLUMN_VALUE
FROM TABLE(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 cell indexes and their distances in a filled square k-ring centered at the origin in no particular order.

Input parameters

• origin: NUMBER Quadbin index of the origin.

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

Return type

QUADBIN_DISTANCE_ARRAY (pipelined TABLE OF QUADBIN_DISTANCE_PAIR, fields quadbin_index, distance)

Example

SELECT t.quadbin_index, t.distance
FROM TABLE(carto.QUADBIN_KRING_DISTANCES(5207251884775047167, 1)) t;
-- QUADBIN_INDEX        DISTANCE
-- 5207128739472736255  1
-- 5207234292589002751  1
-- 5207269476961091583  1
-- 5207146331658780671  1
-- 5207251884775047167  0
-- 5207287069147135999  1
-- 5207902795658690559  1
-- 5208008348774957055  1
-- 5208043533147045887  1

tip

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

QUADBIN_POLYFILL

QUADBIN_POLYFILL(geometry, resolution)

Description

Returns the Quadbins that intersect with the given geometry at a requested resolution. The function is pipelined; each row is one Quadbin.

Input parameters

• geometry: SDO_GEOMETRY geometry to extract the Quadbins from. Interpreted as WGS84 (EPSG:4326).

• resolution: NUMBER level of detail or zoom.

Return type

QUADBIN_INDEX_ARRAY (pipelined TABLE OF NUMBER)

Example

SELECT COLUMN_VALUE
FROM TABLE(carto.QUADBIN_POLYFILL(
    SDO_UTIL.FROM_WKTGEOMETRY('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
    17));
-- 5265786693153193983
-- 5265786693163941887
-- 5265786693164466175
-- 5265786693164204031
-- 5265786693164728319
-- 5265786693165514751

QUADBIN_POLYFILL_TABLE

QUADBIN_POLYFILL_TABLE(input_query, resolution, polyfill_mode, output_table)

Description

Materializes the quadbin polyfill of every row in input_query into a new table. The resulting table joins each input row with the polyfill cells of its geom column, preserving every other column the input query exposes.

This is the procedural form of QUADBIN_POLYFILL. Unlike the function, which returns the cells as a collection, this procedure writes the result directly to a table, avoiding the collection/array size limits that can be reached when polyfilling large geographies at high resolutions.

Invalid polyfill_mode, resolution outside 0..26, or NULL arguments cause the procedure to silently no-op (output table is not created).

Input parameters

• input_query: VARCHAR2 SELECT statement; must expose a column named geom of type SDO_GEOMETRY. Any other columns are passed through to the output table.

• resolution: NUMBER quadbin resolution (level of detail) between 0 and 26.

• polyfill_mode: VARCHAR2 'center', 'intersects', or 'contains'. Accepted for forward compatibility. (Named polyfill_mode rather than mode because mode is a reserved word in Oracle PL/SQL.) The native coverage produced by QUADBIN_POLYFILL is used regardless of the value; mode-specific containment will be supported in a future release.

• output_table: VARCHAR2 fully-qualified name of the table to create. Sanitized via DBMS_ASSERT.QUALIFIED_SQL_NAME.

Return type

None — creates the named table as a side effect. The output table has columns:

• quadbin NUMBER — the polyfill cell.

• every other column produced by input_query except geom.

Example

BEGIN
    carto.QUADBIN_POLYFILL_TABLE(
        'SELECT SDO_UTIL.FROM_WKTGEOMETRY(''POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'') AS geom FROM DUAL',
        17,
        'intersects',
        '<my-schema>.<my-output-table>'
    );
END;
/

SELECT quadbin FROM <my-schema>.<my-output-table> ORDER BY quadbin;
-- 5265786693153193983
-- 5265786693163941887
-- 5265786693164204031
-- 5265786693164466175
-- 5265786693164728319
-- 5265786693165514751

QUADBIN_RESOLUTION

QUADBIN_RESOLUTION(quadbin)

Description

Returns the resolution of the input Quadbin.

Input parameters

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

Return type

NUMBER

Example

SELECT carto.QUADBIN_RESOLUTION(5207251884775047167) FROM DUAL;
-- 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: NUMBER Quadbin to get the sibling from.

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

Return type

NUMBER

Example

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

QUADBIN_TOCHILDREN

QUADBIN_TOCHILDREN(quadbin, resolution)

Description

Returns 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: NUMBER Quadbin to get the children from.

• resolution: NUMBER resolution of the desired children.

Return type

QUADBIN_INDEX_ARRAY (pipelined TABLE OF NUMBER)

Example

SELECT COLUMN_VALUE
FROM TABLE(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: NUMBER Quadbin to get the parent from.

• resolution: NUMBER resolution of the desired parent.

Return type

NUMBER

Example

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

QUADBIN_TOQUADKEY

QUADBIN_TOQUADKEY(quadbin)

Description

Compute a quadkey from a quadbin index.

Input parameters

• quadbin: NUMBER Quadbin index.

Return type

VARCHAR2

Example

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

Note

In Oracle '' is NULL, so the zoom-0 quadbin's quadkey is returned as NULL rather than as an empty string.

QUADBIN_TOZXY

QUADBIN_TOZXY(quadbin)

Description

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

Input parameters

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

Return type

QUADBIN_ZXY (object with z, x, y of type NUMBER)

Example

SELECT t.zxy.z, t.zxy.x, t.zxy.y
FROM (SELECT carto.QUADBIN_TOZXY(5207251884775047167) AS zxy FROM DUAL) t;
-- Z  X  Y
-- 4  7  6