​
ClickHouse function reference

​
geoToS2

Converts geographical coordinates to an S2 cell index.

Syntax:

geoToS2(lon, lat)

Arguments:

  • lon (Float64): Longitude of the point.
  • lat (Float64): Latitude of the point.

Returns:

  • S2 cell index (UInt64).

Example:

SELECT geoToS2(-97.7431, 30.2672) AS austin_s2;

Result:

| austin_s2           |
|---------------------|
| 9675056946716684281 |

This example converts the geographical coordinates of Austin, Texas to its corresponding S2 cell index.

S2 is a hierarchical geocoding system that represents the Earth’s surface as a hierarchy of cells. It’s useful for spatial indexing and querying in geographical applications.

​
s2ToGeo

Converts an S2 cell index to geographic coordinates (longitude, latitude).

Syntax:

s2ToGeo(s2Index)

Arguments:

  • s2Index (UInt64): The S2 cell index to convert.

Returns:

A tuple containing two Float64 values:

  1. Longitude
  2. Latitude

Example:

SELECT s2ToGeo(5471080288399851520) AS taco_location;

Result:

| taco_location                          |
|----------------------------------------|
| (-47.79030971062747,53.68370417642377) |

In this example, we convert an S2 cell index to geographic coordinates, which could represent the location of a taco truck.

The S2 indexing system is used for efficient geospatial indexing and querying. It’s particularly useful for location-based services and geospatial analysis in ClickHouse.

​
s2GetNeighbors

Returns the S2 neighbor indexes corresponding to the provided S2 index. Each cell in the S2 system is a quadrilateral bounded by four geodesics, so each cell has 4 neighbors.

Syntax:

s2GetNeighbors(s2index)

Arguments:

  • s2index (UInt64): The S2 index for which to find neighbors.

Returns:

An array of 4 neighbor indexes: [s2index1, s2index2, s2index3, s2index4] (Array(UInt64)).

Example:

SELECT s2GetNeighbors(9074766849661468672) AS s2Neighbors;

Result:

| taco_truck_neighbors                                                              |
|-----------------------------------------------------------------------------------|
| [9074766849667760128,9074766839089725440,9074766849659371520,9074766849663565824] |

In this example, we find the S2 neighbors of a taco truck location in Austin, Texas. The function returns an array of 4 S2 indexes representing the neighboring cells.

The order of the returned neighbors is consistent but not guaranteed to be in any particular orientation relative to the input cell.

This function is useful for spatial analysis, such as finding nearby locations or implementing efficient geospatial search algorithms.

​
s2CellsIntersect

Determines if two S2 cells intersect.

Syntax:

s2CellsIntersect(s2Index1, s2Index2)

Arguments:

  • s2Index1 (UInt64): The first S2 cell index.
  • s2Index2 (UInt64): The second S2 cell index.

Returns:

  • 1 if the cells intersect, 0 otherwise. [UInt8]

Example:

SELECT
    s2CellsIntersect(
        geoToS2(-97.7431, 30.2672), -- Austin, TX (longitude, latitude)
        geoToS2(-97.7466, 30.2649)  -- Nearby taco truck (longitude, latitude)
    ) AS tacos_in_range;

Result:

| tacos_in_range |
|----------------|
| 1              |

In this example, we check if the S2 cell containing Austin, TX intersects with the S2 cell of a nearby taco truck. The result 0 indicates that the cells don’t intersect, suggesting the taco truck is not within the specified area.

This function is useful for efficient geospatial queries, such as determining if two areas on Earth overlap or are adjacent.

​
s2CapContains

Determines if a spherical cap contains a given S2 point.

Syntax:

s2CapContains(center, degrees, point)

Arguments:

  • center (UInt64): S2 point index representing the center of the cap.
  • degrees (Float64): Radius of the cap in degrees.
  • point (UInt64): S2 point index to check for containment.

Returns:

  • 1 if the cap contains the S2 point. [UInt8]
  • 0 if the cap does not contain the S2 point. [UInt8]

Example:

SELECT
	s2CapContains(
		geoToS2(-98.5795, 19.4513), -- Center: Taco Stands   in Mexico City
		1.0, -- Radius: 1 degree
		geoToS2(-98.5800, 19.4510) -- Point: Nearby taco stand
	) AS is_in_taco_zone;

Result:

| is_in_taco_zone |
|-----------------|
| 1               |

In this example, we check if a nearby taco stand is within a 1-degree radius of a Taco Stands in Mexico City. The result 1 indicates that the point is indeed within the specified cap.

The S2 geometry system represents geographical points as 64-bit integers. Use the geoToS2 function to convert latitude and longitude to S2 point indexes.

​
s2CapUnion

Determines the smallest cap that contains the given two input caps. A cap represents a portion of the sphere that has been cut off by a plane. It is defined by a point on a sphere and a radius in degrees.

Syntax:

s2CapUnion(center1, radius1, center2, radius2)

Arguments:

  • center1, center2 (UInt64): S2 point indexes corresponding to the two input caps.
  • radius1, radius2 (Float64): Radius of the two input caps in degrees.

Returns:

  • center (UInt64): S2 point index corresponding to the center of the smallest cap containing the two input caps.
  • radius (Float64): Radius of the smallest cap containing the two input caps.

Example:

SELECT
	s2CapUnion(
		geoToS2(-98.5795, 19.4513), 1.0,  -- Center of Mexico City with 1 degree radius
		geoToS2(-99.1332, 19.4326), 1.0   -- Center of Tacubaya with 1 degree radius
	) AS taco_delivery_zone;

Result:

| taco_delivery_zone                      |
|-----------------------------------------|
| (9642735348476658091,1.2612310696093425)|

In this example, we calculate the smallest cap that contains two areas in Mexico City, potentially representing a taco delivery zone. The result includes the center point of the new cap (as an S2 index) and its radius in degrees.

​
s2RectAdd

Increases the size of a bounding rectangle to include a given S2 point.

Syntax:

s2RectAdd(s2pointLow, s2pointHigh, s2Point)

Arguments:

  • s2PointLow (UInt64): Low S2 point index of the rectangle.
  • s2PointHigh (UInt64): High S2 point index of the rectangle.
  • s2Point (UInt64): Target S2 point index to include in the rectangle.

Returns:

A tuple containing two UInt64 values:

  1. Low S2 cell id of the expanded rectangle.
  2. High S2 cell id of the expanded rectangle.

Example:

SELECT
	s2RectAdd(
		geoToS2(30.0, 10.0),  -- Southwest corner of taco shop
		geoToS2(31.0, 11.0),  -- Northeast corner of taco shop
		geoToS2(31.5, 10.5)   -- New taco truck location
	) AS expanded_taco_zone;

Result:

| expanded_taco_zone                           |
|----------------------------------------------|
| (1648733934383963393,1649770672972501277)    |

In this example, we expand the bounding rectangle of a taco shop to include a new taco truck location. The function returns the new low and high S2 cell ids that define the expanded rectangle.

The S2 cell ids returned by this function can be converted back to geographic coordinates using the s2ToGeo function if needed.

​
s2RectContains

Determines if a given rectangle contains an S2 point. In the S2 system, a rectangle is represented by a type of S2Region called an S2LatLngRect that represents a rectangle in latitude-longitude space.

Syntax:

s2RectContains(s2PointLow, s2PointHigh, s2Point)

Arguments:

  • s2PointLow (UInt64): Low S2 point index corresponding to the rectangle.
  • s2PointHigh (UInt64): High S2 point index corresponding to the rectangle.
  • s2Point (UInt64): Target S2 point index to check for containment.

Returns:

  • 1 if the rectangle contains the given S2 point.
  • 0 if the rectangle doesn’t contain the given S2 point.

Example:

SELECT
	s2RectContains(
		5179062030687166815, -- s2PointLow (Taco Stand)
		5177056748191934217, -- s2PointHigh (Burrito Cart)
		5178914411069187297  -- s2Point (Random taco truck location)
	) AS is_taco_truck_in_rectangle;

Result:

| is_taco_truck_in_rectangle |
|----------------------------|
| 0                          |

In this example, we check if a taco truck’s location (represented by an S2 point) is within a rectangle defined by the S2 indexes of Taco Stands and Burrito Cart. The result 0 indicates that the taco truck is not within this “fast-casual Mexican food” rectangle.

This function is useful for efficient geospatial queries, such as determining if a point of interest falls within a specific geographical area defined by two corner points.

​
s2RectUnion

Returns the smallest rectangle containing the union of two given rectangles in the S2 coordinate system.

Syntax:

s2RectUnion(s2Rect1PointLow, s2Rect1PointHi, s2Rect2PointLow, s2Rect2PointHi)

Arguments:

  • s2Rect1PointLow, s2Rect1PointHi (UInt64): Low and High S2 point indexes corresponding to the first rectangle.
  • s2Rect2PointLow, s2Rect2PointHi (UInt64): Low and High S2 point indexes corresponding to the second rectangle.

Returns:

  • A tuple containing two UInt64 values:
    1. s2UnionRect2PointLow — Low S2 cell id corresponding to the union rectangle.
    2. s2UnionRect2PointHi — High S2 cell id corresponding to the union rectangle.

Example:

SELECT
	s2RectUnion(
		geoToS2(-98.5795, 19.4341),  -- Taco Stands in Mexico City
		geoToS2(-98.5795, 19.4341),  -- Same point for simplicity
		geoToS2(-122.4194, 37.7749), -- Taco Stands in San Francisco
		geoToS2(-122.4194, 37.7749)  -- Same point for simplicity
	) AS taco_union_rect;

Result:

| taco_union_rect                              |
|----------------------------------------------|
| (9330755937768936359,9774026387702302447)    |

This example calculates the smallest rectangle that contains both a taco stand in Mexico City and one in San Francisco. The result is represented by two S2 cell ids defining the corners of the rectangle.

In the S2 system, a rectangle is represented by a type of S2Region called an S2LatLngRect, which represents a rectangle in latitude-longitude space.

​
s2RectIntersection

Returns the smallest rectangle containing the intersection of two given rectangles in the S2 coordinate system.

Syntax:

s2RectIntersection(s2Rect1PointLow, s2Rect1PointHi, s2Rect2PointLow, s2Rect2PointHi)

Arguments:

  • s2Rect1PointLow, s2Rect1PointHi (UInt64): Low and High S2 point indexes corresponding to the first rectangle.
  • s2Rect2PointLow, s2Rect2PointHi (UInt64): Low and High S2 point indexes corresponding to the second rectangle.

Returns:

  • A tuple containing two UInt64 values:
    1. Low S2 cell id corresponding to the intersection rectangle.
    2. High S2 cell id corresponding to the intersection rectangle.

Example:

SELECT
	s2RectIntersection(
		geoToS2(-98.5795, 19.4341),  -- Taco Stands in Toluca
		geoToS2(-98.5785, 19.4351),
		geoToS2(-98.5790, 19.4346),  -- Nearby taco stand
		geoToS2(-98.5780, 19.4356)
	) AS taco_intersection_area;

Result:

| taco_intersection_area                       |
|----------------------------------------------|
| (9642724229385195235,9642724228791259811)    |

This example calculates the intersection of two rectangles representing areas around a Taco Stands and a nearby taco stand in Toluca, Mexico. The result represents the overlapping area where taco enthusiasts might compare offerings from both establishments.

In the S2 system, a rectangle is represented by a type of S2Region called S2LatLngRect, which represents a rectangle in latitude-longitude space.

Was this page helpful?

PolygonsSVG