Geometry Functions

This page provides a comprehensive overview of Geometry functions in Databend, organized by functionality for easy reference.

Geometry Creation Functions

Function	Description	Example
ST_MAKEGEOMPOINT / ST_GEOM_POINT	Constructs a Point geometry	`ST_MAKEGEOMPOINT(-122.35, 37.55)` → `POINT(-122.35 37.55)`
ST_MAKELINE / ST_MAKE_LINE	Creates 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	Creates a Polygon from a LineString	`ST_MAKEPOLYGON(ST_MAKELINE(...))` → `POLYGON(...)`
ST_POLYGON	Creates a Polygon	`ST_POLYGON(...)` → `POLYGON(...)`

Function	Description	Example
ST_GEOMETRYFROMTEXT / ST_GEOMFROMTEXT	Converts WKT to geometry	`ST_GEOMETRYFROMTEXT('POINT(-122.35 37.55)')` → `POINT(-122.35 37.55)`
ST_GEOMETRYFROMWKB / ST_GEOMFROMWKB	Converts WKB to geometry	`ST_GEOMETRYFROMWKB(...)` → `POINT(...)`
ST_GEOMETRYFROMEWKT / ST_GEOMFROMEWKT	Converts EWKT to geometry	`ST_GEOMETRYFROMEWKT('SRID=4326;POINT(-122.35 37.55)')` → `POINT(-122.35 37.55)`
ST_GEOMETRYFROMEWKB / ST_GEOMFROMEWKB	Converts EWKB to geometry	`ST_GEOMETRYFROMEWKB(...)` → `POINT(...)`
ST_GEOMFROMGEOHASH	Converts GeoHash to geometry	`ST_GEOMFROMGEOHASH('9q8yyk8')` → `POLYGON(...)`
ST_GEOMPOINTFROMGEOHASH	Converts GeoHash to Point	`ST_GEOMPOINTFROMGEOHASH('9q8yyk8')` → `POINT(...)`
TO_GEOMETRY	Converts various formats to geometry	`TO_GEOMETRY('POINT(-122.35 37.55)')` → `POINT(-122.35 37.55)`

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

Function	Description	Example
ST_DIMENSION	Returns the dimension of a geometry	`ST_DIMENSION(ST_MAKEGEOMPOINT(-122.35, 37.55))` → `0`
ST_SRID	Returns the SRID of a geometry	`ST_SRID(ST_MAKEGEOMPOINT(-122.35, 37.55))` → `4326`
ST_NPOINTS / ST_NUMPOINTS	Returns the number of points in a geometry	`ST_NPOINTS(ST_MAKELINE(...))` → `2`
ST_X	Returns the X coordinate of a Point	`ST_X(ST_MAKEGEOMPOINT(-122.35, 37.55))` → `-122.35`
ST_Y	Returns the Y coordinate of a Point	`ST_Y(ST_MAKEGEOMPOINT(-122.35, 37.55))` → `37.55`
ST_XMIN	Returns the minimum X coordinate	`ST_XMIN(ST_MAKELINE(...))` → `-122.40`
ST_XMAX	Returns the maximum X coordinate	`ST_XMAX(ST_MAKELINE(...))` → `-122.35`
ST_YMIN	Returns the minimum Y coordinate	`ST_YMIN(ST_MAKELINE(...))` → `37.55`
ST_YMAX	Returns the maximum Y coordinate	`ST_YMAX(ST_MAKELINE(...))` → `37.60`
ST_LENGTH	Returns the length of a LineString	`ST_LENGTH(ST_MAKELINE(...))` → `5.57`

Function	Description	Example
ST_POINTN	Returns a specific Point from a LineString	`ST_POINTN(ST_MAKELINE(...), 1)` → `POINT(-122.35 37.55)`
ST_STARTPOINT	Returns the first Point of a LineString	`ST_STARTPOINT(ST_MAKELINE(...))` → `POINT(-122.35 37.55)`
ST_ENDPOINT	Returns the last Point of a LineString	`ST_ENDPOINT(ST_MAKELINE(...))` → `POINT(-122.40 37.60)`

Function	Description	Example
ST_DISTANCE	Returns the distance between two geometries	`ST_DISTANCE(ST_MAKEGEOMPOINT(-122.35, 37.55), ST_MAKEGEOMPOINT(-122.40, 37.60))` → `5.57`
HAVERSINE	Returns the great-circle distance between two points	`HAVERSINE(37.55, -122.35, 37.60, -122.40)` → `6.12`
ST_CONTAINS	Checks if one geometry contains another	`ST_CONTAINS(ST_MAKEPOLYGON(...), ST_MAKEGEOMPOINT(...))` → `TRUE`
ST_TRANSFORM	Transforms geometry from one SRID to another	`ST_TRANSFORM(ST_MAKEGEOMPOINT(-122.35, 37.55), 3857)` → `POINT(-13618288.8 4552395.0)`
ST_SETSRID	Sets the SRID of a geometry	`ST_SETSRID(ST_MAKEGEOMPOINT(-122.35, 37.55), 3857)` → `POINT(-122.35 37.55)`