​
ClickHouse function reference

​
greatCircleDistance

Calculates the distance between two points on the Earth’s surface using the great-circle formula.

Syntax:

greatCircleDistance(lon1Deg, lat1Deg, lon2Deg, lat2Deg)

Arguments:

  • lon1Deg (Float64): Longitude of the first point in degrees. Range: [-180°, 180°].
  • lat1Deg (Float64): Latitude of the first point in degrees. Range: [-90°, 90°].
  • lon2Deg (Float64): Longitude of the second point in degrees. Range: [-180°, 180°].
  • lat2Deg (Float64): Latitude of the second point in degrees. Range: [-90°, 90°].

Positive values correspond to North latitude and East longitude, and negative values correspond to South latitude and West longitude.

Returns:

The distance between two points on the Earth’s surface, in meters. [Float64]

Exception:

Throws an exception when input parameter values fall outside the specified ranges.

Example:

SELECT
	greatCircleDistance(
		-118.24368, 34.05223,  -- Longitude and latitude of Los Angeles Taco Festival
		-99.12766, 19.42847    -- Longitude and latitude of Mexico City Taco Festival
	) AS distance_between_taco_festivals;

Result:

| distance_between_taco_festivals |
|---------------------------------|
| 2491210.485439329               |

This example calculates the great-circle distance between the Los Angeles Taco Festival and the Mexico City Taco Festival, returning the result in meters.

While greatCircleDistance provides a good approximation, for more precise calculations on the Earth’s surface, consider using the geoDistance function, which uses the WGS-84 ellipsoid model.

​
geoDistance

Calculates the distance between two points on the Earth’s surface using the WGS-84 ellipsoid model.

Syntax:

geoDistance(lon1Deg, lat1Deg, lon2Deg, lat2Deg)

Arguments:

  • lon1Deg (Float64): Longitude of the first point in degrees. Range: [-180°, 180°].
  • lat1Deg (Float64): Latitude of the first point in degrees. Range: [-90°, 90°].
  • lon2Deg (Float64): Longitude of the second point in degrees. Range: [-180°, 180°].
  • lat2Deg (Float64): Latitude of the second point in degrees. Range: [-90°, 90°].

Positive values correspond to North latitude and East longitude, and negative values correspond to South latitude and West longitude.

Returns:

The distance between two points on the Earth’s surface, in meters. [Float64]

Exception:

Throws an exception when input parameter values fall outside the specified ranges.

  1. This function is more precise than greatCircleDistance as it uses the WGS-84 ellipsoid model instead of a sphere.
  2. For close points, it calculates the distance using planar approximation with the metric on the tangent plane at the midpoint of the coordinates.
  3. The performance is the same as greatCircleDistance (no performance drawback).

Example:

SELECT
	geoDistance(
		-99.12766, 19.42847,  -- Longitude and latitude of a taco stand in Mexico City
		-118.24368, 34.05223  -- Longitude and latitude of a taco truck in Los Angeles
	) AS distance_to_la_tacos;

Result:

| distance_to_la_tacos |
|----------------------|
| 2491210.485439329    |

This example calculates the distance between a taco stand in Mexico City and a taco truck in Los Angeles, returning the result in meters.

It is recommended to use geoDistance over greatCircleDistance for more accurate calculations of distances on Earth.

​
greatCircleAngle

Calculates the central angle between two points on the Earth’s surface using the great-circle formula.

Syntax:

greatCircleAngle(lon1Deg, lat1Deg, lon2Deg, lat2Deg)

Arguments:

  • lon1Deg (Float64): Longitude of the first point in degrees. Range: [-180°, 180°].
  • lat1Deg (Float64): Latitude of the first point in degrees. Range: [-90°, 90°].
  • lon2Deg (Float64): Longitude of the second point in degrees. Range: [-180°, 180°].
  • lat2Deg (Float64): Latitude of the second point in degrees. Range: [-90°, 90°].

Positive values correspond to North latitude and East longitude, and negative values correspond to South latitude and West longitude.

Returns:

The central angle between two points in degrees. [Float64]

Example:

SELECT
	greatCircleAngle(
		-118.24368, 34.05223,  -- Longitude and latitude of a taco truck in Los Angeles
		-74.00597, 40.71427    -- Longitude and latitude of a taco truck in New York
	) AS angle_between_taco_trucks;

Result:

| angle_between_taco_trucks |
|---------------------------|
| 35.39468598839788         |

This example calculates the central angle between two taco trucks, one in Los Angeles and another in New York. The result is approximately 40.7 degrees, representing the angular distance between these two locations on the Earth’s surface.

The greatCircleAngle function is useful for calculating angular distances on a spherical model of the Earth. For more precise distance calculations that take into account the Earth’s ellipsoidal shape, consider using the geoDistance function.

​
pointInEllipses

Checks whether a point belongs to at least one of the specified ellipses on a 2D plane.

Syntax:

pointInEllipses(x, y, x0, y0, a0, b0, ..., xn, yn, an, bn)

Arguments:

  • x, y (Float64): Coordinates of the point to check.
  • xi, yi (Float64): Coordinates of the center of the i-th ellipse.
  • ai, bi (Float64): Lengths of the semi-major and semi-minor axes of the i-th ellipse.

The function accepts 2 + 4n arguments, where n is the number of ellipses.

Returns:

1 if the point is inside at least one of the ellipses, 0 otherwise. [UInt8]

Example:

SELECT
  pointInEllipses(
    5.0, 5.0,  -- Point to check
    0.0, 0.0, 10.0, 5.0,  -- First ellipse: center (0.0, 0.0), semi-major axis 10.0, semi-minor axis 5.0
    10.0, 10.0, 5.0, 3.0  -- Second ellipse: center (10.0, 10.0), semi-major axis 5.0, semi-minor axis 3.0
  ) AS is_in_taco_zone;

Result:

| is_in_taco_zone |
|-----------------|
| 0               |

In this example, we check if the point (5, 5) is within either of two ellipses representing “taco zones”. The result 1 indicates that the point is inside at least one of the ellipses, suggesting it’s in a taco zone.

The function uses the Cartesian coordinate system. Make sure to convert geographical coordinates to an appropriate projection if working with map data.

Syntax:

pointInPolygon((x, y), [(a, b), (c, d) ...], ...)

Arguments:

  • (x, y) (Tuple(Float64, Float64)): Coordinates of a point on the plane.
  • [(a, b), (c, d) ...] (Array(Tuple(Float64, Float64))): Polygon vertices.
    • Each vertex is represented by a pair of coordinates (a, b).
    • Vertices should be specified in a clockwise or counterclockwise order.
    • The minimum number of vertices is 3.
    • The polygon must be constant.

Returns:

  • 1 if the point is inside the polygon, 0 if it is not. (UInt8)
  • If the point is on the polygon boundary, the function may return either 0 or 1.
  • The function supports polygons with holes (cut out sections). Add polygons that define the cut out sections using additional arguments of the function.
  • The function does not support non-simply-connected polygons.

Example:

SELECT
	pointInPolygon(
		(3, 3),
		[(0, 0), (0, 5), (5, 5), (5, 0)]
	) AS is_inside_taco_zone

Result:

| is_inside_taco_zone |
|---------------------|
| 1                   |

In this example, we check if the point (3, 3) is inside a square-shaped “taco zone” defined by the vertices (0, 0), (0, 5), (5, 5), and (5, 0). The result 1 indicates that the point is inside the taco zone.

Was this page helpful?

OverviewGeohash