Analytics Toolbox for Redshift

Analytics Toolbox for Redshift

s2

S2 is a library for spherical geometry that aims to have the same robustness, flexibility, and performance as the very best planar geometry libraries.

You can learn more about s2 on their website.

S2_BOUNDARY

Description

Returns the boundary for a given S2 Cell ID as a WKT string. Note that S2 cell vertices should be joined with geodesic edges (great circles), not straight lines in a planar projection.

  • id: INT8 id to get the boundary geography from.

Return type

VARCHAR(MAX)

Example

1
2
SELECT carto.S2_BOUNDARY(1733885856537640960);
-- POLYGON ((-3.88131218692 39.9812346589, -3.58252176617 45.0, 0.0 45.0, 0.0 39.9812346589, -3.88131218692 39.9812346589))

S2_FROMGEOGPOINT

Description

Returns the S2 cell ID of a given point at a given level of detail.

  • point: GEOGRAPHY vertical coordinate of the map.
  • resolution: INT4 level of detail or zoom.

Return type

INT8

Example

1
2
SELECT carto.S2_FROMGEOGPOINT(ST_Point(40.4168, -3.7038), 4);
-- 1733885856537640960

S2_FROMHILBERTQUADKEY

Description

Returns the conversion of a Hilbert quadkey (a.k.a Hilbert curve quadtree ID) into a S2 cell ID.

  • hquadkey: VARCHAR(MAX) Hilbert quadkey to be converted.

Return type

INT8

Example

1
2
SELECT carto.S2_FROMHILBERTQUADKEY('0/30002221');
-- 1735346007979327488

S2_FROMLONGLAT

Description

Returns the S2 cell ID representation for a given level of detail and geographic coordinates.

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

Return type

INT8

Example

1
2
SELECT carto.S2_FROMLONGLAT(40.4168, -3.7038, 4);
-- 1733885856537640960

S2_FROMTOKEN

Description

Returns the conversion of an S2 cell token (hexified ID) into an unsigned, 64 bit ID.

  • token: VARCHAR(MAX) S2 cell token.

Return type

INT8

Example

1
2
SELECT carto.S2_FROMTOKEN('89c25a3');
-- -8520148382826627072

S2_FROMUINT64REPR

Description

Returns an INT64 cell ID from its UINT64 representation.

  • uid: VARCHAR(MAX) UINT64 representation of a S2 cell ID.

Return type

INT8

Example

1
2
SELECT carto.S2_FROMUINT64REPR('9926595690882924544');
-- -8520148382826627072

S2_POLYFILL_BBOX

Description

Returns a SUPER containing an array of S2 cell IDs that cover a planar bounding box. Note that this is a compact coverage (polyfill), so the bounding box is covered with the least amount of cells by using the largest cells possible.

Two optional arguments can be passed with the minimum and maximum resolution for coverage. If you desire a coverage at a single resolution level, simply set the maximum and minimum longitude to be of equal value.

  • min_longitude: FLOAT8 minimum longitude of the bounding box.
  • max_longitude: FLOAT8 maximum longitude of the bounding box.
  • min_latitude: FLOAT8 minimum latitude of the bounding box.
  • max_latitude: FLOAT8 maximum latitude of the bounding box.
  • min_resolution (optional): INT4 minimum resolution level for cells covering the bounding box. Defaults to 0.
  • max_resolution (optional): INT4 maximum resolution level for cells covering the bounding box. Defaults to 30.

Return type

SUPER

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT carto.S2_POLYFILL_BBOX(-3.688531, -3.680077, 40.409771, 40.421501);
-- [955367986615549952,955367988763033600,955367994131742720,955368019096240128,
--  955368020975288320,955370721435975680,955370742910812160,955370751500746752]

SELECT carto.S2_POLYFILL_BBOX(-3.688531, -3.680077, 40.409771, 40.421501, 4, 8);
-- [955378847514099712]

-- Single level coverage
SELECT carto.S2_POLYFILL_BBOX(-3.688531, -3.680077, 40.409771, 40.421501, 12, 12);
-- [955367921117298688,955368058556252160,955370669896368128,955370807335321600]

S2_RESOLUTION

Description

Returns an integer with the resolution of a given cell ID.

  • id: INT8 id to get the resolution from.

Return type

INT4

Example

1
2
SELECT carto.S2_RESOLUTION('1733885856537640960');
-- 4

S2_TOCHILDREN

Description

Returns a SUPER containing a plain array of children IDs of a given cell ID for a specific resolution. A child is an S2 cell of higher level of detail that is contained within the current cell. Each cell has four direct children by definition.

By default, this function returns the direct children (where parent resolution is children resolution - 1). However, an optional resolution argument can be passed with the desired parent resolution. Note that the amount of children grows to the power of four per zoom level.

  • id: INT8 id to get the children from.
  • resolution (optional): INT4 resolution of the desired children.

Return type

SUPER

Example

1
2
3
4
5
6
7
8
SELECT carto.S2_TOCHILDREN(1733885856537640960);
-- [1730508156817113088,1732759956630798336,1735011756444483584,1737263556258168832]

SELECT carto.S2_TOCHILDREN(1733885856537640960, 6);
-- [1729663731886981120,1730226681840402432,1730789631793823744,1731352581747245056,
--  1731915531700666368,1732478481654087680,1733041431607508992,1733604381560930304,
--  1734167331514351616,1734730281467772928,1735293231421194240,1735856181374615552,
--  1736419131328036864,1736982081281458176,1737545031234879488,1738107981188300800]

S2_TOHILBERTQUADKEY

Description

Returns the conversion of a S2 cell ID into a Hilbert quadkey (a.k.a Hilbert curve quadtree ID).

  • id: INT8 S2 cell ID to be converted.

Return type

VARCHAR(MAX)

Example

1
2
SELECT carto.S2_TOHILBERTQUADKEY(1735346007979327488);
-- 0/30002221

S2_TOPARENT

Description

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

By default, this function returns the direct parent (where parent resolution is child resolution + 1). However, an optional resolution argument can be passed with the desired parent resolution.

  • id: INT8 quadint to get the parent from.
  • resolution (optional): INT4 resolution of the desired parent.

Return type

INT8

Example

1
2
3
4
5
SELECT carto.S2_TOPARENT(1733885856537640960);
-- 1747396655419752448

SELECT carto.S2_TOPARENT(1733885856537640960, 1);
-- 2017612633061982208

S2_TOTOKEN

Description

Returns the conversion of a S2 cell ID into a token (S2 cell hexified ID).

  • id: INT8 S2 cell ID.

Return type

VARCHAR(MAX)

Example

1
2
SELECT carto.S2_TOTOKEN(-8520148382826627072);
-- 89c25a3

S2_TOUINT64REPR

Description

Returns the UINT64 representation of a cell ID.

  • id: INT8 S2 cell ID.

Return type

VARCHAR(MAX)

Example

1
2
SELECT carto.S2_TOUINT64REPR(-8520148382826627072);
-- 9926595690882924544