Geo
Geo functions reference.
MGRSToGeo
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)Introduced in version 26.8.0.
MVTBoundingBox
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) │
└─────────────────────────────────────────────────┘Introduced in version 26.7.0.
MVTBoundingBoxMercator
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) │
└─────────────────────────────┘Introduced in version 26.7.0.
UTMToGeo
Converts Universal Transverse Mercator (UTM) coordinates back to WGS84 geographic coordinates (longitude, latitude). This is the inverse of geoToUTM.
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:1for the northern hemisphere,0for the southern.(U)Int*
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)Introduced in version 26.8.0.
areaCartesian
Returns the area of the object.
Syntax
areaCartesian(object)Arguments
object— geometry objectGeometry
Returned value
Returns the area of the object. Float64
Introduced in version 25.12.0.
areaSpherical
Returns the area of the object.
Syntax
areaSpherical(object)Arguments
object— geometry objectGeometry
Returned value
Returns the area of the object. Float64
Introduced in version 25.12.0.
geoDistance
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.73 │
└─────────────┘Introduced in version 1.1.0.
geoToH3
Returns H3 point index for the given latitude, longitude, and resolution.
:::note
In RawTree v25.4 or older, geoToH3() arguments are in the order (lon, lat). As per RawTree v25.5, the input values are ordered (lat, lon).
The previous behavior can be restored using setting geotoh3_argument_order = 'lon_lat'.
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 │
└────────────────────┘Introduced in version 20.1.0.
geoToMGRS
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)31UDQ482119Introduced in version 26.8.0.
geoToS2
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)4704772434919038107Introduced in version 21.9.0.
geoToUTM
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')Introduced in version 26.8.0.
geohashDecode
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) │
└─────────────────────────────────┘Introduced in version 20.1.0.
geohashEncode
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 │
└──────────────┘Introduced in version 20.1.0.
geohashesInBox
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.
:::note
All coordinate parameters must be of the same type: either Float32 or Float64.
:::
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'] │
└─────────────────────────────────────────────┘Introduced in version 20.1.0.
geometryIntersectCartesian
Returns true if two geometries intersect (share any common point, line or area).
Unlike polygonsIntersectCartesian, it accepts any geometry data type
(Point, 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(⋯)─┐
│ 1 │
└───────────────────────────────┘Introduced in version 26.7.0.
geometryIntersectSpherical
Returns true if two geometries intersect (share any common point, line or area).
Unlike polygonsIntersectSpherical, it accepts any geometry data type
(Point, 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(⋯)─┐
│ 1 │
└───────────────────────────────┘Introduced in version 26.7.0.
greatCircleAngle
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─┐
│ 45 │
└───────┘Introduced in version 1.1.0.
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— 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 │
└─────────────────────┘Introduced in version 1.1.0.
h3CellAreaM2
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.9233 │
└────────────────────┘Introduced in version 22.1.0.
h3CellAreaRads2
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.10116268528089567 │
└─────────────────────┘Introduced in version 22.1.0.
h3Distance
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 │
└──────────┘Introduced in version 22.6.0.
h3EdgeAngle
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┌───────h3EdgeAngle(10)─┐
│ 0.0005927224846720883 │
└───────────────────────┘Introduced in version 20.1.0.
h3EdgeLengthKm
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.000509713 │
└──────────────┘Introduced in version 20.1.0.
h3EdgeLengthM
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.509713273 │
└─────────────┘Introduced in version 20.1.0.
h3ExactEdgeLengthKm
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 h3ExactEdgeLengthKm(1310277011704381439) AS exactEdgeLengthKm┌──exactEdgeLengthKm─┐
│ 195.44963163407317 │
└────────────────────┘Introduced in version 22.2.0.
h3ExactEdgeLengthM
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 h3ExactEdgeLengthM(1310277011704381439) AS exactEdgeLengthM┌───exactEdgeLengthM─┐
│ 195449.63163407316 │
└────────────────────┘Introduced in version 22.2.0.
h3ExactEdgeLengthRads
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 h3ExactEdgeLengthRads(1310277011704381439) AS exactEdgeLengthRads┌──exactEdgeLengthRads─┐
│ 0.030677980118976447 │
└──────────────────────┘Introduced in version 22.2.0.
h3GetBaseCell
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 │
└──────────┘Introduced in version 20.3.0.
h3GetDestinationIndexFromUnidirectionalEdge
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 an exception if the input is not a valid directed edge (controlled by the functions_h3_default_if_invalid setting). UInt64
Examples
Get destination index from a unidirectional edge
SELECT h3GetDestinationIndexFromUnidirectionalEdge(1248204388774707197) AS destination┌────────destination─┐
│ 599686043507097597 │
└────────────────────┘Introduced in version 22.6.0.
h3GetFaces
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] │
└───────┘Introduced in version 21.11.0.
h3GetIndexesFromUnidirectionalEdge
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 an exception if the input is not a valid directed edge (controlled by the functions_h3_default_if_invalid setting). Tuple(UInt64, UInt64)
Examples
Get origin and destination indices from a unidirectional edge
SELECT h3GetIndexesFromUnidirectionalEdge(1248204388774707199) AS indexes┌─indexes─────────────────────────────────┐
│ (599686042433355775,599686043507097599) │
└─────────────────────────────────────────┘Introduced in version 22.6.0.
h3GetOriginIndexFromUnidirectionalEdge
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 an exception if the input is not a valid directed edge (controlled by the functions_h3_default_if_invalid setting). UInt64
Examples
Get origin index from a unidirectional edge
SELECT h3GetOriginIndexFromUnidirectionalEdge(1248204388774707197) AS origin┌─────────────origin─┐
│ 599686042433355773 │
└────────────────────┘Introduced in version 22.6.0.
h3GetPentagonIndexes
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,...] │
└────────────────────────────────────────────────────────────────┘Introduced in version 22.6.0.
h3GetRes0Indexes
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,...] │
└─────────────────────────────────────────────┘Introduced in version 22.6.0.
h3GetResolution
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 │
└─────┘Introduced in version 20.1.0.
h3GetUnidirectionalEdge
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. UInt64
Examples
Basic usage
SELECT h3GetUnidirectionalEdge(599686042433355775, 599686030622195711)1248204388774707199Introduced in version 22.6.0.
h3GetUnidirectionalEdgeBoundary
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 (longitude, latitude) pairs defining a unidirectional edge. Throws an exception if the input is not a valid directed edge (controlled by the functions_h3_default_if_invalid setting). Array(Float64, Float64)
Examples
Get boundary coordinates of a unidirectional edge
SELECT h3GetUnidirectionalEdgeBoundary(1248204388774707199) AS boundary┌─boundary────────────────────────────────────────────────────────────────────────┐
│ [(37.42012867767779,-122.03773496427027),(37.33755608435299,-122.090428929044)] │
└─────────────────────────────────────────────────────────────────────────────────┘Introduced in version 22.6.0.
h3GetUnidirectionalEdgesFromHexagon
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. Array(UInt64)
Examples
Get all edges for an H3 index
SELECT h3GetUnidirectionalEdgesFromHexagon(599686042433355775) AS edges┌─edges─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ [1248204388774707199,1320261982812635135,1392319576850563071,1464377170888491007,1536434764926418943,1608492358964346879] │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘Introduced in version 22.6.0.
h3HexAreaKm2
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.0000439 │
└───────────┘Introduced in version 22.1.0.
h3HexAreaM2
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.9 │
└──────┘Introduced in version 20.3.0.
h3HexRing
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] │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘Introduced in version 22.6.0.
h3IndexesAreNeighbors
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 │
└───┘Introduced in version 20.3.0.
h3IsPentagon
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 │
└──────────┘Introduced in version 21.11.0.
h3IsResClassIII
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 │
└─────┘Introduced in version 21.11.0.
h3IsValid
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(12345) AS isValid┌─isValid─┐
│ 0 │
└─────────┘Introduced in version 20.1.0.
h3Line
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] │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘Introduced in version 22.6.0.
h3NumHexagons
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 │
└─────────────┘Introduced in version 22.2.0.
h3PointDistKm
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.901039504589 │
└───────────────────┘Introduced in version 22.6.0.
h3PointDistM
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.039504589 │
└───────────────────┘Introduced in version 22.6.0.
h3PointDistRads
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 │
└────────────────────┘Introduced in version 22.6.0.
h3PolygonToCells
Returns the hexagons (at specified resolution) contained by the provided geometry, either ring or (multi-)polygon.
Syntax
h3PolygonToCells(geometry, resolution)Introduced in version 25.11.0.
h3PolygonToCellsWithContainment
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. See H3 docs.
Syntax
h3PolygonToCellsWithContainment(geometry, resolution, flags)Introduced in version 26.6.0.
h3ToCenterChild
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 │
└────────────────────┘Introduced in version 22.2.0.
h3ToChildren
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] │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘Introduced in version 20.3.0.
h3ToGeo
Returns the centroid latitude and longitude corresponding to the provided H3 index.
:::note
In RawTree v24.12 or older, h3ToGeo() accepts arguments in the order (lon, lat). As per RawTree v25.1, the returned values are ordered (lat, lon).
The previous behavior can be restored using setting h3togeo_lon_lat_result_order = true.
:::
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.71290243145668,37.79506616830252) │
└───────────────────────────────────────┘Introduced in version 21.9.0.
h3ToGeoBoundary
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┌─h3ToGeoBoundary(599686042433355775)────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ [(37.2713558667319,-121.91508032705622),(37.353926450852256,-121.8622232890249),(37.42834118609435,-121.92354999630156),(37.42012867767779,-122.03773496427027),(37.33755608435299,-122.090428929044),(37.26319797461824,-122.02910130919001)] │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘Introduced in version 21.11.0.
h3ToParent
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 │
└────────────────────┘Introduced in version 20.3.0.
h3ToString
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 │
└─────────────────┘Introduced in version 20.3.0.
h3UnidirectionalEdgeIsValid
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 │
└────────────┘Introduced in version 22.6.0.
h3kRing
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 │
└────────────────────┘Introduced in version 20.1.0.
perimeterCartesian
Returns the perimeter of the object.
Syntax
perimeterCartesian(object)Arguments
object— geometry objectVariant
Returned value
Returns the perimeter of the object. Float64
Introduced in version 25.12.0.
perimeterSpherical
Returns the perimeter of the object.
Syntax
perimeterSpherical(object)Arguments
object— geometry objectVariant
Returned value
Returns the perimeter of the object. Float64
Introduced in version 25.12.0.
pointInEllipses
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 │
└─────────────────────────────────────────────────┘Introduced in version 1.1.0.
pointInPolygon
Checks whether the point belongs to the polygon on the plane.
:::note
- You can set
validate_polygons = 0to bypass geometry validation. pointInPolygonassumes every polygon is well-formed. If the input is self-intersecting, has mis-ordered rings, or overlapping edges, results become unreliable—especially for points that sit exactly on an edge, a vertex, or inside a self-intersection where the notion of "inside" vs. "outside" is undefined.- The polygon-shaped types (
Ring,Polygon,MultiPolygon, andGeometry) may be passed either as constants or as regular (non-constant) table columns. When the polygon is provided across several separate arguments (an outer ring followed by holes, or several polygons of a multipolygon), all of those arguments must be constant. :::
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 │
└────┴─────┘Introduced in version 1.1.0.
polygonsIntersectCartesian
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()─┐
│ 1 │
└──────────────────────────────┘Introduced in version 25.7.0.
polygonsIntersectSpherical
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()─┐
│ 1 │
└──────────────────────────────┘Introduced in version 25.7.0.
readWKB
Parses a Well-Known Binary (WKB) representation of a Geometry and returns it in the internal RawTree format.
Syntax
readWKB(wkb_string)Arguments
wkb_string— The input WKB string representing a Point geometry.String
Returned value
Returns a RawTree internal representation of the Geometry. Geo
Examples
Usage example
SELECT readWKB(unhex('010300000001000000050000000000000000000040000000000000000000000000000024400000000000000000000000000000244000000000000024400000000000000000000000000000244000000000000000400000000000000000'));[[(2,0),(10,0),(10,10),(0,10),(2,0)]]Introduced in version 25.12.0.
readWKT
Parses a Well-Known Text (WKT) representation of Geometry and returns it in the internal RawTree format.
Syntax
readWKT(wkt_string)Arguments
wkt_string— The input WKT string representing a LineString geometry.String
Returned value
Returns a RawTree 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)] │
└────────────────────────────────────────────┘Introduced in version 25.12.0.
s2CapContains
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)1Introduced in version 21.9.0.
s2CapUnion
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)(1157339245694594829,1.0049841409301968)Introduced in version 21.9.0.
s2CellsIntersect
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)1Introduced in version 21.9.0.
s2GetNeighbors
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)[5765131099698741248, 5765131099950653440, 5765131100074868736, 5765131100088901632]Introduced in version 21.9.0.
s2RectAdd
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)Introduced in version 21.9.0.
s2RectContains
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(5765131099823669248, 5765131099956887552, 5765131099880128512)1Introduced in version 21.9.0.
s2RectIntersection
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(5765131099823669248, 5765131099956887552, 5765131099880128512, 5765131100088901632)Introduced in version 21.9.0.
s2RectUnion
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(5765131099823669248, 5765131099956887552, 5765131100074868736, 5765131100088901632)Introduced in version 21.9.0.
s2ToGeo
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)Introduced in version 21.9.0.
stringToH3
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 │
└────────────────────┘Introduced in version 20.4.0.
svg
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, Ring, Polygon, MultiPolygon).PointorRingorPolygonorMultiPolygonstyle— 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"/>Introduced in version 21.4.0.
wkb
Parses a Well-Known Binary (WKB) representation of a Point geometry and returns it in the internal RawTree 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))─-----------------------------------------┐
│ 010100000000000000000000000000000000003440 │
└──────────────────────────────────────────────────────┘Introduced in version 25.7.0.
wkt
Converts a RawTree geometry object to its Well-Known Text (WKT) representation.
Syntax
wkt(geometry)Arguments
geometry— Geometry object (Point, Ring, Polygon, MultiPolygon).PointorRingorPolygonorMultiPolygon
Returned value
Returns the WKT string representation of the geometry. String
Examples
Basic point
SELECT wkt((0.0, 1.0))POINT (0 1)Introduced in version 21.4.0.