Geography functions

GoogleSQL for BigQuery supports geography functions. Geography functions operate on or generate GoogleSQL GEOGRAPHY values. The signature of most geography functions starts with ST_. GoogleSQL for 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 GoogleSQL geography functions return NULL if any input argument is NULL.

Categories

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

Category	Functions	Description
Constructors	ST_GEOGPOINT ST_MAKELINE ST_MAKEPOLYGON ST_MAKEPOLYGONORIENTED	Functions that build new geography values from coordinates or existing geographies.
Parsers	ST_GEOGFROM 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_BUFFER ST_BUFFERWITHTOLERANCE ST_CENTROID ST_CENTROID_AGG (Aggregate) ST_CLOSESTPOINT ST_CONVEXHULL ST_DIFFERENCE ST_EXTERIORRING ST_INTERIORRINGS 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_ENDPOINT ST_GEOMETRYTYPE ST_ISCLOSED ST_ISCOLLECTION ST_ISEMPTY ST_ISRING ST_NPOINTS ST_NUMGEOMETRIES ST_NUMPOINTS ST_POINTN ST_STARTPOINT 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_ANGLE ST_AREA ST_AZIMUTH ST_BOUNDINGBOX ST_DISTANCE ST_EXTENT (Aggregate) ST_LENGTH ST_MAXDISTANCE ST_PERIMETER	Functions that compute measurements of one or more geographies.
Clustering	ST_CLUSTERDBSCAN	Functions that perform clustering on geographies.
S2 functions	S2_CELLIDFROMPOINT S2_COVERINGCELLIDS	Functions for working with S2 cell coverings of GEOGRAPHY.

S2_CELLIDFROMPOINT

S2_CELLIDFROMPOINT(point_geography[, level => cell_level])

Description

Returns the S2 cell ID covering a point GEOGRAPHY.

• The optional INT64 parameter level specifies the S2 cell level for the returned cell. Naming this argument is optional.

This is advanced functionality for interoperability with systems utilizing the S2 Geometry Library.

Constraints

• Returns the cell ID as a signed INT64 bit-equivalent to unsigned 64-bit integer representation.
• Can return negative cell IDs.
• Valid S2 cell levels are 0 to 30.
• level defaults to 30 if not explicitly specified.
• The function only supports a single point GEOGRAPHY. Use the SAFE prefix if the input can be multipoint, linestring, polygon, or an empty GEOGRAPHY.
• To compute the covering of a complex GEOGRAPHY, use S2_COVERINGCELLIDS.

Return type

INT64

Example

WITH data AS (
  SELECT 1 AS id, ST_GEOGPOINT(-122, 47) AS geo
  UNION ALL
  -- empty geography is not supported
  SELECT 2 AS id, ST_GEOGFROMTEXT('POINT EMPTY') AS geo
  UNION ALL
  -- only points are supported
  SELECT 3 AS id, ST_GEOGFROMTEXT('LINESTRING(1 2, 3 4)') AS geo
)
SELECT id,
       SAFE.S2_CELLIDFROMPOINT(geo) cell30,
       SAFE.S2_CELLIDFROMPOINT(geo, level => 10) cell10
FROM data;

+----+---------------------+---------------------+
| id | cell30              | cell10              |
+----+---------------------+---------------------+
| 1  | 6093613931972369317 | 6093613287902019584 |
| 2  | NULL                | NULL                |
| 3  | NULL                | NULL                |
+----+---------------------+---------------------+

S2_COVERINGCELLIDS

S2_COVERINGCELLIDS(
    geography
    [, min_level => cell_level]
    [, max_level => cell_level]
    [, max_cells => max_cells]
    [, buffer => buffer])

Description

Returns an array of S2 cell ids that cover the input GEOGRAPHY. The function returns at most max_cells cells. The optional arguments min_level and max_level specify minimum and maximum levels for returned S2 cells. The array size is limited by the optional max_cells argument. The optional buffer argument specifies a buffering factor in meters; the region being covered is expanded from the extent of the input geography by this amount.

This is advanced functionality for interoperability with systems utilizing the S2 Geometry Library.

Constraints

• Returns the cell ID as a signed INT64 bit-equivalent to unsigned 64-bit integer representation.
• Can return negative cell IDs.
• Valid S2 cell levels are 0 to 30.
• max_cells defaults to 8 if not explicitly specified.
• buffer should be nonnegative. It defaults to 0.0 meters if not explicitly specified.

Return type

ARRAY<INT64>

Example

WITH data AS (
  SELECT 1 AS id, ST_GEOGPOINT(-122, 47) AS geo
  UNION ALL
  SELECT 2 AS id, ST_GEOGFROMTEXT('POINT EMPTY') AS geo
  UNION ALL
  SELECT 3 AS id, ST_GEOGFROMTEXT('LINESTRING(-122.12 47.67, -122.19 47.69)') AS geo
)
SELECT id, S2_COVERINGCELLIDS(geo, min_level => 12) cells
FROM data;

+----+--------------------------------------------------------------------------------------+
| id | cells                                                                                |
+----+--------------------------------------------------------------------------------------+
| 1  | [6093613931972369317]                                                                |
| 2  | []                                                                                   |
| 3  | [6093384954555662336, 6093390709811838976, 6093390735581642752, 6093390740145045504, |
|    |  6093390791416217600, 6093390812891054080, 6093390817187069952, 6093496378892222464] |
+----+--------------------------------------------------------------------------------------+

ST_ANGLE

ST_ANGLE(point_geography_1, point_geography_2, point_geography_3)

Description

Takes three point GEOGRAPHY values, which represent two intersecting lines. Returns the angle between these lines. Point 2 and point 1 represent the first line and point 2 and point 3 represent the second line. The angle between these lines is in radians, in the range [0, 2pi). The angle is measured clockwise from the first line to the second line.

ST_ANGLE has the following edge cases:

• If points 2 and 3 are the same, returns NULL.
• If points 2 and 1 are the same, returns NULL.
• If points 2 and 3 are exactly antipodal, returns NULL.
• If points 2 and 1 are exactly antipodal, returns NULL.
• If any of the input geographies are not single points or are the empty geography, then throws an error.

Return type

FLOAT64

Example

WITH geos AS (
  SELECT 1 id, ST_GEOGPOINT(1, 0) geo1, ST_GEOGPOINT(0, 0) geo2, ST_GEOGPOINT(0, 1) geo3 UNION ALL
  SELECT 2 id, ST_GEOGPOINT(0, 0), ST_GEOGPOINT(1, 0), ST_GEOGPOINT(0, 1) UNION ALL
  SELECT 3 id, ST_GEOGPOINT(1, 0), ST_GEOGPOINT(0, 0), ST_GEOGPOINT(1, 0) UNION ALL
  SELECT 4 id, ST_GEOGPOINT(1, 0) geo1, ST_GEOGPOINT(0, 0) geo2, ST_GEOGPOINT(0, 0) geo3 UNION ALL
  SELECT 5 id, ST_GEOGPOINT(0, 0), ST_GEOGPOINT(-30, 0), ST_GEOGPOINT(150, 0) UNION ALL
  SELECT 6 id, ST_GEOGPOINT(0, 0), NULL, NULL UNION ALL
  SELECT 7 id, NULL, ST_GEOGPOINT(0, 0), NULL UNION ALL
  SELECT 8 id, NULL, NULL, ST_GEOGPOINT(0, 0))
SELECT ST_ANGLE(geo1,geo2,geo3) AS angle FROM geos ORDER BY id;

+---------------------+
| angle               |
+---------------------+
| 4.71238898038469    |
| 0.78547432161873854 |
| 0                   |
| NULL                |
| NULL                |
| NULL                |
| NULL                |
| NULL                |
+---------------------+

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 GoogleSQL GEOGRAPHY has spherical geodesic edges, whereas a GeoJSON Geometry object explicitly has planar edges. To convert between these two types of edges, GoogleSQL adds additional points to the line where necessary so that the resulting sequence of edges remains within 10 meters of the original edge.

See ST_GEOGFROMGEOJSON 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_AZIMUTH

ST_AZIMUTH(point_geography_1, point_geography_2)

Description

Takes two point GEOGRAPHY values, and returns the azimuth of the line segment formed by points 1 and 2. The azimuth is the angle in radians measured between the line from point 1 facing true North to the line segment from point 1 to point 2.

The positive angle is measured clockwise on the surface of a sphere. For example, the azimuth for a line segment:

• Pointing North is 0
• Pointing East is PI/2
• Pointing South is PI
• Pointing West is 3PI/2

ST_AZIMUTH has the following edge cases:

• If the two input points are the same, returns NULL.
• If the two input points are exactly antipodal, returns NULL.
• If either of the input geographies are not single points or are the empty geography, throws an error.

Return type

FLOAT64

Example

WITH geos AS (
  SELECT 1 id, ST_GEOGPOINT(1, 0) AS geo1, ST_GEOGPOINT(0, 0) AS geo2 UNION ALL
  SELECT 2, ST_GEOGPOINT(0, 0), ST_GEOGPOINT(1, 0) UNION ALL
  SELECT 3, ST_GEOGPOINT(0, 0), ST_GEOGPOINT(0, 1) UNION ALL
  -- identical
  SELECT 4, ST_GEOGPOINT(0, 0), ST_GEOGPOINT(0, 0) UNION ALL
  -- antipode
  SELECT 5, ST_GEOGPOINT(-30, 0), ST_GEOGPOINT(150, 0) UNION ALL
  -- nulls
  SELECT 6, ST_GEOGPOINT(0, 0), NULL UNION ALL
  SELECT 7, NULL, ST_GEOGPOINT(0, 0))
SELECT ST_AZIMUTH(geo1, geo2) AS azimuth FROM geos ORDER BY id;

+--------------------+
| azimuth            |
+--------------------+
| 4.71238898038469   |
| 1.5707963267948966 |
| 0                  |
| NULL               |
| NULL               |
| NULL               |
| NULL               |
+--------------------+

ST_BOUNDARY

ST_BOUNDARY(geography_expression)

Description

Returns a single GEOGRAPHY that contains the union of the boundaries of each component in the given input GEOGRAPHY.

The boundary of each component of a GEOGRAPHY is defined as follows:

• The boundary of a point is empty.
• The boundary of a linestring consists of the endpoints of the linestring.
• The boundary of a polygon consists of the linestrings that form the polygon shell and each of the polygon's holes.

Return type

GEOGRAPHY

ST_BOUNDINGBOX

ST_BOUNDINGBOX(geography_expression)

Description

Returns a STRUCT that represents the bounding box for the specified geography. The bounding box is the minimal rectangle that encloses the geography. The edges of the rectangle follow constant lines of longitude and latitude.

Caveats:

• Returns NULL if the input is NULL or an empty geography.
• The bounding box might cross the antimeridian if this allows for a smaller rectangle. In this case, the bounding box has one of its longitudinal bounds outside of the [-180, 180] range, so that xmin is smaller than the eastmost value xmax.

Return type

STRUCT<xmin FLOAT64, ymin FLOAT64, xmax FLOAT64, ymax FLOAT64>.

Bounding box parts:

• xmin: The westmost constant longitude line that bounds the rectangle.
• xmax: The eastmost constant longitude line that bounds the rectangle.
• ymin: The minimum constant latitude line that bounds the rectangle.
• ymax: The maximum constant latitude line that bounds the rectangle.

Example

WITH data AS (
  SELECT 1 id, ST_GEOGFROMTEXT('POLYGON((-125 48, -124 46, -117 46, -117 49, -125 48))') g
  UNION ALL
  SELECT 2 id, ST_GEOGFROMTEXT('POLYGON((172 53, -130 55, -141 70, 172 53))') g
  UNION ALL
  SELECT 3 id, ST_GEOGFROMTEXT('POINT EMPTY') g
  UNION ALL
  SELECT 4 id, ST_GEOGFROMTEXT('POLYGON((172 53, -141 70, -130 55, 172 53))', oriented => TRUE)
)
SELECT id, ST_BOUNDINGBOX(g) AS box
FROM data

+----+------------------------------------------+
| id | box                                      |
+----+------------------------------------------+
| 1  | {xmin:-125, ymin:46, xmax:-117, ymax:49} |
| 2  | {xmin:172, ymin:53, xmax:230, ymax:70}   |
| 3  | NULL                                     |
| 4  | {xmin:-180, ymin:-90, xmax:180, ymax:90} |
+----+------------------------------------------+

See ST_EXTENT for the aggregate version of ST_BOUNDINGBOX.

ST_BUFFER

Preview

This product or feature is covered by the Pre-GA Offerings Terms of the Google Cloud Platform Terms of Service. Pre-GA products and features may have limited support, and changes to pre-GA products and features may not be compatible with other pre-GA versions. For more information, see the launch stage descriptions.

ST_BUFFER(
    geography,
    buffer_radius
    [, num_seg_quarter_circle => num_segments]
    [, use_spheroid => boolean_expression]
    [, endcap => endcap_style]
    [, side => line_side])

Description

Returns a GEOGRAPHY that represents the buffer around the input GEOGRAPHY. This function is similar to ST_BUFFERWITHTOLERANCE, but you specify the number of segments instead of providing tolerance to determine how much the resulting geography can deviate from the ideal buffer radius.

• geography: The input GEOGRAPHY to encircle with the buffer radius.
• buffer_radius: DOUBLE that represents the radius of the buffer around the input geography. The radius is in meters. Note that polygons contract when buffered with a negative buffer_radius. Polygon shells and holes that are contracted to a point are discarded.
• num_seg_quarter_circle: (Optional) DOUBLE specifies the number of segments that are used to approximate a quarter circle. The default value is 8.0. Naming this argument is optional.
• endcap: (Optional) STRING allows you to specify one of two endcap styles: ROUND and FLAT. The default value is ROUND. This option only affects the endcaps of buffered linestrings.
• side: (Optional) STRING allows you to specify one of three possibilities for lines: BOTH, LEFT, and RIGHT. The default is BOTH. This option only affects how linestrings are buffered.
• use_spheroid: (Optional) BOOL determines how this function measures distance. If use_spheroid is FALSE, the function measures distance on the surface of a perfect sphere. The use_spheroid parameter currently only supports the value FALSE. The default value of use_spheroid is FALSE.

Return type

Polygon GEOGRAPHY

Example

The following example shows the result of ST_BUFFER on a point. A buffered point is an approximated circle. When num_seg_quarter_circle = 2, there are two line segments in a quarter circle, and therefore the buffered circle has eight sides and ST_NUMPOINTS returns nine vertices. When num_seg_quarter_circle = 8, there are eight line segments in a quarter circle, and therefore the buffered circle has thirty-two sides and ST_NUMPOINTS returns thirty-three vertices.

SELECT
  -- num_seg_quarter_circle=2
  ST_NUMPOINTS(ST_BUFFER(ST_GEOGFROMTEXT('POINT(1 2)'), 50, 2)) AS eight_sides,
  -- num_seg_quarter_circle=8, since 8 is the default
  ST_NUMPOINTS(ST_BUFFER(ST_GEOGFROMTEXT('POINT(100 2)'), 50)) AS thirty_two_sides;

+-------------+------------------+
| eight_sides | thirty_two_sides |
+-------------+------------------+
| 9           | 33               |
+-------------+------------------+

ST_BUFFERWITHTOLERANCE

Preview

This product or feature is covered by the Pre-GA Offerings Terms of the Google Cloud Platform Terms of Service. Pre-GA products and features may have limited support, and changes to pre-GA products and features may not be compatible with other pre-GA versions. For more information, see the launch stage descriptions.

ST_BUFFERWITHTOLERANCE(
    geography,
    buffer_radius,
    tolerance_meters => tolernace
    [, use_spheroid => boolean_expression]
    [, endcap => endcap_style]
    [, side => line_side])

Returns a GEOGRAPHY that represents the buffer around the input GEOGRAPHY. This function is similar to ST_BUFFER, but you provide tolerance instead of segments to determine how much the resulting geography can deviate from the ideal buffer radius.

• geography: The input GEOGRAPHY to encircle with the buffer radius.
• buffer_radius: DOUBLE that represents the radius of the buffer around the input geography. The radius is in meters. Note that polygons contract when buffered with a negative buffer_radius. Polygon shells and holes that are contracted to a point are discarded.
• tolerance_meters: DOUBLE specifies a tolerance in meters with which the shape is approximated. Tolerance determines how much a polygon can deviate from the ideal radius. Naming this argument is optional.
• endcap: (Optional) STRING allows you to specify one of two endcap styles: ROUND and FLAT. The default value is ROUND. This option only affects the endcaps of buffered linestrings.
• side: (Optional) STRING allows you to specify one of three possible line styles: BOTH, LEFT, and RIGHT. The default is BOTH. This option only affects the endcaps of buffered linestrings.
• use_spheroid: (Optional) BOOL determines how this function measures distance. If use_spheroid is FALSE, the function measures distance on the surface of a perfect sphere. The use_spheroid parameter currently only supports the value FALSE. The default value of use_spheroid is FALSE.

Return type

Polygon GEOGRAPHY

Example

The following example shows the results of ST_BUFFERWITHTOLERANCE on a point, given two different values for tolerance but with the same buffer radius of 100. A buffered point is an approximated circle. When tolerance_meters=25, the tolerance is a large percentage of the buffer radius, and therefore only five segments are used to approximate a circle around the input point. When tolerance_meters=1, the tolerance is a much smaller percentage of the buffer radius, and therefore twenty-four edges are used to approximate a circle around the input point.

SELECT
  -- tolerance_meters=25, or 25% of the buffer radius.
  ST_NumPoints(ST_BUFFERWITHTOLERANCE(ST_GEOGFROMTEXT('POINT(1 2)'), 100, 25)) AS five_sides,
  -- tolerance_meters=1, or 1% of the buffer radius.
  st_NumPoints(ST_BUFFERWITHTOLERANCE(ST_GEOGFROMTEXT('POINT(100 2)'), 100, 1)) AS twenty_four_sides;

+------------+-------------------+
| five_sides | twenty_four_sides |
+------------+-------------------+
| 6          | 24                |
+------------+-------------------+

ST_CENTROID_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

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

Point GEOGRAPHY

ST_CLOSESTPOINT

ST_CLOSESTPOINT(geography_1, geography_2[, use_spheroid])

Description

Returns a GEOGRAPHY containing a point on geography_1 with the smallest possible distance to geography_2. This implies that the distance between the point returned by ST_CLOSESTPOINT and geography_2 is less than or equal to the distance between any other point on geography_1 and geography_2.

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

Point GEOGRAPHY

ST_CLUSTERDBSCAN

ST_CLUSTERDBSCAN(geography_column, epsilon, minimum_geographies)
OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]

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

To learn more about the OVER clause and how to use it, see Window function calls.

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.

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.
• An empty geography 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. Therefore, the result consists of the part of geography_1 that does not intersect with geography_2.

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

Constraints

The underlying geometric objects that a GoogleSQL GEOGRAPHY represents correspond to a closed point set. Therefore, ST_DIFFERENCE is the closure of the point set difference of geography_1 and geography_2. This implies that if geography_1 and geography_2 intersect, then a portion of the boundary of geography_2 could be in the difference.

Return type

GEOGRAPHY

Example

The following query illustrates the difference between geog1, a larger polygon POLYGON((0 0, 10 0, 10 10, 0 0)) and geog1, a smaller polygon POLYGON((4 2, 6 2, 8 6, 4 2)) that intersects with geog1. The result is geog1 with a hole where geog2 intersects with it.

SELECT
  ST_DIFFERENCE(
      ST_GEOGFROMTEXT('POLYGON((0 0, 10 0, 10 10, 0 0))'),
      ST_GEOGFROMTEXT('POLYGON((4 2, 6 2, 8 6, 4 2))')
  );

+--------------------------------------------------------+
| difference_of_geog1_and_geog2                          |
+--------------------------------------------------------+
| POLYGON((0 0, 10 0, 10 10, 0 0), (8 6, 6 2, 4 2, 8 6)) |
+--------------------------------------------------------+

ST_DIMENSION

ST_DIMENSION(geography_expression)

Description

Returns the dimension of the highest-dimensional element in the input GEOGRAPHY.

The dimension of each possible element is as follows:

• The dimension of a point is 0.
• The dimension of a linestring is 1.
• The dimension of a polygon is 2.

If the input GEOGRAPHY is empty, ST_DIMENSION returns -1.

Return type

INT64

ST_DISJOINT

ST_DISJOINT(geography_1, geography_2)

Description

Returns TRUE if the intersection of geography_1 and geography_2 is empty, that is, no point in geography_1 also appears in geography_2.

ST_DISJOINT is the logical negation of ST_INTERSECTS.

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. If use_spheroid is TRUE, the function measures distance on the surface of the WGS84 spheroid. 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<GEOGRAPHY>

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_ENDPOINT

ST_ENDPOINT(linestring_geography)

Description

Returns the last point of a linestring geography as a point geography. Returns an error if the input is not a linestring or if the input is empty. Use the SAFE prefix to obtain NULL for invalid input instead of an error.

Return Type

Point GEOGRAPHY

Example

SELECT ST_ENDPOINT(ST_GEOGFROMTEXT('LINESTRING(1 1, 2 1, 3 2, 3 3)')) last

+--------------+
| last         |
+--------------+
| POINT(3 3)   |
+--------------+

ST_EQUALS

ST_EQUALS(geography_1, geography_2)

Description

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_EXTENT

ST_EXTENT(geography_expression)

Description

Returns a STRUCT that represents the bounding box for the set of input GEOGRAPHY values. The bounding box is the minimal rectangle that encloses the geography. The edges of the rectangle follow constant lines of longitude and latitude.

Caveats:

• Returns NULL if all the inputs are NULL or empty geographies.
• The bounding box might cross the antimeridian if this allows for a smaller rectangle. In this case, the bounding box has one of its longitudinal bounds outside of the [-180, 180] range, so that xmin is smaller than the eastmost value xmax.
• If the longitude span of the bounding box is larger than or equal to 180 degrees, the function returns the bounding box with the longitude range of [-180, 180].

Return type

STRUCT<xmin FLOAT64, ymin FLOAT64, xmax FLOAT64, ymax FLOAT64>.

Bounding box parts:

• xmin: The westmost constant longitude line that bounds the rectangle.
• xmax: The eastmost constant longitude line that bounds the rectangle.
• ymin: The minimum constant latitude line that bounds the rectangle.
• ymax: The maximum constant latitude line that bounds the rectangle.

Example

WITH data AS (
  SELECT 1 id, ST_GEOGFROMTEXT('POLYGON((-125 48, -124 46, -117 46, -117 49, -125 48))') g
  UNION ALL
  SELECT 2 id, ST_GEOGFROMTEXT('POLYGON((172 53, -130 55, -141 70, 172 53))') g
  UNION ALL
  SELECT 3 id, ST_GEOGFROMTEXT('POINT EMPTY') g
)
SELECT ST_EXTENT(g) AS box
FROM data

+----------------------------------------------+
| box                                          |
+----------------------------------------------+
| {xmin:172, ymin:46, xmax:243, ymax:70}       |
+----------------------------------------------+

ST_BOUNDINGBOX for the non-aggregate version of ST_EXTENT.

ST_EXTERIORRING

ST_EXTERIORRING(polygon_geography)

Description

Returns a linestring geography that corresponds to the outermost ring of a polygon geography.

• If the input geography is a polygon, gets the outermost ring of the polygon geography and returns the corresponding linestring.
• If the input is the full GEOGRAPHY, returns an empty geography.
• Returns an error if the input is not a single polygon.

Use the SAFE prefix to return NULL for invalid input instead of an error.

Return type

• Linestring GEOGRAPHY
• Empty GEOGRAPHY

Examples

WITH geo as
 (SELECT ST_GEOGFROMTEXT('POLYGON((0 0, 1 4, 2 2, 0 0))') AS g UNION ALL
  SELECT ST_GEOGFROMTEXT('''POLYGON((1 1, 1 10, 5 10, 5 1, 1 1),
                                  (2 2, 3 4, 2 4, 2 2))''') as g)
SELECT ST_EXTERIORRING(g) AS ring FROM geo;

+---------------------------------------+
| ring                                  |
+---------------------------------------+
| LINESTRING(2 2, 1 4, 0 0, 2 2)        |
| LINESTRING(5 1, 5 10, 1 10, 1 1, 5 1) |
+---------------------------------------+

ST_GEOGFROM

ST_GEOGFROM(expression)

Description

Converts an expression for a STRING or BYTES value into a GEOGRAPHY value.

If expression represents a STRING value, it must be a valid GEOGRAPHY representation in one of the following formats:

• WKT format. To learn more about this format and the requirements to use it, see ST_GEOGFROMTEXT.
• WKB in hexadecimal text format. To learn more about this format and the requirements to use it, see ST_GEOGFROMWKB.
• GeoJSON format. To learn more about this format and the requirements to use it, see ST_GEOGFROMGEOJSON.

If expression represents a BYTES value, it must be a valid GEOGRAPHY binary expression in WKB format. To learn more about this format and the requirements to use it, see ST_GEOGFROMWKB.

If expression is NULL, the output is NULL.

Return type

GEOGRAPHY

Examples

This takes a WKT-formatted string and returns a GEOGRAPHY polygon:

SELECT ST_GEOGFROM('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))') AS WKT_format

+------------------------------------+
| WKT_format                         |
+------------------------------------+
| POLYGON((2 0, 2 2, 0 2, 0 0, 2 0)) |
+------------------------------------+

This takes a WKB-formatted hexadecimal-encoded string and returns a GEOGRAPHY point:

SELECT ST_GEOGFROM(FROM_HEX('010100000000000000000000400000000000001040')) AS WKB_format

+----------------+
| WKB_format     |
+----------------+
| POINT(2 4)     |
+----------------+

This takes WKB-formatted bytes and returns a GEOGRAPHY point:

SELECT ST_GEOGFROM('010100000000000000000000400000000000001040')-AS WKB_format

+----------------+
| WKB_format     |
+----------------+
| POINT(2 4)     |
+----------------+

This takes a GeoJSON-formatted string and returns a GEOGRAPHY polygon:

SELECT ST_GEOGFROM(
  '{ "type": "Polygon", "coordinates": [ [ [2, 0], [2, 2], [1, 2], [0, 2], [0, 0], [2, 0] ] ] }'
) AS GEOJSON_format

+-----------------------------------------+
| GEOJSON_format                          |
+-----------------------------------------+
| POLYGON((2 0, 2 2, 1 2, 0 2, 0 0, 2 0)) |
+-----------------------------------------+

ST_GEOGFROMGEOJSON

ST_GEOGFROMGEOJSON(geojson_string [, make_valid => constant_expression])

Description

Returns a GEOGRAPHY value that corresponds to the input GeoJSON 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 GoogleSQL GEOGRAPHY has spherical geodesic edges, whereas a GeoJSON Geometry object explicitly has planar edges. To convert between these two types of edges, GoogleSQL adds additional points to the line where necessary so that the resulting sequence of edges remains within 10 meters of the original edge.

See ST_ASGEOJSON to format a GEOGRAPHY as GeoJSON.

Constraints

The JSON 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

• Signature 1
• Signature 2

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 geometry collection.

Return type

GEOGRAPHY

Example

The following query reads the WKT string POLYGON((0 0, 0 2, 2 2, 2 0, 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 GoogleSQL 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 geometry collection.
• 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