Geography functions in Standard SQL

The geography functions operate on or generate BigQuery GEOGRAPHY values. The signature of any geography function starts with ST_. BigQuery supports the following functions that can be used to analyze geographical data, determine spatial relationships between geographical features, and construct or manipulate GEOGRAPHYs.

All BigQuery geography functions return NULL if any input argument is NULL.

Category	Functions	Description
Constructors	`ST_GEOGPOINT` `ST_MAKELINE` `ST_MAKEPOLYGON` `ST_MAKEPOLYGONORIENTED`	Functions that build new geography values from coordinates or existing geographies.
Parsers	`ST_GEOGFROMGEOJSON` `ST_GEOGFROMTEXT` `ST_GEOGFROMWKB` `ST_GEOGPOINTFROMGEOHASH`	Functions that create geographies from an external format such as WKT and GeoJSON.
Formatters	`ST_ASBINARY` `ST_ASGEOJSON` `ST_ASTEXT` `ST_GEOHASH`	Functions that export geographies to an external format such as WKT.
Transformations	`ST_BOUNDARY` `ST_CENTROID` `ST_CENTROID_AGG` (Aggregate) `ST_CLOSESTPOINT` `ST_CONVEXHULL` `ST_DIFFERENCE` `ST_DUMP` `ST_INTERSECTION` `ST_SIMPLIFY` `ST_SNAPTOGRID` `ST_UNION` `ST_UNION_AGG` (Aggregate)	Functions that generate a new geography based on input.
Accessors	`ST_DIMENSION` `ST_DUMP` `ST_ISCOLLECTION` `ST_ISEMPTY` `ST_NPOINTS` `ST_NUMPOINTS` `ST_X` `ST_Y`	Functions that provide access to properties of a geography without side-effects.
Predicates	`ST_CONTAINS` `ST_COVEREDBY` `ST_COVERS` `ST_DISJOINT` `ST_DWITHIN` `ST_EQUALS` `ST_INTERSECTS` `ST_INTERSECTSBOX` `ST_TOUCHES` `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_AREA` `ST_DISTANCE` `ST_LENGTH` `ST_MAXDISTANCE` `ST_PERIMETER`	Functions that compute measurements of one or more geographies.
Clustering	`ST_CLUSTERDBSCAN`	Functions that perform clustering on geographies.

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 does not 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 representation of an input GEOGRAPHY.

See ST_GEOGFROMWKB to construct a GEOGRAPHY from WKB.

Return type

BYTES

ST_ASGEOJSON

ST_ASGEOJSON(geography_expression)

Description

Returns the RFC 7946 compliant GeoJSON representation of the input GEOGRAPHY.

A BigQuery GEOGRAPHY has spherical geodesic edges, whereas a GeoJSON Geometry object explicitly has planar edges. To convert between these two types of edges, BigQuery 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 to construct a GEOGRAPHY from GeoJSON.

Return type

STRING

ST_ASTEXT

ST_ASTEXT(geography_expression)

Description

Returns the WKT representation of an input GEOGRAPHY.

See ST_GEOGFROMTEXT to construct a GEOGRAPHY from WKT.

Return type

STRING

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_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 cannot 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

GEOGRAPHY

ST_CENTROID_AGG

ST_CENTROID_AGG(geography)

Description

Computes the centroid of the set of input GEOGRAPHYs as a single point GEOGRAPHY.

The centroid over the set of input GEOGRAPHYs is the weighted average of the centroid of each individual GEOGRAPHY. Only the GEOGRAPHYs with the highest dimension present in the input contribute to the centroid of the entire set. For example, if the input contains both GEOGRAPHYs with lines and GEOGRAPHYs with only points, ST_CENTROID_AGG returns the weighted average of the GEOGRAPHYs with lines, since those have maximal dimension. In this example, ST_CENTROID_AGG ignores GEOGRAPHYs with only points when calculating the aggregate centroid.

ST_CENTROID_AGG ignores NULL input GEOGRAPHY values.

See ST_CENTROID for the non-aggregate version of ST_CENTROID_AGG and the definition of centroid for an individual GEOGRAPHY value.

Return type

GEOGRAPHY

Example

The following queries compute the aggregate centroid over a set of GEOGRAPHY values. The input to the first query contains only points, and therefore each value contribute to the aggregate centroid. Also notice that ST_CENTROID_AGG is not equivalent to calling ST_CENTROID on the result of ST_UNION_AGG; duplicates are removed by the union, unlike ST_CENTROID_AGG. The input to the second query has mixed dimensions, and only values with the highest dimension in the set, the lines, affect the aggregate centroid.

SELECT ST_CENTROID_AGG(points) AS st_centroid_agg,
ST_CENTROID(ST_UNION_AGG(points)) AS centroid_of_union
FROM UNNEST([ST_GEOGPOINT(1, 5),
             ST_GEOGPOINT(1, 2),
             ST_GEOGPOINT(1, -1),
             ST_GEOGPOINT(1, -1)]) points;

+---------------------------+-------------------+
| st_centroid_agg           | centroid_of_union |
+---------------------------+-------------------+
| POINT(1 1.24961422620969) | POINT(1 2)        |
+---------------------------+-------------------+

SELECT ST_CENTROID_AGG(points) AS st_centroid_agg
FROM UNNEST([ST_GEOGPOINT(50, 26),
             ST_GEOGPOINT(34, 33.3),
             ST_GEOGFROMTEXT('LINESTRING(0 -1, 0 1)')
             ST_GEOGFROMTEXT('LINESTRING(0 1, 0 3)')]) points;

+-----------------+
| st_centroid_agg |
+-----------------+
| POINT(0 1)      |
+-----------------+

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 GEOGRAPHYs 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

GEOGRAPHY

ST_CLUSTERDBSCAN

ST_CLUSTERDBSCAN(geography_column, epsilon, minimum_geographies) OVER (...)

Performs DBSCAN clustering on a column of geographies. Returns a 0-based cluster number.

Input parameters

geography_column: A column of GEOGRAPHYs that is clustered.
epsilon: The epsilon that specifies the radius, measured in meters, around a core value. Non-negative FLOAT64 value.
minimum_geographies: Specifies the minimum number of geographies in a single cluster. Only dense input forms a cluster, otherwise it is classified as noise. Non-negative INT64 value.
OVER: Specifies a window. See Analytic Functions. Usage of the OVER clause with ST_CLUSTERDBSCAN is compatible with the following:
- PARTITION BY: Optional.
- ORDER BY: Optional.
- window_frame_clause: Disallowed.

Geography types and the DBSCAN algorithm

The DBSCAN algorithm identifies high-density clusters of data and marks outliers in low-density areas of noise. Geographies passed in through geography_column are classified in one of three ways by the DBSCAN algorithm:

Core value: A geography is a core value if it is within epsilon distance of minimum_geographies geographies, including itself. The core value starts a new cluster, or is added to the same cluster as a core value within epsilon distance. Core values are grouped in a cluster together with all other core and border values that are within epsilon distance.
Border value: A geography is a border value if it is within epsilon distance of a core value. It is added to the same cluster as a core value within epsilon distance. A border value may be within epsilon distance of more than one cluster. In this case, it may be arbitrarily assigned to either cluster and the function will produce the same result in subsequent calls.
Noise: A geography is noise if it is neither a core nor a border value. Noise values are assigned to a NULL cluster. An empty GEOGRAPHY is always classified as noise.

Constraints

The argument minimum_geographies is a non-negative INT64and epsilon is a non-negative FLOAT64.
The EMPTY geography object cannot join any cluster.
Multiple clustering assignments could be possible for a border value. If a geography is a border value, ST_CLUSTERDBSCAN will assign it to an arbitrary valid cluster.

Return type

INT64 for each geography in the geography column.

Examples

This example performs DBSCAN clustering with a radius of 100,000 meters with a minimum_geographies argument of 1. The geographies being analyzed are a mixture of points, lines, and polygons.

WITH Geos as
  (SELECT 1 as row_id, st_geogfromtext('point empty') as geo UNION ALL
    SELECT 2, st_geogfromtext('multipoint(1 1, 2 2, 4 4, 5 2)') UNION ALL
    SELECT 3, st_geogfromtext('point(14 15)') UNION ALL
    SELECT 4, st_geogfromtext('linestring(40 1, 42 34, 44 39)') UNION ALL
    SELECT 5, st_geogfromtext('polygon((40 2, 40 1, 41 2, 40 2))'))
SELECT row_id, geo, ST_CLUSTERDBSCAN(geo, 1e5, 1) OVER () AS cluster_num FROM
Geos ORDER BY row_id
+--------+-----------------------------------+-------------+
| row_id |                geo                | cluster_num |
+--------+-----------------------------------+-------------+
|      1 |          GEOMETRYCOLLECTION EMPTY |        NULL |
|      2 |    MULTIPOINT(1 1, 2 2, 5 2, 4 4) |           0 |
|      3 |                      POINT(14 15) |           1 |
|      4 |    LINESTRING(40 1, 42 34, 44 39) |           2 |
|      5 | POLYGON((40 2, 40 1, 41 2, 40 2)) |           2 |
+--------+-----------------------------------+-------------+

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: A GEOGRAPHY does not contain its own boundary. Compare with 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

Examples

The convex hull returned by ST_CONVEXHULL can be a point, linestring, or a polygon, depending on the input.

WITH Geographies AS
 (SELECT ST_GEOGFROMTEXT('POINT(1 1)') AS g UNION ALL
  SELECT ST_GEOGFROMTEXT('LINESTRING(1 1, 2 2)') AS g UNION ALL
  SELECT ST_GEOGFROMTEXT('MULTIPOINT(2 11, 4 12, 0 15, 1 9, 1 12)') AS g)
SELECT
  g AS input_geography,
  ST_CONVEXHULL(g) AS convex_hull
FROM Geographies;

+-----------------------------------------+--------------------------------------------------------+
|             input_geography             |                      convex_hull                       |
+-----------------------------------------+--------------------------------------------------------+
|                              POINT(1 1) |                             POINT(0.999999999999943 1) |
|                    LINESTRING(1 1, 2 2) | LINESTRING(2 2, 1.49988573656168 1.5000570914792, 1 1) |
| MULTIPOINT(1 9, 4 12, 2 11, 1 12, 0 15) |                        POLYGON((1 9, 4 12, 0 15, 1 9)) |
+-----------------------------------------+--------------------------------------------------------+

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 GEOGRAPHYs a and b, ST_COVEREDBY(a, b) returns the same result as 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.

If geometry_1 is completely contained in geometry_2, then ST_DIFFERENCE returns an empty GEOGRAPHY.

Constraints

The underlying geometric objects that a BigQuery 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

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.

Return type

BOOL

ST_DISTANCE

ST_DISTANCE(geography_1, geography_2[, use_spheroid])

Description

Returns the shortest distance in meters between two non-empty GEOGRAPHYs.

If either of the input GEOGRAPHYs 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.

The use_spheroid parameter currently only supports the value FALSE. The default value of use_spheroid is FALSE.

Return type

FLOAT64

ST_DUMP

ST_DUMP(geography[, dimension])

Description

Returns an ARRAY of simple GEOGRAPHYs 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 GEOGRAPHYs of the corresponding dimension. A dimension of -1 is equivalent to omitting dimension.

Return Type

ARRAY

Examples

The following example shows how ST_DUMP returns the simple geographies within a complex geography.

WITH example AS (
  SELECT ST_GeogFromText('POINT(0 0)') AS geography
  UNION ALL
  SELECT ST_GeogFromText('MULTIPOINT(0 0, 1 1)') AS geography
  UNION ALL
  SELECT ST_GeogFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(1 2, 2 1))'))
SELECT
  geography AS original_geography,
  ST_DUMP(geography) AS dumped_geographies
FROM example

+-------------------------------------+-------------------------------+
|         original_geographies        |      dumped_geographies       |
+-------------------------------------+-------------------------------+
| POINT(0 0)                          | [POINT(0 0)]                  |
+-------------------------------------+-------------------------------+
| MULTIPOINT(0 0, 1 1)                | [POINT(0 0), POINT(1 1)]      |
+-------------------------------------+-------------------------------+
| GEOMETRYCOLLECTION(POINT(0 0),      | [POINT(0 0),                  |
|   LINESTRING(1 2, 2 1))             |   LINESTRING(1 2, 2 1)]       |
+-------------------------------------+-------------------------------+

The following example shows how ST_DUMP with the dimension argument only returns simple geographies of the given dimension.

WITH example AS (
  SELECT ST_GeogFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(1 2, 2 1))') AS geography)
SELECT
  geography AS original_geography,
  ST_DUMP(geography, 1) AS dumped_geographies
FROM example

+-------------------------------------+------------------------------+
|         original_geographies        |      dumped_geographies      |
+-------------------------------------+------------------------------+
| GEOMETRYCOLLECTION(POINT(0 0),      | [LINESTRING(1 2, 2 1)]       |
|   LINESTRING(1 2, 2 1))             |                              |
+-------------------------------------+------------------------------+

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_EQUALS

ST_EQUALS(geography_1, geography_2)

Description

Returns TRUE if geography_1 and geography_2 represent the same

GEOGRAPHY value. More precisely, this means that one of the following conditions holds: + ST_COVERS(geography_1, geography_2) = TRUE and ST_COVERS(geography_2, geography_1) = TRUE + Both geography_1 and geography_2 are empty.

Therefore, two GEOGRAPHYs may be equal even if the ordering of points or vertices differ, as long as they still represent the same geometric structure.

Constraints

ST_EQUALS is not guaranteed to be a transitive function.

Return type

BOOL

ST_GEOGFROMGEOJSON

ST_GEOGFROMGEOJSON(geojson_string [, make_valid => constant_expression])

Description

Returns a GEOGRAPHY value that corresponds to the input GeoJSON representation.

ST_GEOGFROMGEOJSON accepts input that is RFC 7946 compliant.

If the parameter make_valid is set to TRUE, the function attempts to repair polygons that don't conform to Open Geospatial Consortium semantics. This parameter uses named argument syntax, and should be specified using make_valid => argument_value syntax.

See ST_ASGEOJSON to format a GEOGRAPHY as GeoJSON.

Constraints

The input is subject to the following constraints:

ST_GEOGFROMGEOJSON only accepts JSON geometry fragments and cannot 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 does not support the optional third element for a position in the coordinates member.

Return type

GEOGRAPHY

Signature 1

ST_GEOGFROMTEXT(wkt_string[, oriented])

Description

Returns a GEOGRAPHY value that corresponds to the input WKT representation.

This function supports an optional parameter of type BOOL, oriented. If this parameter is set to TRUE, any polygons in the input are assumed to be oriented 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 allows WKT to represent polygons larger than a hemisphere. If oriented is FALSE or omitted, this function returns the polygon with the smaller area. See also ST_MAKEPOLYGONORIENTED which is similar to ST_GEOGFROMTEXT with oriented=TRUE.

To format GEOGRAPHY as WKT, use ST_ASTEXT.

Constraints

All input edges are assumed to be spherical geodesics, and not planar straight lines. For reading data in a planar projection, consider using ST_GEOGFROMGEOJSON. For more information on the differences between spherical geodesics and planar lines, see Coordinate systems and edges.
The function does not support three-dimensional geometries that have a Z suffix, nor does it support linear referencing system geometries with an M suffix.
The function only supports geometry primitives and multipart geometries. In particular it supports only Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon, and GeometryCollection.

Return type

GEOGRAPHY

Example

The following query reads the WKT string POLYGON((0 0, 0 2, 2 2, 0 2, 0 0)) both as a non-oriented polygon and as an oriented polygon, and checks whether each result contains the point (1, 1).

WITH polygon AS (SELECT 'Polygon((0 0, 0 2, 2 2, 2 0, 0 0))' AS p)
SELECT
  ST_CONTAINS(ST_GEOGFROMTEXT(p), ST_GEOGPOINT(1, 1)) AS fromtext_default,
  ST_CONTAINS(ST_GEOGFROMTEXT(p, FALSE), ST_GEOGPOINT(1, 1)) AS non_oriented,
  ST_CONTAINS(ST_GEOGFROMTEXT(p, TRUE),  ST_GEOGPOINT(1, 1)) AS oriented
FROM polygon;

+-------------------+---------------+-----------+
| fromtext_default  | non_oriented  | oriented  |
+-------------------+---------------+-----------+
| true              | true          | false     |
+-------------------+---------------+-----------+

Signature 2

ST_GEOGFROMTEXT(wkt_string[, oriented => boolean_constant_1]
    [, planar => boolean_constant_2] [, make_valid => boolean_constant_3])

Description

Returns a GEOGRAPHY value that corresponds to the input WKT representation.

This function supports three optional parameters of type BOOL: oriented, planar, and make_valid. This signature uses named arguments syntax, and the parameters should be specified using parameter_name => parameter_value syntax, in any order.

If the oriented parameter is set to TRUE, any polygons in the input are assumed to be oriented 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 allows WKT to represent polygons larger than a hemisphere. If oriented is FALSE or omitted, this function returns the polygon with the smaller area. See also ST_MAKEPOLYGONORIENTED which is similar to ST_GEOGFROMTEXT with oriented=TRUE.

If the parameter planar is set to TRUE, the edges of the line strings and polygons are assumed to use planar map semantics, rather than BigQuery default spherical geodesics semantics. For more information on the differences between spherical geodesics and planar lines, see Coordinate systems and edges.

If the parameter make_valid is set to TRUE, the function attempts to repair polygons that don't conform to Open Geospatial Consortium semantics.

To format GEOGRAPHY as WKT, use ST_ASTEXT.

Constraints

All input edges are assumed to be spherical geodesics by default, and not planar straight lines. For reading data in a planar projection, pass planar => TRUE argument, or consider using ST_GEOGFROMGEOJSON. For more information on the differences between spherical geodesics and planar lines, see Coordinate systems and edges.
The function does not support three-dimensional geometries that have a Z suffix, nor does it support linear referencing system geometries with an M suffix.
The function only supports geometry primitives and multipart geometries. In particular it supports only Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon, and GeometryCollection.
oriented and planar cannot be equal to TRUE at the same time.
oriented and make_valid cannot be equal to TRUE at the same time.

Example

The following query reads the WKT string POLYGON((0 0, 0 2, 2 2, 0 2, 0 0)) both as a non-oriented polygon and as an oriented polygon, and checks whether each result contains the point (1, 1).

WITH polygon AS (SELECT 'Polygon((0 0, 0 2, 2 2, 2 0, 0 0))' AS p)
SELECT
  ST_CONTAINS(ST_GEOGFROMTEXT(p), ST_GEOGPOINT(1, 1)) AS fromtext_default,
  ST_CONTAINS(ST_GEOGFROMTEXT(p, oriented => FALSE), ST_GEOGPOINT(1, 1)) AS non_oriented,
  ST_CONTAINS(ST_GEOGFROMTEXT(p, oriented => TRUE),  ST_GEOGPOINT(1, 1)) AS oriented
FROM polygon;

+-------------------+---------------+-----------+
| fromtext_default  | non_oriented  | oriented  |
+-------------------+---------------+-----------+
| true              | true          | false     |
+-------------------+---------------+-----------+

The following query converts a WKT string with an invalid polygon to GEOGRAPHY. The WKT string violates two properties of a valid polygon - the loop describing the polygon is not closed, and it contains self-intersection. With the make_valid option, ST_GeogFromText successfully converts it to a multipolygon shape.

WITH data AS (
  SELECT 'polygon((0 -1, 2 1, 2 -1, 0 1))' wkt)
SELECT
  SAFE.ST_GeogFromText(wkt) as geom,
  SAFE.ST_GeogFromText(wkt, make_valid => TRUE) as valid_geom
FROM data

+------+-----------------------------------------------------------------+
| geom | valid_geom                                                      |
+------+-----------------------------------------------------------------+
| NULL | MULTIPOLYGON(((0 -1, 1 0, 0 1, 0 -1)), ((1 0, 2 -1, 2 1, 1 0))) |
+------+-----------------------------------------------------------------+

ST_GEOGFROMWKB

ST_GEOGFROMWKB(wkb_bytes_expression)
ST_GEOGFROMWKB(wkb_hex_string_expression)

Description

Converts an expression for a hexadecimal-text STRING or BYTES value into a GEOGRAPHY value. The expression must be in WKB format.

To format GEOGRAPHY as WKB, use ST_ASBINARY.

Constraints

All input edges are assumed to be spherical geodesics, and not planar straight lines. For reading data in a planar projection, consider using ST_GEOGFROMGEOJSON.

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 and latitude parameters and returns that point in a GEOGRAPHY value.

Constraints

Latitudes must be in the range [-90, 90]. Latitudes outside this range will result in an error.
Longitudes outside the range [-180, 180] are allowed; ST_GEOGPOINT uses the input longitude modulo 360 to obtain a longitude within [-180, 180].

Return type

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.

Return type

GEOGRAPHY

ST_GEOHASH

ST_GEOHASH(geography_expression, maxchars)

Description

Returns a GeoHash representation of geography_expression. The resulting GeoHash will contain at most maxchars characters. Fewer characters corresponds to lower precision (or, described differently, to a bigger bounding box).

ST_GeoHash over an empty GEOGRAPHY object returns NULL.

Constraints

Only GEOGRAPHY objects representing single points are supported.
The maximum value of maxchars is 20.

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   |
+--------------+

Return type

STRING

ST_INTERSECTION

ST_INTERSECTION(geography_1, geography_2)

Description

Returns a GEOGRAPHY that represents the point set intersection of the two input GEOGRAPHYs. Thus, every point in the intersection appears in both geography_1 and geography_2.

If the two input GEOGRAPHYs 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, 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 GEOGRAPHYs.

If ST_INTERSECTS returns TRUE, it implies that 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 minimum and maximum 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 be in the [-180, 180] degree range.

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 is not a collection.

Return type

BOOL

ST_ISEMPTY

ST_ISEMPTY(geography_expression)

Description

Returns TRUE if the given GEOGRAPHY is empty; that is, the GEOGRAPHY does not contain any points, lines, or polygons.

NOTE: A BigQuery empty GEOGRAPHY is not 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_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 does not 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_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 GEOGRAPHYs in the order they are given.

ST_MAKELINE comes in two variants. For the first variant, input must be two GEOGRAPHYs. 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: BigQuery's snapping process may discard sufficiently short edges and snap the two endpoints together. For instance, if two input GEOGRAPHYs 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

GEOGRAPHY

ST_MAKEPOLYGON

ST_MAKEPOLYGON(geography_expression[, array_of_geography])

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 GEOGRAPHYs, each containing exactly one linestring. The first GEOGRAPHY in either variant is used to construct the polygon shell. Additional GEOGRAPHYs 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: 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 cannot 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.

NOTE: BigQuery's snapping process may discard sufficiently short edges and snap the two endpoints together. Hence, when vertices are snapped together, it is possible that a polygon hole that is 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: 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 cannot 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 for an alternate polygon constructor, and other constraints on building a valid polygon.

NOTE: Due to BigQuery's snapping process, edges with a sufficiently short length will be discarded and the two endpoints will be snapped to a single point. Therefore, it is possible that vertices in a linestring may be snapped together such that one or more edge disappears. Hence, it is possible that a polygon hole that is 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 GEOGRAPHYs; 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 GEOGRAPHYs 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.

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: 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 does not 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_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

Examples

The following example shows how ST_SIMPLIFY simplifies the input line GEOGRAPHY by removing intermediate vertices.

WITH example AS
 (SELECT ST_GEOGFROMTEXT('LINESTRING(0 0, 0.05 0, 0.1 0, 0.15 0, 2 0)') AS line)
SELECT
   line AS original_line,
   ST_SIMPLIFY(line, 1) AS simplified_line
FROM example;

+---------------------------------------------+----------------------+
|                original_line                |   simplified_line    |
+---------------------------------------------+----------------------+
| LINESTRING(0 0, 0.05 0, 0.1 0, 0.15 0, 2 0) | LINESTRING(0 0, 2 0) |
+---------------------------------------------+----------------------+

The following example illustrates how the result of ST_SIMPLIFY can have a lower dimension than the original shape.

WITH example AS
 (SELECT
    ST_GEOGFROMTEXT('POLYGON((0 0, 0.1 0, 0.1 0.1, 0 0))') AS polygon,
    t AS tolerance
  FROM UNNEST([1000, 10000, 100000]) AS t)
SELECT
  polygon AS original_triangle,
  tolerance AS tolerance_meters,
  ST_SIMPLIFY(polygon, tolerance) AS simplified_result
FROM example

+-------------------------------------+------------------+-------------------------------------+
|          original_triangle          | tolerance_meters |          simplified_result          |
+-------------------------------------+------------------+-------------------------------------+
| POLYGON((0 0, 0.1 0, 0.1 0.1, 0 0)) |             1000 | POLYGON((0 0, 0.1 0, 0.1 0.1, 0 0)) |
| POLYGON((0 0, 0.1 0, 0.1 0.1, 0 0)) |            10000 |            LINESTRING(0 0, 0.1 0.1) |
| POLYGON((0 0, 0.1 0, 0.1 0.1, 0 0)) |           100000 |                          POINT(0 0) |
+-------------------------------------+------------------+-------------------------------------+

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 are not supported. The grid_size parameter is rounded so that it is of the form \(10^n\), where \(-10 < n < 0\).

Return type

GEOGRAPHY

ST_TOUCHES

ST_TOUCHES(geography_1, geography_2)

Description

Returns TRUE provided the following two conditions are satisfied:

geography_1 intersects geography_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 GEOGRAPHYs.

ST_UNION comes in two variants. For the first variant, input must be two GEOGRAPHYs. 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 do not affect the output.

See ST_UNION_AGG for the aggregate version of ST_UNION.

Return type

GEOGRAPHY

ST_UNION_AGG

ST_UNION_AGG(geography)

Description

Returns a GEOGRAPHY that represents the point set union of all input GEOGRAPHYs.

ST_UNION_AGG ignores NULL input GEOGRAPHY values.

See ST_UNION for the non-aggregate version of ST_UNION_AGG.

Return type

GEOGRAPHY

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(b, a). Note the opposite order of arguments.

Return type

BOOL

ST_X

ST_X(geography_expression)

Description

Returns the longitude in degrees of the single-point input GEOGRAPHY.

For any input GEOGRAPHY that is not a single point, including an empty GEOGRAPHY, ST_X returns an error. Use the SAFE. prefix to obtain NULL.

Return type

FLOAT64

Example

The following example uses ST_X and ST_Y to extract coordinates from single-point geographies.

WITH points AS
   (SELECT ST_GEOGPOINT(i, i + 1) AS p FROM UNNEST([0, 5, 12]) AS i)
 SELECT
   p,
   ST_X(p) as longitude,
   ST_Y(p) as latitude
FROM points;

+--------------+-----------+----------+
| p            | longitude | latitude |
+--------------+-----------+----------+
| POINT(0 1)   | 0.0       | 1.0      |
| POINT(5 6)   | 5.0       | 6.0      |
| POINT(12 13) | 12.0      | 13.0     |
+--------------+-----------+----------+

ST_Y

ST_Y(geography_expression)

Description

Returns the latitude in degrees of the single-point input GEOGRAPHY.

For any input GEOGRAPHY that is not a single point, including an empty GEOGRAPHY, ST_Y returns an error. Use the SAFE. prefix to return NULL instead.

Return type

FLOAT64

Example

See ST_X for example usage.

Geography functions in Standard SQL

Categories

ST_AREA

ST_ASBINARY

ST_ASGEOJSON

ST_ASTEXT

ST_BOUNDARY

ST_CENTROID

ST_CENTROID_AGG

ST_CLOSESTPOINT

ST_CLUSTERDBSCAN

ST_CONTAINS

ST_CONVEXHULL

ST_COVEREDBY

ST_COVERS

ST_DIFFERENCE

ST_DIMENSION

ST_DISJOINT

ST_DISTANCE

ST_DUMP

ST_DWITHIN

ST_EQUALS

ST_GEOGFROMGEOJSON

ST_GEOGFROMTEXT

Signature 1

Signature 2

ST_GEOGFROMWKB

ST_GEOGPOINT

ST_GEOGPOINTFROMGEOHASH

ST_GEOHASH

ST_INTERSECTION

ST_INTERSECTS

ST_INTERSECTSBOX

ST_ISCOLLECTION

ST_ISEMPTY

ST_LENGTH

ST_MAKELINE

ST_MAKEPOLYGON

ST_MAKEPOLYGONORIENTED

ST_MAXDISTANCE

ST_NPOINTS

ST_NUMPOINTS

ST_PERIMETER

ST_SIMPLIFY

ST_SNAPTOGRID

ST_TOUCHES

ST_UNION

ST_UNION_AGG

ST_WITHIN

ST_X

ST_Y