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.

The geography functions are grouped into the following categories based on their behavior:

  • Constructors: Functions that build new GEOGRAPHY values from coordinates or existing GEOGRAPHYs, such as ST_GEOGPOINT.
  • Parsers: Functions that create GEOGRAPHYs from an external format such as WKT and GeoJSON. For example, ST_GEOGFROMTEXT, creates a GEOGRAPHY from WKT.
  • Formatters: Functions that export GEOGRAPHYs to an external format such as WKT and GeoJSON. For example, ST_ASTEXT formats a GEOGRAPHY to WKT.
  • Transformations: Functions that generate a new GEOGRAPHY from others that adhere to some property. Examples include ST_INTERSECTION and ST_BOUNDARY.
  • Predicates: Functions that return TRUE or FALSE for some spatial relationship between two GEOGRAPHYs, or for some property of a geography. These functions are commonly used in filter clauses. For example, ST_DWITHIN is a predicate.
  • Accessors: Functions that provide access to properties of a GEOGRAPHY without side-effects, such as ST_NUMPOINTS.
  • Measures: Functions that compute measurements of one or more GEOGRAPHY, for example ST_DISTANCE.
  • Aggregate Functions: Geography-specific aggregate functions, such as ST_UNION_AGG.

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

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

Input can either be two GEOGRAPHYs or an ARRAY of type GEOGRAPHY. Each input GEOGRAPHY must consist of one of the following values:

  • Exactly one point.
  • Exactly one linestring.

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)

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.

The linestring input is either provided by a single GEOGRAPHY containing exactly one linestring, or by a single GEOGRAPHY and an array of GEOGRAPHYs, each containing exactly one linestring. The first GEOGRAPHY will always be used to construct the polygon shell. Any additional GEOGRAPHYs beyond the first in the 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.

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.

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_GEOGFROMGEOJSON

ST_GEOGFROMGEOJSON(geojson_string)

Description

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

ST_GEOGFROMGEOJSON accepts input that is RFC 7946 compliant.

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

ST_GEOGFROMTEXT

ST_GEOGFROMTEXT(wkt_string)

Description

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

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.

Return type

GEOGRAPHY

ST_GEOGFROMWKB

ST_GEOGFROMWKB(wkb_bytes)

Description

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

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

ST_CLOSESTPOINT(geography_1, geography_2[, spheroid=FALSE])

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.

The spheroid parameter determines how distance is measured for this function. If spheroid is set to FALSE, then distance is measured on the surface of a perfect sphere. If spheroid is set to TRUE, distance is measured on the surface of the WGS84 spheroid. The default for the spheroid parameter is FALSE.

Return type

GEOGRAPHY

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

Input can either be two GEOGRAPHYs or an ARRAY of type GEOGRAPHY.

See ST_UNION_AGG for the aggregate version of ST_UNION.

Return type

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: 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_COVEREDBY

ST_COVEREDBY(geography_1, geography_2)

Description

Returns FALSE if geography_1 or geography_2 is empty. Returns TRUE 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_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

GEOGRAPHY

ST_DWITHIN

ST_DWITHIN(geography_1, geography_2, distance[, spheroid=FALSE])

Description

Returns TRUE if the distance between geography_1 and geography_2 is less than or equal to the distance given by the distance argument; otherwise, returns FALSE. The given distance is in meters on the surface of the Earth.

The spheroid parameter determines how distance is measured for this function. If spheroid is set to FALSE, then distance is measured on the surface of a perfect sphere. If spheroid is set to TRUE, distance is measured on the surface of the WGS84 spheroid. The default for the spheroid parameter 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_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_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_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_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_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_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_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_AREA

ST_AREA(geography_expression[, spheroid=FALSE])

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.

If spheroid is set to FALSE, the area of the input is measured on the surface of a perfect sphere; if TRUE, the area is measured on the surface of the WGS84 spheroid. The default for the spheroid parameter is FALSE.

Return type

FLOAT64

ST_DISTANCE

ST_DISTANCE(geography_1, geography_2[, spheroid=FALSE])

Description

Returns the shortest distance in meters between two GEOGRAPHYs.

If spheroid is set to FALSE, distance is measured on the surface of a perfect sphere; if TRUE, distance is measured on the surface of the WGS84 spheroid. The default for the spheroid parameter is FALSE.

Return type

FLOAT64

ST_LENGTH

ST_LENGTH(geography_expression[, spheroid=FALSE])

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.

If spheroid is set to FALSE, the length of the input is measured on the surface of a perfect sphere; if TRUE, the length is measured on the surface of the WGS84 spheroid. The default for the spheroid parameter is FALSE.

Return type

FLOAT64

ST_MAXDISTANCE

ST_MAXDISTANCE(geography_1, geography_2[, spheroid=FALSE])

Returns the longest distance in meters between two 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 spheroid is set to FALSE, distance is measured on the surface of a perfect sphere; if TRUE, distance is measured on the surface of the WGS84 spheroid. The default for the spheroid parameter is FALSE.

Return type

FLOAT64

ST_PERIMETER

ST_PERIMETER(geography_expression[, spheroid=FALSE])

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.

If spheroid is set to FALSE, the length of the input is measured on the surface of a perfect sphere; if TRUE, distance is measured on the surface of the WGS84 spheroid. The default for the spheroid parameter is FALSE.

Return type

FLOAT64

ST_UNION_AGG

ST_UNION_AGG(geography)

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

See ST_UNION for the non-aggregate version of ST_UNION_AGG.

Return type

GEOGRAPHY

Was this page helpful? Let us know how we did:

Send feedback about...

This page
Documentation feedback
BigQuery
Product feedback