GoogleSQL for Bigtable supports geography functions. Geography functions operate on or generate GoogleSQL `GEOGRAPHY` values. The signature of most geography functions starts with `ST_`. GoogleSQL for Bigtable supports the following functions that can be used to analyze geographical data, determine spatial relationships between geographical features, and construct or manipulate `GEOGRAPHY`s. All GoogleSQL geography functions return `NULL` if any input argument is `NULL`. ## Categories The geography functions are grouped into the following categories based on their behavior: | Category | Functions | Description | |---|---|---| | Constructors | [`ST_GEOGPOINT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogpoint) [`ST_MAKELINE`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_makeline) [`ST_MAKEPOLYGON`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_makepolygon) [`ST_MAKEPOLYGONORIENTED`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_makepolygonoriented) | Functions that build new geography values from coordinates or existing geographies. | | Parsers | [`ST_GEOGFROM`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogfrom) [`ST_GEOGFROMGEOJSON`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogfromgeojson) [`ST_GEOGFROMKML`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogfromkml) [`ST_GEOGFROMTEXT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogfromtext) [`ST_GEOGFROMWKB`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogfromwkb) [`ST_GEOGPOINTFROMGEOHASH`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogpointfromgeohash) | Functions that create geographies from an external format such as [WKT](https://en.wikipedia.org/wiki/Well-known_text) and [GeoJSON](https://en.wikipedia.org/wiki/GeoJSON). | | Formatters | [`ST_ASBINARY`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_asbinary) [`ST_ASGEOJSON`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_asgeojson) [`ST_ASKML`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_askml) [`ST_ASTEXT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_astext) [`ST_GEOHASH`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geohash) | Functions that export geographies to an external format such as WKT. | | Transformations | [`ST_ACCUM`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_accum) (Aggregate) [`ST_BOUNDARY`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_boundary) [`ST_BUFFER`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_buffer) [`ST_BUFFERWITHTOLERANCE`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_bufferwithtolerance) [`ST_CENTROID`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_centroid) [`ST_CENTROID_AGG`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_centroid_agg) (Aggregate) [`ST_CLOSESTPOINT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_closestpoint) [`ST_CONVEXHULL`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_convexhull) [`ST_DIFFERENCE`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_difference) [`ST_EXTERIORRING`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_exteriorring) [`ST_INTERIORRINGS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_interiorrings) [`ST_INTERSECTION`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_intersection) [`ST_LINEINTERPOLATEPOINT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_lineinterpolatepoint) [`ST_LINESUBSTRING`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_linesubstring) [`ST_SIMPLIFY`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_simplify) [`ST_SNAPTOGRID`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_snaptogrid) [`ST_UNION`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_union) [`ST_UNION_AGG`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_union_agg) (Aggregate) | Functions that generate a new geography based on input. | | Accessors | [`ST_DIMENSION`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_dimension) [`ST_DUMP`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_dump) [`ST_DUMPPOINTS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_dumppoints) [`ST_ENDPOINT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_endpoint) [`ST_GEOMETRYTYPE`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geometrytype) [`ST_ISCLOSED`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_isclosed) [`ST_ISCOLLECTION`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_iscollection) [`ST_ISEMPTY`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_isempty) [`ST_ISRING`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_isring) [`ST_NPOINTS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_npoints) [`ST_NUMGEOMETRIES`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_numgeometries) [`ST_NUMPOINTS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_numpoints) [`ST_POINTN`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_pointn) [`ST_STARTPOINT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_startpoint) [`ST_X`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_x) [`ST_Y`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_y) | Functions that provide access to properties of a geography without side-effects. | | Predicates | [`ST_CONTAINS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_contains) [`ST_COVEREDBY`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_coveredby) [`ST_COVERS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_covers) [`ST_DISJOINT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_disjoint) [`ST_DWITHIN`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_dwithin) [`ST_EQUALS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_equals) [`ST_HAUSDORFFDWITHIN`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_hausdorffdwithin) [`ST_INTERSECTS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_intersects) [`ST_INTERSECTSBOX`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_intersectsbox) [`ST_TOUCHES`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_touches) [`ST_WITHIN`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_within) | Functions that return `TRUE` or `FALSE` for some spatial relationship between two geographies or some property of a geography. These functions are commonly used in filter clauses. | | Measures | [`ST_ANGLE`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_angle) [`ST_AREA`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_area) [`ST_AZIMUTH`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_azimuth) [`ST_BOUNDINGBOX`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_boundingbox) [`ST_DISTANCE`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_distance) [`ST_EXTENT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_extent) (Aggregate) [`ST_HAUSDORFFDISTANCE`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_hausdorffdistance) [`ST_LINELOCATEPOINT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_linelocatepoint) [`ST_LENGTH`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_length) [`ST_MAXDISTANCE`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_maxdistance) [`ST_PERIMETER`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_perimeter) | Functions that compute measurements of one or more geographies. | | S2 functions | [`S2_CELLIDFROMPOINT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#s2_cellidfrompoint) [`S2_COVERINGCELLIDS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#s2_coveringcellids) | Functions for working with S2 cell coverings of GEOGRAPHY. | | Raster functions | [`ST_REGIONSTATS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_regionstats) | Functions for analyzing geospatial rasters using geographies. | ## Function list | Name | Summary | |---|---| | [`S2_CELLIDFROMPOINT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#s2_cellidfrompoint) | Gets the S2 cell ID covering a point `GEOGRAPHY` value. | | [`S2_COVERINGCELLIDS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#s2_coveringcellids) | Gets an array of S2 cell IDs that cover a `GEOGRAPHY` value. | | [`ST_ACCUM`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_accum) | Aggregates `GEOGRAPHY` values into an array of `GEOGRAPHY` elements. | | [`ST_ANGLE`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_angle) | Takes three point `GEOGRAPHY` values, which represent two intersecting lines, and returns the angle between these lines. | | [`ST_AREA`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_area) | Gets the area covered by the polygons in a `GEOGRAPHY` value. | | [`ST_ASBINARY`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_asbinary) | Converts a `GEOGRAPHY` value to a `BYTES` WKB geography value. | | [`ST_ASGEOJSON`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_asgeojson) | Converts a `GEOGRAPHY` value to a `STRING` GeoJSON geography value. | | [`ST_ASKML`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_askml) | Converts a `GEOGRAPHY` value to a `STRING` KML geometry value. | | [`ST_ASTEXT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_astext) | Converts a `GEOGRAPHY` value to a `STRING` WKT geography value. | | [`ST_AZIMUTH`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_azimuth) | Gets the azimuth of a line segment formed by two point `GEOGRAPHY` values. | | [`ST_BOUNDARY`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_boundary) | Gets the union of component boundaries in a `GEOGRAPHY` value. | | [`ST_BOUNDINGBOX`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_boundingbox) | Gets the bounding box for a `GEOGRAPHY` value. | | [`ST_BUFFER`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_buffer) | Gets the buffer around a `GEOGRAPHY` value, using a specific number of segments. | | [`ST_BUFFERWITHTOLERANCE`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_bufferwithtolerance) | Gets the buffer around a `GEOGRAPHY` value, using tolerance. | | [`ST_CENTROID`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_centroid) | Gets the centroid of a `GEOGRAPHY` value. | | [`ST_CLOSESTPOINT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_closestpoint) | Gets the point on a `GEOGRAPHY` value which is closest to any point in a second `GEOGRAPHY` value. | | [`ST_CONTAINS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_contains) | Checks if one `GEOGRAPHY` value contains another `GEOGRAPHY` value. | | [`ST_CONVEXHULL`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_convexhull) | Returns the convex hull for a `GEOGRAPHY` value. | | [`ST_COVEREDBY`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_coveredby) | Checks if all points of a `GEOGRAPHY` value are on the boundary or interior of another `GEOGRAPHY` value. | | [`ST_COVERS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_covers) | Checks if all points of a `GEOGRAPHY` value are on the boundary or interior of another `GEOGRAPHY` value. | | [`ST_DIFFERENCE`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_difference) | Gets the point set difference between two `GEOGRAPHY` values. | | [`ST_DIMENSION`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_dimension) | Gets the dimension of the highest-dimensional element in a `GEOGRAPHY` value. | | [`ST_DISJOINT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_disjoint) | Checks if two `GEOGRAPHY` values are disjoint (don't intersect). | | [`ST_DISTANCE`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_distance) | Gets the shortest distance in meters between two `GEOGRAPHY` values. | | [`ST_DUMP`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_dump) | Returns an array of simple `GEOGRAPHY` components in a `GEOGRAPHY` value. | | [`ST_DUMPPOINTS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_dumppoints) | Produces an array of `GEOGRAPHY` points with all points, line vertices, and polygon vertices in a `GEOGRAPHY` value. | | [`ST_DWITHIN`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_dwithin) | Checks if any points in two `GEOGRAPHY` values are within a given distance. | | [`ST_ENDPOINT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_endpoint) | Gets the last point of a linestring `GEOGRAPHY` value. | | [`ST_EQUALS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_equals) | Checks if two `GEOGRAPHY` values represent the same `GEOGRAPHY` value. | | [`ST_EXTENT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_extent) | Gets the bounding box for a group of `GEOGRAPHY` values. | | [`ST_EXTERIORRING`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_exteriorring) | Returns a linestring `GEOGRAPHY` value that corresponds to the outermost ring of a polygon `GEOGRAPHY` value. | | [`ST_GEOGFROM`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogfrom) | Converts a `STRING` or `BYTES` value into a `GEOGRAPHY` value. | | [`ST_GEOGFROMGEOJSON`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogfromgeojson) | Converts a `STRING` GeoJSON geometry value into a `GEOGRAPHY` value. | | [`ST_GEOGFROMKML`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogfromkml) | Converts a `STRING` KML geometry value into a `GEOGRAPHY` value. | | [`ST_GEOGFROMTEXT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogfromtext) | Converts a `STRING` WKT geometry value into a `GEOGRAPHY` value. | | [`ST_GEOGFROMWKB`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogfromwkb) | Converts a `BYTES` or hexadecimal-text `STRING` WKT geometry value into a `GEOGRAPHY` value. | | [`ST_GEOGPOINT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogpoint) | Creates a point `GEOGRAPHY` value for a given longitude and latitude. | | [`ST_GEOGPOINTFROMGEOHASH`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogpointfromgeohash) | Gets a point `GEOGRAPHY` value that's in the middle of a bounding box defined in a `STRING` GeoHash value. | | [`ST_GEOHASH`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geohash) | Converts a point `GEOGRAPHY` value to a `STRING` GeoHash value. | | [`ST_GEOMETRYTYPE`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geometrytype) | Gets the Open Geospatial Consortium (OGC) geometry type for a `GEOGRAPHY` value. | | [`ST_HAUSDORFFDISTANCE`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_hausdorffdistance) | Gets the discrete Hausdorff distance between two geometries. | | [`ST_HAUSDORFFDWITHIN`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_hausdorffdwithin) | Checks if the Hausdorff distance between two `GEOGRAPHY` values is within a given distance. | | [`ST_INTERIORRINGS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_interiorrings) | Gets the interior rings of a polygon `GEOGRAPHY` value. | | [`ST_INTERSECTION`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_intersection) | Gets the point set intersection of two `GEOGRAPHY` values. | | [`ST_INTERSECTS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_intersects) | Checks if at least one point appears in two `GEOGRAPHY` values. | | [`ST_INTERSECTSBOX`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_intersectsbox) | Checks if a `GEOGRAPHY` value intersects a rectangle. | | [`ST_ISCLOSED`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_isclosed) | Checks if all components in a `GEOGRAPHY` value are closed. | | [`ST_ISCOLLECTION`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_iscollection) | Checks if the total number of points, linestrings, and polygons is greater than one in a `GEOGRAPHY` value. | | [`ST_ISEMPTY`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_isempty) | Checks if a `GEOGRAPHY` value is empty. | | [`ST_ISRING`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_isring) | Checks if a `GEOGRAPHY` value is a closed, simple linestring. | | [`ST_LENGTH`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_length) | Gets the total length of lines in a `GEOGRAPHY` value. | | [`ST_LINEINTERPOLATEPOINT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_lineinterpolatepoint) | Gets a point at a specific fraction in a linestring `GEOGRAPHY` value. | | [`ST_LINELOCATEPOINT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_linelocatepoint) | Gets a section of a linestring `GEOGRAPHY` value between the start point and a point `GEOGRAPHY` value. | | [`ST_LINESUBSTRING`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_linesubstring) | Gets a segment of a single linestring at a specific starting and ending fraction. | | [`ST_MAKELINE`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_makeline) | Creates a linestring `GEOGRAPHY` value by concatenating the point and linestring vertices of `GEOGRAPHY` values. | | [`ST_MAKEPOLYGON`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_makepolygon) | Constructs a polygon `GEOGRAPHY` value by combining a polygon shell with polygon holes. | | [`ST_MAKEPOLYGONORIENTED`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_makepolygonoriented) | Constructs a polygon `GEOGRAPHY` value, using an array of linestring `GEOGRAPHY` values. The vertex ordering of each linestring determines the orientation of each polygon ring. | | [`ST_MAXDISTANCE`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_maxdistance) | Gets the longest distance between two non-empty `GEOGRAPHY` values. | | [`ST_NPOINTS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_npoints) | An alias of `ST_NUMPOINTS`. | | [`ST_NUMGEOMETRIES`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_numgeometries) | Gets the number of geometries in a `GEOGRAPHY` value. | | [`ST_NUMPOINTS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_numpoints) | Gets the number of vertices in the a `GEOGRAPHY` value. | | [`ST_PERIMETER`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_perimeter) | Gets the length of the boundary of the polygons in a `GEOGRAPHY` value. | | [`ST_POINTN`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_pointn) | Gets the point at a specific index of a linestring `GEOGRAPHY` value. | | [`ST_REGIONSTATS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_regionstats) | Computes statistics describing the pixels in a geospatial raster image that intersect a `GEOGRAPHY` value. | | [`ST_SIMPLIFY`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_simplify) | Converts a `GEOGRAPHY` value into a simplified `GEOGRAPHY` value, using tolerance. | | [`ST_SNAPTOGRID`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_snaptogrid) | Produces a `GEOGRAPHY` value, where each vertex has been snapped to a longitude/latitude grid. | | [`ST_STARTPOINT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_startpoint) | Gets the first point of a linestring `GEOGRAPHY` value. | | [`ST_TOUCHES`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_touches) | Checks if two `GEOGRAPHY` values intersect and their interiors have no elements in common. | | [`ST_UNION`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_union) | Gets the point set union of multiple `GEOGRAPHY` values. | | [`ST_UNION_AGG`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_union_agg) | Aggregates over `GEOGRAPHY` values and gets their point set union. | | [`ST_WITHIN`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_within) | Checks if one `GEOGRAPHY` value contains another `GEOGRAPHY` value. | ## `S2_CELLIDFROMPOINT` S2_CELLIDFROMPOINT(point_geography[, level => cell_level]) **Description** Returns the [S2 cell ID](https://s2geometry.io/devguide/s2cell_hierarchy) covering a point `GEOGRAPHY`. - The optional `INT64` parameter `level` specifies the S2 cell level for the returned cell. Naming this argument is optional. This is advanced functionality for interoperability with systems utilizing the [S2 Geometry Library](https://s2geometry.io/). **Constraints** - Returns the cell ID as a signed `INT64` bit-equivalent to [unsigned 64-bit integer representation](https://s2geometry.io/devguide/s2cell_hierarchy). - Can return negative cell IDs. - Valid S2 cell levels are 0 to 30. - `level` defaults to 30 if not explicitly specified. - The function only supports a single point GEOGRAPHY. - To compute the covering of a complex `GEOGRAPHY`, use [S2_COVERINGCELLIDS](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#s2_coveringcellids). **Return type** `INT64` ## `S2_COVERINGCELLIDS` S2_COVERINGCELLIDS( geography [, min_level => cell_level] [, max_level => cell_level] [, max_cells => max_cells] [, buffer => buffer]) **Description** Returns an array of [S2 cell IDs](https://s2geometry.io/devguide/s2cell_hierarchy) that cover the input `GEOGRAPHY`. The function returns at most `max_cells` cells. The optional arguments `min_level` and `max_level` specify minimum and maximum levels for returned S2 cells. The array size is limited by the optional `max_cells` argument. The optional `buffer` argument specifies a buffering factor in meters; the region being covered is expanded from the extent of the input geography by this amount. This is advanced functionality for interoperability with systems utilizing the [S2 Geometry Library](https://s2geometry.io/). **Constraints** - Returns the cell ID as a signed `INT64` bit-equivalent to [unsigned 64-bit integer representation](https://s2geometry.io/devguide/s2cell_hierarchy). - Can return negative cell IDs. - Valid S2 cell levels are 0 to 30. - `max_cells` defaults to 8 if not explicitly specified. - `buffer` should be nonnegative. It defaults to 0.0 meters if not explicitly specified. **Return type** `ARRAY` ## `ST_ACCUM` ST_ACCUM(geography) **Description** Takes a `GEOGRAPHY` and returns an array of `GEOGRAPHY` elements. This function is identical to [ARRAY_AGG](https://docs.cloud.google.com/bigtable/docs/reference/sql/aggregate_functions#array_agg), but only applies to `GEOGRAPHY` objects. **Return type** `ARRAY` ## `ST_ANGLE` ST_ANGLE(point_geography_1, point_geography_2, point_geography_3) **Description** Takes three point `GEOGRAPHY` values, which represent two intersecting lines. Returns the angle between these lines. Point 2 and point 1 represent the first line and point 2 and point 3 represent the second line. The angle between these lines is in radians, in the range `[0, 2pi)`. The angle is measured clockwise from the first line to the second line. `ST_ANGLE` has the following edge cases: - If points 2 and 3 are the same, returns `NULL`. - If points 2 and 1 are the same, returns `NULL`. - If points 2 and 3 are exactly antipodal, returns `NULL`. - If points 2 and 1 are exactly antipodal, returns `NULL`. - If any of the input geographies aren't single points or are the empty geography, then throws an error. **Return type** `FLOAT64` ## `ST_AREA` ST_AREA(geography_expression[, use_spheroid]) **Description** Returns the area in square meters covered by the polygons in the input `GEOGRAPHY`. If `geography_expression` is a point or a line, returns zero. If `geography_expression` is a collection, returns the area of the polygons in the collection; if the collection doesn't contain polygons, returns zero. The optional `use_spheroid` parameter determines how this function measures distance. If `use_spheroid` is `FALSE`, the function measures distance on the surface of a perfect sphere. The `use_spheroid` parameter currently only supports the value `FALSE`. The default value of `use_spheroid` is `FALSE`. **Return type** `FLOAT64` ## `ST_ASBINARY` ST_ASBINARY(geography_expression) **Description** Returns the [WKB](https://en.wikipedia.org/wiki/Well-known_text#Well-known_binary) representation of an input `GEOGRAPHY`. See [`ST_GEOGFROMWKB`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogfromwkb) to construct a `GEOGRAPHY` from WKB. **Return type** `BYTES` ## `ST_ASGEOJSON` ST_ASGEOJSON(geography_expression) **Description** Returns the [RFC 7946](https://tools.ietf.org/html/rfc7946) compliant [GeoJSON](https://en.wikipedia.org/wiki/GeoJSON) representation of the input `GEOGRAPHY`. A GoogleSQL `GEOGRAPHY` has spherical geodesic edges, whereas a GeoJSON `Geometry` object explicitly has planar edges. To convert between these two types of edges, GoogleSQL adds additional points to the line where necessary so that the resulting sequence of edges remains within 10 meters of the original edge. See [`ST_GEOGFROMGEOJSON`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogfromgeojson) to construct a `GEOGRAPHY` from GeoJSON. **Return type** `STRING` ## `ST_ASKML` ST_ASKML(geography) **Description** Takes a `GEOGRAPHY` and returns a `STRING` [KML geometry](https://developers.google.com/kml/documentation/kmlreference#geometry). Coordinates are formatted with as few digits as possible without loss of precision. **Return type** `STRING` ## `ST_ASTEXT` ST_ASTEXT(geography_expression) **Description** Returns the [WKT](https://en.wikipedia.org/wiki/Well-known_text) representation of an input `GEOGRAPHY`. See [`ST_GEOGFROMTEXT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogfromtext) to construct a `GEOGRAPHY` from WKT. **Return type** `STRING` ## `ST_AZIMUTH` ST_AZIMUTH(point_geography_1, point_geography_2) **Description** Takes two point `GEOGRAPHY` values, and returns the azimuth of the line segment formed by points 1 and 2. The azimuth is the angle in radians measured between the line from point 1 facing true North to the line segment from point 1 to point 2. The positive angle is measured clockwise on the surface of a sphere. For example, the azimuth for a line segment: - Pointing North is `0` - Pointing East is `PI/2` - Pointing South is `PI` - Pointing West is `3PI/2` `ST_AZIMUTH` has the following edge cases: - If the two input points are the same, returns `NULL`. - If the two input points are exactly antipodal, returns `NULL`. - If either of the input geographies aren't single points or are the empty geography, throws an error. **Return type** `FLOAT64` ## `ST_BOUNDARY` ST_BOUNDARY(geography_expression) **Description** Returns a single `GEOGRAPHY` that contains the union of the boundaries of each component in the given input `GEOGRAPHY`. The boundary of each component of a `GEOGRAPHY` is defined as follows: - The boundary of a point is empty. - The boundary of a linestring consists of the endpoints of the linestring. - The boundary of a polygon consists of the linestrings that form the polygon shell and each of the polygon's holes. **Return type** `GEOGRAPHY` ## `ST_BOUNDINGBOX` ST_BOUNDINGBOX(geography_expression) **Description** Returns a `STRUCT` that represents the bounding box for the specified geography. The bounding box is the minimal rectangle that encloses the geography. The edges of the rectangle follow constant lines of longitude and latitude. Caveats: - Returns `NULL` if the input is `NULL` or an empty geography. - The bounding box might cross the antimeridian if this allows for a smaller rectangle. In this case, the bounding box has one of its longitudinal bounds outside of the \[-180, 180\] range, so that `xmin` is smaller than the eastmost value `xmax`. **Return type** `STRUCT`. Bounding box parts: - `xmin`: The westmost constant longitude line that bounds the rectangle. - `xmax`: The eastmost constant longitude line that bounds the rectangle. - `ymin`: The minimum constant latitude line that bounds the rectangle. - `ymax`: The maximum constant latitude line that bounds the rectangle. See [`ST_EXTENT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_extent) for the aggregate version of `ST_BOUNDINGBOX`. ## `ST_BUFFER` ST_BUFFER( geography, buffer_radius [, num_seg_quarter_circle => num_segments] [, use_spheroid => boolean_expression] [, endcap => endcap_style] [, side => line_side]) **Description** Returns a `GEOGRAPHY` that represents the buffer around the input `GEOGRAPHY`. This function is similar to [`ST_BUFFERWITHTOLERANCE`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_bufferwithtolerance), but you specify the number of segments instead of providing tolerance to determine how much the resulting geography can deviate from the ideal buffer radius. - `geography`: The input `GEOGRAPHY` to encircle with the buffer radius. - `buffer_radius`: `FLOAT64` that represents the radius of the buffer around the input geography. The radius is in meters. Note that polygons contract when buffered with a negative `buffer_radius`. Polygon shells and holes that are contracted to a point are discarded. - `num_seg_quarter_circle`: (Optional) `FLOAT64` specifies the number of segments that are used to approximate a quarter circle. The default value is `8.0`. Naming this argument is optional. - `endcap`: (Optional) `STRING` allows you to specify one of two endcap styles: `ROUND` and `FLAT`. The default value is `ROUND`. This option only affects the endcaps of buffered linestrings. - `side`: (Optional) `STRING` allows you to specify one of three possibilities for lines: `BOTH`, `LEFT`, and `RIGHT`. The default is `BOTH`. This option only affects how linestrings are buffered. - `use_spheroid`: (Optional) `BOOL` determines how this function measures distance. If `use_spheroid` is `FALSE`, the function measures distance on the surface of a perfect sphere. The `use_spheroid` parameter currently only supports the value `FALSE`. The default value of `use_spheroid` is `FALSE`. **Return type** Polygon `GEOGRAPHY` **Example** The following example shows the result of `ST_BUFFER` on a point. A buffered point is an approximated circle. When `num_seg_quarter_circle = 2`, there are two line segments in a quarter circle, and therefore the buffered circle has eight sides and [`ST_NUMPOINTS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_numpoints) returns nine vertices. When `num_seg_quarter_circle = 8`, there are eight line segments in a quarter circle, and therefore the buffered circle has thirty-two sides and [`ST_NUMPOINTS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_numpoints) returns thirty-three vertices. SELECT -- num_seg_quarter_circle=2 ST_NUMPOINTS(ST_BUFFER(ST_GEOGFROMTEXT('POINT(1 2)'), 50, 2)) AS eight_sides, -- num_seg_quarter_circle=8, since 8 is the default ST_NUMPOINTS(ST_BUFFER(ST_GEOGFROMTEXT('POINT(100 2)'), 50)) AS thirty_two_sides; /*---+---+ | eight_sides | thirty_two_sides | +---+---+ | 9 | 33 | +---+---*/ ## `ST_BUFFERWITHTOLERANCE` ST_BUFFERWITHTOLERANCE( geography, buffer_radius, tolerance_meters => tolerance [, use_spheroid => boolean_expression] [, endcap => endcap_style] [, side => line_side]) Returns a `GEOGRAPHY` that represents the buffer around the input `GEOGRAPHY`. This function is similar to [`ST_BUFFER`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_buffer), but you provide tolerance instead of segments to determine how much the resulting geography can deviate from the ideal buffer radius. - `geography`: The input `GEOGRAPHY` to encircle with the buffer radius. - `buffer_radius`: `FLOAT64` that represents the radius of the buffer around the input geography. The radius is in meters. Note that polygons contract when buffered with a negative `buffer_radius`. Polygon shells and holes that are contracted to a point are discarded. - `tolerance_meters`: `FLOAT64` specifies a tolerance in meters with which the shape is approximated. Tolerance determines how much a polygon can deviate from the ideal radius. Naming this argument is optional. - `endcap`: (Optional) `STRING` allows you to specify one of two endcap styles: `ROUND` and `FLAT`. The default value is `ROUND`. This option only affects the endcaps of buffered linestrings. - `side`: (Optional) `STRING` allows you to specify one of three possible line styles: `BOTH`, `LEFT`, and `RIGHT`. The default is `BOTH`. This option only affects the endcaps of buffered linestrings. - `use_spheroid`: (Optional) `BOOL` determines how this function measures distance. If `use_spheroid` is `FALSE`, the function measures distance on the surface of a perfect sphere. The `use_spheroid` parameter currently only supports the value `FALSE`. The default value of `use_spheroid` is `FALSE`. **Return type** Polygon `GEOGRAPHY` **Example** The following example shows the results of `ST_BUFFERWITHTOLERANCE` on a point, given two different values for tolerance but with the same buffer radius of `100`. A buffered point is an approximated circle. When `tolerance_meters=25`, the tolerance is a large percentage of the buffer radius, and therefore only five segments are used to approximate a circle around the input point. When `tolerance_meters=1`, the tolerance is a much smaller percentage of the buffer radius, and therefore twenty-four edges are used to approximate a circle around the input point. SELECT -- tolerance_meters=25, or 25% of the buffer radius. ST_NumPoints(ST_BUFFERWITHTOLERANCE(ST_GEOGFROMTEXT('POINT(1 2)'), 100, 25)) AS five_sides, -- tolerance_meters=1, or 1% of the buffer radius. st_NumPoints(ST_BUFFERWITHTOLERANCE(ST_GEOGFROMTEXT('POINT(100 2)'), 100, 1)) AS twenty_four_sides; /*---+---+ | five_sides | twenty_four_sides | +---+---+ | 6 | 24 | +---+---*/ ## `ST_CENTROID` ST_CENTROID(geography_expression) **Description** Returns the *centroid* of the input `GEOGRAPHY` as a single point `GEOGRAPHY`. The *centroid* of a `GEOGRAPHY` is the weighted average of the centroids of the highest-dimensional components in the `GEOGRAPHY`. The centroid for components in each dimension is defined as follows: - The centroid of points is the arithmetic mean of the input coordinates. - The centroid of linestrings is the centroid of all the edges weighted by length. The centroid of each edge is the geodesic midpoint of the edge. - The centroid of a polygon is its center of mass. If the input `GEOGRAPHY` is empty, an empty `GEOGRAPHY` is returned. **Constraints** In the unlikely event that the centroid of a `GEOGRAPHY` can't be defined by a single point on the surface of the Earth, a deterministic but otherwise arbitrary point is returned. This can only happen if the centroid is exactly at the center of the Earth, such as the centroid for a pair of antipodal points, and the likelihood of this happening is vanishingly small. **Return type** Point `GEOGRAPHY` ## `ST_CLOSESTPOINT` ST_CLOSESTPOINT(geography_1, geography_2[, use_spheroid]) **Description** Returns a `GEOGRAPHY` containing a point on `geography_1` with the smallest possible distance to `geography_2`. This implies that the distance between the point returned by `ST_CLOSESTPOINT` and `geography_2` is less than or equal to the distance between any other point on `geography_1` and `geography_2`. If either of the input `GEOGRAPHY`s is empty, `ST_CLOSESTPOINT` returns `NULL`. The optional `use_spheroid` parameter determines how this function measures distance. If `use_spheroid` is `FALSE`, the function measures distance on the surface of a perfect sphere. The `use_spheroid` parameter currently only supports the value `FALSE`. The default value of `use_spheroid` is `FALSE`. **Return type** Point `GEOGRAPHY` ## `ST_CONTAINS` ST_CONTAINS(geography_1, geography_2) **Description** Returns `TRUE` if no point of `geography_2` is outside `geography_1`, and the interiors intersect; returns `FALSE` otherwise. > [!NOTE] > **Note:** A `GEOGRAPHY` *does not* contain its own boundary. Compare with [`ST_COVERS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_covers). **Return type** `BOOL` **Example** The following query tests whether the polygon `POLYGON((1 1, 20 1, 10 20, 1 1))` contains each of the three points `(0, 0)`, `(1, 1)`, and `(10, 10)`, which lie on the exterior, the boundary, and the interior of the polygon respectively. SELECT ST_GEOGPOINT(i, i) AS p, ST_CONTAINS(ST_GEOGFROMTEXT('POLYGON((1 1, 20 1, 10 20, 1 1))'), ST_GEOGPOINT(i, i)) AS `contains` FROM UNNEST([0, 1, 10]) AS i; /*---+---+ | p | contains | +---+---+ | POINT(0 0) | FALSE | | POINT(1 1) | FALSE | | POINT(10 10) | TRUE | +---+---*/ ## `ST_CONVEXHULL` ST_CONVEXHULL(geography_expression) **Description** Returns the convex hull for the input `GEOGRAPHY`. The convex hull is the smallest convex `GEOGRAPHY` that covers the input. A `GEOGRAPHY` is convex if for every pair of points in the `GEOGRAPHY`, the geodesic edge connecting the points are also contained in the same `GEOGRAPHY`. In most cases, the convex hull consists of a single polygon. Notable edge cases include the following: - The convex hull of a single point is also a point. - The convex hull of two or more collinear points is a linestring as long as that linestring is convex. - If the input `GEOGRAPHY` spans more than a hemisphere, the convex hull is the full globe. This includes any input that contains a pair of antipodal points. - `ST_CONVEXHULL` returns `NULL` if the input is either `NULL` or the empty `GEOGRAPHY`. **Return type** `GEOGRAPHY` ## `ST_COVEREDBY` ST_COVEREDBY(geography_1, geography_2) **Description** Returns `FALSE` if `geography_1` or `geography_2` is empty. Returns `TRUE` if no points of `geography_1` lie in the exterior of `geography_2`. Given two `GEOGRAPHY`s `a` and `b`, `ST_COVEREDBY(a, b)` returns the same result as [`ST_COVERS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_covers)`(b, a)`. Note the opposite order of arguments. **Return type** `BOOL` ## `ST_COVERS` ST_COVERS(geography_1, geography_2) **Description** Returns `FALSE` if `geography_1` or `geography_2` is empty. Returns `TRUE` if no points of `geography_2` lie in the exterior of `geography_1`. **Return type** `BOOL` **Example** The following query tests whether the polygon `POLYGON((1 1, 20 1, 10 20, 1 1))` covers each of the three points `(0, 0)`, `(1, 1)`, and `(10, 10)`, which lie on the exterior, the boundary, and the interior of the polygon respectively. SELECT ST_GEOGPOINT(i, i) AS p, ST_COVERS(ST_GEOGFROMTEXT('POLYGON((1 1, 20 1, 10 20, 1 1))'), ST_GEOGPOINT(i, i)) AS `covers` FROM UNNEST([0, 1, 10]) AS i; /*---+---+ | p | covers | +---+---+ | POINT(0 0) | FALSE | | POINT(1 1) | TRUE | | POINT(10 10) | TRUE | +---+---*/ ## `ST_DIFFERENCE` ST_DIFFERENCE(geography_1, geography_2) **Description** Returns a `GEOGRAPHY` that represents the point set difference of `geography_1` and `geography_2`. Therefore, the result consists of the part of `geography_1` that doesn't intersect with `geography_2`. If `geometry_1` is completely contained in `geometry_2`, then `ST_DIFFERENCE` returns an empty `GEOGRAPHY`. **Constraints** The underlying geometric objects that a GoogleSQL `GEOGRAPHY` represents correspond to a *closed* point set. Therefore, `ST_DIFFERENCE` is the closure of the point set difference of `geography_1` and `geography_2`. This implies that if `geography_1` and `geography_2` intersect, then a portion of the boundary of `geography_2` could be in the difference. **Return type** `GEOGRAPHY` **Example** The following query illustrates the difference between `geog1`, a larger polygon `POLYGON((0 0, 10 0, 10 10, 0 0))` and `geog2`, a smaller polygon `POLYGON((4 2, 6 2, 8 6, 4 2))` that intersects with `geog1`. The result is `geog1` with a hole where `geog2` intersects with it. SELECT ST_DIFFERENCE( ST_GEOGFROMTEXT('POLYGON((0 0, 10 0, 10 10, 0 0))'), ST_GEOGFROMTEXT('POLYGON((4 2, 6 2, 8 6, 4 2))') ); /*---+ | difference_of_geog1_and_geog2 | +---+ | POLYGON((0 0, 10 0, 10 10, 0 0), (8 6, 6 2, 4 2, 8 6)) | +---*/ ## `ST_DIMENSION` ST_DIMENSION(geography_expression) **Description** Returns the dimension of the highest-dimensional element in the input `GEOGRAPHY`. The dimension of each possible element is as follows: - The dimension of a point is `0`. - The dimension of a linestring is `1`. - The dimension of a polygon is `2`. If the input `GEOGRAPHY` is empty, `ST_DIMENSION` returns `-1`. **Return type** `INT64` ## `ST_DISJOINT` ST_DISJOINT(geography_1, geography_2) **Description** Returns `TRUE` if the intersection of `geography_1` and `geography_2` is empty, that is, no point in `geography_1` also appears in `geography_2`. `ST_DISJOINT` is the logical negation of [`ST_INTERSECTS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_intersects). **Return type** `BOOL` ## `ST_DISTANCE` ST_DISTANCE(geography_1, geography_2[, use_spheroid]) **Description** Returns the shortest distance in meters between two non-empty `GEOGRAPHY`s. If either of the input `GEOGRAPHY`s is empty, `ST_DISTANCE` returns `NULL`. The optional `use_spheroid` parameter determines how this function measures distance. If `use_spheroid` is `FALSE`, the function measures distance on the surface of a perfect sphere. If `use_spheroid` is `TRUE`, the function measures distance on the surface of the [WGS84](https://en.wikipedia.org/wiki/World_Geodetic_System) spheroid. The default value of `use_spheroid` is `FALSE`. **Return type** `FLOAT64` ## `ST_DUMP` ST_DUMP(geography[, dimension]) **Description** Returns an `ARRAY` of simple `GEOGRAPHY`s where each element is a component of the input `GEOGRAPHY`. A simple `GEOGRAPHY` consists of a single point, linestring, or polygon. If the input `GEOGRAPHY` is simple, the result is a single element. When the input `GEOGRAPHY` is a collection, `ST_DUMP` returns an `ARRAY` with one simple `GEOGRAPHY` for each component in the collection. If `dimension` is provided, the function only returns `GEOGRAPHY`s of the corresponding dimension. A dimension of -1 is equivalent to omitting `dimension`. **Return Type** `ARRAY` ## `ST_DUMPPOINTS` ST_DUMPPOINTS(geography) **Description** Takes an input geography and returns all of its points, line vertices, and polygon vertices as an array of point geographies. **Return Type** `ARRAY` ## `ST_DWITHIN` ST_DWITHIN(geography_1, geography_2, distance[, use_spheroid]) **Description** Returns `TRUE` if the distance between at least one point in `geography_1` and one point in `geography_2` is less than or equal to the distance given by the `distance` argument; otherwise, returns `FALSE`. If either input `GEOGRAPHY` is empty, `ST_DWithin` returns `FALSE`. The given `distance` is in meters on the surface of the Earth. The optional `use_spheroid` parameter determines how this function measures distance. If `use_spheroid` is `FALSE`, the function measures distance on the surface of a perfect sphere. The `use_spheroid` parameter currently only supports the value `FALSE`. The default value of `use_spheroid` is `FALSE`. **Return type** `BOOL` ## `ST_ENDPOINT` ST_ENDPOINT(linestring_geography) **Description** Returns the last point of a linestring geography as a point geography. Returns an error if the input isn't a linestring or if the input is empty. **Return Type** Point `GEOGRAPHY` **Example** SELECT ST_ENDPOINT(ST_GEOGFROMTEXT('LINESTRING(1 1, 2 1, 3 2, 3 3)')) last /*---+ | last | +---+ | POINT(3 3) | +---*/ ## `ST_EQUALS` ST_EQUALS(geography_1, geography_2) **Description** Checks if two `GEOGRAPHY` values represent the same `GEOGRAPHY` value. Returns `TRUE` if the values are the same, otherwise returns `FALSE`. **Definitions** - `geography_1`: The first `GEOGRAPHY` value to compare. - `geography_2`: The second `GEOGRAPHY` value to compare. **Details** As long as they still represent the same geometric structure, two `GEOGRAPHY` values can be equal even if the ordering of points or vertices differ. This means that one of the following conditions must be true for this function to return `TRUE`: - Both `ST_COVERS(geography_1, geography_2)` and `ST_COVERS(geography_2, geography_1)` are `TRUE`. - Both `geography_1` and `geography_2` are empty. `ST_EQUALS` isn't guaranteed to be a transitive function. **Return type** `BOOL` ## `ST_EXTENT` ST_EXTENT(geography_expression) **Description** Returns a `STRUCT` that represents the bounding box for the set of input `GEOGRAPHY` values. The bounding box is the minimal rectangle that encloses the geography. The edges of the rectangle follow constant lines of longitude and latitude. Caveats: - Returns `NULL` if all the inputs are `NULL` or empty geographies. - The bounding box might cross the antimeridian if this allows for a smaller rectangle. In this case, the bounding box has one of its longitudinal bounds outside of the \[-180, 180\] range, so that `xmin` is smaller than the eastmost value `xmax`. - If the longitude span of the bounding box is larger than or equal to 180 degrees, the function returns the bounding box with the longitude range of \[-180, 180\]. **Return type** `STRUCT`. Bounding box parts: - `xmin`: The westmost constant longitude line that bounds the rectangle. - `xmax`: The eastmost constant longitude line that bounds the rectangle. - `ymin`: The minimum constant latitude line that bounds the rectangle. - `ymax`: The maximum constant latitude line that bounds the rectangle. [`ST_BOUNDINGBOX`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_boundingbox) for the non-aggregate version of `ST_EXTENT`. ## `ST_EXTERIORRING` ST_EXTERIORRING(polygon_geography) **Description** Returns a linestring geography that corresponds to the outermost ring of a polygon geography. - If the input geography is a polygon, gets the outermost ring of the polygon geography and returns the corresponding linestring. - If the input is the full `GEOGRAPHY`, returns an empty geography. - Returns an error if the input isn't a single polygon. **Return type** - Linestring `GEOGRAPHY` - Empty `GEOGRAPHY` ## `ST_GEOGFROM` ST_GEOGFROM(expression) **Description** Converts an expression for a `STRING` or `BYTES` value into a `GEOGRAPHY` value. If `expression` represents a `STRING` value, it must be a valid `GEOGRAPHY` representation in one of the following formats: - WKT format. To learn more about this format and the requirements to use it, see [ST_GEOGFROMTEXT](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogfromtext). - WKB in hexadecimal text format. To learn more about this format and the requirements to use it, see [ST_GEOGFROMWKB](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogfromwkb). - GeoJSON format. To learn more about this format and the requirements to use it, see [ST_GEOGFROMGEOJSON](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogfromgeojson). If `expression` represents a `BYTES` value, it must be a valid `GEOGRAPHY` binary expression in WKB format. To learn more about this format and the requirements to use it, see [ST_GEOGFROMWKB](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_geogfromwkb). If `expression` is `NULL`, the output is `NULL`. **Return type** `GEOGRAPHY` **Examples** This takes a WKT-formatted string and returns a `GEOGRAPHY` polygon: SELECT ST_GEOGFROM('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))') AS WKT_format; /*---+ | WKT_format | +---+ | POLYGON((2 0, 2 2, 0 2, 0 0, 2 0)) | +---*/ This takes a WKB-formatted hexadecimal-encoded string and returns a `GEOGRAPHY` point: SELECT ST_GEOGFROM(FROM_HEX('010100000000000000000000400000000000001040')) AS WKB_format; /*---+ | WKB_format | +---+ | POINT(2 4) | +---*/ This takes WKB-formatted bytes and returns a `GEOGRAPHY` point: SELECT ST_GEOGFROM('010100000000000000000000400000000000001040') AS WKB_format; /*---+ | WKB_format | +---+ | POINT(2 4) | +---*/ This takes a GeoJSON-formatted string and returns a `GEOGRAPHY` polygon: SELECT ST_GEOGFROM( '{ "type": "Polygon", "coordinates": [ [ [2, 0], [2, 2], [1, 2], [0, 2], [0, 0], [2, 0] ] ] }' ) AS GEOJSON_format; /*---+ | GEOJSON_format | +---+ | POLYGON((2 0, 2 2, 1 2, 0 2, 0 0, 2 0)) | +---*/ ## `ST_GEOGFROMGEOJSON` ST_GEOGFROMGEOJSON( geojson_string [, make_valid => constant_expression ] ) **Description** Returns a `GEOGRAPHY` value that corresponds to the input [GeoJSON](https://en.wikipedia.org/wiki/GeoJSON) representation. `ST_GEOGFROMGEOJSON` accepts input that's [RFC 7946](https://tools.ietf.org/html/rfc7946) compliant. If the named argument `make_valid` is set to `TRUE`, the function attempts to repair polygons that don't conform to [Open Geospatial Consortium](https://www.ogc.org/standards/sfa) semantics. A GoogleSQL `GEOGRAPHY` has spherical geodesic edges, whereas a GeoJSON `Geometry` object explicitly has planar edges. To convert between these two types of edges, GoogleSQL adds additional points to the line where necessary so that the resulting sequence of edges remains within 10 meters of the original edge. See [`ST_ASGEOJSON`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_asgeojson) to format a `GEOGRAPHY` as GeoJSON. **Constraints** The JSON input is subject to the following constraints: - `ST_GEOGFROMGEOJSON` only accepts JSON geometry fragments and can't be used to ingest a whole JSON document. - The input JSON fragment must consist of a GeoJSON geometry type, which includes `Point`, `MultiPoint`, `LineString`, `MultiLineString`, `Polygon`, `MultiPolygon`, and `GeometryCollection`. Any other GeoJSON type such as `Feature` or `FeatureCollection` will result in an error. - A position in the `coordinates` member of a GeoJSON geometry type must consist of exactly two elements. The first is the longitude and the second is the latitude. Therefore, `ST_GEOGFROMGEOJSON` doesn't support the optional third element for a position in the `coordinates` member. **Return type** `GEOGRAPHY` ## `ST_GEOGFROMKML` ST_GEOGFROMKML(kml_geometry) Takes a `STRING` [KML geometry](https://developers.google.com/kml/documentation/kmlreference#geometry) and returns a `GEOGRAPHY`. The KML geomentry can include: - Point with coordinates element only - Linestring with coordinates element only - Polygon with boundary elements only - Multigeometry ## `ST_GEOGFROMTEXT` ST_GEOGFROMTEXT( wkt_string [ , oriented => value ] [ , planar => value ] [ , make_valid => value ] ) **Description** Converts a `STRING` [WKT](https://en.wikipedia.org/wiki/Well-known_text) geometry value into a `GEOGRAPHY` value. To format `GEOGRAPHY` value as WKT, use [`ST_ASTEXT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_astext). **Definitions** - `wkt_string`: A `STRING` value that contains the [WKT](https://en.wikipedia.org/wiki/Well-known_text) format. - `oriented`: A named argument with a `BOOL` literal. - If the value is `TRUE`, any polygons in the input are assumed to be oriented as follows: when traveling along the boundary of the polygon in the order of the input vertices, the interior of the polygon is on the left. This allows WKT to represent polygons larger than a hemisphere. See also [`ST_MAKEPOLYGONORIENTED`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_makepolygonoriented), which is similar to `ST_GEOGFROMTEXT` with `oriented=TRUE`. - If the value is `FALSE` or omitted, this function returns the polygon with the smaller area. - `planar`: A named argument with a `BOOL` literal. If the value is `TRUE`, the edges of the linestrings and polygons are assumed to use planar map semantics, rather than GoogleSQL default spherical geodesics semantics. - `make_valid`: A named argument with a `BOOL` literal. If the value is `TRUE`, the function attempts to repair polygons that don't conform to [Open Geospatial Consortium](https://www.ogc.org/standards/sfa) semantics. **Details** - The function doesn't support three-dimensional geometries that have a `Z` suffix, nor does it support linear referencing system geometries with an `M` suffix. - `oriented` and `planar` can't be `TRUE` at the same time. - `oriented` and `make_valid` can't be `TRUE` at the same time. ## `ST_GEOGFROMWKB` ST_GEOGFROMWKB( wkb_bytes_expression [ , oriented => value ] [ , planar => value ] [ , make_valid => value ] ) ST_GEOGFROMWKB( wkb_hex_string_expression [, oriented => value ] [, planar => value ] [, make_valid => value ] ) **Description** Converts an expression from a hexadecimal-text `STRING` or `BYTES` value into a `GEOGRAPHY` value. The expression must be in [WKB](https://en.wikipedia.org/wiki/Well-known_text#Well-known_binary) format. To format `GEOGRAPHY` as WKB, use [`ST_ASBINARY`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_asbinary). **Definitions** - `wkb_bytes_expression`: A `BYTES` value that contains the [WKB](https://en.wikipedia.org/wiki/Well-known_text#Well-known_binary) format. - `wkb_hex_string_expression`: A `STRING` value that contains the hexadecimal-encoded [WKB](https://en.wikipedia.org/wiki/Well-known_text#Well-known_binary) format. - `oriented`: A named argument with a `BOOL` literal. - If the value is `TRUE`, any polygons in the input are assumed to be oriented as follows: when traveling along the boundary of the polygon in the order of the input vertices, the interior of the polygon is on the left. This allows WKB to represent polygons larger than a hemisphere. See also [`ST_MAKEPOLYGONORIENTED`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_makepolygonoriented), which is similar to `ST_GEOGFROMWKB` with `oriented=TRUE`. - If the value is `FALSE` or omitted, this function returns the polygon with the smaller area. - `planar`: A named argument with a `BOOL` literal. If the value is `TRUE`, the edges of the linestrings and polygons are assumed to use planar map semantics, rather than GoogleSQL default spherical geodesics semantics. - `make_valid`: A named argument with a `BOOL` literal. If the value is `TRUE`, the function attempts to repair polygons that don't conform to [Open Geospatial Consortium](https://www.ogc.org/standards/sfa) semantics. **Details** - The function doesn't support three-dimensional geometries that have a `Z` suffix, nor does it support linear referencing system geometries with an `M` suffix. - `oriented` and `planar` can't be `TRUE` at the same time. - `oriented` and `make_valid` can't be `TRUE` at the same time. **Return type** `GEOGRAPHY` ## `ST_GEOGPOINT` ST_GEOGPOINT(longitude, latitude) **Description** Creates a `GEOGRAPHY` with a single point. `ST_GEOGPOINT` creates a point from the specified `FLOAT64` longitude (in degrees, negative west of the Prime Meridian, positive east) and latitude (in degrees, positive north of the Equator, negative south) parameters and returns that point in a `GEOGRAPHY` value. > [!NOTE] > **Note:** Some systems present latitude first; take care with argument order. **Constraints** - Longitudes outside the range \[-180, 180\] are allowed; `ST_GEOGPOINT` uses the input longitude modulo 360 to obtain a longitude within \[-180, 180\]. - Latitudes must be in the range \[-90, 90\]. Latitudes outside this range will result in an error. **Return type** Point `GEOGRAPHY` ## `ST_GEOGPOINTFROMGEOHASH` ST_GEOGPOINTFROMGEOHASH(geohash) **Description** Returns a `GEOGRAPHY` value that corresponds to a point in the middle of a bounding box defined in the [GeoHash](https://en.wikipedia.org/wiki/Geohash). **Return type** Point `GEOGRAPHY` ## `ST_GEOHASH` ST_GEOHASH(geography_expression[, maxchars]) **Description** Takes a single-point `GEOGRAPHY` and returns a [GeoHash](https://en.wikipedia.org/wiki/Geohash) representation of that `GEOGRAPHY` object. - `geography_expression`: Represents a `GEOGRAPHY` object. Only a `GEOGRAPHY` object that represents a single point is supported. If `ST_GEOHASH` is used over an empty `GEOGRAPHY` object, returns `NULL`. - `maxchars`: This optional `INT64` parameter specifies the maximum number of characters the hash will contain. Fewer characters corresponds to lower precision (or, described differently, to a bigger bounding box). `maxchars` defaults to 20 if not explicitly specified. A valid `maxchars` value is 1 to 20. Any value below or above is considered unspecified and the default of 20 is used. **Return type** `STRING` **Example** Returns a GeoHash of the Seattle Center with 10 characters of precision. SELECT ST_GEOHASH(ST_GEOGPOINT(-122.35, 47.62), 10) geohash /*---+ | geohash | +---+ | c22yzugqw7 | +---*/ ## `ST_GEOMETRYTYPE` ST_GEOMETRYTYPE(geography_expression) **Description** Returns the [Open Geospatial Consortium](https://www.ogc.org/standards/sfa) (OGC) geometry type that describes the input `GEOGRAPHY`. The OGC geometry type matches the types that are used in [WKT](https://en.wikipedia.org/wiki/Well-known_text) and [GeoJSON](https://en.wikipedia.org/wiki/GeoJSON) formats and printed for [ST_ASTEXT](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_astext) and [ST_ASGEOJSON](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_asgeojson). `ST_GEOMETRYTYPE` returns the OGC geometry type with the "ST_" prefix. `ST_GEOMETRYTYPE` returns the following given the type on the input: - Single point geography: Returns `ST_Point`. - Collection of only points: Returns `ST_MultiPoint`. - Single linestring geography: Returns `ST_LineString`. - Collection of only linestrings: Returns `ST_MultiLineString`. - Single polygon geography: Returns `ST_Polygon`. - Collection of only polygons: Returns `ST_MultiPolygon`. - Collection with elements of different dimensions, or the input is the empty geography: Returns `ST_GeometryCollection`. **Return type** `STRING` ## `ST_HAUSDORFFDISTANCE` ST_HAUSDORFFDISTANCE( geography_1, geography_2 [, directed => { TRUE | FALSE } ] ) **Description** Gets the discrete [Hausdorff distance](http://en.wikipedia.org/wiki/Hausdorff_distance), which is the greatest of all the distances from a discrete point in one geography to the closest point in another geography. **Definitions** - `geography_1`: A `GEOGRAPHY` value that represents the first geography. - `geography_2`: A `GEOGRAPHY` value that represents the second geography. - `directed`: A named argument with a `BOOL` value. Represents the type of computation to use on the input geographies. If this argument isn't specified, `directed => FALSE` is used by default. - `FALSE`: The largest Hausdorff distance found in (`geography_1`, `geography_2`) and (`geography_2`, `geography_1`). - `TRUE` (default): The Hausdorff distance for (`geography_1`, `geography_2`). **Details** If an input geography is `NULL`, the function returns `NULL`. **Return type** `FLOAT64` ## `ST_HAUSDORFFDWITHIN` ST_HAUSDORFFDWITHIN( geography_1, geography_2, distance [, directed => { TRUE | FALSE } ] ) **Description** Returns `TRUE` if the [Hausdorff distance](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_hausdorffdistance) between `geography_1` and `geography_2` is less than or equal to the distance given by the `distance` argument; otherwise, returns `FALSE`. **Definitions** - `geography_1`: A `GEOGRAPHY` value that represents the first geography. - `geography_2`: A `GEOGRAPHY` value that represents the second geography. - `distance`: A `FLOAT64` value that represents meters on the surface of the Earth. - `directed`: A named argument with a `BOOL` value. Represents the type of computation to use on the input geographies. If this argument isn't specified, `directed => FALSE` is used by default. - `FALSE`: The largest Hausdorff distance found in (`geography_1`, `geography_2`) and (`geography_2`, `geography_1`). - `TRUE` (default): The Hausdorff distance for (`geography_1`, `geography_2`). **Details** If an input geography is `NULL`, the function returns `NULL`. **Return type** `BOOL` **Examples** The following example checks whether the Hausdorff distance between the first and second geographies is less than or equal to 100,000 meters. SELECT ST_HAUSDORFFDWITHIN( ST_GEOGFROMTEXT('LINESTRING(10 1, 20 1)'), ST_GEOGFROMTEXT('LINESTRING(10 2, 20 2)'), 100000) AS is_close; /*---+ | is_close | +---+ | false | +---*/ ## `ST_INTERIORRINGS` ST_INTERIORRINGS(polygon_geography) **Description** Returns an array of linestring geographies that corresponds to the interior rings of a polygon geography. Each interior ring is the border of a hole within the input polygon. - If the input geography is a polygon, excludes the outermost ring of the polygon geography and returns the linestrings corresponding to the interior rings. - If the input is the full `GEOGRAPHY`, returns an empty array. - If the input polygon has no holes, returns an empty array. - Returns an error if the input isn't a single polygon. **Return type** `ARRAY` ## `ST_INTERSECTION` ST_INTERSECTION(geography_1, geography_2) **Description** Returns a `GEOGRAPHY` that represents the point set intersection of the two input `GEOGRAPHY`s. Thus, every point in the intersection appears in both `geography_1` and `geography_2`. If the two input `GEOGRAPHY`s are disjoint, that is, there are no points that appear in both input `geometry_1` and `geometry_2`, then an empty `GEOGRAPHY` is returned. See [ST_INTERSECTS](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_intersects), [ST_DISJOINT](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_disjoint) for related predicate functions. **Return type** `GEOGRAPHY` ## `ST_INTERSECTS` ST_INTERSECTS(geography_1, geography_2) **Description** Returns `TRUE` if the point set intersection of `geography_1` and `geography_2` is non-empty. Thus, this function returns `TRUE` if there is at least one point that appears in both input `GEOGRAPHY`s. If `ST_INTERSECTS` returns `TRUE`, it implies that [`ST_DISJOINT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_disjoint) returns `FALSE`. **Return type** `BOOL` ## `ST_INTERSECTSBOX` ST_INTERSECTSBOX(geography, lng1, lat1, lng2, lat2) **Description** Returns `TRUE` if `geography` intersects the rectangle between `[lng1, lng2]` and `[lat1, lat2]`. The edges of the rectangle follow constant lines of longitude and latitude. `lng1` and `lng2` specify the westmost and eastmost constant longitude lines that bound the rectangle, and `lat1` and `lat2` specify the minimum and maximum constant latitude lines that bound the rectangle. Specify all longitude and latitude arguments in degrees. **Constraints** The input arguments are subject to the following constraints: - Latitudes should be in the `[-90, 90]` degree range. - Longitudes should follow either of the following rules: - Both longitudes are in the `[-180, 180]` degree range. - One of the longitudes is in the `[-180, 180]` degree range, and `lng2 - lng1` is in the `[0, 360]` interval. **Return type** `BOOL` **Example** SELECT p, ST_INTERSECTSBOX(p, -90, 0, 90, 20) AS box1, ST_INTERSECTSBOX(p, 90, 0, -90, 20) AS box2 FROM UNNEST([ST_GEOGPOINT(10, 10), ST_GEOGPOINT(170, 10), ST_GEOGPOINT(30, 30)]) p /*---+---+---+ | p | box1 | box2 | +---+---+---+ | POINT(10 10) | TRUE | FALSE | | POINT(170 10) | FALSE | TRUE | | POINT(30 30) | FALSE | FALSE | +---+---+---*/ ## `ST_ISCLOSED` ST_ISCLOSED(geography_expression) **Description** Returns `TRUE` for a non-empty Geography, where each element in the Geography has an empty boundary. The boundary for each element can be defined with [`ST_BOUNDARY`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_boundary). - A point is closed. - A linestring is closed if the start and end points of the linestring are the same. - A polygon is closed only if it's a full polygon. - A collection is closed if and only if every element in the collection is closed. An empty `GEOGRAPHY` isn't closed. **Return type** `BOOL` ## `ST_ISCOLLECTION` ST_ISCOLLECTION(geography_expression) **Description** Returns `TRUE` if the total number of points, linestrings, and polygons is greater than one. An empty `GEOGRAPHY` isn't a collection. **Return type** `BOOL` ## `ST_ISEMPTY` ST_ISEMPTY(geography_expression) **Description** Returns `TRUE` if the given `GEOGRAPHY` is empty; that is, the `GEOGRAPHY` doesn't contain any points, lines, or polygons. > [!NOTE] > **Note:** An empty `GEOGRAPHY` isn't associated with a particular geometry shape. For example, the results of expressions `ST_GEOGFROMTEXT('POINT EMPTY')` and `ST_GEOGFROMTEXT('GEOMETRYCOLLECTION EMPTY')` are identical. **Return type** `BOOL` ## `ST_ISRING` ST_ISRING(geography_expression) **Description** Returns `TRUE` if the input `GEOGRAPHY` is a linestring and if the linestring is both [`ST_ISCLOSED`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_isclosed) and simple. A linestring is considered simple if it doesn't pass through the same point twice (with the exception of the start and endpoint, which may overlap to form a ring). An empty `GEOGRAPHY` isn't a ring. **Return type** `BOOL` ## `ST_LENGTH` ST_LENGTH(geography_expression[, use_spheroid]) **Description** Returns the total length in meters of the lines in the input `GEOGRAPHY`. If `geography_expression` is a point or a polygon, returns zero. If `geography_expression` is a collection, returns the length of the lines in the collection; if the collection doesn't contain lines, returns zero. The optional `use_spheroid` parameter determines how this function measures distance. If `use_spheroid` is `FALSE`, the function measures distance on the surface of a perfect sphere. The `use_spheroid` parameter currently only supports the value `FALSE`. The default value of `use_spheroid` is `FALSE`. **Return type** `FLOAT64` ## `ST_LINEINTERPOLATEPOINT` ST_LINEINTERPOLATEPOINT(linestring_geography, fraction) **Description** Gets a point at a specific fraction in a linestring `GEOGRAPHY` value. **Definitions** - `linestring_geography`: A linestring `GEOGRAPHY` on which the target point is located. - `fraction`: A `FLOAT64` value that represents a fraction along the linestring `GEOGRAPHY` where the target point is located. This should be an inclusive value between `0` (start of the linestring) and `1` (end of the linestring). **Details** - Returns `NULL` if any input argument is `NULL`. - Returns an empty geography if `linestring_geography` is an empty geography. - Returns an error if `linestring_geography` isn't a linestring or an empty geography, or if `fraction` is outside the `[0, 1]` range. **Return Type** `GEOGRAPHY` ## `ST_LINELOCATEPOINT` ST_LINELOCATEPOINT(linestring_geography, point_geography) **Description** Gets a section of a linestring between the start point and a selected point (a point on the linestring closest to the `point_geography` argument). Returns the percentage that this section represents in the linestring. Details: - To select a point on the linestring `GEOGRAPHY` (`linestring_geography`), this function takes a point `GEOGRAPHY` (`point_geography`) and finds the [closest point](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_closestpoint) to it on the linestring. - If two points on `linestring_geography` are an equal distance away from `point_geography`, it isn't guaranteed which one will be selected. - The return value is an inclusive value between 0 and 1 (0-100%). - If the selected point is the start point on the linestring, function returns 0 (0%). - If the selected point is the end point on the linestring, function returns 1 (100%). `NULL` and error handling: - Returns `NULL` if any input argument is `NULL`. - Returns an error if `linestring_geography` isn't a linestring or if `point_geography` isn't a point. **Return Type** `FLOAT64` ## `ST_LINESUBSTRING` ST_LINESUBSTRING(linestring_geography, start_fraction, end_fraction); **Description** Gets a segment of a linestring at a specific starting and ending fraction. **Definitions** - `linestring_geography`: The LineString `GEOGRAPHY` value that represents the linestring from which to extract a segment. - `start_fraction`: `FLOAT64` value that represents the starting fraction of the total length of `linestring_geography`. This must be an inclusive value between 0 and 1 (0-100%). - `end_fraction`: `FLOAT64` value that represents the ending fraction of the total length of `linestring_geography`. This must be an inclusive value between 0 and 1 (0-100%). **Details** `end_fraction` must be greater than or equal to `start_fraction`. If `start_fraction` and `end_fraction` are equal, a linestring with only one point is produced. **Return type** - LineString `GEOGRAPHY` if the resulting geography has more than one point. - Point `GEOGRAPHY` if the resulting geography has only one point. ## `ST_MAKELINE` ST_MAKELINE(geography_1, geography_2) ST_MAKELINE(array_of_geography) **Description** Creates a `GEOGRAPHY` with a single linestring by concatenating the point or line vertices of each of the input `GEOGRAPHY`s in the order they are given. `ST_MAKELINE` comes in two variants. For the first variant, input must be two `GEOGRAPHY`s. For the second, input must be an `ARRAY` of type `GEOGRAPHY`. In either variant, each input `GEOGRAPHY` must consist of one of the following values: - Exactly one point. - Exactly one linestring. For the first variant of `ST_MAKELINE`, if either input `GEOGRAPHY` is `NULL`, `ST_MAKELINE` returns `NULL`. For the second variant, if input `ARRAY` or any element in the input `ARRAY` is `NULL`, `ST_MAKELINE` returns `NULL`. **Constraints** Every edge must span strictly less than 180 degrees. > [!NOTE] > **Note:** The GoogleSQL snapping process may discard sufficiently short edges and snap the two endpoints together. For instance, if two input `GEOGRAPHY`s each contain a point and the two points are separated by a distance less than the snap radius, the points will be snapped together. In such a case the result will be a `GEOGRAPHY` with exactly one point. **Return type** - LineString `GEOGRAPHY` - MultiLineString `GEOGRAPHY` (if the snapped input contains self-overlapping lines) - Point `GEOGRAPHY` (if the input contains a single point only) **Example** The following example constructs different types of `GEOGRAPHY`s: WITH segments AS ( SELECT 1 AS id, [ST_GEOGPOINT(1, 2), ST_GEOGPOINT(2, 3)] AS geos UNION ALL SELECT 2, [ST_GEOGPOINT(1, 2), ST_GEOGPOINT(1, 2)] UNION ALL SELECT 3, [ ST_MAKELINE(ST_GEOGPOINT(1, 2), ST_GEOGPOINT(2, 3)), ST_MAKELINE(ST_GEOGPOINT(2, 3), ST_GEOGPOINT(3, 4)), ST_MAKELINE(ST_GEOGPOINT(3, 4), ST_GEOGPOINT(4, 5))] UNION ALL SELECT 4, [ ST_MAKELINE(ST_GEOGPOINT(1, 0), ST_GEOGPOINT(10, 0)), ST_MAKELINE(ST_GEOGPOINT(10, 0), ST_GEOGPOINT(5, 0)), ST_MAKELINE(ST_GEOGPOINT(5, 0), ST_GEOGPOINT(5, 4))] ) SELECT id, ST_MAKELINE(geos) AS constructed_line FROM segments; /*---+---+ | id | constructed_line | +---+---+ | 1 | LINESTRING(1 2, 2 3) | | 2 | POINT(1 2) | | 3 | LINESTRING(1 2, 2 3, 3 4, 4 5) | | 4 | MULTILINESTRING((1 0, 5 0, 10 0), (5 0, 5 4)) | +---+---*/ ## `ST_MAKEPOLYGON` ST_MAKEPOLYGON(polygon_shell[, array_of_polygon_holes]) **Description** Creates a `GEOGRAPHY` containing a single polygon from linestring inputs, where each input linestring is used to construct a polygon ring. `ST_MAKEPOLYGON` comes in two variants. For the first variant, the input linestring is provided by a single `GEOGRAPHY` containing exactly one linestring. For the second variant, the input consists of a single `GEOGRAPHY` and an array of `GEOGRAPHY`s, each containing exactly one linestring. The first `GEOGRAPHY` in either variant is used to construct the polygon shell. Additional `GEOGRAPHY`s provided in the input `ARRAY` specify a polygon hole. For every input `GEOGRAPHY` containing exactly one linestring, the following must be true: - The linestring must consist of at least three distinct vertices. - The linestring must be closed: that is, the first and last vertex have to be the same. If the first and last vertex differ, the function constructs a final edge from the first vertex to the last. For the first variant of `ST_MAKEPOLYGON`, if either input `GEOGRAPHY` is `NULL`, `ST_MAKEPOLYGON` returns `NULL`. For the second variant, if input `ARRAY` or any element in the `ARRAY` is `NULL`, `ST_MAKEPOLYGON` returns `NULL`. > [!NOTE] > **Note:** `ST_MAKEPOLYGON` accepts an empty `GEOGRAPHY` as input. `ST_MAKEPOLYGON` interprets an empty `GEOGRAPHY` as having an empty linestring, which will create a full loop: that is, a polygon that covers the entire Earth. **Constraints** Together, the input rings must form a valid polygon: - The polygon shell must cover each of the polygon holes. - There can be only one polygon shell (which has to be the first input ring). This implies that polygon holes can't be nested. - Polygon rings may only intersect in a vertex on the boundary of both rings. Every edge must span strictly less than 180 degrees. Each polygon ring divides the sphere into two regions. The first input linesting to `ST_MAKEPOLYGON` forms the polygon shell, and the interior is chosen to be the smaller of the two regions. Each subsequent input linestring specifies a polygon hole, so the interior of the polygon is already well-defined. In order to define a polygon shell such that the interior of the polygon is the larger of the two regions, see [`ST_MAKEPOLYGONORIENTED`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_makepolygonoriented). > [!NOTE] > **Note:** The GoogleSQL snapping process may discard sufficiently short edges and snap the two endpoints together. Hence, when vertices are snapped together, it's possible that a polygon hole that's sufficiently small may disappear, or the output `GEOGRAPHY` may contain only a line or a point. **Return type** `GEOGRAPHY` ## `ST_MAKEPOLYGONORIENTED` ST_MAKEPOLYGONORIENTED(array_of_geography) **Description** Like `ST_MAKEPOLYGON`, but the vertex ordering of each input linestring determines the orientation of each polygon ring. The orientation of a polygon ring defines the interior of the polygon as follows: if someone walks along the boundary of the polygon in the order of the input vertices, the interior of the polygon is on the left. This applies for each polygon ring provided. This variant of the polygon constructor is more flexible since `ST_MAKEPOLYGONORIENTED` can construct a polygon such that the interior is on either side of the polygon ring. However, proper orientation of polygon rings is critical in order to construct the desired polygon. If the input `ARRAY` or any element in the `ARRAY` is `NULL`, `ST_MAKEPOLYGONORIENTED` returns `NULL`. > [!NOTE] > **Note:** The input argument for `ST_MAKEPOLYGONORIENTED` may contain an empty `GEOGRAPHY`. `ST_MAKEPOLYGONORIENTED` interprets an empty `GEOGRAPHY` as having an empty linestring, which will create a full loop: that is, a polygon that covers the entire Earth. **Constraints** Together, the input rings must form a valid polygon: - The polygon shell must cover each of the polygon holes. - There must be only one polygon shell, which must to be the first input ring. This implies that polygon holes can't be nested. - Polygon rings may only intersect in a vertex on the boundary of both rings. Every edge must span strictly less than 180 degrees. `ST_MAKEPOLYGONORIENTED` relies on the ordering of the input vertices of each linestring to determine the orientation of the polygon. This applies to the polygon shell and any polygon holes. `ST_MAKEPOLYGONORIENTED` expects all polygon holes to have the opposite orientation of the shell. See [`ST_MAKEPOLYGON`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_makepolygon) for an alternate polygon constructor, and other constraints on building a valid polygon. > [!NOTE] > **Note:** Due to the GoogleSQL snapping process, edges with a sufficiently short length will be discarded and the two endpoints will be snapped to a single point. Therefore, it's possible that vertices in a linestring may be snapped together such that one or more edge disappears. Hence, it's possible that a polygon hole that's sufficiently small may disappear, or the resulting `GEOGRAPHY` may contain only a line or a point. **Return type** `GEOGRAPHY` ## `ST_MAXDISTANCE` ST_MAXDISTANCE(geography_1, geography_2[, use_spheroid]) Returns the longest distance in meters between two non-empty `GEOGRAPHY`s; that is, the distance between two vertices where the first vertex is in the first `GEOGRAPHY`, and the second vertex is in the second `GEOGRAPHY`. If `geography_1` and `geography_2` are the same `GEOGRAPHY`, the function returns the distance between the two most distant vertices in that `GEOGRAPHY`. If either of the input `GEOGRAPHY`s is empty, `ST_MAXDISTANCE` returns `NULL`. The optional `use_spheroid` parameter determines how this function measures distance. If `use_spheroid` is `FALSE`, the function measures distance on the surface of a perfect sphere. The `use_spheroid` parameter currently only supports the value `FALSE`. The default value of `use_spheroid` is `FALSE`. **Return type** `FLOAT64` ## `ST_NPOINTS` ST_NPOINTS(geography_expression) **Description** An alias of [ST_NUMPOINTS](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_numpoints). ## `ST_NUMGEOMETRIES` ST_NUMGEOMETRIES(geography_expression) **Description** Returns the number of geometries in the input `GEOGRAPHY`. For a single point, linestring, or polygon, `ST_NUMGEOMETRIES` returns `1`. For any collection of geometries, `ST_NUMGEOMETRIES` returns the number of geometries making up the collection. `ST_NUMGEOMETRIES` returns `0` if the input is the empty `GEOGRAPHY`. **Return type** `INT64` ## `ST_NUMPOINTS` ST_NUMPOINTS(geography_expression) **Description** Returns the number of vertices in the input `GEOGRAPHY`. This includes the number of points, the number of linestring vertices, and the number of polygon vertices. > [!NOTE] > **Note:** The first and last vertex of a polygon ring are counted as distinct vertices. **Return type** `INT64` ## `ST_PERIMETER` ST_PERIMETER(geography_expression[, use_spheroid]) **Description** Returns the length in meters of the boundary of the polygons in the input `GEOGRAPHY`. If `geography_expression` is a point or a line, returns zero. If `geography_expression` is a collection, returns the perimeter of the polygons in the collection; if the collection doesn't contain polygons, returns zero. The optional `use_spheroid` parameter determines how this function measures distance. If `use_spheroid` is `FALSE`, the function measures distance on the surface of a perfect sphere. The `use_spheroid` parameter currently only supports the value `FALSE`. The default value of `use_spheroid` is `FALSE`. **Return type** `FLOAT64` ## `ST_POINTN` ST_POINTN(linestring_geography, index) **Description** Returns the Nth point of a linestring geography as a point geography, where N is the index. The index is 1-based. Negative values are counted backwards from the end of the linestring, so that -1 is the last point. Returns an error if the input isn't a linestring, if the input is empty, or if there is no vertex at the given index. **Return Type** Point `GEOGRAPHY` ## `ST_REGIONSTATS` ST_REGIONSTATS( geography, raster_id [ , [band => ] value ] [ , include => value ] [ , options => value ] ) **Description** Returns statistics summarizing the pixel values of the raster image referenced by `raster_id` that intersect with `geography`. The statistics include the count, minimum, maximum, sum, standard deviation, mean, and area of the valid pixels of the raster band named `band_name`. computes the results of the function call. - `geography`: A `GEOGRAPHY` value to intersect with the raster image. - `raster_id`: A string that identifies a raster image. The following formats are supported: - A URI from an image table provided by in (formerly Analytics Hub). - A URI for a readable GeoTIFF raster file. - A asset path that references public catalog data or project-owned assets with read access. - `band_name`: A string in one of the following formats: - A single band within the raster image specified by `raster_id`. - A formula to compute a value from the available bands in the raster image. The formula uses the [image expression syntax](https://developers.google.com/earth-engine/guides/image_math#expressions). Bands can be referenced by their name, `band_name`, in expressions. If you don't specify a band, the first band of the image is used. - `include`: An optional string formula that uses the [image expression syntax](https://developers.google.com/earth-engine/guides/image_math#expressions) to compute a pixel weight. The formula should return values from 0 to 1. Values outside this range are set to the nearest limit, either 0 or 1. A value of 0 means that the pixel is *invalid* and it's excluded from analysis. A positive value means that a pixel is *valid* . Values between 0 and 1 represent proportional weights for calculations, such as weighted means. For more information, see [Pixel weights](https://docs.cloud.google.com/bigquery/docs/raster-data#pixel-weights). - `options`: A case-sensitive `JSON` value that specifies options as key value pairs, such as `JSON '{"scale": 10.0}'`. The following options are supported: - `"scale"`: The scale in meters at which raster processing should be performed. By default, the scale is determined from the dataset. For more information, see [Pixel size and scale of analysis](https://docs.cloud.google.com/bigquery/docs/raster-data#pixel-scale). **Return type** STRUCT< count , min FLOAT64, max FLOAT64, stdDev FLOAT64, sum FLOAT64, mean FLOAT64, area FLOAT64 > Return values: - `count`: The number of pixels that intersect with `geography`, including partially intersecting pixels. - `min`: The minimum band value of the valid pixels that intersect with `geography`. - `max`: The maximum band value of the valid pixels that intersect with `geography`. - `stdDev`: The weighted standard deviation of the band values of the pixels that intersect with `geography`. - `sum`: The weighted sum of the band values of the pixels that intersect with `geography`. - `mean`: The weighted mean of the band values of the pixels that intersect with `geography`. - `area`: The sum of the area of valid pixels, or parts of valid pixels, that intersect `geography`. - For point geometries, the area of the entire intersecting pixel is used. - For polygon geometries, partial intersections are weighted by the proportional overlap of the polygon with the pixel. - The `area` value might differ from the area returned by the `ST_AREA` function. The difference is typically by less than 1% but increases for polygons that are smaller than the pixel size in the raster image. If no valid pixels intersect `geography`, then the function returns 0 for all statistics. ## `ST_SIMPLIFY` ST_SIMPLIFY(geography, tolerance_meters) **Description** Returns a simplified version of `geography`, the given input `GEOGRAPHY`. The input `GEOGRAPHY` is simplified by replacing nearly straight chains of short edges with a single long edge. The input `geography` will not change by more than the tolerance specified by `tolerance_meters`. Thus, simplified edges are guaranteed to pass within `tolerance_meters` of the *original* positions of all vertices that were removed from that edge. The given `tolerance_meters` is in meters on the surface of the Earth. Note that `ST_SIMPLIFY` preserves topological relationships, which means that no new crossing edges will be created and the output will be valid. For a large enough tolerance, adjacent shapes may collapse into a single object, or a shape could be simplified to a shape with a smaller dimension. **Constraints** For `ST_SIMPLIFY` to have any effect, `tolerance_meters` must be non-zero. `ST_SIMPLIFY` returns an error if the tolerance specified by `tolerance_meters` is one of the following: - A negative tolerance. - Greater than \~7800 kilometers. **Return type** `GEOGRAPHY` ## `ST_SNAPTOGRID` ST_SNAPTOGRID(geography_expression, grid_size) **Description** Returns the input `GEOGRAPHY`, where each vertex has been snapped to a longitude/latitude grid. The grid size is determined by the `grid_size` parameter which is given in degrees. **Constraints** Arbitrary grid sizes aren't supported. The `grid_size` parameter is rounded so that it's of the form `10^n`, where `-10 < n < 0`. **Return type** `GEOGRAPHY` ## `ST_STARTPOINT` ST_STARTPOINT(linestring_geography) **Description** Returns the first point of a linestring geography as a point geography. Returns an error if the input isn't a linestring or if the input is empty. **Return Type** Point `GEOGRAPHY` **Example** SELECT ST_STARTPOINT(ST_GEOGFROMTEXT('LINESTRING(1 1, 2 1, 3 2, 3 3)')) first /*---+ | first | +---+ | POINT(1 1) | +---*/ ## `ST_TOUCHES` ST_TOUCHES(geography_1, geography_2) **Description** Returns `TRUE` provided the following two conditions are satisfied: 1. `geography_1` intersects `geography_2`. 2. The interior of `geography_1` and the interior of `geography_2` are disjoint. **Return type** `BOOL` ## `ST_UNION` ST_UNION(geography_1, geography_2) ST_UNION(array_of_geography) **Description** Returns a `GEOGRAPHY` that represents the point set union of all input `GEOGRAPHY`s. `ST_UNION` comes in two variants. For the first variant, input must be two `GEOGRAPHY`s. For the second, the input is an `ARRAY` of type `GEOGRAPHY`. For the first variant of `ST_UNION`, if an input `GEOGRAPHY` is `NULL`, `ST_UNION` returns `NULL`. For the second variant, if the input `ARRAY` value is `NULL`, `ST_UNION` returns `NULL`. For a non-`NULL` input `ARRAY`, the union is computed and `NULL` elements are ignored so that they don't affect the output. See [`ST_UNION_AGG`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_union_agg) for the aggregate version of `ST_UNION`. **Return type** `GEOGRAPHY` **Example** SELECT ST_UNION( ST_GEOGFROMTEXT('LINESTRING(-122.12 47.67, -122.19 47.69)'), ST_GEOGFROMTEXT('LINESTRING(-122.12 47.67, -100.19 47.69)') ) AS results /*---+ | results | +---+ | LINESTRING(-100.19 47.69, -122.12 47.67, -122.19 47.69) | +---*/ ## `ST_UNION_AGG` ST_UNION_AGG(geography) **Description** Returns a `GEOGRAPHY` that represents the point set union of all input `GEOGRAPHY`s. `ST_UNION_AGG` ignores `NULL` input `GEOGRAPHY` values. See [`ST_UNION`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_union) for the non-aggregate version of `ST_UNION_AGG`. **Return type** `GEOGRAPHY` **Example** SELECT ST_UNION_AGG(items) AS results FROM UNNEST([ ST_GEOGFROMTEXT('LINESTRING(-122.12 47.67, -122.19 47.69)'), ST_GEOGFROMTEXT('LINESTRING(-122.12 47.67, -100.19 47.69)'), ST_GEOGFROMTEXT('LINESTRING(-122.12 47.67, -122.19 47.69)')]) as items; /*---+ | results | +---+ | LINESTRING(-100.19 47.69, -122.12 47.67, -122.19 47.69) | +---*/ ## `ST_WITHIN` ST_WITHIN(geography_1, geography_2) **Description** Returns `TRUE` if no point of `geography_1` is outside of `geography_2` and the interiors of `geography_1` and `geography_2` intersect. Given two geographies `a` and `b`, `ST_WITHIN(a, b)` returns the same result as [`ST_CONTAINS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/geography_functions#st_contains)`(b, a)`. Note the opposite order of arguments. **Return type** `BOOL`