# Geospatial Functions

# greatCircleDistance

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

greatCircleDistance(lon1Deg, lat1Deg, lon2Deg, lat2Deg)

Input parameters

  • lon1Deg — Longitude of the first point in degrees. Range: [-180°, 180°].
  • lat1Deg — Latitude of the first point in degrees. Range: [-90°, 90°].
  • lon2Deg — Longitude of the second point in degrees. Range: [-180°, 180°].
  • lat2Deg — 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.

Returned value

  • The distance between two points on the Earth’s surface, in meters.
  • Generates an exception when the input parameter values fall outside of the range.

Example

SELECT greatCircleDistance(55.755831, 37.617673, 55.755831, 37.617673) AS greatCircleDistance
┌─greatCircleDistance─┐
│                   0 │
└─────────────────────┘

# geoDistance

Similar to greatCircleDistance but calculates the distance on WGS-84 ellipsoid instead of sphere. This is more precise approximation of the Earth Geoid. The performance is the same as for greatCircleDistance (no performance drawback). It is recommended to use geoDistance to calculate the distances on Earth.

Technical note: for close enough points we calculate the distance using planar approximation with the metric on the tangent plane at the midpoint of the coordinates.

geoDistance(lon1Deg, lat1Deg, lon2Deg, lat2Deg)

Input parameters

  • lon1Deg — Longitude of the first point in degrees. Range: [-180°, 180°].
  • lat1Deg — Latitude of the first point in degrees. Range: [-90°, 90°].
  • lon2Deg — Longitude of the second point in degrees. Range: [-180°, 180°].
  • lat2Deg — 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.

Returned value

  • The distance between two points on the Earth’s surface, in meters.
  • Generates an exception when the input parameter values fall outside of the range.

Example

SELECT geoDistance(38.8976, 77.0366, 39.9496, 75.1503) AS geoDistance
┌─geoDistance─┐
│ 212458.73   │
└─────────────┘

# H3

Get an index: https://wolf-h3-viewer.glitch.me (opens new window)

  • (8831aa5039fffff)16 = (613363270285262847)10

Check Valid h3IsValid(h3index)

SELECT h3IsValid(613363270285262847) AS h3IsValid;
┌─h3IsValid─┐
│ 1         │
└───────────┘

H3 Index into Longtitude & Latitude h3ToGeo(h3Index)

SELECT h3ToGeo(613363270285262847) AS coordinates;
┌─coordinates───────────────────────────┐
│(116.29523629955709,39.984633235997315)│
└───────────────────────────────────────┘

Into array of vertexes h3ToGeoBoundar

SELECT h3ToGeoBoundary(613363270285262847) AS coordinates;
┌─coordinates──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ [(39.98791325592796,116.2925333574096),(39.98386803718472,116.29020686993137),(39.98058822646115,116.29290984546513),(39.98135339622932,116.297938782017),(39.98539816328819,116.30026548576008),(39.98867821224181,116.29756303675612)] │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

# h3IsValid

Verifies whether the number is a valid H3 (opens new window) index.

h3IsValid(h3index)

Parameter

h3index — Hexagon index number. Type: UInt64.

Returned values

  • 1 — The number is a valid H3 index.
  • 0 — The number is not a valid H3 index.
  • Type: UInt8.

Example

SELECT h3IsValid(617301318803390464) AS h3IsValid;
┌─h3IsValid─┐
│ 1         │
└───────────┘

# h3ToGeo

Returns the centroid longitude and latitude corresponding to the provided H3 (opens new window) index.

h3ToGeo(h3Index)

Arguments

h3Index — H3 Index. UInt64.

Returned values

A tuple consisting of two values: tuple(lon,lat). lon — Longitude. Float64. lat — Latitude. Float64.

Example

SELECT h3ToGeo(617301318803390464) AS coordinates;
┌─coordinates───────────────────────────┐
│ (35.537867206326645,54.20746993771632)│
└───────────────────────────────────────┘

# h3ToGeoBoundary

Returns array of pairs (lon, lat), which corresponds to the boundary of the provided H3 index.

h3ToGeoBoundary(h3Index)

Arguments

h3Index — H3 Index. Type: UInt64.

Returned values

Array of pairs '(lon, lat)'. Type: Array(Float64, Float64).

Example

SELECT h3ToGeoBoundary(617301318803390464) AS coordinates;
┌─coordinates───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ [(54.20668646205218,35.53529359582246),(54.20566163343774,35.537605905213915),(54.20644505711934,35.54017955058831),(54.208253376128475,35.54044097223983),(54.209278241418595,35.53812853031426),(54.20849475102178,35.535554799273164)] │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘