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
GEOGRAPHY
s.
All BigQuery 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 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 GEOGRAPHY
s
as a single point GEOGRAPHY
.
The centroid over the set of input GEOGRAPHY
s is
the weighted average of the centroid of each individual
GEOGRAPHY
. Only the
GEOGRAPHY
s with the highest dimension present in the
input contribute to the centroid of the entire set. For example, if
the input contains both GEOGRAPHY
s with lines and
GEOGRAPHY
s with only points, ST_CENTROID_AGG
returns the weighted average of the
GEOGRAPHY
s with lines, since those have maximal
dimension. In this example, ST_CENTROID_AGG
ignores
GEOGRAPHY
s 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 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
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 ofGEOGRAPHY
s that is clustered.epsilon
: The epsilon that specifies the radius, measured in meters, around a core value. Non-negativeFLOAT64
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-negativeINT64
value.OVER
: Specifies a window. See Analytic Functions. Usage of theOVER
clause withST_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 ofminimum_geographies
geographies, including itself. The core value starts a new cluster, or is added to the same cluster as a core value withinepsilon
distance. Core values are grouped in a cluster together with all other core and border values that are withinepsilon
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 withinepsilon
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 emptyGEOGRAPHY
is always classified as noise.
Constraints
- The argument
minimum_geographies
is a non-negativeINT64
andepsilon
is a non-negativeFLOAT64
. - 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
returnsNULL
if the input is eitherNULL
or the emptyGEOGRAPHY
.
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 GEOGRAPHY
s 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
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.
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
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
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 GEOGRAPHY
s 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.
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
, andGeometryCollection
. Any other GeoJSON type such asFeature
orFeatureCollection
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 thecoordinates
member.
Return type
GEOGRAPHY
ST_GEOGFROMTEXT
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 anM
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 usingST_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 anM
suffix. - The function only supports geometry primitives and multipart geometries. In particular it supports only Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon, and GeometryCollection.
oriented
andplanar
cannot be equal toTRUE
at the same time.oriented
andmake_valid
cannot be equal toTRUE
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)
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_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 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, 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
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
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: BigQuery's 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
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
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: 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
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.
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
intersectsgeography_2
.- The interior of
geography_1
and the interior ofgeography_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 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 GEOGRAPHY
s.
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.