Geospatial/Geometry Functions¶

Tip

Use ST_ISVALID to determine if a geometry object is valid. The functions below work best with valid geometry objects.
Use the REMOVE_NULLABLE function to remove any nullable column types that could result from calculating a derived column (e.g., as in Projections) using one of the functions below.

Enhanced Performance Scalar Functions¶

The functions below all compare x and y coordinates to geometry objects (or vice versa), thus increasing their performance in queries. Each of these functions have a geometry-to-geometry version listed in the next section.

Function	Description
`STXY_CONTAINS(geom, x, y)`	Returns `1` (true) if `geom` contains the `x` and `y` coordinate, e.g. lies in the interior of `geom`. The coordinate cannot be on the boundary and also be contained because `geom` does not contain its boundary
`STXY_CONTAINSPROPERLY(geom, x, y)`	Returns `1` (true) if the `x` and `y` coordinate intersects the interior of `geom` but not the boundary (or exterior) because `geom` does not contain its boundary but does contain itself
`STXY_COVEREDBY(x, y, geom)`	Returns `1` (true) if the `x` and `y` coordinate is covered by `geom`
`STXY_COVERS(geom, x, y)`	Returns `1` (true) if `geom` covers the `x` and `y` coordinate
`STXY_DISJOINT(x, y, geom)`	Returns `1` (true) if the given `x` and `y` coordinate and the geometry `geom` do not spatially intersect.
`STXY_DISTANCE(x, y, geom[, solution])`	Calculates the minimum distance between the given `x` and `y` coordinate and `geom` using the specified solution type. Solution types available: `0` (default) - Euclidean; returns 2-D Euclidean distance `1` - Haversine; returns minimum sphere distance in meters `2` - Vincenty; returns minimum spheroid distance in meters, more accurate than Haversine but slower performance Note: If the `x` and `y` coordinate and `geom` intersect (verify using `ST_INTERSECTS`), the distance will always be `0`.
`STXY_DWITHIN(x, y, geom, distance[, solution])`	Returns `1` (true) if the `x` and `y` coordinate is within the specified `distance` from `geom` using the specified solution type. Solution types available: `0` (default) - Euclidean; uses degrees to calculate distance `1` - Sphere; uses meters to calculate sphere distance `2` - Spheroid; uses meters to calculate spheroid distance
`STXY_ENVDWITHIN(x, y, geom, distance[, solution])`	Returns `1` (true) if the `x` and `y` coordinate is within the specified `distance` from the bounding box of `geom` using the specified solution type. Solution types available: `0` (default) - Euclidean; uses degrees to calculate distance `1` - Sphere; uses meters to calculate distance
`STXY_ENVINTERSECTS(x, y, geom)`	Returns `1` (true) if the bounding box of the given geometry `geom` intersects the `x` and `y` coordinate.
`STXY_INTERSECTION(x, y, geom)`	Returns the shared portion between the `x` and `y` coordinate and the given geometry `geom`, i.e. the point itself.
`STXY_INTERSECTS(x, y, geom)`	Returns `1` (true) if the `x` and `y` coordinate and `geom` intersect in 2-D.
`STXY_TOUCHES(x, y, geom)`	Returns `1` (true) if the `x` and `y` coordinate and geometry `geom` have at least one point in common but their interiors do not intersect. If `geom` is a GEOMETRYCOLLECTION, a `0` is returned regardless if the point and geometry touch
`STXY_WITHIN(x, y, geom)`	Returns `1` (true) if the `x` and `y` coordinate is completely inside the `geom` geometry i.e., not on the boundary

Scalar Functions¶

Function	Description
`DIST(x1, y1, x2, y2)`	Computes the Euclidean distance (in degrees), i.e. `SQRT( (x1-x2)(x1-x2) + (y1-y2)(y1-y2) )`.
`GEODIST(lon1, lat1, lon2, lat2)`	Computes the geographic great-circle distance (in meters) between two lat/lon points.
`GEOHASH_DECODE_LATITUDE(geohash)`	Decodes a given `geohash` and returns the latitude value for the given hash string. Supports a maximum geohash character length of 16.
`GEOHASH_DECODE_LONGITUDE(geohash)`	Decodes a given `geohash` and returns the longitude value for the given hash string. Supports a maximum geohash character length of 16.
`GEOHASH_ENCODE(lat, lon, precision)`	Encodes a given coordinate pair and returns a hash string with a given `precision`.
`ST_ADDPOINT(linestring, point, position)`	Adds a the given `point` geometry to the given `linestring` geometry at the specified `position`, which is a 0-based index.
`ST_ALMOSTEQUALS(geom1, geom2, decimal)`	Returns `1` (true) if given geometries, `geom1` and `geom2`, are almost spatially equal within the given amount of `decimal` scale. Note that geometries will still be considered equal if the decimal scale for the geometries is within a half order of magnitude of each other, e.g, if `decimal` is set to 2, then POINT(63.4 123.45) and POINT(63.4 123.454) are equal, but POINT(63.4 123.45) and POINT(63.4 123.459) are not equal. The geometry types must match to be considered equal.
`ST_AREA(geom[, solution])`	Returns the area of the given geometry `geom` if it is a POLYGON or MULTIPOLYGON using the specified solution type. Returns `0` if the input geometry type is (MULTI)POINT or (MULTI)LINESTRING. Solution types available: `0` (default) - 2D Euclidean area `1` - curved surface area on a sphere in square meters `2` - curved surface area on a spheroid in square meters
`ST_AZIMUTH(geom1, geom2)`	Returns the azimuth in radians defined by the segment between two POINTs, `geom1` and `geom2`. Returns a `null` if the input geometry type is MULTIPOINT, (MULTI)LINESTRING, or (MULTI)POLYGON.
`ST_BOUNDARY(geom)`	Returns the closure of the combinatorial boundary of a given geometry `geom`. Returns an empty geometry if `geom` is an empty geometry. Returns a `null` if `geom` is a GEOMETRYCOLLECTION
`ST_BOUNDINGDIAGONAL(geom)`	Returns the diagonal of the given geometry's (`geom`) bounding box.
`ST_BUFFER(geom, radius[, style[, solution]])`	Returns a geometry that represents all points whose distance from the given geometry `geom` is less than or equal to the given distance `radius`. The `radius` units can be specified by the `solution` type (default is in degrees) and the `radius` is created in the provided `style`. The `style` options are specified as a list of blank-separated key-value pairs, e.g., `'quad_segs=8 endcap=round'`. If an empty `style` list (`''`) is provided, the default settings will be used. The `style` parameter must be specified to provide a `solution` type. Available `style` options: `quad_segs` -- the number of segments used to approximate a quarter circle (default is `8`) `endcap` -- the endcap style of the buffer (default is `round`); options are `round`, `flat` (or `butt`), and `square` `join` -- the join style of the buffer (default is `round`); options are `round`, `mitre` (or `miter`), and `bevel` `mitre_limit` -- the mitre ratio limit expressed as a floating point number (`miter_limit` is also acceptable) Available `solution` types: `0` (default) - 2D Euclidean radius distance in degrees `1` - curved surface radius distance on a sphere in meters `2` - curved surface radius distance on a spheroid in meters Tip To create a 5-meter buffer around `geom` using the default styles: `ST_BUFFER(geom, 5, '', 1)`. To create a 5-foot (converting feet to meters) buffer around `geom` using the following styles: `ST_BUFFER(geom, 5*0.3048,'quad_segs=4 endcap=flat', 1)`
`ST_CENTROID(geom)`	Calculates the center of the given geometry `geom` as a POINT. For (MULTI)POINTs, the center is calculated as the average of the input coordinates. For (MULTI)LINESTRINGs, the center is calculated as the weighted length of each given LINESTRING. For (MULTI)POLYGONs, the center is calculated as the weighted area of each given POLYGON. If `geom` is an empty geometry, an empty GEOMETRYCOLLECTION is returned
`ST_CLIP(geom1, geom2)`	Returns the geometry shared between given geometries `geom1` and `geom2`
`ST_CLOSESTPOINT(geom1, geom2[, solution])`	Calculates the 2-D `POINT` in `geom1` that is closest to `geom2` using the specified solution type. If `geom1` or `geom2` is empty, a `null` is returned. Solution types available: `0` (default) - Euclidean; calculates the closest point using 2-D Euclidean distance `1` - Haversine; calculates the closest point using sphere distance in meters `2` - Vincenty; returns minimum spheroid distance in meters, more accurate than Haversine but slower performance
`ST_COLLECT(geom1, geom2)`	Returns a MULTI* or GEOMETRYCOLLECTION comprising `geom1` and `geom2`. If `geom1` and `geom2` are the same, singular geometry type, a MULTI* is returned, e.g., if `geom1` and `geom2` are both POINTs (empty or no), a MULTIPOINT is returned. If `geom1` and `geom2` are neither the same type nor singular geometries, a GEOMETRYCOLLECTION is returned.
`ST_COLLECTIONEXTRACT(collection, type)`	Returns only the specified `type` from the given geometry `collection`. Type is a number that maps to the following: `1` = `POINT` `2` = `LINESTRING` `3` = `POLYGON`
`ST_COLLECTIONHOMOGENIZE(collection)`	Returns the simplest form of the given `collection`, e.g., a collection with a single POINT will be returned as `POINT(x y)`, and a collection with multiple individual points will be returned as a MULTIPOINT.
`ST_CONCAVEHULL(geom, target_percent[, allow_holes])`	Returns a potentially concave geometry that encloses all geometries found in the given `geom` set. Use `target_percent` (values between 0 and 1) to determine the percent of area of a convex hull the concave hull will attempt to fill; `1` will return the same geometry as an `ST_CONVEXHULL` operation. Set `allow_holes` to `1` (true) to allow holes in the resulting geometry; default value is `0` (false). Note that `allow_holes` is independent of the area of `target_percent`.
`ST_CONTAINS(geom1, geom2)`	Returns `1` (true) if no points of `geom2` lie in the exterior of `geom1` and at least one point of `geom2` lies in the interior of `geom1`. Note that `geom1` does not contain its boundary but does contain itself.
`ST_CONTAINSPROPERLY(geom1, geom2)`	Returns `1` (true) if `geom2` intersects the interior of `geom1` but not the boundary (or exterior). Note that `geom1` does not contain its boundary but does contain itself.
`ST_CONVEXHULL(geom)`	Returns the minimum convex geometry that encloses all geometries in the given `geom` set.
`ST_COORDDIM(geom)`	Returns the coordinate dimension of the given `geom`, e.g., a geometry with `x`, `y`, and `z` coordinates would return `3`.
`ST_COVEREDBY(geom1, geom2)`	Returns `1` (true) if no point in `geom1` is outside `geom2`.
`ST_COVERS(geom1, geom2)`	Returns `1` (true) if no point in `geom2` is outside `geom1`.
`ST_CROSSES(geom1, geom2)`	Returns `1` (true) if the given geometries, `geom1` and `geom2`, spatially cross, meaning some but not all interior points in common. If `geom1` and/or `geom2` are a GEOMETRYCOLLECTION, a `0` is returned regardless if the two geometries cross
`ST_DIFFERENCE(geom1, geom2)`	Returns a geometry that represents the part of `geom1` that does not intersect with `geom2`.
`ST_DIMENSION(geom)`	Returns the dimension of the given geometry `geom`, which is less than or equal to the coordinate dimension. If `geom` is a single geometry, a `0` is for `POINT`, a `1` is for `LINESTRING`, and a `2` is for `POLYGON`. If `geom` is a collection, it will return the largest dimension from the collection. If `geom` is empty, `0` is returned.
`ST_DISJOINT(geom1, geom2)`	Returns `1` (true) if the given geometries, `geom1` and `geom2`, do not spatially intersect.
`ST_DISTANCE(geom1, geom2[, solution])`	Calculates the minimum distance between the given geometries, `geom1` and `geom2`, using the specified solution type. Solution types available: `0` (default) - Euclidean; returns 2-D Euclidean distance `1` - Haversine; returns minimum sphere distance in meters `2` - Vincenty; returns minimum spheroid distance in meters, more accurate than Haversine but slower performance Note: If `geom1` and `geom2` intersect (verify using `ST_INTERSECTS`), the distance will always be `0`.
`ST_DISTANCEPOINTS(x1, y1, x2, y2[, solution])`	Calculates the minimum distance between the given points, `x1, y1` and `x2, y2`, using the specified solution type. Solution types available: `0` (default) - Euclidean; returns 2-D Euclidean distance `1` - Haversine; returns minimum sphere distance in meters `2` - Vincenty; returns minimum spheroid distance in meters, more accurate than Haversine but slower performance
`ST_DFULLYWITHIN(geom1, geom2, distance[, solution])`	Returns `1` (true) if the maximum distance between geometries `geom1` and `geom2` is less than or equal to the specified `distance` of each other using the specified solution type. If `geom1` or `geom2` is `null`, `0` (false) is returned. Solution types available: `0` (default) - Euclidean; uses degrees to calculate distance `1` - Sphere; uses meters to calculate distance `2` - Spheroid; uses meters to calculate distance, more accurate than sphere but slower performance
`ST_DWITHIN(geom1, geom2, distance[, solution])`	Returns `1` (true) if the minimum distance between geometries `geom1` and `geom2` is within the specified `distance` of each other using the specified solution type. Solution types available: `0` (default) - Euclidean; uses degrees to calculate distance `1` - Sphere; uses meters to calculate distance `2` - Spheroid; uses meters to calculate distance, more accurate than sphere but slower performance
`ST_ELLIPSE(centerx, centery, height, width)`	Returns an ellipse using the following values: `centerx` -- the x coordinate or longitude used to center the ellipse `centery` -- the y coordinate or latitude used to center the ellipse `height` -- the height of the ellipse (in degrees) `width` -- the width of the ellipse (in degrees)
`ST_ENDPOINT(geom)`	Returns the last point of the given `geom` as a POINT if it's a LINESTRING. If `geom` is not a a LINESTRING, `null` is returned.
`ST_ENVDWITHIN(geom1, geom2, distance[, solution])`	Returns `1` (true) if `geom1` is within the specified `distance` of the bounding box of `geom2` using the specified solution type. Solution types available: `0` (default) - Euclidean; uses degrees to calculate distance `1` - Sphere; uses meters to calculate distance
`ST_ENVELOPE(geom)`	Returns the bounding box of a given geometry `geom`.
`ST_ENVINTERSECTS(geom1, geom2)`	Returns `1` (true) if the bounding box of the given geometries, `geom1` and `geom2`, intersect.
`ST_EQUALS(geom1, geom2)`	Returns `1` (true) if the given geometries, `geom1` and `geom2`, are spatially equal. Note that order does not matter.
`ST_EQUALSEXACT(geom1, geom2, tolerance)`	Returns `1` (true) if the given geometries, `geom1` and `geom2`, are almost spatially equal within some given `tolerance`. If the values within the given geometries are within the `tolerance` value of each other, they're considered equal, e.g., if `tolerance` is 2, POINT(1 1) and POINT(1 3) are considered equal, but POINT(1 1) and POINT(1 3.1) are not. Note that the geometry types have to match for them to be considered equal.
`ST_ERASE(geom1, geom2)`	Returns the result of erasing a portion of `geom1` equal to the size of `geom2`.
`ST_EXPAND(geom, units)`	Returns the bounding box expanded in all directions by the given `units` of the given `geom`. The expansion can also be defined for separate directions by providing separate parameters for each direction, e.g., `ST_EXPAND(geom, unitsx, unitsy, unitsz, unitsm)`.
`ST_EXPANDBYRATE(geom, rate)`	Returns the bounding box expanded by a given `rate` (a ratio of width and height) for the given geometry `geom`. The `rate` must be between 0 and 1.
`ST_EXTERIORRING(geom)`	Returns a LINESTRING representing the exterior ring of the given POLYGON `geom`
`ST_FORCE2D(geom)`	Returns the 2-dimensional version (e.g., X and Y coordinates) of `geom`, the provided geometry or set of geometries (e.g., via GEOMETRYCOLLECTION or WKT column name).
`ST_FORCE3D(geom[, z])`	Returns the 3-dimensional version (e.g., X, Y, and Z coordinates) of `geom`, a provided geometry or set of geometries (e.g., via GEOMETRYCOLLECTION or WKT column name), using `z` as the geometry's new z-value. The provided z-values can also be derived from a numeric column. If no `z` is provided, a `0` will be applied. Note If a WKT column is provided for `geom` and a numeric column is provided for `z`, the z values will be matched to the provided geometries by row in the source table. If a singular geometry is provided for `geom` and a column is provided for `z`, three-dimensional versions of the provided geometry will be returned for each z value found in the provided `z` column. If columns are provided for both `geom` and `z` and nulls are present in either column, the row containing null values will be skipped in the results.
`ST_GENERATEPOINTS(geom, num)`	Creates a MULTIPOINT containing a number `num` of randomly generated points within the boundary of `geom`.
`ST_GEOHASH(geom, precision)`	Returns a hash string representation of the given geometry `geom` with specified `precision` (the length of the geohash string). The longer the `precision`, the more precise the hash is. By default, `precision` is set to `20`; the max for `precision` is `32`. Returns `null` if `geom` is an empty geometry. Note The value returned will not be a geohash of the exact geometry but a geohash of the centroid of the given geometry
`ST_GEOMETRYN(geom, index)`	Returns the `index` geometry back from the given `geom` geometry. The `index` starts from 1 to the number of geometry in `geom`.
`ST_GEOMETRYTYPE(geom)`	Returns the type of geometry from the given `geom`.
`ST_GEOMETRYTYPEID(geom)`	Returns the type ID of from `geom`. Type and ID mappings: POINT = 0 LINESTRING = 1 POLYGON = 3 MULTIPOINT = 4 MULTILINESTRING = 5 MULTIPOLYGON = 6 GEOMETRYCOLLECTION = 7
`ST_GEOMFROMGEOHASH(geohash, precision)`	Returns a POLYGON boundary box using the given `geohash` with a precision set by the integer `precision`. If `precision` is specified, the function will use as many characters in the hash equal to `precision` to create the geometry. If no `precision` is specified, the full length of the `geohash` is used.
`ST_GEOMFROMTEXT(wkt)`	Returns a geometry from the given Well-Known text representation `wkt`. Note that this function is only compatible with constants
`ST_HEXGRID(xmin, ymin, xmax, ymax, cell_side[, limit])`	Creates a MULTIPOLYGON containing a grid of hexagons between given minimum and maximum points of a bounding box. The minimum point cannot be greater than or equal to the maximum point. The size (in meters) of the individual hexagons' sides is determined by `cell_side`. The `cell_side` cannot be greater than the width or height of the bounding box. The maximum number of cells that can be produced is determined by `limit`, a positive integer. Supported values for `limit`: `-1` - No limit to the number of cells generated (effectively limited by system memory) `0` (default) - 100 million cells `<n>` - Custom limit of `n` cells If the custom limit request specifies more cells (based on the bounding box and the `cell_side`) than the system limit, a `null` is returned.
`ST_INTERIORRINGN(geom, n)`	Returns the `n`-th interior LINESTRING ring of the POLYGON `geom`. If `geom` is not a POLYGON or the given `n` is out of range, a `null` is returned. The index begins at 1
`ST_INTERSECTION(geom1, geom2)`	Returns the shared portion between given geometries `geom1` and `geom2`
`ST_INTERSECTS(geom1, geom2)`	Returns `1` (true) if the given geometries, `geom1` and `geom2`, intersect in 2-D
`ST_ISCLOSED(geom)`	Returns `1` (true) if the given geometry's (`geom`) start and end points coincide
`ST_ISCOLLECTION(geom)`	Returns `1` (true) if `geom` is a collection, e.g., GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING, etc.
`ST_ISEMPTY(geom)`	Returns `1` (true) if `geom` is empty
`ST_ISRING(geom)`	Returns `1` (true) if LINESTRING `geom` is both closed (per `ST_ISCLOSED`) and "simple" (per `ST_ISSIMPLE`). Returns `0` if `geom` is not a LINESTRING
`ST_ISSIMPLE(geom)`	Returns `1` (true) if `geom` has no anomalous geometric points, e.g., self-intersection or self-tangency
`ST_ISVALID(geom)`	Returns `1` (true) if `geom` (typically a [MULTI]POLYGON) is well formed. A POLYGON is valid if its rings do not cross and its boundary intersects only at POINTs (not along a line). The POLYGON must also not have dangling LINESTRINGs. A MULTIPOLYGON is valid if all of its elements are also valid and the interior rings of those elements do not intersect. Each element's boundaries may touch but only at POINTs (not along a line)
`ST_LENGTH(geom[, solution])`	Returns the length of the geometry if it is a LINESTRING or MULTILINESTRING. Returns `0` if another type of geometry, e.g., POINT, MULTIPOINT, etc. GEOMETRYCOLLECTIONs are also supported but the aforementioned type limitation still applies; the collection will be recursively searched for LINESTRINGs and MULTILINESTRINGs and the summation of all supported geometry types is returned (unsupported types are ignored). Solution types available: `0` (default) - 2D Euclidean length `1` - length on a sphere in meters `2` - length on a spheroid in meters
`ST_LINEFROMMULTIPOINT(geom)`	Creates a LINESTRING from `geom` if it is a MULTIPOINT. Returns `null` if `geom` is not a MULTIPOINT
`ST_LINEINTERPOLATEPOINT(geom, fraction)`	Returns a POINT that represents the specified `fraction` of the LINESTRING `geom`. If `geom` is either empty or not a LINESTRING, `null` is returned
`ST_LINELOCATEPOINT(linestring, point)`	Returns the location of the closest point in the given `linestring` to the given `point` as a value between `0` and `1`. The return value is a fraction of the total `linestring` length.
`ST_LINEMERGE(geom)`	Returns a LINESTRING or MULTILINESTRING from a given `geom`. If `geom` is a MULTILINESTRING comprising LINESTRINGs with shared endpoints, a contiguous LINESTRING is returned. If `geom` is a LINESTRING or a MULTILINESTRING comprising LINESTRINGS without shared endpoints, `geom` is returned If `geom` is an empty (MULTI)LINESTRING or a (MULTI)POINT or (MULTI)POLYGON, an empty GEOMETRYCOLLECTION is returned.
`ST_LINESUBSTRING(geom, start_fraction, end_fraction)`	Returns the fraction of a given `geom` LINESTRING where `start_fraction` and `end_fraction` are between `0` and `1`. For example, given `LINESTRING(1 1, 2 2, 3 3)` a `start_fraction` of `0` and an `end_fraction` of `0.25` would yield the first quarter of the given LINESTRING, or `LINESTRING(1 1, 1.5 1.5)`. Returns `null` if `start_fraction` is greater than `end_fraction`. Returns `null` if input geometry is (MULTI)POINT, MULTILINESTRING, or (MULTI)POLYGON. Returns `null` if `start_fraction` and/or `end_fraction` are less than `0` or more than `1`.
`ST_LONGESTLINE(geom1, geom2[, solution])`	Returns the LINESTRING that represents the longest line of points between the two geometries. If multiple longest lines are found, only the first line found is returned. If `geom1` or `geom2` is empty, `null` is returned. Solution types available: `0` (default) - Euclidean; uses degrees to calculate the longest line `1` - Sphere; uses meters to calculate the longest line `2` - Spheroid; uses meters to calculate the longest line, more accurate than sphere but slower performance
`ST_MAKEENVELOPE(xmin, ymin, xmax, ymax)`	Creates a rectangular POLYGON from the given min and max parameters
`ST_MAKELINE(geom[, geom2])`	Creates a LINESTRING from `geom` if it is a MULTIPOINT. If `geom` is a POINT, there must be at least one other POINT to construct a LINESTRING. If `geom` is a LINESTRING, it must have at least two points. Returns `null` if `geom` is not a POINT, MULTIPOINT, or LINESTRING Note This function can be rather costly in terms of performance
`ST_MAKEPOINT(x, y)`	Creates a POINT at the given coordinate Note This function can be rather costly in terms of performance
`ST_MAKEPOLYGON(geom)`	Creates a POLYGON from `geom`. Inputs must be closed LINESTRINGs Note This function can be rather costly in terms of performance
`ST_MAKETRIANGLE2D(x1, y1, x2, y2, x3, y3)`	Creates a closed 2-D POLYGON with three vertices
`ST_MAKETRIANGLE3D(x1, y1, z1, x2, y2, z2,` `x3, y3, z3)`	Creates a closed 3-D POLYGON with three vertices
`ST_MAXDISTANCE(geom1, geom2[, solution])`	Returns the maximum distance between the given `geom1` and `geom2` geometries using the specifed solution type. If `geom1` or `geom2` is empty, `null` is returned. Solution types available: `0` (default) - returns maximum 2-D Euclidean distance `1` - Sphere; returns maximum distance in meters `2` - Spheroid; returns maximum distance in meters, more accurate than sphere but slower performance
`ST_MAXX(geom)`	Returns the maximum x coordinate of a bounding box for the given `geom` geometry. This function works for 2-D and 3-D geometries.
`ST_MAXY(geom)`	Returns the maximum y coordinate of a bounding box for the given `geom` geometry. This function works for 2-D and 3-D geometries.
`ST_MAXZ(geom)`	Returns the maximum z coordinate of a bounding box for the given `geom` geometry. This function works for 2-D and 3-D geometries.
`ST_MINX(geom)`	Returns the minimum x coordinate of a bounding box for the given `geom` geometry. This function works for 2-D and 3-D geometries.
`ST_MINY(geom)`	Returns the minimum y coordinate of a bounding box for the given `geom` geometry. This function works for 2-D and 3-D geometries.
`ST_MINZ(geom)`	Returns the minimum z coordinate of a bounding box for the given `geom` geometry. This function works for 2-D and 3-D geometries.
`ST_MULTI(geom)`	Returns `geom` as a MULTI- geometry, e.g., a POINT would return a MULTIPOINT.
`ST_MULTIPLERINGBUFFERS(geom, distance, outside)`	Creates multiple buffers at specified `distance` around the given `geom` geometry. Multiple distances are specified as comma-separated values in an array, e.g., `[10,20,30]`. Valid values for `outside` are: `FULL` -- indicates that buffers will overlap or cover the given `geom` geometry. This is the default. `OUTSIDE_ONLY` -- indicates that buffers will be rings around the given `geom` geometry.
`ST_NEAR(geom1, geom2)`	Returns the portion of `geom2` that is closest to `geom1`. If `geom2` is a singular geometry object (e.g., POINT, LINESTRING, POLYGON), `geom2` will be returned. If `geom2` a multi-geometry, e.g., MULTIPOINT, MULTILINESTRING, etc., the nearest singular geometry in `geom2` will be returned.
`ST_NORMALIZE(geom)`	Returns `geom` in its normalized (canonical) form, which may rearrange the points in lexicographical order.
`ST_NPOINTS(geom)`	Returns the number of points (vertices) in `geom`.
`ST_NUMGEOMETRIES(geom)`	If `geom` is a collection or MULTI- geometry, returns the number of geometries. If `geom` is a single geometry, returns 1.
`ST_NUMINTERIORRINGS(geom)`	Returns the number of interior rings if `geom` is a POLYGON. Returns `null` if `geom` is anything else.
`ST_NUMPOINTS(geom)`	Returns the number of points in the `geom` LINESTRING. Returns `null` if `geom` is not a LINESTRING.
`ST_OVERLAPS(geom1, geom2)`	Returns `1` (true) if given geometries `geom1` and `geom2` share space. If `geom1` and/or `geom2` are a GEOMETRYCOLLECTION, a `0` is returned regardless if the two geometries overlap
`ST_PARTITION(geom, threshold)`	Returns a MULTIPOLYGON representing the given `geom` partitioned into a number of POLYGONs with a maximum number of vertices equal to the given `threshold`. Minimum value for `threshold` is `10`; default value is `10000`. If `geom` is not a POLYGON or MULTIPOLYGON, `geom` is returned. If the number of vertices in `geom` is less than the `threshold`, `geom` is returned.
`ST_PERIMETER(geom[, solution])`	Returns the perimeter of the geometry if it is a POLYGON or MULTIPOLYGON. Returns `0` if another type of geometry, e.g., POINT, MULTIPOINT, LINESTRING, or MULTILINESTRING. GEOMETRYCOLLECTIONs are also supported but the aforementioned type limitation still applies; the collection will be recursively searched for POLYGONs and MULTIPOLYGONs and the summation of all supported geometry types is returned (unsupported types are ignored). Solution types available: `0` (default) - 2D Euclidean length `1` - length on a sphere in meters `2` - length on a spheroid in meters
`ST_POINT(x, y)`	Returns a POINT with the given `x` and `y` coordinates.
`ST_POINTFROMGEOHASH(geohash, precision)`	Returns a POINT using the given `geohash` with a precision set by the integer `precision`. If `precision` is specified, the function will use as many characters in the hash equal to `precision` to create the geometry. If no `precision` is specified, the full length of the `geohash` is used. Note The POINT returned represents the center of the bounding box of the geohash
`ST_POINTGRID(xmin, ymin, xmax, ymax, cell_side[, limit])`	Creates a MULTIPOLYGON containing a square-shaped grid of points between given minimum and maximum points of a bounding box. The minimum point cannot be greater than or equal to the maximum point. The distance between the points (in meters) is determined by `cell_side`. The `cell_side` cannot be greater than the width or height of the bounding box. The maximum number of cells that can be produced is determined by `limit`, a positive integer. Supported values for `limit`: `-1` - No limit to the number of cells generated (effectively limited by system memory) `0` (default) - 100 million cells `<n>` - Custom limit of `n` cells If the custom limit request specifies more cells (based on the bounding box and the `cell_side`) than the system limit, a `null` is returned.
`ST_POINTN(geom, n)`	Returns the `n`-th point in LINESTRING `geom`. Negative values are valid, but note that they are counted backwards from the end of `geom`. A `null` is returned if `geom` is not a LINESTRING.
`ST_POINTS(geom)`	Returns a MULTIPOINT containing all of the coordinates of `geom`.
`ST_PROJECT(geom, distance, azimuth)`	Returns a POINT projected from a start point `geom` along a geodesic calculated using `distance` and `azimuth`. If `geom` is not a POINT, `null` is returned.
`ST_REMOVEPOINT(geom, offset)`	Remove a point from LINESTRING `geom` using `offset` to skip over POINTs in the LINESTRING. The `offset` is 0-based.
`ST_REMOVEREPEATEDPOINTS(geom, tolerance)`	Removes points from `geom` if the point's vertices are greater than or equal to the `tolerance` of the previous point in the geometry's list. If `geom` is not a MULTIPOINT, MULTILINESTRING, or a MULTIPOLYGON, no points will be removed.
`ST_REVERSE(geom)`	Return the geometry with its coordinate order reversed.
`ST_SCALE(geom, x, y)`	Scales `geom` by multiplying its respective vertices by the given `x` and `y` values. This function also supports scaling `geom` using another geometry object, e.g., `ST_SCALE('POINT(3 4)', 'POINT(5 6)')` would return `POINT(15 24)`. If specifying `x` and `y` for scale, note that the default value is `0`, e.g., `ST_SCALE('POINT(1 3)', 4)` would return `POINT(4 0)`.
`ST_SEGMENTIZE(geom, max_segment_length[, solution])`	Returns the given `geom` but segmentized n number of times depending on how the `max_segment_length` distance (in units based on the `solution` type) divides up the original geometry. The new `geom` is guaranteed to have segments that are smaller than the given `max_segment_length`. Note that POINTs are not able to be segmentized. Collection geometries (GEOMETRYCOLLECTION, MULTILINESTRING, MULTIPOINT, etc.) can be segmentized, but only the individual parts will be segmentized, not the collection as a whole. Solution types available: `0` - Euclidean; uses degrees to calculate distance `1` (default) - Sphere; uses meters to calculate distance
`ST_SETPOINT(geom1, position, geom2)`	Replace a point of LINESTRING `geom1` with POINT `geom2` at `position` (base 0). Negative values are valid, but note that they are counted backwards from the end of `geom`.
`ST_SHAREDPATH(geom1, geom2)`	Returns a collection containing paths shared by `geom1` and `geom2`.
`ST_SHORTESTLINE(geom1, geom2)`	Returns the 2-D LINESTRING that represents the shortest line of points between the two geometries. If multiple shortest lines are found, only the first line found is returned. If `geom1` or `geom2` is empty, `null` is returned
`ST_SIMPLIFY(geom, tolerance)`	Returns a simplified version of the given `geom` using an algorithm to reduce the number of points comprising a given geometry while attempting to best retain the original shape. The given `tolerance` determines how much to simplify the geometry. The higher the `tolerance`, the more simplified the returned geometry. Some holes might be removed and some invalid polygons (e.g., self-intersecting, etc.) might be present in the returned geometry. Only (MULTI)LINESTRINGs and (MULTI)POLYGONs can be simplified, including those found within GEOMETRYCOLLECTIONs; any other geometry objects will be returned unsimplified. Note The `tolerance` should be provided in the same units as the data. As a rule of thumb, a `tolerance` of `0.00001` would correspond to about one meter.
`ST_SIMPLIFYPRESERVETOPOLOGY(geom, tolerance)`	Returns a simplified version of the given `geom` using an algorithm to reduce the number of points comprising a given geometry while attempting to best retain the original shape. The given `tolerance` determines how much to simplify the geometry. The higher the `tolerance`, the more simplified the returned geometry. No holes will be removed and no invalid polygons (e.g., self-intersecting, etc.) will be present in the returned geometry. Only (MULTI)LINESTRINGs and (MULTI)POLYGONs can be simplified, including those found within GEOMETRYCOLLECTIONs; any other geometry objects will be returned unsimplified. Note The `tolerance` should be provided in the same units as the data. As a rule of thumb, a `tolerance` of `0.00001` would correspond to about one meter.
`ST_SNAP(geom1, geom2, tolerance)`	Snaps `geom1` to `geom2` within the given `tolerance`. If the `tolerance` causes `geom1` to not snap, the geometries will be returned unchanged.
`ST_SPLIT(geom1, geom2)`	Returns a collection of geometries resulting from the split between `geom1` and `geom2` geometries.
`ST_SQUAREGRID(xmin, ymin, xmax, ymax, cell_side[, limit])`	Creates a MULTIPOLYGON containing a grid of squares between given minimum and maximum points of a bounding box. The minimum point cannot be greater than or equal to the maximum point. The size (in meters) of the individual squares' sides is determined by `cell_side`. The `cell_side` cannot be greater than the width or height of the bounding box. The maximum number of cells that can be produced is determined by `limit`, a positive integer. Supported values for `limit`: `-1` - No limit to the number of cells generated (effectively limited by system memory) `0` (default) - 100 million cells `<n>` - Custom limit of `n` cells If the custom limit request specifies more cells (based on the bounding box and the `cell_side`) than the system limit, a `null` is returned.
`ST_STARTPOINT(geom)`	Returns the first point of LINESTRING `geom` as a POINT. Returns `null` if `geom` is not a LINESTRING.
`ST_SYMDIFFERENCE(geom1, geom2)`	Returns a geometry that represents the portions of `geom1` and `geom2` geometries that do not intersect.
`ST_TOUCHES(geom1, geom2)`	Returns `1` (true) if the given geometries, `geom1` and `geom2`, have at least one point in common but their interiors do not intersect. If `geom1` and/or `geom2` are a GEOMETRYCOLLECTION, a `0` is returned regardless if the two geometries touch
`ST_TRANSLATE(geom, deltax, deltay[, deltaz])`	Translate `geom` by given offsets `deltax` and `deltay`. A z-coordinate offset can be applied using `deltaz`.
`ST_TRIANGLEGRID(xmin, ymin, xmax, ymax, cell_side[, limit])`	Creates a MULTIPOLYGON containing a grid of triangles between given minimum and maximum points of a bounding box. The minimum point cannot be greater than or equal to the maximum point. The size (in meters) of the individual triangles' sides is determined by `cell_side`. The `cell_side` cannot be greater than the width or height of the bounding box. The maximum number of cells that can be produced is determined by `limit`, a positive integer. Supported values for `limit`: `-1` - No limit to the number of cells generated (effectively limited by system memory) `0` (default) - 100 million cells `<n>` - Custom limit of `n` cells If the custom limit request specifies more cells (based on the bounding box and the `cell_side`) than the system limit, a `null` is returned.
`ST_UNION(geom1, geom2)`	Returns a geometry that represents the point set union of the two given geometries, `geom1` and `geom2`.
`ST_UNIONCOLLECTION(geom)`	Returns a geometry that represents the point set union of a single given geometry `geom`.
`ST_UPDATE(geom1, geom2)`	Returns a geometry that is `geom1` geometry updated by `geom2` geometry
`ST_VORONOIPOLYGONS(geom, tolerance)`	Returns a GEOMETRYCOLLECTION containing Voronoi polygons (regions consisting of points closer to a vertex in `geom` than any other vertices in `geom`) calculated from the vertices in `geom` and the given `tolerance`. The `tolerance` determines the distance at which points will be considered the same. An empty GEOMETRYCOLLECTION is returned if `geom` is an empty geometry, a single POINT, or a LINESTRING or POLYGON composed of equivalent vertices (e.g., `POLYGON((0 0, 0 0, 0 0, 0 0))`, `LINESTRING(0 0, 0 0)`).
`ST_WITHIN(geom1, geom2)`	Returns `1` (true) if the `geom1` geometry is inside the `geom2` geometry. Note that as long as at least one point is inside of `geom2`, `geom1` is considered within `geom2` even if the rest of the `geom1` lies along the boundary of `geom2`
`ST_WKTTOWKB(geom)`	Returns the binary form (WKB) of a `geom` (WKT) Note This function can only be used in queries against a single table.
`ST_X(geom)`	Returns the X coordinate of the POINT `geom`; if the coordinate is not available, `null` is returned. `geom` must be a POINT.
`ST_XMAX(geom)`	Alias for `ST_MAXX()`
`ST_XMIN(geom)`	Alias for `ST_MINX()`
`ST_Y(geom)`	Returns the Y coordinate of the POINT `geom`; if the coordinate is not available, `null` is returned. `geom` must be a POINT.
`ST_YMAX(geom)`	Alias for `ST_MAXY()`
`ST_YMIN(geom)`	Alias for `ST_MINY()`
`ST_ZMAX(geom)`	Alias for `ST_MAXZ()`
`ST_ZMIN(geom)`	Alias for `ST_MINZ()`

Aggregation Functions¶

Function	Description
`ST_AGGREGATE_COLLECT(geom)`	Alias for `ST_COLLECT_AGGREGATE()`
`ST_AGGREGATE_INTERSECTION(geom)`	Alias for `ST_INTERSECTION_AGGREGATE()`
`ST_COLLECT_AGGREGATE(geom)`	Returns a GEOMETRYCOLLECTION comprising all geometries found in the `geom` set. Any MULTI* geometries will be divided into separate singular geometries, e.g., MULTIPOINT((0 0), (1 1)) would be divided into POINT(0 0) and POINT(1 1) in the results; the same is true for elements of a GEOMETRYCOLLECTION found in `geom`, where a GEOMETRYCOLLECTION within the provided `geom` set will also be parsed, effectively flattening it and adding the individual geometries to the resulting GEOMETRYCOLLECTION. Any empty geometries in `geom` are ignored even if they are part of a GEOMETRYCOLLECTION. Any duplicate WKTs will be retained.
`ST_DISSOLVE(geom)`	Dissolves all geometries within a given set into a single geometry. Note that the resulting single geometry can still be a group of noncontiguous geometries but represented as a single group, e.g., a GEOMETRYCOLLECTION. Best performance when used in conjunction with adjacent geometries
`ST_DISSOLVEOVERLAPPING(geom)`	Dissolves all geometries within a given set into a single geometry. Note that the resulting single geometry can still be a group of noncontiguous geometries but represented as a single group, e.g., a GEOMETRYCOLLECTION. Best performance when used in conjunction with overlapping geometries
`ST_INTERSECTION_AGGREGATE(geom)`	Returns a POLYGON or MULTIPOLYGON comprising the shared portion between all geometries found in the `geom` set. Returns an empty GEOMETRYCOLLECTION if there is no shared portion between all geometries. Functionally equivalent to `ST_INTERSECTION(ST_INTERSECTION(ST_INTERSECTION(geom1, geom2), geom3), ... geomN)`.
`ST_LINESTRINGFROMORDEREDPOINTS(x, y, t)`	Returns a LINESTRING that represents a "track" of the given points (`x`, `y`) ordered by the given sort column `t` (e.g., a timestamp or sequence number). If any of the values in the specified columns are `null`, the null "point" will be left out of the resulting LINESTRING. If there's only one non-null "point" in the source table, a POINT is returned. If there are no non-null "points" in the source table, a `null` is returned
`ST_LINESTRINGFROMORDEREDPOINTS3D(x, y, z, t)`	Returns a LINESTRING that represents a "track" of the given 3D points (`x`, `y`, `z`) ordered by the given sort column `t` (e.g., a timestamp or sequence number). If any of the values in the specified columns are `null`, the null "point" will be left out of the resulting LINESTRING. If there's only one non-null "point" in the source table, a POINT is returned. If there are no non-null "points" in the source table, a `null` is returned
`ST_POLYGONIZE(geom)`	Returns a GEOMETRYCOLLECTION containing POLYGONs comprising the provided (MULTI)LINESTRING(s). (MULTI)POINT and (MULTI)POLYGON geometries are ignored when calculating the resulting GEOMETRYCOLLECTION. If a valid POLYGON cannot be constructed from the provided (MULTI)LINESTRING(s), an empty GEOMETRYCOLLECTION will be returned.