Geospatial Functions

Databend ships with two complementary sets of geospatial capabilities: PostGIS-style geometry functions for building and analysing shapes, and H3 utilities for global hexagonal indexing. The tables below group the functions by task so you can quickly locate the right tool, similar to the layout used in the Snowflake documentation.

Constructors

Function	Description	Note	Example
ST_MAKEGEOMPOINT / ST_GEOM_POINT	Construct a Point geometry	GEOMETRY only	ST_MAKEGEOMPOINT(-122.35, 37.55) → POINT(-122.35 37.55)
ST_MAKEPOINT / ST_POINT	Construct a Point geography	GEOGRAPHY only	ST_MAKEPOINT(-122.35, 37.55) → POINT(-122.35 37.55)
ST_MAKELINE / ST_MAKE_LINE	Create a LineString from points		ST_MAKELINE(ST_MAKEGEOMPOINT(-122.35, 37.55), ST_MAKEGEOMPOINT(-122.40, 37.60)) → LINESTRING(-122.35 37.55, -122.40 37.60)
ST_MAKEPOLYGON	Create a Polygon from a closed LineString		ST_MAKEPOLYGON(ST_MAKELINE(...)) → POLYGON(...)
ST_POLYGON	Create a Polygon from coordinate rings	GEOMETRY only	ST_POLYGON(...) → POLYGON(...)

Conversion

Function	Description	Note	Example
ST_GEOMETRYFROMTEXT / ST_GEOMFROMTEXT	Convert WKT to geometry	GEOMETRY only	ST_GEOMETRYFROMTEXT('POINT(-122.35 37.55)') → POINT(-122.35 37.55)
ST_GEOMETRYFROMWKB / ST_GEOMFROMWKB	Convert WKB to geometry	GEOMETRY only	ST_GEOMETRYFROMWKB(...) → POINT(...)
ST_GEOMETRYFROMEWKT / ST_GEOMFROMEWKT	Convert EWKT to geometry	GEOMETRY only	ST_GEOMETRYFROMEWKT('SRID=4326;POINT(-122.35 37.55)') → POINT(-122.35 37.55)
ST_GEOMETRYFROMEWKB / ST_GEOMFROMEWKB	Convert EWKB to geometry	GEOMETRY only	ST_GEOMETRYFROMEWKB(...) → POINT(...)
ST_GEOGRAPHYFROMWKT / ST_GEOGFROMWKT	Convert WKT/EWKT to geography	GEOGRAPHY only	ST_GEOGRAPHYFROMWKT('POINT(-122.35 37.55)') → POINT(-122.35 37.55)
ST_GEOGRAPHYFROMWKB / ST_GEOGFROMWKB	Convert WKB/EWKB to geography	GEOGRAPHY only	ST_GEOGRAPHYFROMWKB(...) → POINT(...)
ST_GEOMFROMGEOHASH	Convert GeoHash to geometry	GEOMETRY only	ST_GEOMFROMGEOHASH('9q8yyk8') → POLYGON(...)
ST_GEOMPOINTFROMGEOHASH	Convert GeoHash to Point geometry	GEOMETRY only	ST_GEOMPOINTFROMGEOHASH('9q8yyk8') → POINT(...)
ST_GEOGFROMGEOHASH	Convert GeoHash to geography polygon	GEOGRAPHY only	ST_GEOGFROMGEOHASH('9q8yyk8') → POLYGON(...)
ST_GEOGPOINTFROMGEOHASH	Convert GeoHash to geography point	GEOGRAPHY only	ST_GEOGPOINTFROMGEOHASH('9q8yyk8') → POINT(...)
TO_GEOMETRY	Parse various formats into geometry	GEOMETRY only	TO_GEOMETRY('POINT(-122.35 37.55)') → POINT(-122.35 37.55)
TO_GEOGRAPHY / TRY_TO_GEOGRAPHY	Parse various formats into geography	GEOGRAPHY only	TO_GEOGRAPHY('POINT(-122.35 37.55)') → POINT(-122.35 37.55)

Output

Function	Description	Note	Example
ST_ASTEXT	Convert geometry to WKT		ST_ASTEXT(ST_MAKEGEOMPOINT(-122.35, 37.55)) → 'POINT(-122.35 37.55)'
ST_ASWKT	Convert geometry to WKT		ST_ASWKT(ST_MAKEGEOMPOINT(-122.35, 37.55)) → 'POINT(-122.35 37.55)'
ST_ASBINARY / ST_ASWKB	Convert geometry to WKB		ST_ASBINARY(ST_MAKEGEOMPOINT(-122.35, 37.55)) → WKB representation
ST_ASEWKT	Convert geometry to EWKT		ST_ASEWKT(ST_MAKEGEOMPOINT(-122.35, 37.55)) → 'SRID=4326;POINT(-122.35 37.55)'
ST_ASEWKB	Convert geometry to EWKB		ST_ASEWKB(ST_MAKEGEOMPOINT(-122.35, 37.55)) → EWKB representation
ST_ASGEOJSON	Convert geometry to GeoJSON		ST_ASGEOJSON(ST_MAKEGEOMPOINT(-122.35, 37.55)) → '{"type":"Point","coordinates":[-122.35,37.55]}'
ST_GEOHASH	Convert geometry to GeoHash		ST_GEOHASH(ST_MAKEGEOMPOINT(-122.35, 37.55), 7) → '9q8yyk8'
TO_STRING	Convert geometry to string		TO_STRING(ST_MAKEGEOMPOINT(-122.35, 37.55)) → 'POINT(-122.35 37.55)'

Accessors & Properties

Function	Description	Note	Example
ST_DIMENSION	Return the topological dimension		ST_DIMENSION(ST_MAKEGEOMPOINT(-122.35, 37.55)) → 0
ST_SRID	Return the SRID of a geometry		ST_SRID(ST_MAKEGEOMPOINT(-122.35, 37.55)) → 4326
ST_POINTN	Return a specific point from a LineString		ST_POINTN(ST_MAKELINE(...), 1) → POINT(-122.35 37.55)
ST_STARTPOINT	Return the first point in a LineString		ST_STARTPOINT(ST_MAKELINE(...)) → POINT(-122.35 37.55)
ST_ENDPOINT	Return the last point in a LineString		ST_ENDPOINT(ST_MAKELINE(...)) → POINT(-122.40 37.60)
ST_X / ST_Y	Return the X or Y coordinate of a Point		ST_X(ST_MAKEGEOMPOINT(-122.35, 37.55)) → -122.35
ST_XMIN / ST_XMAX	Return the min/max X coordinate		ST_XMIN(ST_MAKELINE(...)) → -122.40
ST_YMIN / ST_YMAX	Return the min/max Y coordinate		ST_YMAX(ST_MAKELINE(...)) → 37.60

Relationship and measurement

Function	Description	Note	Example
HAVERSINE	Compute great-circle distance between coordinates		HAVERSINE(37.55, -122.35, 37.60, -122.40) → 6.12
ST_AREA	Measure the area of geometry or geography object		ST_AREA(TO_GEOMETRY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))')) → 1.0
ST_CONTAINS	Test whether one geometry contains another	GEOMETRY only	ST_CONTAINS(ST_MAKEPOLYGON(...), ST_MAKEGEOMPOINT(...)) → TRUE
ST_CONVEXHULL	Compute the convex hull of a geometry	GEOMETRY only	ST_CONVEXHULL(TO_GEOMETRY('POLYGON((0 0,2 0,2 2,0 2,0 0))')) → POLYGON((0 0,2 0,2 2,0 2,0 0))
ST_NPOINTS	Count points in a geometry		ST_NPOINTS(ST_MAKELINE(...)) → 2
ST_NUMPOINTS	Count points in a geometry	GEOMETRY only	ST_NUMPOINTS(ST_MAKELINE(...)) → 2
ST_INTERSECTS	Test whether two geometries intersect	GEOMETRY only	ST_INTERSECTS(TO_GEOMETRY('LINESTRING(0 0, 2 2)'), TO_GEOMETRY('LINESTRING(0 2, 2 0)')) → TRUE
ST_DISJOINT	Test whether two geometries are disjoint	GEOMETRY only	ST_DISJOINT(TO_GEOMETRY('POINT(3 3)'), TO_GEOMETRY('POLYGON((0 0,2 0,2 2,0 2,0 0))')) → TRUE
ST_WITHIN	Test whether one geometry is within another	GEOMETRY only	ST_WITHIN(TO_GEOMETRY('POINT(1 1)'), TO_GEOMETRY('POLYGON((0 0,2 0,2 2,0 2,0 0))')) → TRUE
ST_EQUALS	Test whether two geometries are spatially equal	GEOMETRY only	ST_EQUALS(TO_GEOMETRY('POINT(1 1)'), TO_GEOMETRY('POINT(1 1)')) → TRUE
ST_LENGTH	Measure the length of a LineString		ST_LENGTH(ST_MAKELINE(...)) → 5.57
ST_DISTANCE	Measure the distance between geometries		ST_DISTANCE(ST_MAKEGEOMPOINT(-122.35, 37.55), ST_MAKEGEOMPOINT(-122.40, 37.60)) → 5.57

Transformation

Function	Description	Note	Example
ST_HILBERT	Encode geometry or geography into a Hilbert curve index		ST_HILBERT(TO_GEOMETRY('POINT(0.5 0.5)'), [0, 0, 1, 1]) → 715827882
ST_SETSRID	Assign an SRID to a geometry	GEOMETRY only	ST_SETSRID(ST_MAKEGEOMPOINT(-122.35, 37.55), 3857) → POINT(-122.35 37.55)
ST_TRANSFORM	Transform geometry to a new SRID	GEOMETRY only	ST_TRANSFORM(ST_MAKEGEOMPOINT(-122.35, 37.55), 3857) → POINT(-13618288.8 4552395.0)

H3 Indexing & Conversion

Function	Description	Example
GEO_TO_H3	Convert longitude/latitude to an H3 index	GEO_TO_H3(37.7950, 55.7129, 15) → 644325524701193974
H3_TO_GEO	Convert an H3 index to longitude/latitude	H3_TO_GEO(644325524701193974) → [37.7950, 55.7129]
H3_TO_STRING	Convert an H3 index to its string form	H3_TO_STRING(644325524701193974) → '8f2830828052d25'
STRING_TO_H3	Convert an H3 string to an index	STRING_TO_H3('8f2830828052d25') → 644325524701193974
GEOHASH_ENCODE	Encode longitude/latitude to GeoHash	GEOHASH_ENCODE(37.7950, 55.7129, 12) → 'ucfv0nzpt3s7'
GEOHASH_DECODE	Decode a GeoHash to longitude/latitude	GEOHASH_DECODE('ucfv0nzpt3s7') → [37.7950, 55.7129]

H3 Cell Properties

Function	Description	Example
H3_GET_RESOLUTION	Return the resolution of an H3 index	H3_GET_RESOLUTION(644325524701193974) → 15
H3_GET_BASE_CELL	Return the base cell number	H3_GET_BASE_CELL(644325524701193974) → 14
H3_IS_VALID	Check whether an H3 index is valid	H3_IS_VALID(644325524701193974) → TRUE
H3_IS_PENTAGON	Check whether an H3 index is a pentagon	H3_IS_PENTAGON(644325524701193974) → FALSE
H3_IS_RES_CLASS_III	Check whether an H3 index is class III	H3_IS_RES_CLASS_III(644325524701193974) → FALSE
H3_GET_FACES	Return intersecting icosahedron faces	H3_GET_FACES(644325524701193974) → [7]
H3_TO_PARENT	Return the parent index at a lower resolution	H3_TO_PARENT(644325524701193974, 10) → 622236721289822207
H3_TO_CHILDREN	Return child indexes at a higher resolution	H3_TO_CHILDREN(622236721289822207, 11) → [...]
H3_TO_CENTER_CHILD	Return the center child for a resolution	H3_TO_CENTER_CHILD(622236721289822207, 11) → 625561602857582591
H3_CELL_AREA_M2	Return the area of a cell in square meters	H3_CELL_AREA_M2(644325524701193974) → 0.8953
H3_CELL_AREA_RADS2	Return the area of a cell in square radians	H3_CELL_AREA_RADS2(644325524701193974) → 2.2e-14
H3_HEX_AREA_KM2	Return the average hexagon area in km²	H3_HEX_AREA_KM2(10) → 0.0152
H3_HEX_AREA_M2	Return the average hexagon area in m²	H3_HEX_AREA_M2(10) → 15200
H3_TO_GEO_BOUNDARY	Return the boundary of a cell	H3_TO_GEO_BOUNDARY(644325524701193974) → [[lon1,lat1], ...]
H3_NUM_HEXAGONS	Return the number of hexagons at a resolution	H3_NUM_HEXAGONS(2) → 5882
GEO_DISTANCE	Approximate distance in meters using WGS84	GEO_DISTANCE(0, 0, 0, 0) → 0
GREAT_CIRCLE_DISTANCE	Great-circle distance in meters	GREAT_CIRCLE_DISTANCE(0, 0, 0, 0) → 0
GREAT_CIRCLE_ANGLE	Great-circle central angle in degrees	GREAT_CIRCLE_ANGLE(0, 0, 45, 0) → 45
POINT_IN_POLYGON	Check if a point lies inside a polygon	POINT_IN_POLYGON([lon, lat], [[p1_lon, p1_lat], ...]) → TRUE
POINT_IN_ELLIPSES	Check if a point lies inside any ellipse	POINT_IN_ELLIPSES(10, 10, 10, 9.1, 1, 0.9999) → 1

H3 Neighborhoods

Function	Description	Example
H3_DISTANCE	Return the grid distance between two indexes	H3_DISTANCE(599119489002373119, 599119491149856767) → 1
H3_INDEXES_ARE_NEIGHBORS	Test whether two indexes are neighbors	H3_INDEXES_ARE_NEIGHBORS(599119489002373119, 599119491149856767) → TRUE
H3_K_RING	Return all indexes within k distance	H3_K_RING(599119489002373119, 1) → [599119489002373119, ...]
H3_HEX_RING	Return indexes exactly k steps away	H3_HEX_RING(599119489002373119, 1) → [599119491149856767, ...]
H3_LINE	Return indexes along a path	H3_LINE(from_h3, to_h3) → [from_h3, ..., to_h3]

H3 Edge Operations

Function	Description	Example
H3_GET_UNIDIRECTIONAL_EDGE	Return the edge between two adjacent cells	H3_GET_UNIDIRECTIONAL_EDGE(from_h3, to_h3) → edge_index
H3_UNIDIRECTIONAL_EDGE_IS_VALID	Check whether an edge index is valid	H3_UNIDIRECTIONAL_EDGE_IS_VALID(edge_index) → TRUE
H3_GET_ORIGIN_INDEX_FROM_UNIDIRECTIONAL_EDGE	Return the origin cell from an edge	H3_GET_ORIGIN_INDEX_FROM_UNIDIRECTIONAL_EDGE(edge_index) → from_h3
H3_GET_DESTINATION_INDEX_FROM_UNIDIRECTIONAL_EDGE	Return the destination cell from an edge	H3_GET_DESTINATION_INDEX_FROM_UNIDIRECTIONAL_EDGE(edge_index) → to_h3
H3_GET_INDEXES_FROM_UNIDIRECTIONAL_EDGE	Return both cells for an edge	H3_GET_INDEXES_FROM_UNIDIRECTIONAL_EDGE(edge_index) → [from_h3, to_h3]
H3_GET_UNIDIRECTIONAL_EDGES_FROM_HEXAGON	List edges originating from a cell	H3_GET_UNIDIRECTIONAL_EDGES_FROM_HEXAGON(h3_index) → [edge1, edge2, ...]
H3_GET_UNIDIRECTIONAL_EDGE_BOUNDARY	Return the boundary of an edge	H3_GET_UNIDIRECTIONAL_EDGE_BOUNDARY(edge_index) → [[lon1,lat1], [lon2,lat2]]

H3 Measurements & Angles

Function	Description	Example
H3_EDGE_LENGTH_KM	Return the average edge length in kilometres	H3_EDGE_LENGTH_KM(10) → 0.065
H3_EDGE_LENGTH_M	Return the average edge length in metres	H3_EDGE_LENGTH_M(10) → 65.91
H3_EXACT_EDGE_LENGTH_KM	Return the exact edge length in kilometres	H3_EXACT_EDGE_LENGTH_KM(edge_index) → 0.066
H3_EXACT_EDGE_LENGTH_M	Return the exact edge length in metres	H3_EXACT_EDGE_LENGTH_M(edge_index) → 66.12
H3_EXACT_EDGE_LENGTH_RADS	Return the exact edge length in radians	H3_EXACT_EDGE_LENGTH_RADS(edge_index) → 0.00001
H3_EDGE_ANGLE	Return the angle in radians between two edges	H3_EDGE_ANGLE(edge1, edge2) → 1.047