Set up docs MCPareaCartesian
Introduced in: v25.12.0
Returns the area of the object.
Syntax
areaCartesian(object)Arguments
object— geometry objectGeometry
Returned value
Returns the area of the object. Float64
areaSpherical
Introduced in: v25.12.0
Returns the area of the object.
Syntax
areaSpherical(object)Arguments
object— geometry objectGeometry
Returned value
Returns the area of the object. Float64
geoDistance
Introduced in: v1.1.0
Similar to the greatCircleDistance function but it calculates the distance on the WGS-84 ellipsoid, which is a more accurate representation of Earth’s shape than a perfect sphere.
It offers the same performance as for greatCircleDistance and it is therefore recommended to use geoDistance to calculate distances on Earth.
Technical note: for close enough points it calculates the distance using planar approximation with the metric on the tangent plane at the midpoint of the coordinates.
Syntax
geoDistance(lon1Deg, lat1Deg, lon2Deg, lat2Deg)Arguments
lon1Deg— Longitude of the first point in degrees. Range:[-180°, 180°].(U)Int*orFloat*orDecimallat1Deg— Latitude of the first point in degrees. Range:[-90°, 90°].(U)Int*orFloat*orDecimallon2Deg— Longitude of the second point in degrees. Range:[-180°, 180°].(U)Int*orFloat*orDecimallat2Deg— Latitude of the second point in degrees. Range:[-90°, 90°].(U)Int*orFloat*orDecimal
Returned value
Returns the distance between two points on the Earth’s surface, in meters Float64
Examples
Basic usage
SELECT geoDistance(38.8976, -77.0366, 39.9496, -75.1503) AS geoDistance┌────────geoDistance─┐
│ 212458.82819586992 │
└────────────────────┘geohashDecode
Introduced in: v20.1.0
Decodes any geohash-encoded string into longitude and latitude coordinates.
Syntax
geohashDecode(hash_str)Arguments
hash_str— Geohash-encoded string to decode.StringorFixedString
Returned value
Returns a tuple of (longitude, latitude) with Float64 precision. Tuple(Float64, Float64)
Examples
Basic usage
SELECT geohashDecode('ezs42') AS res┌─res─────────────────────────────┐
│ (-5.60302734375,42.60498046875) │
└─────────────────────────────────┘geohashEncode
Introduced in: v20.1.0
Encodes longitude and latitude as a geohash-string.
:::
All coordinate parameters must be of the same type: either Float32 or Float64.
For the precision parameter, any value less than 1 or greater than 12 is silently converted to 12.
:::
Syntax
geohashEncode(longitude, latitude, [precision])Arguments
longitude— Longitude part of the coordinate to encode. Range:[-180°, 180°].Float32orFloat64latitude— Latitude part of the coordinate to encode. Range:[-90°, 90°].Float32orFloat64precision— Optional. Length of the resulting encoded string. Default: 12. Range:[1, 12].(U)Int*
Returned value
Returns an alphanumeric string of the encoded coordinate (modified version of the base32-encoding alphabet is used) String
Examples
Basic usage with default precision
SELECT geohashEncode(-5.60302734375, 42.593994140625) AS res┌─res──────────┐
│ ezs42d000000 │
└──────────────┘geohashesInBox
Introduced in: v20.1.0
Returns an array of geohash-encoded strings of given precision that fall inside and intersect boundaries of given box, essentially a 2D grid flattened into an array.
This function throws an exception if the size of the resulting array exceeds more than 10,000,000 items.
Syntax
geohashesInBox(longitude_min, latitude_min, longitude_max, latitude_max, precision)Arguments
longitude_min— Minimum longitude. Range:[-180°, 180°].Float32orFloat64latitude_min— Minimum latitude. Range:[-90°, 90°].Float32orFloat64longitude_max— Maximum longitude. Range:[-180°, 180°].Float32orFloat64latitude_max— Maximum latitude. Range:[-90°, 90°].Float32orFloat64precision— Geohash precision. Range:[1, 12].UInt8
Returned value
Returns an array of precision-long strings of geohash-boxes covering the provided area, or an empty array if the minimum longitude and latitude values aren’t less than the corresponding maximum values. Array(String)
Examples
Basic usage
SELECT geohashesInBox(24.48, 40.56, 24.785, 40.81, 4) AS thasos┌─thasos──────────────────────────────────────┐
│ ['sx1q','sx1r','sx32','sx1w','sx1x','sx38'] │
└─────────────────────────────────────────────┘geometryIntersectCartesian
Introduced in: v26.7.0
Returns true if two geometries intersect (share any common point, line or area).
Unlike polygonsIntersectCartesian, it accepts any geometry data type
(Point, MultiPoint, LineString,
MultiLineString, Ring,
Polygon, MultiPolygon),
including the common Geometry type, and the two arguments may be of different types.
Coordinates are interpreted in the Cartesian plane.
Syntax
geometryIntersectCartesian(geometry1, geometry2)Arguments
geometry1— A value of any geometry data type orGeometry. -geometry2— A value of any geometry data type orGeometry.
Returned value
Returns true (1) if the two geometries intersect. Bool.
Examples
Usage example
SELECT geometryIntersectCartesian([(2., 2.), (2., 3.), (3., 3.), (3., 2.)]::Ring, [(1., 1.), (1., 4.), (4., 4.), (4., 1.)]::Ring)┌─geometryIntersectCartesian(CAST('[(2., 2.), (2., 3.), (3., 3.), (3., 2.)]', 'Ring'), CAST('[(1., 1.), (1., 4.), (4., 4.), (4., 1.)]', 'Ring'))─┐
│ 1 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘geometryIntersectSpherical
Introduced in: v26.7.0
Returns true if two geometries intersect (share any common point, line or area).
Unlike polygonsIntersectSpherical, it accepts any geometry data type
(Point, MultiPoint, LineString,
MultiLineString, Ring,
Polygon, MultiPolygon),
including the common Geometry type, and the two arguments may be of different types.
Coordinates are interpreted as being on an ideal sphere.
Syntax
geometryIntersectSpherical(geometry1, geometry2)Arguments
geometry1— A value of any geometry data type orGeometry. -geometry2— A value of any geometry data type orGeometry.
Returned value
Returns true (1) if the two geometries intersect. Bool.
Examples
Usage example
SELECT geometryIntersectSpherical([[[(4.3613577, 50.8651821), (4.349556, 50.8535879), (4.3602419, 50.8435626), (4.3830299, 50.8428851), (4.3904543, 50.8564867), (4.3613148, 50.8651279)]]], (4.36, 50.85))┌─geometryIntersectSpherical([[[(4.3613577, 50.8651821), (4.349556, 50.8535879), (4.3602419, 50.8435626), (4.3830299, 50.8428851), (4.3904543, 50.8564867), (4.3613148, 50.8651279)]]], (4.36, 50.85))─┐
│ 1 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘geoToH3
Introduced in: v20.1.0
Returns H3 point index for the given latitude, longitude, and resolution.
Syntax
geoToH3(lat, lon, resolution)Arguments
lat— Latitude in degrees.Float64lon— Longitude in degrees.Float64resolution— Index resolution with range[0, 15].UInt8
Returned value
Returns the H3 index number, or 0 in case of error. UInt64
Examples
Convert coordinates to H3 index
SELECT geoToH3(55.71290588, 37.79506683, 15) AS h3Index┌────────────h3Index─┐
│ 644325524701193974 │
└────────────────────┘geoToMGRS
Introduced in: v26.7.0
Encodes WGS84 geographic coordinates (longitude, latitude) as a Military Grid Reference System (MGRS) string.
The string is <zone><band><100km square><easting><northing>, for example 31UDQ4825111935. The precision argument controls the number of digits used for each of the easting and northing: 5 (default) for 1 m, 4 for 10 m, down to 0 for the 100 km grid square only. MGRS is only defined for latitudes in the range [-80°, 84°].
Syntax
geoToMGRS(longitude, latitude[, precision])Arguments
longitude— Longitude in degrees. Range:[-180°, 180°].Float32orFloat64latitude— Latitude in degrees. Range:[-80°, 84°].Float32orFloat64precision— Optional. Number of digits for each of easting and northing. Default: 5. Range:[0, 5].(U)Int*
Returned value
Returns the MGRS reference string. String
Examples
Basic usage
SELECT geoToMGRS(2.294497, 48.858222)31UDQ4825111935Lower precision (100 m)
SELECT geoToMGRS(2.294497, 48.858222, 3)31UDQ482119geoToS2
Introduced in: v21.9.0
Returns the S2 point index corresponding to the provided coordinates (longitude, latitude).
An S2 point index is a number that internally encodes a point on the surface of a unit sphere, unlike traditional (longitude, latitude) pairs. Use s2ToGeo to get the coordinates from an S2 point index.
Syntax
geoToS2(lon, lat)Arguments
Returned value
Returns the S2 cell identifier. UInt64
Examples
Basic usage
SELECT geoToS2(37.79506683, 55.71290588)4704772434919038107geoToUTM
Introduced in: v26.7.0
Converts WGS84 geographic coordinates (longitude, latitude) to Universal Transverse Mercator (UTM) coordinates.
The zone is selected automatically from the longitude, applying the standard exceptions for Norway and Svalbard, unless an explicit zone is given. UTM is only defined for latitudes in the range [-80°, 84°].
Syntax
geoToUTM(longitude, latitude[, zone])Arguments
longitude— Longitude in degrees. Range:[-180°, 180°].Float32orFloat64latitude— Latitude in degrees. Range:[-80°, 84°].Float32orFloat64zone— Optional. Force projection into this UTM zone instead of selecting it automatically. Range:[1, 60].(U)Int*
Returned value
Returns a named tuple (easting, northing, zone, band): easting and northing in metres, the UTM zone number, and the MGRS latitude band letter (band >= 'N' is the northern hemisphere). Tuple(Float64, Float64, UInt8, FixedString(1))
Examples
Basic usage
SELECT geoToUTM(2.294497, 48.858222)(448251.5978370684,5411935.125629659,31,'U')greatCircleAngle
Introduced in: v1.1.0
Calculates the central angle between two points on the Earth’s surface using the great-circle formula. This function returns the angle in degrees between two points on a sphere.
Syntax
greatCircleAngle(lon1Deg, lat1Deg, lon2Deg, lat2Deg)Arguments
lon1Deg— Longitude of the first point in degrees. Range:[-180°, 180°](U)Int*orFloat*orDecimallat1Deg— Latitude of the first point in degrees. Range:[-90°, 90°].(U)Int*orFloat*orDecimallon2Deg— Longitude of the second point in degrees. Range:[-180°, 180°].(U)Int*orFloat*orDecimallat2Deg— Latitude of the second point in degrees. Range:[-90°, 90°].(U)Int*orFloat*orDecimal
Returned value
Returns the central angle between the two points in degrees Float64
Examples
Basic usage
SELECT greatCircleAngle(0, 0, 45, 0) AS angle┌────angle─┐
│ 44.99998 │
└──────────┘greatCircleDistance
Introduced in: v1.1.0
Calculates the distance between two points on the Earth’s surface using the great-circle formula.
Syntax
greatCircleDistance(lon1Deg, lat1Deg, lon2Deg, lat2Deg)Arguments
lon1Deg— Longitude of the first point in degrees. Range:[-180°, 180°].(U)Int*orFloat*orDecimallat1Deg— Latitude of the first point in degrees. Range:[-90°, 90°].(U)Int*orFloat*orDecimallon2Deg— Longitude of the second point in degrees. Range:[-180°, 180°].(U)Int*orFloat*orDecimallat2Deg— Latitude of the second point in degrees. Range:[-90°, 90°].(U)Int*orFloat*orDecimal
Returned value
Returns the distance between two points on the Earth’s surface, in meters Float64
Examples
Basic usage
SELECT greatCircleDistance(55.755831, 37.617673, -55.755831, -37.617673) AS greatCircleDistance┌─greatCircleDistance─┐
│ 14128352.575065022 │
└─────────────────────┘h3CellAreaM2
Introduced in: v22.1.0
Returns the exact area of a specific cell in square meters corresponding to the given input H3 index.
Syntax
h3CellAreaM2(index)Arguments
index— Hexagon index number.UInt64
Returned value
Returns the exact area of the H3 cell in square meters. Float64
Examples
Get area of an H3 cell
SELECT h3CellAreaM2(579205133326352383) AS area┌───────────────area─┐
│ 4106166334463.9214 │
└────────────────────┘h3CellAreaRads2
Introduced in: v22.1.0
Returns the exact area of a specific cell in square radians corresponding to the given input H3 index.
Syntax
h3CellAreaRads2(index)Arguments
index— Hexagon index number.UInt64
Returned value
Returns the exact area of the H3 cell in square radians. Float64
Examples
Get area of an H3 cell in square radians
SELECT h3CellAreaRads2(579205133326352383) AS area┌────────────────area─┐
│ 0.10116268528089563 │
└─────────────────────┘h3Distance
Introduced in: v22.6.0
Returns the distance in grid cells between two H3 indices.
This function calculates the minimum number of grid cells between the start and end indices, following the connections of the H3 grid.
Syntax
h3Distance(start, end)Arguments
start— Hexagon index number that represents the starting point.UInt64end— Hexagon index number that represents the ending point.UInt64
Returned value
Returns the number of grid cells between the start and end indices. Returns a negative number if the distance cannot be computed. Int64
Examples
Calculate distance between two H3 indices
SELECT h3Distance(590080540275638271, 590103561300344831) AS distance┌─distance─┐
│ 6 │
└──────────┘h3EdgeAngle
Introduced in: v20.1.0
Calculates the average length of an H3 hexagon edge in grades.
Syntax
h3EdgeAngle(resolution)Arguments
resolution— Index resolution. Range:[0, 15].UInt8
Returned value
Returns the average length of an H3 hexagon edge in grades. Float64
Examples
Get edge angle for resolution 10
SELECT h3EdgeAngle(10) AS edgeAngle┌─────────────edgeAngle─┐
│ 0.0006822586214258879 │
└───────────────────────┘h3EdgeLengthKm
Introduced in: v20.1.0
Calculates the average length of an H3 hexagon edge in kilometers.
Syntax
h3EdgeLengthKm(resolution)Arguments
resolution— Index resolution with range[0, 15].UInt8
Returned value
Returns the average length of an H3 hexagon edge in kilometers. Float64
Examples
Get edge length for maximum resolution
SELECT h3EdgeLengthKm(15) AS edgeLengthKm┌─edgeLengthKm─┐
│ 0.000584169 │
└──────────────┘h3EdgeLengthM
Introduced in: v20.1.0
Calculates the average length of an H3 hexagon edge in meters.
Syntax
h3EdgeLengthM(resolution)Arguments
resolution— Index resolution. Range:[0, 15].UInt8
Returned value
Returns the average edge length of an H3 hexagon in meters. Float64
Examples
Get edge length for maximum resolution
SELECT h3EdgeLengthM(15) AS edgeLengthM┌─edgeLengthM─┐
│ 0.58416863 │
└─────────────┘h3ExactEdgeLengthKm
Introduced in: v22.2.0
Returns the exact edge length of the unidirectional edge represented by the input H3 in kilometers.
Syntax
h3ExactEdgeLengthKm(index)Arguments
index— Hexagon index number.UInt64
Returned value
Returns the exact length of the H3 edge in kilometers. Throws an exception if the input is not a valid directed edge (controlled by the functions_h3_default_if_invalid setting). Float64
Examples
Get exact edge length in kilometers
SELECT round(h3ExactEdgeLengthKm(1310277011704381439), 8) AS exactEdgeLengthKm┌─exactEdgeLengthKm─┐
│ 195.44963163 │
└───────────────────┘h3ExactEdgeLengthM
Introduced in: v22.2.0
Returns the exact edge length of the unidirectional edge represented by the input H3 in meters.
Syntax
h3ExactEdgeLengthM(index)Arguments
index— Hexagon index number.UInt64
Returned value
Returns the exact length of the H3 edge in meters. Throws an exception if the input is not a valid directed edge (controlled by the functions_h3_default_if_invalid setting). Float64
Examples
Get exact edge length in meters
SELECT round(h3ExactEdgeLengthM(1310277011704381439), 6) AS exactEdgeLengthM┌─exactEdgeLengthM─┐
│ 195449.631634 │
└──────────────────┘h3ExactEdgeLengthRads
Introduced in: v22.2.0
Returns the exact edge length of the unidirectional edge represented by the input H3 in radians.
Syntax
h3ExactEdgeLengthRads(index)Arguments
index— Hexagon index number.UInt64
Returned value
Returns the exact length of the H3 edge in radians. Throws an exception if the input is not a valid directed edge (controlled by the functions_h3_default_if_invalid setting). Float64
Examples
Get exact edge length in radians
SELECT round(h3ExactEdgeLengthRads(1310277011704381439), 12) AS exactEdgeLengthRads┌─exactEdgeLengthRads─┐
│ 0.030677980119 │
└─────────────────────┘h3GetBaseCell
Introduced in: v20.3.0
Returns the base cell number of the H3 index.
Syntax
h3GetBaseCell(index)Arguments
index— Hexagon index number.UInt64
Returned value
Returns the hexagon base cell number. Throws an exception if the input is not a valid H3 cell (controlled by the functions_h3_default_if_invalid setting). UInt8
Examples
Get base cell number
SELECT h3GetBaseCell(612916788725809151) AS basecell┌─basecell─┐
│ 12 │
└──────────┘h3GetDestinationIndexFromUnidirectionalEdge
Introduced in: v22.6.0
Returns the destination hexagon index from the unidirectional edge H3.
Syntax
h3GetDestinationIndexFromUnidirectionalEdge(edge)Arguments
edge— Hexagon index number that represents a unidirectional edge.UInt64
Returned value
Returns the destination hexagon index from the unidirectional edge. Throws INCORRECT_DATA if the input is not a valid directed edge unless functions_h3_default_if_invalid = 1, in which case it returns 0. UInt64
Examples
Get destination index from a unidirectional edge
SELECT h3GetDestinationIndexFromUnidirectionalEdge(1248204388774707199) AS destination┌────────destination─┐
│ 599686043507097599 │
└────────────────────┘h3GetFaces
Introduced in: v21.11.0
Returns icosahedron faces intersected by a given H3 index.
Syntax
h3GetFaces(index)Arguments
index— Hexagon index number.UInt64
Returned value
Returns an array containing the indices (0-19) of the icosahedron faces that the H3 index intersects with. Array(UInt8)
Examples
Get icosahedron faces for an H3 index
SELECT h3GetFaces(599686042433355775) AS faces┌─faces─┐
│ [7] │
└───────┘h3GetIndexesFromUnidirectionalEdge
Introduced in: v22.6.0
Returns the origin and destination hexagon indexes from the given unidirectional edge H3Index.
Syntax
h3GetIndexesFromUnidirectionalEdge(edge)Arguments
edge— Hexagon index number that represents a unidirectional edge.UInt64
Returned value
Returns a tuple containing the origin and destination hexagon indices from the unidirectional edge. Throws INCORRECT_DATA if the input is not a valid directed edge unless functions_h3_default_if_invalid = 1, in which case it returns (0, 0). Tuple(UInt64, UInt64)
Examples
Get origin and destination indices from a unidirectional edge
SELECT h3GetIndexesFromUnidirectionalEdge(1248204388774707199) AS indexes┌─indexes─────────────────────────────────┐
│ (599686042433355775,599686043507097599) │
└─────────────────────────────────────────┘h3GetOriginIndexFromUnidirectionalEdge
Introduced in: v22.6.0
Returns the origin hexagon index from the unidirectional edge H3.
Syntax
h3GetOriginIndexFromUnidirectionalEdge(edge)Arguments
edge— Hexagon index number that represents a unidirectional edge.UInt64
Returned value
Returns the origin hexagon index from the unidirectional edge. Throws INCORRECT_DATA if the input is not a valid directed edge unless functions_h3_default_if_invalid = 1, in which case it returns 0. UInt64
Examples
Get origin index from a unidirectional edge
SELECT h3GetOriginIndexFromUnidirectionalEdge(1248204388774707199) AS origin┌─────────────origin─┐
│ 599686042433355775 │
└────────────────────┘h3GetPentagonIndexes
Introduced in: v22.6.0
Returns all the pentagon H3 indices at the specified resolution.
Syntax
h3GetPentagonIndexes(resolution)Arguments
resolution— Index resolution with range[0, 15].UInt8
Returned value
Returns an array of all pentagon H3 indices at the specified resolution. Array(UInt64)
Examples
Get all pentagon indices at resolution 3
SELECT h3GetPentagonIndexes(3) AS indexes┌─indexes───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ [590112357393367039,590464201114255359,590816044835143679,591308626044387327,591695654137364479,592012313486163967,592188235346608127,592504894695407615,592891922788384767,593384503997628415,593736347718516735,594088191439405055] │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘h3GetRes0Indexes
Introduced in: v22.6.0
Returns an array of all the resolution 0 H3 indices.
Syntax
h3GetRes0Indexes()Returned value
Returns an array of all resolution 0 H3 indices. Array(UInt64)
Examples
Get all resolution 0 H3 indices
SELECT h3GetRes0Indexes() AS indexes┌─indexes────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ [576495936675512319,576531121047601151,576566305419689983,576601489791778815,576636674163867647,576671858535956479,576707042908045311,576742227280134143,576777411652222975,576812596024311807,576847780396400639,576882964768489471,576918149140578303,576953333512667135,576988517884755967,577023702256844799,577058886628933631,577094071001022463,577129255373111295,577164439745200127,577199624117288959,577234808489377791,577269992861466623,577305177233555455,577340361605644287,577375545977733119,577410730349821951,577445914721910783,577481099093999615,577516283466088447,577551467838177279,577586652210266111,577621836582354943,577657020954443775,577692205326532607,577727389698621439,577762574070710271,577797758442799103,577832942814887935,577868127186976767,577903311559065599,577938495931154431,577973680303243263,578008864675332095,578044049047420927,578079233419509759,578114417791598591,578149602163687423,578184786535776255,578219970907865087,578255155279953919,578290339652042751,578325524024131583,578360708396220415,578395892768309247,578431077140398079,578466261512486911,578501445884575743,578536630256664575,578571814628753407,578606999000842239,578642183372931071,578677367745019903,578712552117108735,578747736489197567,578782920861286399,578818105233375231,578853289605464063,578888473977552895,578923658349641727,578958842721730559,578994027093819391,579029211465908223,579064395837997055,579099580210085887,579134764582174719,579169948954263551,579205133326352383,579240317698441215,579275502070530047,579310686442618879,579345870814707711,579381055186796543,579416239558885375,579451423930974207,579486608303063039,579521792675151871,579556977047240703,579592161419329535,579627345791418367,579662530163507199,579697714535596031,579732898907684863,579768083279773695,579803267651862527,579838452023951359,579873636396040191,579908820768129023,579944005140217855,579979189512306687,580014373884395519,580049558256484351,580084742628573183,580119927000662015,580155111372750847,580190295744839679,580225480116928511,580260664489017343,580295848861106175,580331033233195007,580366217605283839,580401401977372671,580436586349461503,580471770721550335,580506955093639167,580542139465727999,580577323837816831,580612508209905663,580647692581994495,580682876954083327,580718061326172159,580753245698260991] │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘h3GetResolution
Introduced in: v20.1.0
Returns the resolution of the H3 index.
Syntax
h3GetResolution(index)Arguments
index— Hexagon index number.UInt64
Returned value
Returns the resolution of the H3 index with range [0, 15]. Throws an exception if the input is not a valid H3 cell (controlled by the functions_h3_default_if_invalid setting). UInt8
Examples
Get resolution of an H3 index
SELECT h3GetResolution(617420388352917503) AS res┌─res─┐
│ 9 │
└─────┘h3GetUnidirectionalEdge
Introduced in: v22.6.0
Returns a unidirectional edge H3 index for two adjacent H3 cell indices (origin and destination).
Syntax
h3GetUnidirectionalEdge(origin, destination)Arguments
Returned value
Returns the H3 unidirectional edge index for adjacent cells, or 0 when both inputs are valid cells that do not form a directed edge. Throws an exception if either input is not a valid H3 cell (controlled by the functions_h3_default_if_invalid setting). UInt64
Examples
Basic usage
SELECT h3GetUnidirectionalEdge(599686042433355775, 599686043507097599)1248204388774707199h3GetUnidirectionalEdgeBoundary
Introduced in: v22.6.0
Returns the coordinates defining the unidirectional edge H3.
Syntax
h3GetUnidirectionalEdgeBoundary(index)Arguments
index— Hexagon index number that represents a unidirectional edge.UInt64
Returned value
Returns an array of (latitude, longitude) pairs defining a unidirectional edge. Throws INCORRECT_DATA if the input is not a valid directed edge unless functions_h3_default_if_invalid = 1, in which case it returns []. Array(Tuple(Float64, Float64))
Examples
Get boundary coordinates of a unidirectional edge
SELECT h3GetUnidirectionalEdgeBoundary(1248204388774707199) AS boundary┌─boundary─────────────────────────────────────────────────────────────────────┐
│ [(37.4201286776778,-122.03773496427027),(37.337556084353,-122.090428929044)] │
└──────────────────────────────────────────────────────────────────────────────┘h3GetUnidirectionalEdgesFromHexagon
Introduced in: v22.6.0
Provides all of the unidirectional edges from the provided H3Index.
Syntax
h3GetUnidirectionalEdgesFromHexagon(index)Arguments
index— Hexagon index number that represents a cell.UInt64
Returned value
Returns an array of H3 indexes representing each unidirectional edge. Throws an exception if the input is not a valid H3 cell (controlled by the functions_h3_default_if_invalid setting); returns an empty array only when that setting is enabled. Array(UInt64)
Examples
Get all edges for an H3 index
SELECT h3GetUnidirectionalEdgesFromHexagon(599686042433355775) AS edges┌─edges─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ [1248204388774707199,1320261982812635135,1392319576850563071,1464377170888491007,1536434764926418943,1608492358964346879] │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘h3HexAreaKm2
Introduced in: v22.1.0
Returns average hexagon area in square kilometers at the given H3 resolution.
Syntax
h3HexAreaKm2(resolution)Arguments
resolution— Index resolution with range[0, 15].UInt8
Returned value
Returns the average area of an H3 hexagon in square kilometers for the given resolution. Float64
Examples
Get hexagon area at resolution 13
SELECT h3HexAreaKm2(13) AS area┌───────────────────area─┐
│ 0.00004387026794728296 │
└────────────────────────┘h3HexAreaM2
Introduced in: v20.3.0
Returns average hexagon area in square meters at the given H3 resolution.
Syntax
h3HexAreaM2(resolution)Arguments
resolution— Index resolution with range[0, 15].UInt8
Returned value
Returns the average area of an H3 hexagon in square meters for the given resolution. Float64
Examples
Get hexagon area at resolution 13
SELECT h3HexAreaM2(13) AS area┌──────────────area─┐
│ 43.87026794728301 │
└───────────────────┘h3HexRing
Introduced in: v22.6.0
Returns the indexes of the hexagonal ring centered at the provided origin H3 and length k. The ring is hollow when k > 0.
Syntax
h3HexRing(index, k)Arguments
index— Hexagon index number that represents the origin.UInt64k— Distance from the origin (ring size).UInt16
Returned value
Returns an array of H3 indices forming a hexagonal ring around the origin, or 0 if a pentagonal distortion is encountered. Array(UInt64)
Examples
Get hexagonal ring of distance 1
SELECT h3HexRing(590080540275638271, toUInt16(1)) AS hexRing┌─hexRing─────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ [590080815153545215,590080471556161535,590080677714591743,590077585338138623,590077447899185151,590079509483487231] │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘h3IndexesAreNeighbors
Introduced in: v20.3.0
Returns whether or not the provided H3 indexes are neighbors.
Syntax
h3IndexesAreNeighbors(index1, index2)Arguments
Returned value
Returns 1 if the indexes are neighbors (sharing an edge), 0 otherwise. UInt8
Examples
Check if two H3 indexes are neighbors
SELECT h3IndexesAreNeighbors(617420388351344639, 617420388352655359) AS n┌─n─┐
│ 1 │
└───┘h3IsPentagon
Introduced in: v21.11.0
Returns whether an H3 index represents a pentagonal cell.
In the H3 grid system, most cells are hexagonal, but there are exactly 12 pentagonal cells at each resolution to account for the topological constraints of mapping a sphere to a grid.
Syntax
h3IsPentagon(index)Arguments
index— Hexagon index number.UInt64
Returned value
Returns 1 if the index represents a pentagonal cell, 0 otherwise. UInt8
Examples
Check if H3 index is a pentagon
SELECT h3IsPentagon(644721767722457330) AS pentagon┌─pentagon─┐
│ 0 │
└──────────┘h3IsResClassIII
Introduced in: v21.11.0
Returns whether an H3 index has a resolution with Class III orientation.
Class III resolutions have an odd-numbered resolution, while Class II resolutions are even-numbered.
Syntax
h3IsResClassIII(index)Arguments
index— Hexagon index number.UInt64
Returned value
Returns 1 if the index has a Class III resolution (odd-numbered), 0 otherwise. UInt8
Examples
Check if H3 index has Class III resolution
SELECT h3IsResClassIII(617420388352917503) AS res┌─res─┐
│ 1 │
└─────┘h3IsValid
Introduced in: v20.1.0
Verifies whether the number is a valid H3 index.
Syntax
h3IsValid(h3index)Arguments
h3index— Hexagon index number.UInt64
Returned value
Returns 1 if the number is a valid H3 index, 0 otherwise. UInt8
Examples
Check valid H3 index
SELECT h3IsValid(630814730351855103) AS isValid┌─isValid─┐
│ 1 │
└─────────┘Check invalid H3 index
SELECT h3IsValid(toUInt64(12345)) AS isValid┌─isValid─┐
│ 0 │
└─────────┘h3kRing
Introduced in: v20.1.0
Lists all the H3 hexagons in the radius of k from the given hexagon in random order.
Syntax
h3kRing(h3index, k)Arguments
Returned value
Returns an array of H3 indexes that are within k rings of the origin hexagon. Array(UInt64)
Examples
Get all hexagons within 1 ring of the origin
SELECT arrayJoin(h3kRing(644325529233966508, 1)) AS h3index┌────────────h3index─┐
│ 644325529233966508 │
│ 644325529233966497 │
│ 644325529233966510 │
│ 644325529233966504 │
│ 644325529233966509 │
│ 644325529233966355 │
│ 644325529233966354 │
└────────────────────┘h3Line
Introduced in: v22.6.0
Returns the line of H3 indices between the two provided indices.
Syntax
h3Line(start, end)Arguments
start— Hexagon index number that represents the starting point.UInt64end— Hexagon index number that represents the ending point.UInt64
Returned value
Returns an array of H3 indices representing the line between the start and end indices. Array(UInt64)
Examples
Get line of H3 indices between two points
SELECT h3Line(590080540275638271, 590103561300344831) AS indexes┌─indexes────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ [590080540275638271,590080471556161535,590080883873021951,590106516237844479,590104385934065663,590103630019821567,590103561300344831] │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘h3NumHexagons
Introduced in: v22.2.0
Returns the number of unique H3 indices at the given resolution.
Syntax
h3NumHexagons(resolution)Arguments
resolution— Index resolution with range[0, 15].UInt8
Returned value
Returns the number of unique H3 indices at the specified resolution. Int64
Examples
Get number of hexagons at resolution 3
SELECT h3NumHexagons(3) AS numHexagons┌─numHexagons─┐
│ 41162 │
└─────────────┘h3PointDistKm
Introduced in: v22.6.0
Returns the “great circle” or “haversine” distance between pairs of GeoCoord points (latitude/longitude) in kilometers.
Syntax
h3PointDistKm(lat1, lon1, lat2, lon2)Arguments
lat1— Latitude of point1 in degrees.Float64lon1— Longitude of point1 in degrees.Float64lat2— Latitude of point2 in degrees.Float64lon2— Longitude of point2 in degrees.Float64
Returned value
Returns the haversine or great circle distance in kilometers. Float64
Examples
Calculate distance between two points in kilometers
SELECT h3PointDistKm(-10.0, 0.0, 10.0, 0.0) AS h3PointDistKm┌──────h3PointDistKm─┐
│ 2223.9010395045884 │
└────────────────────┘h3PointDistM
Introduced in: v22.6.0
Returns the “great circle” or “haversine” distance between pairs of GeoCoord points (latitude/longitude) in meters.
Syntax
h3PointDistM(lat1, lon1, lat2, lon2)Arguments
lat1— Latitude of point1 in degrees.Float64lon1— Longitude of point1 in degrees.Float64lat2— Latitude of point2 in degrees.Float64lon2— Longitude of point2 in degrees.Float64
Returned value
Returns the haversine or great circle distance in meters. Float64
Examples
Calculate distance between two points in meters
SELECT h3PointDistM(-10.0, 0.0, 10.0, 0.0) AS h3PointDistM┌───────h3PointDistM─┐
│ 2223901.0395045886 │
└────────────────────┘h3PointDistRads
Introduced in: v22.6.0
Returns the “great circle” or “haversine” distance between pairs of GeoCoord points (latitude/longitude) in radians.
Syntax
h3PointDistRads(lat1, lon1, lat2, lon2)Arguments
lat1— Latitude of point1 in degrees.Float64lon1— Longitude of point1 in degrees.Float64lat2— Latitude of point2 in degrees.Float64lon2— Longitude of point2 in degrees.Float64
Returned value
Returns the haversine or great circle distance in radians. Float64
Examples
Calculate distance between two points in radians
SELECT h3PointDistRads(-10.0, 0.0, 10.0, 0.0) AS h3PointDistRads┌────h3PointDistRads─┐
│ 0.3490658503988659 │
└────────────────────┘h3PolygonToCells
Introduced in: v25.11.0
Returns the hexagons (at specified resolution) contained by the provided geometry, either ring or (multi-)polygon. Every vertex must be on the sphere: longitude in -180..180 and latitude in -90..90 degrees. The order of the returned cells is not guaranteed.
Syntax
h3PolygonToCells(geometry, resolution)h3PolygonToCellsWithContainment
Introduced in: v26.6.0
Returns the hexagons (at specified resolution) covering the provided geometry, using H3’s experimental algorithm with selectable containment mode. Flags: 0=CONTAINMENT_CENTER, 1=CONTAINMENT_FULL, 2=CONTAINMENT_OVERLAPPING, 3=CONTAINMENT_OVERLAPPING_BBOX. The flags argument is passed to the H3 API as UInt32; use integer literals (0..3) or toUInt32. Other native integer types are converted with an accurate cast. Every vertex must be on the sphere: longitude in -180..180 and latitude in -90..90 degrees. The order of the returned cells is not guaranteed. See H3 docs.
Syntax
h3PolygonToCellsWithContainment(geometry, resolution, flags)h3ToCenterChild
Introduced in: v22.2.0
Returns the center child (finer) H3 index contained by the given H3 index at the given resolution.
This function finds the center child of an H3 index at a specified finer resolution. The resolution must be greater than the resolution of the input index.
Syntax
h3ToCenterChild(index, resolution)Arguments
index— Parent H3 index.UInt64resolution— Resolution of the center child with range[0, 15].UInt8
Returned value
Returns the H3 index of the center child at the specified resolution. UInt64
Examples
Get center child at resolution 1
SELECT h3ToCenterChild(577023702256844799, 1) AS centerToChild┌──────centerToChild─┐
│ 581496515558637567 │
└────────────────────┘h3ToChildren
Introduced in: v20.3.0
Returns an array of child indexes for the given H3 index at the specified resolution.
Syntax
h3ToChildren(index, resolution)Arguments
index— Parent H3 index.UInt64resolution— Resolution of the child indexes with range[0, 15].UInt8
Returned value
Returns an array of child H3 indexes at the specified resolution. Array(UInt64)
Examples
Get child indexes at resolution 6
SELECT h3ToChildren(599405990164561919, 6) AS children┌─children───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ [603909588852408319,603909588986626047,603909589120843775,603909589255061503,603909589389279231,603909589523496959,603909589657714687] │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘h3ToGeo
Introduced in: v21.9.0
Returns the centroid latitude and longitude corresponding to the provided H3 index.
Syntax
h3ToGeo(h3Index)Arguments
h3Index— H3 index.UInt64
Returned value
Returns a tuple consisting of two values (lat, lon) where lat is latitude and lon is longitude. Tuple(Float64, Float64)
Examples
Get coordinates from H3 index
SELECT h3ToGeo(644325524701193974) AS coordinates┌─coordinates───────────────────────────┐
│ (55.71290243145667,37.79506616830249) │
└───────────────────────────────────────┘h3ToGeoBoundary
Introduced in: v21.11.0
Returns array of pairs (lat, lon), which corresponds to the boundary of the provided H3 index.
Syntax
h3ToGeoBoundary(h3Index)Arguments
h3Index— H3 index.UInt64
Returned value
Returns an array of coordinate pairs (lat, lon) that define the boundary of the H3 hexagon. Array(Tuple(Float64, Float64))
Examples
Get boundary coordinates for an H3 index
SELECT h3ToGeoBoundary(644325524701193974) AS coordinates┌─coordinates─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ [(55.71290022535552,37.79505811173474),(55.71289713485417,37.795065069971834),(55.712899340954834,37.79507312653982),(55.71290463755744,37.79507422487166),(55.71290772805917,37.79506726663345),(55.7129055219579,37.795059210064515)] │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘h3ToParent
Introduced in: v20.3.0
Returns the parent (coarser) H3 index containing the given H3 index at the specified resolution.
Syntax
h3ToParent(index, resolution)Arguments
index— Child H3 index.UInt64resolution— Resolution of the parent index with range[0, 15].UInt8
Returned value
Returns the parent H3 index at the specified resolution. UInt64
Examples
Get parent index at resolution 3
SELECT h3ToParent(599405990164561919, 3) AS parent┌─────────────parent─┐
│ 590398848891879423 │
└────────────────────┘h3ToString
Introduced in: v20.3.0
Converts the H3Index representation of the index to the string representation.
Syntax
h3ToString(index)Arguments
index— H3 index number.UInt64
Returned value
Returns the string representation of the H3 index. String
Examples
Convert H3 index to string
SELECT h3ToString(617420388352917503) AS h3_string┌─h3_string───────┐
│ 89184926cdbffff │
└─────────────────┘h3UnidirectionalEdgeIsValid
Introduced in: v22.6.0
Determines if the provided H3 is a valid unidirectional edge index.
Syntax
h3UnidirectionalEdgeIsValid(index)Arguments
index— Hexagon index number.UInt64
Returned value
Returns 1 if the H3 index is a valid unidirectional edge, 0 otherwise. UInt8
Examples
Check if an H3 index is a valid unidirectional edge
SELECT h3UnidirectionalEdgeIsValid(1248204388774707199) AS validOrNot┌─validOrNot─┐
│ 1 │
└────────────┘MGRSToGeo
Introduced in: v26.7.0
Decodes a Military Grid Reference System (MGRS) string into WGS84 geographic coordinates (longitude, latitude). This is the inverse of geoToMGRS.
The returned point is the centre of the referenced grid square, so the precision of the result matches the precision encoded in the string. Whitespace in the input is ignored and letters are case-insensitive.
Syntax
MGRSToGeo(mgrs)Arguments
mgrs— MGRS reference string to decode.StringorFixedString
Returned value
Returns a named tuple (longitude, latitude) in degrees. Tuple(Float64, Float64)
Examples
Basic usage
SELECT MGRSToGeo('31UDQ4825111935')(2.294495618908297,48.85822536113692)MVTBoundingBox
Introduced in: v26.7.0
Returns the geographic bounding box of the slippy-map tile identified by zoom, tile_x and tile_y as a tuple
(min_lon, min_lat, max_lon, max_lat) in degrees.
The bounding box is the companion of MVTEncodeGeom: use it in a WHERE clause to restrict rows to a tile while
filtering on the longitude/latitude columns directly (so a primary key or index on those columns can be used),
instead of recomputing the Web Mercator projection per row. The optional margin expands the box on every side by that
fraction of the tile size; set margin to buffer / extent to match the clip buffer of MVTEncodeGeom.
Syntax
MVTBoundingBox(zoom, tile_x, tile_y[, margin])Arguments
zoom— Slippy-map zoom level, in the range[0, 32].UInt8tile_x— Tile column index, in the range[0, 2^zoom - 1].UInt32tile_y— Tile row index, in the range[0, 2^zoom - 1].UInt32margin— Optional fraction of the tile size to expand the box on every side. Defaults to0.Float64
Returned value
Returns the tile bounding box as a tuple (min_lon, min_lat, max_lon, max_lat) in degrees. Tuple(Float64, Float64, Float64, Float64)
Examples
Bounding box of the whole world at zoom 0
SELECT MVTBoundingBox(0, 0, 0) AS bbox┌─bbox────────────────────────────────────────────┐
│ (-180,-85.05112877980659,180,85.05112877980659) │
└─────────────────────────────────────────────────┘MVTBoundingBoxMercator
Introduced in: v26.7.0
Returns the bounding box of the slippy-map tile identified by zoom, tile_x and tile_y in the full-UInt32 Web
Mercator coordinate space used internally by MVTEncodeGeom, as a tuple (min_x, min_y, max_x, max_y).
This is the Web Mercator counterpart of MVTBoundingBox, intended for tables that materialize Mercator coordinate columns
and index those instead of longitude/latitude. The y axis grows downward (north at the top), matching MVTEncodeGeom.
The optional margin expands the box on every side by that fraction of the tile size.
Syntax
MVTBoundingBoxMercator(zoom, tile_x, tile_y[, margin])Arguments
zoom— Slippy-map zoom level, in the range[0, 32].UInt8tile_x— Tile column index, in the range[0, 2^zoom - 1].UInt32tile_y— Tile row index, in the range[0, 2^zoom - 1].UInt32margin— Optional fraction of the tile size to expand the box on every side. Defaults to0.Float64
Returned value
Returns the tile bounding box as a tuple (min_x, min_y, max_x, max_y) in Web Mercator coordinates. Tuple(Float64, Float64, Float64, Float64)
Examples
Mercator bounding box of a tile
SELECT MVTBoundingBoxMercator(1, 0, 0) AS bbox┌─bbox────────────────────────┐
│ (0,0,2147483648,2147483648) │
└─────────────────────────────┘perimeterCartesian
Introduced in: v25.12.0
Returns the perimeter of the object.
Syntax
perimeterCartesian(object)Arguments
object— geometry objectVariant
Returned value
Returns the perimeter of the object. Float64
perimeterSpherical
Introduced in: v25.12.0
Returns the perimeter of the object.
Syntax
perimeterSpherical(object)Arguments
object— geometry objectVariant
Returned value
Returns the perimeter of the object. Float64
pointInEllipses
Introduced in: v1.1.0
Checks whether the point belongs to at least one of the ellipses. Coordinates are geometric in the Cartesian coordinate system.
Syntax
pointInEllipses(x, y, x₀, y₀, a₀, b₀,...,xₙ, yₙ, aₙ, bₙ)Arguments
x, y— Coordinates of a point on the plane.Float64xᵢ, yᵢ— Coordinates of the center of the i-th ellipsis.Float64aᵢ, bᵢ— Axes of the i-th ellipsis in units of x, y coordinates.Float64
Returned value
Returns 1 if the point is inside at least one of the ellipses, 0 otherwise UInt8
Examples
Basic usage
SELECT pointInEllipses(10., 10., 10., 9.1, 1., 0.9999)┌─pointInEllipses(10., 10., 10., 9.1, 1., 0.9999)─┐
│ 1 │
└─────────────────────────────────────────────────┘pointInPolygon
Introduced in: v1.1.0
Checks whether the point belongs to the polygon on the plane.
Syntax
pointInPolygon((x, y), [(a, b), (c, d) ...], ...)Arguments
(x, y)— Coordinates of a point on the plane.Tuple(Float64, Float64)orPoint[(a, b), (c, d) ...]— The polygon, either as an array of coordinate pairs or as a named polygon-shaped value. Vertices should be in clockwise or counterclockwise order. Minimum 3 vertices required.Array(Tuple(Float64, Float64))orRingorPolygonorMultiPolygonorGeometry...— Optional. Additional arguments for polygons with holes (each hole as a separate ring) or multipolygons (each component polygon as a separate argument). A wholeMultiPolygoncan only be passed as the sole polygon argument, not as one of several arguments. These additional arguments must be constant.Array(Tuple(Float64, Float64))orRingorPolygon
Returned value
Returns 1 if the point is inside the polygon, 0 if it is not. If the point is on the polygon boundary, the function may return either 0 or 1. UInt8
Examples
Basic usage with a simple polygon
SELECT pointInPolygon((3., 3.), [(6, 0), (8, 4), (5, 8), (0, 2)]) AS res┌─res─┐
│ 1 │
└─────┘Passing the polygon as a named geometric type from a table column
CREATE TABLE poly (id UInt32, shape Polygon) ENGINE = Memory;
INSERT INTO poly VALUES (1, [[(0, 0), (10, 0), (10, 10), (0, 10)], [(4, 4), (6, 4), (6, 6), (4, 6)]]);
SELECT id, pointInPolygon((2., 2.), shape) AS res FROM poly;┌─id─┬─res─┐
│ 1 │ 1 │
└────┴─────┘polygonsIntersectCartesian
Introduced in: v25.7.0
Returns true if the two Polygon or MultiPolygon intersect (share any common area or boundary).
Syntax
polygonsIntersectCartesian(polygon1, polygon2)Arguments
polygon1— A value of typePolygonorMultiPolygon. -polygon2— A value of typePolygonorMultiPolygon.
Returned value
Returns true (1) if the two polygons intersect. Bool.
Examples
Usage example
SELECT polygonsIntersectCartesian([[[(2., 2.), (2., 3.), (3., 3.), (3., 2.)]]], [[[(1., 1.), (1., 4.), (4., 4.), (4., 1.), (1., 1.)]]])┌─polygonsIntersectCartesian([[[(2., 2.), (2., 3.), (3., 3.), (3., 2.)]]], [[[(1., 1.), (1., 4.), (4., 4.), (4., 1.), (1., 1.)]]])─┐
│ 1 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘polygonsIntersectSpherical
Introduced in: v25.7.0
Returns true if the two Polygon or MultiPolygon intersect (share any common area or boundary).
Syntax
polygonsIntersectSpherical(polygon1, polygon2)Arguments
polygon1— A value of typePolygonorMultiPolygon. -polygon2— A value of typePolygonorMultiPolygon.
Returned value
Returns true (1) if the two polygons intersect (share any common area or boundary). Bool.
Examples
Usage example
SELECT polygonsIntersectSpherical([[[(2., 2.), (2., 3.), (3., 3.), (3., 2.)]]], [[[(1., 1.), (1., 4.), (4., 4.), (4., 1.), (1., 1.)]]])┌─polygonsIntersectSpherical([[[(2., 2.), (2., 3.), (3., 3.), (3., 2.)]]], [[[(1., 1.), (1., 4.), (4., 4.), (4., 1.), (1., 1.)]]])─┐
│ 1 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘readWKB
Introduced in: v25.12.0
Parses a Well-Known Binary (WKB) representation of a Geometry and returns it in the internal ClickHouse format.
Syntax
readWKB(wkb_string)Arguments
wkb_string— The input WKB string representing a Point geometry.String
Returned value
Returns a ClickHouse internal representation of the Geometry. Geo
Examples
Usage example
SELECT readWKB(unhex('010300000001000000050000000000000000000040000000000000000000000000000024400000000000000000000000000000244000000000000024400000000000000000000000000000244000000000000000400000000000000000'));[[(2,0),(10,0),(10,10),(0,10),(2,0)]]readWKT
Introduced in: v25.12.0
Parses a Well-Known Text (WKT) representation of Geometry and returns it in the internal ClickHouse format.
Syntax
readWKT(wkt_string)Arguments
wkt_string— The input WKT string representing a LineString geometry.String
Returned value
Returns a ClickHouse internal representation of the Geometry.
Examples
Usage example
SELECT readWKT('LINESTRING (1 1, 2 2, 3 3, 1 1)');┌─readWKT('LINESTRING (1 1, 2 2, 3 3, 1 1)')─┐
│ [(1,1),(2,2),(3,3),(1,1)] │
└────────────────────────────────────────────┘s2CapContains
Introduced in: v21.9.0
Determines if an S2 cap contains an S2 point. A cap represents a portion of the sphere that has been cut off by a plane. It is defined by a center point and a radius in degrees.
Syntax
s2CapContains(center, degrees, point)Arguments
center— S2 cell identifier of the cap center point.UInt64degrees— Radius of the cap in degrees.Float64point— S2 cell identifier of the point to test.UInt64
Returned value
Returns 1 if the cap contains the point and 0 otherwise. UInt8
Examples
Basic usage
SELECT s2CapContains(1157339245694594829, 1.0, 1157347770437378819)1s2CapUnion
Introduced in: v21.9.0
Returns the smallest cap that contains both of the input S2 caps. A cap represents a portion of the sphere that has been cut off by a plane, defined by a center point and a radius in degrees.
Syntax
s2CapUnion(center1, radius1, center2, radius2)Arguments
center1— S2 cell identifier of the first cap center.UInt64radius1— Radius of the first cap in degrees.Float64center2— S2 cell identifier of the second cap center.UInt64radius2— Radius of the second cap in degrees.Float64
Returned value
Returns a tuple (center, radius) representing the smallest cap containing both input caps. Tuple(UInt64, Float64)
Examples
Basic usage
SELECT s2CapUnion(1157339245694594829, 1.0, 1157347770437378819, 1.0)(1157337948443372697,1.0706880308631295)s2CellsIntersect
Introduced in: v21.9.0
Determines if two provided S2 cells intersect or not.
Syntax
s2CellsIntersect(s2index1, s2index2)Arguments
Returned value
Returns 1 if the cells intersect and 0 otherwise. UInt8
Examples
Basic usage
SELECT s2CellsIntersect(9926595209846587392, 9926594385212866560)1s2GetNeighbors
Introduced in: v21.9.0
Returns the S2 neighbor indices corresponding to the provided S2 cell index. Each cell in the S2 system is a quadrilateral bounded by four geodesics. Each cell has exactly 4 neighbors.
Syntax
s2GetNeighbors(s2index)Arguments
s2index— The S2 cell identifier.UInt64
Returned value
Returns an array of 4 neighbor S2 cell identifiers. Array(UInt64)
Examples
Basic usage
SELECT s2GetNeighbors(5765131099823669248)[5765131099830484992,5765131099821047808,5765131099823144960,5765131099824193536]s2RectAdd
Introduced in: v21.9.0
Expands an S2 latitude-longitude rectangle to include the given S2 point. The rectangle is represented by a pair of S2 cell identifiers for its low and high corners.
Syntax
s2RectAdd(s2RectLow, s2RectHigh, s2Point)Arguments
s2RectLow— S2 cell identifier of the low vertex of the rectangle.UInt64s2RectHigh— S2 cell identifier of the high vertex of the rectangle.UInt64s2Point— S2 cell identifier of the point to add.UInt64
Returned value
Returns a tuple (s2RectLow, s2RectHigh) representing the expanded rectangle. Tuple(UInt64, UInt64)
Examples
Basic usage
SELECT s2RectAdd(5765131099823669248, 5765131099823669248, 5765131099956887552)┌─s2RectAdd(5765131099823669248, 5765131099823669248, 5765131099956887552)─┐
│ (5765131100074425153,5765131099940616725) │
└──────────────────────────────────────────────────────────────────────────┘s2RectContains
Introduced in: v21.9.0
Determines if an S2 latitude-longitude rectangle contains the given S2 point. The rectangle is represented by a pair of S2 cell identifiers for its low and high corners.
Syntax
s2RectContains(s2RectLow, s2RectHigh, s2Point)Arguments
s2RectLow— S2 cell identifier of the low vertex of the rectangle.UInt64s2RectHigh— S2 cell identifier of the high vertex of the rectangle.UInt64s2Point— S2 cell identifier of the point to test.UInt64
Returned value
Returns 1 if the rectangle contains the point and 0 otherwise. UInt8
Examples
Basic usage
SELECT s2RectContains(5178914411069187297, 5177056748191934217, 5177222610104078385)1s2RectIntersection
Introduced in: v21.9.0
Returns the intersection of two S2 latitude-longitude rectangles. Each rectangle is represented by a pair of S2 cell identifiers for its low and high corners.
Syntax
s2RectIntersection(s2Rect1Low, s2Rect1High, s2Rect2Low, s2Rect2High)Arguments
s2Rect1Low— S2 cell identifier of the low vertex of the first rectangle.UInt64s2Rect1High— S2 cell identifier of the high vertex of the first rectangle.UInt64s2Rect2Low— S2 cell identifier of the low vertex of the second rectangle.UInt64s2Rect2High— S2 cell identifier of the high vertex of the second rectangle.UInt64
Returned value
Returns a tuple (s2RectLow, s2RectHigh) representing the intersection rectangle. Tuple(UInt64, UInt64)
Examples
Basic usage
SELECT s2RectIntersection(5178914411069187297, 5177056748191934217, 5179062030687166815, 5177056748191934217)(5178914411069187297,5177056748191934217)s2RectUnion
Introduced in: v21.9.0
Returns the smallest S2 latitude-longitude rectangle that contains the union of two input rectangles. Each rectangle is represented by a pair of S2 cell identifiers for its low and high corners.
Syntax
s2RectUnion(s2Rect1Low, s2Rect1High, s2Rect2Low, s2Rect2High)Arguments
s2Rect1Low— S2 cell identifier of the low vertex of the first rectangle.UInt64s2Rect1High— S2 cell identifier of the high vertex of the first rectangle.UInt64s2Rect2Low— S2 cell identifier of the low vertex of the second rectangle.UInt64s2Rect2High— S2 cell identifier of the high vertex of the second rectangle.UInt64
Returned value
Returns a tuple (s2RectLow, s2RectHigh) representing the union rectangle. Tuple(UInt64, UInt64)
Examples
Basic usage
SELECT s2RectUnion(5178914411069187297, 5177056748191934217, 5179062030687166815, 5177056748191934217)(5179062030687166815,5177056748191934217)s2ToGeo
Introduced in: v21.9.0
Returns coordinates (longitude, latitude) corresponding to the provided S2 point index. This is the inverse of geoToS2.
Syntax
s2ToGeo(s2index)Arguments
s2index— The S2 cell identifier.UInt64
Returned value
Returns a tuple (lon, lat) of Float64 values representing the longitude and latitude. Tuple(Float64, Float64)
Examples
Basic usage
SELECT s2ToGeo(4704772434919038107)(37.79506681471008,55.7129059052841)stringToH3
Introduced in: v20.4.0
Converts the string representation of an H3 index to the H3Index (UInt64) representation.
Syntax
stringToH3(index_str)Arguments
index_str— String representation of the H3 index.String
Returned value
Returns the H3 index number, or 0 if the input is not a valid H3 index. UInt64
Examples
Convert string to H3 index
SELECT stringToH3('89184926cc3ffff') AS index┌──────────────index─┐
│ 617420388351344639 │
└────────────────────┘svg
Introduced in: v21.4.0
Returns a string representation of a geometry in SVG format. The output SVG can be used directly in web pages to visualize geospatial data.
Syntax
svg(geometry[, style])Arguments
geometry— Geometry object (Point, MultiPoint, Ring, LineString, MultiLineString, Polygon, MultiPolygon).PointorMultiPointorRingorLineStringorMultiLineStringorPolygonorMultiPolygonstyle— Optional CSS style string to apply to the SVG element.String
Returned value
Returns the SVG representation of the geometry. String
Examples
Basic point
SELECT svg((0.0, 1.0))<circle cx="0" cy="1" r="5" style=""/>UTMToGeo
Introduced in: v26.7.0
Converts Universal Transverse Mercator (UTM) coordinates back to WGS84 geographic coordinates (longitude, latitude). This is the inverse of geoToUTM.
The fourth argument selects the hemisphere. It can be given either as an integer flag (1 for the northern hemisphere, 0 for the southern) or as the MGRS latitude band letter that geoToUTM returns, so a geoToUTM result round-trips through UTMToGeo directly.
Syntax
UTMToGeo(easting, northing, zone, is_north)Arguments
easting— Easting in metres (includes the 500000 m false easting).(U)Int*orFloat*northing— Northing in metres (includes the 10000000 m false northing on the southern hemisphere).(U)Int*orFloat*zone— UTM zone number. Range:[1, 60].(U)Int*is_north— Hemisphere. Either an integer flag (1for the northern hemisphere,0for the southern) or the MGRS latitude band letter returned bygeoToUTM('C'..'X'excluding'I'and'O', case-insensitive; band>= 'N'is the northern hemisphere).(U)Int*orStringorFixedString
Returned value
Returns a named tuple (longitude, latitude) in degrees. Tuple(Float64, Float64)
Examples
Basic usage
SELECT UTMToGeo(448251.6, 5411935.13, 31, 1)(2.2944970289079203,48.85822204127082)Round trip using the band letter from geoToUTM
WITH geoToUTM(4.89, 52.36) AS utm SELECT UTMToGeo(utm.easting, utm.northing, utm.zone, utm.band)(4.890000000320752,52.36000000243152)wkb
Introduced in: v25.7.0
Parses a Well-Known Binary (WKB) representation of a Point geometry and returns it in the internal ClickHouse format.
Syntax
wkb(geometry)Arguments
geometry— The input geometry type to convert into WKB.
Examples
first call
CREATE TABLE IF NOT EXISTS geom1 (a Point) ENGINE = Memory();
INSERT INTO geom1 VALUES((0, 0));
SELECT hex(wkb(a)) FROM geom1;┌─hex(wkb(a))────────────────────────────────┐
│ 010100000000000000000000000000000000000000 │
└────────────────────────────────────────────┘wkt
Introduced in: v21.4.0
Converts a ClickHouse geometry object to its Well-Known Text (WKT) representation.
Syntax
wkt(geometry)Arguments
geometry— Geometry object (Point, MultiPoint, Ring, LineString, MultiLineString, Polygon, MultiPolygon).PointorMultiPointorRingorLineStringorMultiLineStringorPolygonorMultiPolygon
Returned value
Returns the WKT string representation of the geometry. String
Examples
Basic point
SELECT wkt((0.0, 1.0))POINT(0 1)