GoogleSQL for BigQuery supports geography functions.Geography functions operate on or generate GoogleSQLGEOGRAPHY values. The signature of most geographyfunctions starts withST_. GoogleSQL for BigQuery supports the following functionsthat can be used to analyze geographical data, determine spatial relationshipsbetween geographical features, and construct or manipulateGEOGRAPHYs.

All GoogleSQL geography functions returnNULL if any input argumentisNULL.

Categories

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

Function list

S2_CELLIDFROMPOINT

S2_CELLIDFROMPOINT(point_geography[,level=>cell_level])

Description

Returns theS2 cell ID covering a pointGEOGRAPHY.

• The optionalINT64 parameterlevel specifies the S2 cell level for thereturned cell. Naming this argument is optional.

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

Constraints

• Returns the cell ID as a signedINT64 bit-equivalent tounsigned 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 theSAFE prefix ifthe input can be multipoint, linestring, polygon, or an emptyGEOGRAPHY.
• To compute the covering of a complexGEOGRAPHY, useS2_COVERINGCELLIDS.

Return type

INT64

Example

WITHdataAS(SELECT1ASid,ST_GEOGPOINT(-122,47)ASgeoUNIONALL-- empty geography isn't supportedSELECT2ASid,ST_GEOGFROMTEXT('POINT EMPTY')ASgeoUNIONALL-- only points are supportedSELECT3ASid,ST_GEOGFROMTEXT('LINESTRING(1 2, 3 4)')ASgeo)SELECTid,SAFE.S2_CELLIDFROMPOINT(geo)cell30,SAFE.S2_CELLIDFROMPOINT(geo,level=>10)cell10FROMdata;/*----+---------------------+---------------------+ | 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 ofS2 cell IDs that cover the inputGEOGRAPHY. The function returns at mostmax_cells cells. The optionalargumentsmin_level andmax_level specify minimum and maximum levels forreturned S2 cells. The array size is limited by the optionalmax_cellsargument. The optionalbuffer argument specifies a buffering factor inmeters; the region being covered is expanded from the extent of theinput geography by this amount.

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

Constraints

• Returns the cell ID as a signedINT64 bit-equivalent tounsigned 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 explicitlyspecified.

Return type

ARRAY<INT64>

Example

WITHdataAS(SELECT1ASid,ST_GEOGPOINT(-122,47)ASgeoUNIONALLSELECT2ASid,ST_GEOGFROMTEXT('POINT EMPTY')ASgeoUNIONALLSELECT3ASid,ST_GEOGFROMTEXT('LINESTRING(-122.12 47.67, -122.19 47.69)')ASgeo)SELECTid,S2_COVERINGCELLIDS(geo,min_level=>12)cellsFROMdata;/*----+--------------------------------------------------------------------------------------+ | 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 pointGEOGRAPHY values, which represent two intersecting lines.Returns the angle between these lines. Point 2 and point 1 represent the firstline and point 2 and point 3 represent the second line. The angle betweenthese lines is in radians, in the range[0, 2pi). The angle is measuredclockwise from the first line to the second line.

ST_ANGLE has the following edge cases:

• If points 2 and 3 are the same, returnsNULL.
• If points 2 and 1 are the same, returnsNULL.
• If points 2 and 3 are exactly antipodal, returnsNULL.
• If points 2 and 1 are exactly antipodal, returnsNULL.
• If any of the input geographies aren't single points or are the emptygeography, then throws an error.

Return type

FLOAT64

Example

WITHgeosAS(SELECT1id,ST_GEOGPOINT(1,0)geo1,ST_GEOGPOINT(0,0)geo2,ST_GEOGPOINT(0,1)geo3UNIONALLSELECT2id,ST_GEOGPOINT(0,0),ST_GEOGPOINT(1,0),ST_GEOGPOINT(0,1)UNIONALLSELECT3id,ST_GEOGPOINT(1,0),ST_GEOGPOINT(0,0),ST_GEOGPOINT(1,0)UNIONALLSELECT4id,ST_GEOGPOINT(1,0)geo1,ST_GEOGPOINT(0,0)geo2,ST_GEOGPOINT(0,0)geo3UNIONALLSELECT5id,ST_GEOGPOINT(0,0),ST_GEOGPOINT(-30,0),ST_GEOGPOINT(150,0)UNIONALLSELECT6id,ST_GEOGPOINT(0,0),NULL,NULLUNIONALLSELECT7id,NULL,ST_GEOGPOINT(0,0),NULLUNIONALLSELECT8id,NULL,NULL,ST_GEOGPOINT(0,0))SELECTST_ANGLE(geo1,geo2,geo3)ASangleFROMgeosORDERBYid;/*---------------------+ | 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 inputGEOGRAPHY.

Ifgeography_expression is a point or a line, returns zero. Ifgeography_expression is a collection, returns the area of the polygons in thecollection; if the collection doesn't contain polygons, returns zero.

The optionaluse_spheroid parameter determines how this function measuresdistance. Ifuse_spheroid isFALSE, the function measures distance on thesurface of a perfect sphere.

Theuse_spheroid parameter currently only supportsthe valueFALSE. The default value ofuse_spheroid isFALSE.

Return type

FLOAT64

ST_ASBINARY

ST_ASBINARY(geography_expression)

Description

Returns theWKB representation of an inputGEOGRAPHY.

SeeST_GEOGFROMWKB to construct aGEOGRAPHY from WKB.

Return type

BYTES

ST_ASGEOJSON

ST_ASGEOJSON(geography_expression)

Description

Returns theRFC 7946 compliantGeoJSONrepresentation of the inputGEOGRAPHY.

A GoogleSQLGEOGRAPHY has sphericalgeodesic edges, whereas a GeoJSONGeometry object explicitly has planar edges.To convert between these two types of edges, GoogleSQL adds additionalpoints to the line where necessary so that the resulting sequence of edgesremains within 10 meters of the original edge.

SeeST_GEOGFROMGEOJSON to construct aGEOGRAPHY from GeoJSON.

Return type

STRING

ST_ASTEXT

ST_ASTEXT(geography_expression)

Description

Returns theWKT representation of an inputGEOGRAPHY.

SeeST_GEOGFROMTEXT to construct aGEOGRAPHY from WKT.

Return type

STRING

ST_AZIMUTH

ST_AZIMUTH(point_geography_1,point_geography_2)

Description

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

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

• Pointing North is0
• Pointing East isPI/2
• Pointing South isPI
• Pointing West is3PI/2

ST_AZIMUTH has the following edge cases:

• If the two input points are the same, returnsNULL.
• If the two input points are exactly antipodal, returnsNULL.
• If either of the input geographies aren't single points or are the emptygeography, throws an error.

Return type

FLOAT64

Example

WITHgeosAS(SELECT1id,ST_GEOGPOINT(1,0)ASgeo1,ST_GEOGPOINT(0,0)ASgeo2UNIONALLSELECT2,ST_GEOGPOINT(0,0),ST_GEOGPOINT(1,0)UNIONALLSELECT3,ST_GEOGPOINT(0,0),ST_GEOGPOINT(0,1)UNIONALL-- identicalSELECT4,ST_GEOGPOINT(0,0),ST_GEOGPOINT(0,0)UNIONALL-- antipodeSELECT5,ST_GEOGPOINT(-30,0),ST_GEOGPOINT(150,0)UNIONALL-- nullsSELECT6,ST_GEOGPOINT(0,0),NULLUNIONALLSELECT7,NULL,ST_GEOGPOINT(0,0))SELECTST_AZIMUTH(geo1,geo2)ASazimuthFROMgeosORDERBYid;/*--------------------+ | azimuth            | +--------------------+ | 4.71238898038469   | | 1.5707963267948966 | | 0                  | | NULL               | | NULL               | | NULL               | | NULL               | +--------------------*/

ST_BOUNDARY

ST_BOUNDARY(geography_expression)

Description

Returns a singleGEOGRAPHY that contains the unionof the boundaries of each component in the given inputGEOGRAPHY.

The boundary of each component of aGEOGRAPHY isdefined 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 polygonshell and each of the polygon's holes.

Return type

GEOGRAPHY

ST_BOUNDINGBOX

ST_BOUNDINGBOX(geography_expression)

Description

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

Caveats:

• ReturnsNULL if the input isNULL or an empty geography.
• The bounding box might cross the antimeridian if this allows for a smallerrectangle. In this case, the bounding box has one of its longitudinal boundsoutside of the [-180, 180] range, so thatxmin is smaller than the eastmostvaluexmax.

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

WITHdataAS(SELECT1id,ST_GEOGFROMTEXT('POLYGON((-125 48, -124 46, -117 46, -117 49, -125 48))')gUNIONALLSELECT2id,ST_GEOGFROMTEXT('POLYGON((172 53, -130 55, -141 70, 172 53))')gUNIONALLSELECT3id,ST_GEOGFROMTEXT('POINT EMPTY')gUNIONALLSELECT4id,ST_GEOGFROMTEXT('POLYGON((172 53, -141 70, -130 55, 172 53))',oriented=>TRUE))SELECTid,ST_BOUNDINGBOX(g)ASboxFROMdata/*----+------------------------------------------+ | 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} | +----+------------------------------------------*/

SeeST_EXTENT for the aggregate version ofST_BOUNDINGBOX.

ST_BUFFER

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

Description

Returns aGEOGRAPHY that represents the buffer around the inputGEOGRAPHY.This function is similar toST_BUFFERWITHTOLERANCE,but you specify the number of segments instead of providing tolerance todetermine how much the resulting geography can deviate from the idealbuffer radius.

• geography: The inputGEOGRAPHY to encircle with the buffer radius.
• buffer_radius:FLOAT64 that represents the radius of thebuffer around the input geography. The radius is in meters. Note thatpolygons contract when buffered with a negativebuffer_radius. Polygonshells and holes that are contracted to a point are discarded.
• num_seg_quarter_circle: (Optional)FLOAT64 specifies thenumber of segments that are used to approximate a quarter circle. Thedefault value is8.0. Naming this argument is optional.
• endcap: (Optional)STRING allows you to specify one of two endcapstyles:ROUND andFLAT. The default value isROUND. This option onlyaffects the endcaps of buffered linestrings.
• side: (Optional)STRING allows you to specify one of three possibilitiesfor lines:BOTH,LEFT, andRIGHT. The default isBOTH. This optiononly affects how linestrings are buffered.
• use_spheroid: (Optional)BOOL determines how this function measuresdistance. Ifuse_spheroid isFALSE, the function measures distance onthe surface of a perfect sphere. Theuse_spheroid parametercurrently only supports the valueFALSE. The default value ofuse_spheroid isFALSE.

Return type

PolygonGEOGRAPHY

Example

The following example shows the result ofST_BUFFER on a point. A bufferedpoint is an approximated circle. Whennum_seg_quarter_circle = 2, there aretwo line segments in a quarter circle, and therefore the buffered circle haseight sides andST_NUMPOINTS returns nine vertices. Whennum_seg_quarter_circle = 8, there are eight line segments in a quarter circle,and therefore the buffered circle has thirty-two sides andST_NUMPOINTS returns thirty-three vertices.

SELECT-- num_seg_quarter_circle=2ST_NUMPOINTS(ST_BUFFER(ST_GEOGFROMTEXT('POINT(1 2)'),50,2))ASeight_sides,-- num_seg_quarter_circle=8, since 8 is the defaultST_NUMPOINTS(ST_BUFFER(ST_GEOGFROMTEXT('POINT(100 2)'),50))ASthirty_two_sides;/*-------------+------------------+ | eight_sides | thirty_two_sides | +-------------+------------------+ | 9           | 33               | +-------------+------------------*/

ST_BUFFERWITHTOLERANCE

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

Returns aGEOGRAPHY that represents the buffer around the inputGEOGRAPHY.This function is similar toST_BUFFER,but you provide tolerance instead of segments to determine how much theresulting geography can deviate from the ideal buffer radius.

• geography: The inputGEOGRAPHY to encircle with the buffer radius.
• buffer_radius:FLOAT64 that represents the radius of thebuffer around the input geography. The radius is in meters. Note thatpolygons contract when buffered with a negativebuffer_radius. Polygonshells and holes that are contracted to a point are discarded.
• tolerance_meters:FLOAT64 specifies a tolerance inmeters with which the shape is approximated. Tolerance determines how much apolygon can deviate from the ideal radius. Naming this argument is optional.
• endcap: (Optional)STRING allows you to specify one of two endcapstyles:ROUND andFLAT. The default value isROUND. This option onlyaffects the endcaps of buffered linestrings.
• side: (Optional)STRING allows you to specify one of three possible linestyles:BOTH,LEFT, andRIGHT. The default isBOTH. This option onlyaffects the endcaps of buffered linestrings.
• use_spheroid: (Optional)BOOL determines how this function measuresdistance. Ifuse_spheroid isFALSE, the function measures distance onthe surface of a perfect sphere. Theuse_spheroid parametercurrently only supports the valueFALSE. The default value ofuse_spheroid isFALSE.

Return type

PolygonGEOGRAPHY

Example

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

SELECT-- tolerance_meters=25, or 25% of the buffer radius.ST_NumPoints(ST_BUFFERWITHTOLERANCE(ST_GEOGFROMTEXT('POINT(1 2)'),100,25))ASfive_sides,-- tolerance_meters=1, or 1% of the buffer radius.st_NumPoints(ST_BUFFERWITHTOLERANCE(ST_GEOGFROMTEXT('POINT(100 2)'),100,1))AStwenty_four_sides;/*------------+-------------------+ | five_sides | twenty_four_sides | +------------+-------------------+ | 6          | 24                | +------------+-------------------*/

ST_CENTROID

ST_CENTROID(geography_expression)

Description

Returns thecentroid of the inputGEOGRAPHY as a single pointGEOGRAPHY.

Thecentroid of aGEOGRAPHY is the weighted average of the centroids of thehighest-dimensional components in theGEOGRAPHY. The centroid for componentsin 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 bylength. The centroid of each edge is the geodesic midpoint of the edge.
• The centroid of a polygon is its center of mass.

If the inputGEOGRAPHY is empty, an emptyGEOGRAPHY is returned.

Constraints

In the unlikely event that the centroid of aGEOGRAPHY can't be defined by asingle point on the surface of the Earth, a deterministic but otherwisearbitrary point is returned. This can only happen if the centroid is exactly atthe 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

PointGEOGRAPHY

ST_CENTROID_AGG

ST_CENTROID_AGG(geography)

Description

Computes the centroid of the set of inputGEOGRAPHYs as a single pointGEOGRAPHY.

Thecentroid over the set of inputGEOGRAPHYs is the weighted average of thecentroid of each individualGEOGRAPHY. Only theGEOGRAPHYs with the highestdimension present in the input contribute to the centroid of the entire set. Forexample, if the input contains bothGEOGRAPHYs with lines andGEOGRAPHYswith only points,ST_CENTROID_AGG returns the weighted average of theGEOGRAPHYs with lines, since a line has more dimensions than a point. In thisexample,ST_CENTROID_AGG ignoresGEOGRAPHYs with only points whencalculating the aggregate centroid.

ST_CENTROID_AGG ignoresNULL inputGEOGRAPHY values.

SeeST_CENTROID for the non-aggregate version ofST_CENTROID_AGG and the definition of centroid for an individualGEOGRAPHYvalue.

Return type

PointGEOGRAPHY

Example

The following queries compute the aggregate centroid over a set ofGEOGRAPHY values. The input to the first querycontains only points, and therefore each value contribute to the aggregatecentroid. Also notice thatST_CENTROID_AGG isnot equivalent to callingST_CENTROID on the result ofST_UNION_AGG; duplicates are removed by theunion, unlikeST_CENTROID_AGG. The input to the second query has mixeddimensions, and only values with the highest dimension in the set, the lines,affect the aggregate centroid.

SELECTST_CENTROID_AGG(points)ASst_centroid_agg,ST_CENTROID(ST_UNION_AGG(points))AScentroid_of_unionFROMUNNEST([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)        | +---------------------------+-------------------*/

SELECTST_CENTROID_AGG(points)ASst_centroid_aggFROMUNNEST([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 aGEOGRAPHY containing a point ongeography_1 with the smallest possible distance togeography_2. This impliesthat the distance between the point returned byST_CLOSESTPOINT andgeography_2 is less than or equal to the distance between any other point ongeography_1 andgeography_2.

If either of the inputGEOGRAPHYs is empty,ST_CLOSESTPOINT returnsNULL.

The optionaluse_spheroid parameter determines how this function measuresdistance. Ifuse_spheroid isFALSE, the function measures distance on thesurface of a perfect sphere.

Theuse_spheroid parameter currently only supportsthe valueFALSE. The default value ofuse_spheroid isFALSE.

Return type

PointGEOGRAPHY

ST_CLUSTERDBSCAN

ST_CLUSTERDBSCAN(geography_column,epsilon,minimum_geographies)OVERover_clauseover_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{ASC|DESC}][,...]]

PerformsDBSCAN clustering on a column of geographies. Returns a0-based cluster number.

To learn more about theOVER clause and how to use it, seeWindow function calls.

Input parameters

• geography_column: A column ofGEOGRAPHYs thatis clustered.
• epsilon: The epsilon that specifies the radius, measured in meters, arounda core value. Non-negativeFLOAT64 value.
• minimum_geographies: Specifies the minimum number of geographies in asingle cluster. Only dense input forms a cluster, otherwise it's classifiedas noise. Non-negativeINT64 value.

Geography types and the DBSCAN algorithm

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

• Core value: A geography is a core value if it's withinepsilon distanceofminimum_geographies geographies, including itself. The core valuestarts 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 allother core and border values that are withinepsilon distance.
• Border value: A geography is a border value if it's within epsilon distanceof a core value. It's added to the same cluster as a core value withinepsilon distance. A border value may be withinepsilon distance of morethan one cluster. In this case, it may be arbitrarily assigned to eithercluster and the function will produce the same result in subsequent calls.
• Noise: A geography is noise if it's neither a core nor a border value.Noise values are assigned to aNULL cluster. An emptyGEOGRAPHY is always classified as noise.

Constraints

• The argumentminimum_geographies is a non-negativeINT64andepsilon is a non-negativeFLOAT64.
• An empty geography can't join any cluster.
• Multiple clustering assignments could be possible for a border value. If ageography is a border value,ST_CLUSTERDBSCAN will assign it to anarbitrary 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 aminimum_geographies argument of 1. The geographies being analyzed are amixture of points, lines, and polygons.

WITHGeosas(SELECT1asrow_id,ST_GEOGFROMTEXT('POINT EMPTY')asgeoUNIONALLSELECT2,ST_GEOGFROMTEXT('MULTIPOINT(1 1, 2 2, 4 4, 5 2)')UNIONALLSELECT3,ST_GEOGFROMTEXT('POINT(14 15)')UNIONALLSELECT4,ST_GEOGFROMTEXT('LINESTRING(40 1, 42 34, 44 39)')UNIONALLSELECT5,ST_GEOGFROMTEXT('POLYGON((40 2, 40 1, 41 2, 40 2))'))SELECTrow_id,geo,ST_CLUSTERDBSCAN(geo,1e5,1)OVER()AScluster_numFROMGeosORDERBYrow_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

ReturnsTRUE if no point ofgeography_2 is outsidegeography_1, andthe interiors intersect; returnsFALSE otherwise.

NOTE: AGEOGRAPHYdoes not contain its ownboundary. Compare withST_COVERS.

Return type

BOOL

Example

The following query tests whether the polygonPOLYGON((1 1, 20 1, 10 20, 1 1))contains each of the three points(0, 0),(1, 1), and(10, 10), which lieon the exterior, the boundary, and the interior of the polygon respectively.

SELECTST_GEOGPOINT(i,i)ASp,ST_CONTAINS(ST_GEOGFROMTEXT('POLYGON((1 1, 20 1, 10 20, 1 1))'),ST_GEOGPOINT(i,i))AS`contains`FROMUNNEST([0,1,10])ASi;/*--------------+----------+ | 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 inputGEOGRAPHY. The convex hull is thesmallest convexGEOGRAPHY that covers the input. AGEOGRAPHY is convex iffor every pair of points in theGEOGRAPHY, the geodesic edge connecting thepoints are also contained in the sameGEOGRAPHY.

In most cases, the convex hull consists of a single polygon. Notable edge casesinclude 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 asthat linestring is convex.
• If the inputGEOGRAPHY spans more than ahemisphere, the convex hull is the full globe. This includes any input thatcontains a pair of antipodal points.
• ST_CONVEXHULL returnsNULL if the input is eitherNULL or the emptyGEOGRAPHY.

Return type

GEOGRAPHY

Examples

The convex hull returned byST_CONVEXHULL can be a point, linestring, or apolygon, depending on the input.

WITHGeographiesAS(SELECTST_GEOGFROMTEXT('POINT(1 1)')ASgUNIONALLSELECTST_GEOGFROMTEXT('LINESTRING(1 1, 2 2)')ASgUNIONALLSELECTST_GEOGFROMTEXT('MULTIPOINT(2 11, 4 12, 0 15, 1 9, 1 12)')ASg)SELECTgASinput_geography,ST_CONVEXHULL(g)ASconvex_hullFROMGeographies;/*-----------------------------------------+--------------------------------------------------------+ |             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

ReturnsFALSE ifgeography_1 orgeography_2 is empty. ReturnsTRUE if nopoints ofgeography_1 lie in the exterior ofgeography_2.

Given twoGEOGRAPHYsa andb,ST_COVEREDBY(a, b) returns the same result asST_COVERS(b, a). Note the opposite order of arguments.

Return type

BOOL

ST_COVERS

ST_COVERS(geography_1,geography_2)

Description

ReturnsFALSE ifgeography_1 orgeography_2 is empty.ReturnsTRUE if no points ofgeography_2 lie in the exterior ofgeography_1.

Return type

BOOL

Example

The following query tests whether the polygonPOLYGON((1 1, 20 1, 10 20, 1 1))covers each of the three points(0, 0),(1, 1), and(10, 10), which lieon the exterior, the boundary, and the interior of the polygon respectively.

SELECTST_GEOGPOINT(i,i)ASp,ST_COVERS(ST_GEOGFROMTEXT('POLYGON((1 1, 20 1, 10 20, 1 1))'),ST_GEOGPOINT(i,i))AS`covers`FROMUNNEST([0,1,10])ASi;/*--------------+--------+ | p            | covers | +--------------+--------+ | POINT(0 0)   | FALSE  | | POINT(1 1)   | TRUE   | | POINT(10 10) | TRUE   | +--------------+--------*/

ST_DIFFERENCE

ST_DIFFERENCE(geography_1,geography_2)

Description

Returns aGEOGRAPHY that represents the point setdifference ofgeography_1 andgeography_2. Therefore, the result consists ofthe part ofgeography_1 that doesn't intersect withgeography_2.

Ifgeometry_1 is completely contained ingeometry_2, thenST_DIFFERENCEreturns an emptyGEOGRAPHY.

Constraints

The underlying geometric objects that a GoogleSQLGEOGRAPHY represents correspond to aclosed pointset. Therefore,ST_DIFFERENCE is the closure of the point set difference ofgeography_1 andgeography_2. This implies that ifgeography_1 andgeography_2 intersect, then a portion of the boundary ofgeography_2 couldbe in the difference.

Return type

GEOGRAPHY

Example

The following query illustrates the difference betweengeog1, a larger polygonPOLYGON((0 0, 10 0, 10 10, 0 0)) andgeog2, a smaller polygonPOLYGON((4 2, 6 2, 8 6, 4 2)) that intersects withgeog1. The result isgeog1 with a hole wheregeog2 intersects with it.

SELECTST_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 inputGEOGRAPHY.

The dimension of each possible element is as follows:

• The dimension of a point is0.
• The dimension of a linestring is1.
• The dimension of a polygon is2.

If the inputGEOGRAPHY is empty,ST_DIMENSIONreturns-1.

Return type

INT64

ST_DISJOINT

ST_DISJOINT(geography_1,geography_2)

Description

ReturnsTRUE if the intersection ofgeography_1 andgeography_2 is empty,that is, no point ingeography_1 also appears ingeography_2.

ST_DISJOINT is the logical negation ofST_INTERSECTS.

Return type

BOOL

ST_DISTANCE

ST_DISTANCE(geography_1,geography_2[,use_spheroid])

Description

Returns the shortest distance in meters between two non-emptyGEOGRAPHYs.

If either of the inputGEOGRAPHYs is empty,ST_DISTANCE returnsNULL.

The optionaluse_spheroid parameter determines how this function measuresdistance. Ifuse_spheroid isFALSE, the function measures distance on thesurface of a perfect sphere. Ifuse_spheroid isTRUE, the function measuresdistance on the surface of theWGS84 spheroid. The default valueofuse_spheroid isFALSE.

Return type

FLOAT64

ST_DUMP

ST_DUMP(geography[,dimension])

Description

Returns anARRAY of simpleGEOGRAPHYs where each element is a component ofthe inputGEOGRAPHY. A simpleGEOGRAPHY consists of a single point, linestring,or polygon. If the inputGEOGRAPHY is simple, theresult is a single element. When the inputGEOGRAPHY is a collection,ST_DUMP returns anARRAY with one simpleGEOGRAPHY for each component in the collection.

Ifdimension is provided, the function only returnsGEOGRAPHYs of the corresponding dimension. Adimension of -1 is equivalent to omittingdimension.

Return Type

ARRAY<GEOGRAPHY>

Examples

The following example shows howST_DUMP returns the simple geographies withina complex geography.

WITHexampleAS(SELECTST_GEOGFROMTEXT('POINT(0 0)')ASgeographyUNIONALLSELECTST_GEOGFROMTEXT('MULTIPOINT(0 0, 1 1)')ASgeographyUNIONALLSELECTST_GEOGFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(1 2, 2 1))'))SELECTgeographyASoriginal_geography,ST_DUMP(geography)ASdumped_geographiesFROMexample/*-------------------------------------+------------------------------------+ |         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 howST_DUMP with the dimension argument onlyreturns simple geographies of the given dimension.

WITHexampleAS(SELECTST_GEOGFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(1 2, 2 1))')ASgeography)SELECTgeographyASoriginal_geography,ST_DUMP(geography,1)ASdumped_geographiesFROMexample/*-------------------------------------+------------------------------+ |         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

ReturnsTRUE if the distance between at least one point ingeography_1 andone point ingeography_2 is less than or equal to the distance given by thedistance argument; otherwise, returnsFALSE. If either inputGEOGRAPHY is empty,ST_DWithin returnsFALSE. Thegivendistance is in meters on the surface of the Earth.

The optionaluse_spheroid parameter determines how this function measuresdistance. Ifuse_spheroid isFALSE, the function measures distance on thesurface of a perfect sphere.

Theuse_spheroid parameter currently only supportsthe valueFALSE. The default value ofuse_spheroid isFALSE.

Return type

BOOL

ST_ENDPOINT

ST_ENDPOINT(linestring_geography)

Description

Returns the last point of a linestring geography as a point geography. Returnsan error if the input isn't a linestring or if the input is empty. Use theSAFE prefix to obtainNULL for invalid input instead of an error.

Return Type

PointGEOGRAPHY

Example

SELECTST_ENDPOINT(ST_GEOGFROMTEXT('LINESTRING(1 1, 2 1, 3 2, 3 3)'))last/*--------------+ | last         | +--------------+ | POINT(3 3)   | +--------------*/

ST_EQUALS

ST_EQUALS(geography_1,geography_2)

Description

Checks if twoGEOGRAPHY values represent the sameGEOGRAPHY value. ReturnsTRUE if the values are the same, otherwise returnsFALSE.

Definitions

• geography_1: The firstGEOGRAPHY value to compare.
• geography_2: The secondGEOGRAPHY value to compare.

Details

As long as they still represent the same geometric structure, twoGEOGRAPHY values can be equal even if the ordering of points or verticesdiffer. This means that one of the following conditions must be true for thisfunction to returnTRUE:

• BothST_COVERS(geography_1, geography_2) andST_COVERS(geography_2, geography_1) areTRUE.
• Bothgeography_1 andgeography_2 are empty.

ST_EQUALS isn't guaranteed to be a transitive function.

Return type

BOOL

ST_EXTENT

ST_EXTENT(geography_expression)

Description

Returns aSTRUCT that represents the bounding box for the set of inputGEOGRAPHY values. The bounding box is the minimal rectangle that encloses thegeography. The edges of the rectangle follow constant lines of longitude andlatitude.

Caveats:

• ReturnsNULL if all the inputs areNULL or empty geographies.
• The bounding box might cross the antimeridian if this allows for a smallerrectangle. In this case, the bounding box has one of its longitudinal boundsoutside of the [-180, 180] range, so thatxmin is smaller than the eastmostvaluexmax.
• If the longitude span of the bounding box is larger than or equal to 180degrees, 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

WITHdataAS(SELECT1id,ST_GEOGFROMTEXT('POLYGON((-125 48, -124 46, -117 46, -117 49, -125 48))')gUNIONALLSELECT2id,ST_GEOGFROMTEXT('POLYGON((172 53, -130 55, -141 70, 172 53))')gUNIONALLSELECT3id,ST_GEOGFROMTEXT('POINT EMPTY')g)SELECTST_EXTENT(g)ASboxFROMdata/*----------------------------------------------+ | box                                          | +----------------------------------------------+ | {xmin:172, ymin:46, xmax:243, ymax:70}       | +----------------------------------------------*/

ST_BOUNDINGBOX for the non-aggregate version ofST_EXTENT.

ST_EXTERIORRING

ST_EXTERIORRING(polygon_geography)

Description

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

• If the input geography is a polygon, gets the outermost ring of the polygongeography and returns the corresponding linestring.
• If the input is the fullGEOGRAPHY, returns an empty geography.
• Returns an error if the input isn't a single polygon.

Use theSAFE prefix to returnNULL for invalid input instead of an error.

Return type

• LinestringGEOGRAPHY
• EmptyGEOGRAPHY

Examples

WITHgeoas(SELECTST_GEOGFROMTEXT('POLYGON((0 0, 1 4, 2 2, 0 0))')ASgUNIONALLSELECTST_GEOGFROMTEXT('''POLYGON((1 1, 1 10, 5 10, 5 1, 1 1),                                  (2 2, 3 4, 2 4, 2 2))''')asg)SELECTST_EXTERIORRING(g)ASringFROMgeo;/*---------------------------------------+ | 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 aSTRING orBYTES value into aGEOGRAPHY value.

Ifexpression represents aSTRING value, it must be a validGEOGRAPHY representation in one of the following formats:

• WKT format. To learn more about this format and the requirements to use it,seeST_GEOGFROMTEXT.
• WKB in hexadecimal text format. To learn more about this format and therequirements to use it, seeST_GEOGFROMWKB.
• GeoJSON format. To learn more about this format and therequirements to use it, seeST_GEOGFROMGEOJSON.

Ifexpression represents aBYTES value, it must be a validGEOGRAPHYbinary expression in WKB format. To learn more about this format and therequirements to use it, seeST_GEOGFROMWKB.

Ifexpression isNULL, the output isNULL.

Return type

GEOGRAPHY

Examples

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

SELECTST_GEOGFROM('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))')ASWKT_format;/*------------------------------------+ | WKT_format                         | +------------------------------------+ | POLYGON((2 0, 2 2, 0 2, 0 0, 2 0)) | +------------------------------------*/

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

SELECTST_GEOGFROM(FROM_HEX('010100000000000000000000400000000000001040'))ASWKB_format;/*----------------+ | WKB_format     | +----------------+ | POINT(2 4)     | +----------------*/

This takes WKB-formatted bytes and returns aGEOGRAPHY point:

SELECTST_GEOGFROM('010100000000000000000000400000000000001040')ASWKB_format;/*----------------+ | WKB_format     | +----------------+ | POINT(2 4)     | +----------------*/

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

SELECTST_GEOGFROM('{ "type": "Polygon", "coordinates": [ [ [2, 0], [2, 2], [1, 2], [0, 2], [0, 0], [2, 0] ] ] }')ASGEOJSON_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 aGEOGRAPHY value that corresponds to theinputGeoJSON representation.

ST_GEOGFROMGEOJSON accepts input that'sRFC 7946compliant.

If the named argumentmake_valid is set toTRUE, the function attempts torepair polygons that don't conform toOpen Geospatial Consortiumsemantics.

A GoogleSQLGEOGRAPHY has sphericalgeodesic edges, whereas a GeoJSONGeometry object explicitly has planar edges.To convert between these two types of edges, GoogleSQL adds additionalpoints to the line where necessary so that the resulting sequence of edgesremains within 10 meters of the original edge.

SeeST_ASGEOJSON to format aGEOGRAPHY as GeoJSON.

Constraints

The JSON input is subject to the following constraints:

• ST_GEOGFROMGEOJSON only accepts JSON geometry fragments and can't be usedto ingest a whole JSON document.
• The input JSON fragment must consist of a GeoJSON geometry type, whichincludesPoint,MultiPoint,LineString,MultiLineString,Polygon,MultiPolygon, andGeometryCollection. Any other GeoJSON type such asFeature orFeatureCollection will result in an error.
• A position in thecoordinates member of a GeoJSON geometry type mustconsist of exactly two elements. The first is the longitude and the secondis the latitude. Therefore,ST_GEOGFROMGEOJSON doesn't support theoptional third element for a position in thecoordinates member.

Return type

GEOGRAPHY

ST_GEOGFROMTEXT

ST_GEOGFROMTEXT(wkt_string[,oriented=>value][,planar=>value][,make_valid=>value])

Description

Converts aSTRINGWKT geometry value into aGEOGRAPHYvalue.

To formatGEOGRAPHY value as WKT, useST_ASTEXT.

Definitions

• wkt_string: ASTRING value that contains theWKT format.

• oriented: A named argument with aBOOL literal.

  • If the value isTRUE, any polygons in the input are assumed to beoriented as follows: when traveling along the boundary of the polygonin the order of the input vertices, the interior of the polygon is onthe left. This allows WKT to represent polygons larger than ahemisphere. See alsoST_MAKEPOLYGONORIENTED,which is similar toST_GEOGFROMTEXT withoriented=TRUE.

  • If the value isFALSE or omitted, this function returns the polygonwith the smaller area.

• planar: A named argument with aBOOL literal. If the valueisTRUE, the edges of the linestrings and polygons are assumed to useplanar map semantics, rather than GoogleSQL default sphericalgeodesics semantics. For more information about thedifferences between spherical geodesics and planar lines, seeCoordinate systems and edges.

• make_valid: A named argument with aBOOL literal. If thevalue isTRUE, the function attempts to repair polygons that don'tconform toOpen Geospatial Consortium semantics.

Details

• The function doesn't support three-dimensional geometries that have aZsuffix, nor does it support linear referencing system geometries with anMsuffix.
• oriented andplanar can't beTRUE at the same time.
• oriented andmake_valid can't beTRUE at the same time.

Example

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

WITHpolygonAS(SELECT'POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))'ASp)SELECTST_CONTAINS(ST_GEOGFROMTEXT(p),ST_GEOGPOINT(1,1))ASfromtext_default,ST_CONTAINS(ST_GEOGFROMTEXT(p,oriented=>FALSE),ST_GEOGPOINT(1,1))ASnon_oriented,ST_CONTAINS(ST_GEOGFROMTEXT(p,oriented=>TRUE),ST_GEOGPOINT(1,1))ASorientedFROMpolygon;/*-------------------+---------------+-----------+ | fromtext_default  | non_oriented  | oriented  | +-------------------+---------------+-----------+ | TRUE              | TRUE          | FALSE     | +-------------------+---------------+-----------*/

The following query converts a WKT string with an invalid polygon toGEOGRAPHY. The WKT string violates two propertiesof a valid polygon - the loop describing the polygon isn't closed, and itcontains self-intersection. With themake_valid option,ST_GEOGFROMTEXTsuccessfully converts it to a multipolygon shape.

WITHdataAS(SELECT'POLYGON((0 -1, 2 1, 2 -1, 0 1))'wkt)SELECTSAFE.ST_GEOGFROMTEXT(wkt)asgeom,SAFE.ST_GEOGFROMTEXT(wkt,make_valid=>TRUE)asvalid_geomFROMdata/*------+-----------------------------------------------------------------+ | 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[,oriented=>value][,planar=>value][,make_valid=>value])

ST_GEOGFROMWKB(wkb_hex_string_expression[,oriented=>value][,planar=>value][,make_valid=>value])

Description

Converts an expression from a hexadecimal-textSTRING orBYTESvalue into aGEOGRAPHY value. The expression must be inWKB format.

To formatGEOGRAPHY as WKB, useST_ASBINARY.

Definitions

• wkb_bytes_expression: ABYTES value that contains theWKBformat.
• wkb_hex_string_expression: ASTRING value that contains thehexadecimal-encodedWKB format.

• oriented: A named argument with aBOOL literal.

  • If the value isTRUE, any polygons in the input are assumed to beoriented as follows: when traveling along the boundary of the polygonin the order of the input vertices, the interior of the polygon is onthe left. This allows WKB to represent polygons larger than ahemisphere. See alsoST_MAKEPOLYGONORIENTED,which is similar toST_GEOGFROMWKB withoriented=TRUE.

  • If the value isFALSE or omitted, this function returns the polygonwith the smaller area.

• planar: A named argument with aBOOL literal. If the valueisTRUE, the edges of the linestrings and polygons are assumed to useplanar map semantics, rather than GoogleSQL default sphericalgeodesics semantics. For more information about thedifferences between spherical geodesics and planar lines, seeCoordinate systems and edges.

• make_valid: A named argument with aBOOL literal. If thevalue isTRUE, the function attempts to repair polygons thatdon't conform toOpen Geospatial Consortium semantics.

Details

• The function doesn't support three-dimensional geometries that have aZsuffix, nor does it support linear referencing system geometries with anMsuffix.
• oriented andplanar can't beTRUE at the same time.
• oriented andmake_valid can't beTRUE at the same time.

Return type

GEOGRAPHY

Example

The following query reads the hex-encoded WKB data containingLINESTRING(1 1, 3 2) and uses it with planar and geodesic semantics. Whenplanar is used, the function approximates the planar input line usingline that contains a chain of geodesic segments.

WITHwkb_dataAS(SELECT'010200000002000000feffffffffffef3f000000000000f03f01000000000008400000000000000040'geo)SELECTST_GeogFromWkb(geo,planar=>TRUE)ASfrom_planar,ST_GeogFromWkb(geo,planar=>FALSE)ASfrom_geodesic,FROMwkb_data/*---------------------------------------+----------------------+ | from_planar                           | from_geodesic        | +---------------------------------------+----------------------+ | LINESTRING(1 1, 2 1.5, 2.5 1.75, 3 2) | LINESTRING(1 1, 3 2) | +---------------------------------------+----------------------*/

ST_GEOGPOINT

ST_GEOGPOINT(longitude,latitude)

Description

Creates aGEOGRAPHY with a single point.ST_GEOGPOINT creates a point fromthe specifiedFLOAT64 longitude (in degrees,negative west of the Prime Meridian, positive east) and latitude (in degrees,positive north of the Equator, negative south) parameters and returns that pointin aGEOGRAPHY value.

NOTE: Some systems present latitude first; take care with argument order.

Constraints

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

Return type

PointGEOGRAPHY

ST_GEOGPOINTFROMGEOHASH

ST_GEOGPOINTFROMGEOHASH(geohash)

Description

Returns aGEOGRAPHY value that corresponds to apoint in the middle of a bounding box defined in theGeoHash.

Return type

PointGEOGRAPHY

ST_GEOHASH

ST_GEOHASH(geography_expression[,maxchars])

Description

Takes a single-pointGEOGRAPHY and returns aGeoHashrepresentation of thatGEOGRAPHY object.

• geography_expression: Represents aGEOGRAPHY object. Only aGEOGRAPHYobject that represents a single point is supported. IfST_GEOHASH is usedover an emptyGEOGRAPHY object, returnsNULL.
• maxchars: This optionalINT64 parameter specifies the maximum number ofcharacters the hash will contain. Fewer characters corresponds to lowerprecision (or, described differently, to a bigger bounding box).maxcharsdefaults to 20 if not explicitly specified. A validmaxchars value is 1to 20. Any value below or above is considered unspecified and the default of20 is used.

Return type

STRING

Example

Returns a GeoHash of the Seattle Center with 10 characters of precision.

SELECTST_GEOHASH(ST_GEOGPOINT(-122.35,47.62),10)geohash/*--------------+ | geohash      | +--------------+ | c22yzugqw7   | +--------------*/

ST_GEOMETRYTYPE

ST_GEOMETRYTYPE(geography_expression)

Description

Returns theOpen Geospatial Consortium (OGC) geometry type thatdescribes the inputGEOGRAPHY. The OGC geometry type matches thetypes that are used inWKT andGeoJSON formats andprinted forST_ASTEXT andST_ASGEOJSON.ST_GEOMETRYTYPE returns the OGC geometry type with the "ST_" prefix.

ST_GEOMETRYTYPE returns the following given the type on the input:

• Single point geography: ReturnsST_Point.
• Collection of only points: ReturnsST_MultiPoint.
• Single linestring geography: ReturnsST_LineString.
• Collection of only linestrings: ReturnsST_MultiLineString.
• Single polygon geography: ReturnsST_Polygon.
• Collection of only polygons: ReturnsST_MultiPolygon.
• Collection with elements of different dimensions, or the input is the emptygeography: ReturnsST_GeometryCollection.

Return type

STRING

Example

The following example shows howST_GEOMETRYTYPE takes geographies and returnsthe names of their OGC geometry types.

WITHexampleAS(SELECTST_GEOGFROMTEXT('POINT(0 1)')ASgeographyUNIONALLSELECTST_GEOGFROMTEXT('MULTILINESTRING((2 2, 3 4), (5 6, 7 7))')UNIONALLSELECTST_GEOGFROMTEXT('GEOMETRYCOLLECTION(MULTIPOINT(-1 2, 0 12), LINESTRING(-2 4, 0 6))')UNIONALLSELECTST_GEOGFROMTEXT('GEOMETRYCOLLECTION EMPTY'))SELECTgeographyASWKT,ST_GEOMETRYTYPE(geography)ASgeometry_type_nameFROMexample;/*-------------------------------------------------------------------+-----------------------+ | WKT                                                               | geometry_type_name    | +-------------------------------------------------------------------+-----------------------+ | POINT(0 1)                                                        | ST_Point              | | MULTILINESTRING((2 2, 3 4), (5 6, 7 7))                           | ST_MultiLineString    | | GEOMETRYCOLLECTION(MULTIPOINT(-1 2, 0 12), LINESTRING(-2 4, 0 6)) | ST_GeometryCollection | | GEOMETRYCOLLECTION EMPTY                                          | ST_GeometryCollection | +-------------------------------------------------------------------+-----------------------*/

ST_HAUSDORFFDISTANCE

ST_HAUSDORFFDISTANCE(geography_1,geography_2[,directed=>{TRUE|FALSE}])

Description

Gets the discreteHausdorff distance, which is the greatest of allthe distances from a discrete point in one geography to the closestdiscrete point in another geography.

Definitions

• geography_1: AGEOGRAPHY value that represents the first geography.
• geography_2: AGEOGRAPHY value that represents the second geography.

• directed: A named argument with aBOOL value. Represents the type ofcomputation to use on the input geographies. If this argument isn'tspecified,directed => FALSE is used by default.

  • FALSE: The largest Hausdorff distance found in(geography_1,geography_2) and(geography_2,geography_1).

  • TRUE (default): The Hausdorff distance for(geography_1,geography_2).

Details

If an input geography isNULL, the function returnsNULL.

Return type

FLOAT64

Example

The following query gets the Hausdorff distance betweengeo1 andgeo2:

WITHdataAS(SELECTST_GEOGFROMTEXT('LINESTRING(20 70, 70 60, 10 70, 70 70)')ASgeo1,ST_GEOGFROMTEXT('LINESTRING(20 90, 30 90, 60 10, 90 10)')ASgeo2)SELECTST_HAUSDORFFDISTANCE(geo1,geo2,directed=>TRUE)ASdistanceFROMdata;/*--------------------+ | distance           | +--------------------+ | 1688933.9832041925 | +--------------------*/

The following query gets the Hausdorff distance betweengeo2 andgeo1:

WITHdataAS(SELECTST_GEOGFROMTEXT('LINESTRING(20 70, 70 60, 10 70, 70 70)')ASgeo1,ST_GEOGFROMTEXT('LINESTRING(20 90, 30 90, 60 10, 90 10)')ASgeo2)SELECTST_HAUSDORFFDISTANCE(geo2,geo1,directed=>TRUE)ASdistanceFROMdata;/*--------------------+ | distance           | +--------------------+ | 5802892.745488612  | +--------------------*/

The following query gets the largest Hausdorff distance between(geo1 andgeo2) and (geo2 andgeo1):

WITHdataAS(SELECTST_GEOGFROMTEXT('LINESTRING(20 70, 70 60, 10 70, 70 70)')ASgeo1,ST_GEOGFROMTEXT('LINESTRING(20 90, 30 90, 60 10, 90 10)')ASgeo2)SELECTST_HAUSDORFFDISTANCE(geo1,geo2,directed=>FALSE)ASdistanceFROMdata;/*--------------------+ | distance           | +--------------------+ | 5802892.745488612  | +--------------------*/

The following query produces the same results as the previous query becauseST_HAUSDORFFDISTANCE usesdirected=>FALSE by default.

WITHdataAS(SELECTST_GEOGFROMTEXT('LINESTRING(20 70, 70 60, 10 70, 70 70)')ASgeo1,ST_GEOGFROMTEXT('LINESTRING(20 90, 30 90, 60 10, 90 10)')ASgeo2)SELECTST_HAUSDORFFDISTANCE(geo1,geo2)ASdistanceFROMdata;

ST_HAUSDORFFDWITHIN

ST_HAUSDORFFDWITHIN(geography_1,geography_2,distance[,directed=>{TRUE|FALSE}])

Description

ReturnsTRUE if theHausdorff distance betweengeography_1 andgeography_2 is less than or equal to the distance given by thedistance argument; otherwise, returnsFALSE.

Definitions

• geography_1: AGEOGRAPHY value that represents the first geography.
• geography_2: AGEOGRAPHY value that represents the second geography.
• distance: AFLOAT64 value that represents meters on thesurface of the Earth.

• directed: A named argument with aBOOL value. Represents the type ofcomputation to use on the input geographies. If this argument isn'tspecified,directed => FALSE is used by default.

  • FALSE: The largest Hausdorff distance found in(geography_1,geography_2) and(geography_2,geography_1).

  • TRUE (default): The Hausdorff distance for(geography_1,geography_2).

Details

If an input geography isNULL, the function returnsNULL.

Return type

BOOL

Examples

The following example checks whether the Hausdorff distance between the firstand second geographies is less than or equal to 100,000 meters.

SELECTST_HAUSDORFFDWITHIN(ST_GEOGFROMTEXT('LINESTRING(10 1, 20 1)'),ST_GEOGFROMTEXT('LINESTRING(10 2, 20 2)'),100000)ASis_close;/*----------+ | is_close | +----------+ | false    | +----------*/

ST_INTERIORRINGS

ST_INTERIORRINGS(polygon_geography)

Description

Returns an array of linestring geographies that corresponds to the interiorrings of a polygon geography. Each interior ring is the border of a hole withinthe input polygon.

• If the input geography is a polygon, excludes the outermost ring of thepolygon geography and returns the linestrings corresponding to the interiorrings.
• If the input is the fullGEOGRAPHY, returns an empty array.
• If the input polygon has no holes, returns an empty array.
• Returns an error if the input isn't a single polygon.

Use theSAFE prefix to returnNULL for invalid input instead of an error.

Return type

ARRAY<LineString GEOGRAPHY>

Examples

WITHgeoAS(SELECTST_GEOGFROMTEXT('POLYGON((0 0, 1 1, 1 2, 0 0))')ASgUNIONALLSELECTST_GEOGFROMTEXT('POLYGON((1 1, 1 10, 5 10, 5 1, 1 1), (2 2, 3 4, 2 4, 2 2))')UNIONALLSELECTST_GEOGFROMTEXT('POLYGON((1 1, 1 10, 5 10, 5 1, 1 1), (2 2.5, 3.5 3, 2.5 2, 2 2.5), (3.5 7, 4 6, 3 3, 3.5 7))')UNIONALLSELECTST_GEOGFROMTEXT('fullglobe')UNIONALLSELECTNULL)SELECTST_INTERIORRINGS(g)ASringsFROMgeo;/*----------------------------------------------------------------------------+ | rings                                                                      | +----------------------------------------------------------------------------+ | []                                                                         | | [LINESTRING(2 2, 3 4, 2 4, 2 2)]                                           | | [LINESTRING(2.5 2, 3.5 3, 2 2.5, 2.5 2), LINESTRING(3 3, 4 6, 3.5 7, 3 3)] | | []                                                                         | | NULL                                                                       | +----------------------------------------------------------------------------*/

ST_INTERSECTION

ST_INTERSECTION(geography_1,geography_2)

Description

Returns aGEOGRAPHY that represents the point setintersection of the two inputGEOGRAPHYs. Thus,every point in the intersection appears in bothgeography_1 andgeography_2.

If the two inputGEOGRAPHYs are disjoint, that is,there are no points that appear in both inputgeometry_1 andgeometry_2,then an emptyGEOGRAPHY is returned.

SeeST_INTERSECTS,ST_DISJOINT for relatedpredicate functions.

Return type

GEOGRAPHY

ST_INTERSECTS

ST_INTERSECTS(geography_1,geography_2)

Description

ReturnsTRUE if the point set intersection ofgeography_1 andgeography_2is non-empty. Thus, this function returnsTRUE if there is at least one pointthat appears in both inputGEOGRAPHYs.

IfST_INTERSECTS returnsTRUE, it implies thatST_DISJOINTreturnsFALSE.

Return type

BOOL

ST_INTERSECTSBOX

ST_INTERSECTSBOX(geography,lng1,lat1,lng2,lat2)

Description

ReturnsTRUE ifgeography intersects the rectangle between[lng1, lng2]and[lat1, lat2]. The edges of the rectangle follow constant lines oflongitude and latitude.lng1 andlng2 specify the westmost and eastmostconstant longitude lines that bound the rectangle, andlat1 andlat2 specifythe minimum and maximum constant latitude lines that bound the rectangle.

Specify all longitude and latitude arguments in degrees.

Constraints

The input arguments are subject to the following constraints:

• Latitudes should be in the[-90, 90] degree range.
• Longitudes should follow either of the following rules:
  • Both longitudes are in the[-180, 180] degree range.
  • One of the longitudes is in the[-180, 180] degree range, andlng2 - lng1 is in the[0, 360] interval.

Return type

BOOL

Example

SELECTp,ST_INTERSECTSBOX(p,-90,0,90,20)ASbox1,ST_INTERSECTSBOX(p,90,0,-90,20)ASbox2FROMUNNEST([ST_GEOGPOINT(10,10),ST_GEOGPOINT(170,10),ST_GEOGPOINT(30,30)])p/*----------------+--------------+--------------+ | p              | box1         | box2         | +----------------+--------------+--------------+ | POINT(10 10)   | TRUE         | FALSE        | | POINT(170 10)  | FALSE        | TRUE         | | POINT(30 30)   | FALSE        | FALSE        | +----------------+--------------+--------------*/

ST_ISCLOSED

ST_ISCLOSED(geography_expression)

Description

ReturnsTRUE for a non-empty Geography, where each element in the Geographyhas an empty boundary. The boundary for each element can be defined withST_BOUNDARY.

• A point is closed.
• A linestring is closed if the start and end points of the linestring arethe same.
• A polygon is closed only if it's a full polygon.
• A collection is closed if and only if every element in the collection isclosed.

An emptyGEOGRAPHY isn't closed.

Return type

BOOL

Example

WITHexampleAS(SELECTST_GEOGFROMTEXT('POINT(5 0)')ASgeographyUNIONALLSELECTST_GEOGFROMTEXT('LINESTRING(0 1, 4 3, 2 6, 0 1)')ASgeographyUNIONALLSELECTST_GEOGFROMTEXT('LINESTRING(2 6, 1 3, 3 9)')ASgeographyUNIONALLSELECTST_GEOGFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(1 2, 2 1))')ASgeographyUNIONALLSELECTST_GEOGFROMTEXT('GEOMETRYCOLLECTION EMPTY'))SELECTgeography,ST_ISCLOSED(geography)ASis_closed,FROMexample;/*------------------------------------------------------+-----------+ | geography                                            | is_closed | +------------------------------------------------------+-----------+ | POINT(5 0)                                           | TRUE      | | LINESTRING(0 1, 4 3, 2 6, 0 1)                       | TRUE      | | LINESTRING(2 6, 1 3, 3 9)                            | FALSE     | | GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(1 2, 2 1)) | FALSE     | | GEOMETRYCOLLECTION EMPTY                             | FALSE     | +------------------------------------------------------+-----------*/

ST_ISCOLLECTION

ST_ISCOLLECTION(geography_expression)

Description

ReturnsTRUE if the total number of points, linestrings, and polygons isgreater than one.

An emptyGEOGRAPHY isn't a collection.

Return type

BOOL

ST_ISEMPTY

ST_ISEMPTY(geography_expression)

Description

ReturnsTRUE if the givenGEOGRAPHY is empty; that is, theGEOGRAPHYdoesn't contain any points, lines, or polygons.

NOTE: An emptyGEOGRAPHY isn't associated with a particular geometry shape.For example, the results of expressionsST_GEOGFROMTEXT('POINT EMPTY') andST_GEOGFROMTEXT('GEOMETRYCOLLECTION EMPTY') are identical.

Return type

BOOL

ST_ISRING

ST_ISRING(geography_expression)

Description

ReturnsTRUE if the inputGEOGRAPHY is a linestring and if thelinestring is bothST_ISCLOSED andsimple. A linestring is considered simple if it doesn't pass through thesame point twice (with the exception of the start and endpoint, which mayoverlap to form a ring).

An emptyGEOGRAPHY isn't a ring.

Return type

BOOL

ST_LENGTH

ST_LENGTH(geography_expression[,use_spheroid])

Description

Returns the total length in meters of the lines in the inputGEOGRAPHY.

Ifgeography_expression is a point or a polygon, returns zero. Ifgeography_expression is a collection, returns the length of the lines in thecollection; if the collection doesn't contain lines, returns zero.

The optionaluse_spheroid parameter determines how this function measuresdistance. Ifuse_spheroid isFALSE, the function measures distance on thesurface of a perfect sphere.

Theuse_spheroid parameter currently only supportsthe valueFALSE. The default value ofuse_spheroid isFALSE.

Return type

FLOAT64

ST_LINEINTERPOLATEPOINT

ST_LINEINTERPOLATEPOINT(linestring_geography,fraction)

Description

Gets a point at a specific fraction in a linestringGEOGRAPHY value.

Definitions

• linestring_geography: A linestringGEOGRAPHY on which the target pointis located.
• fraction: AFLOAT64 value that represents a fractionalong the linestringGEOGRAPHY where the target point is located.This should be an inclusive value between0 (start of thelinestring) and1 (end of the linestring).

Details

• ReturnsNULL if any input argument isNULL.
• Returns an empty geography iflinestring_geography is an empty geography.
• Returns an error iflinestring_geography isn't a linestring or an emptygeography, or iffraction is outside the[0, 1] range.

Return Type

GEOGRAPHY

Example

The following query returns a few points on a linestring. Notice that the midpoint of the linestringLINESTRING(1 1, 5 5) is slightly different fromPOINT(3 3) because theGEOGRAPHY type uses geodesic line segments.

WITHfractionsAS(SELECT0ASfractionUNIONALLSELECT0.5UNIONALLSELECT1UNIONALLSELECTNULL)SELECTfraction,ST_LINEINTERPOLATEPOINT(ST_GEOGFROMTEXT('LINESTRING(1 1, 5 5)'),fraction)ASpointFROMfractions/*-------------+-------------------------------------------+ | fraction    | point                                     | +-------------+-------------------------------------------+ | 0           | POINT(1 1)                                | | 0.5         | POINT(2.99633827268976 3.00182528336078)  | | 1           | POINT(5 5)                                | | NULL        | NULL                                      | +-------------+-------------------------------------------*/

ST_LINELOCATEPOINT

ST_LINELOCATEPOINT(linestring_geography,point_geography)

Description

Gets a section of a linestring between the start point and a selected point (apoint on the linestring closest to thepoint_geography argument). Returns thepercentage that this section represents in the linestring.

Details:

• To select a point on the linestringGEOGRAPHY (linestring_geography),this function takes a pointGEOGRAPHY (point_geography) and finds theclosest point to it on the linestring.
• If two points onlinestring_geography are an equal distance away frompoint_geography, it isn't guaranteed which one will be selected.
• The return value is an inclusive value between 0 and 1 (0-100%).
• If the selected point is the start point on the linestring, function returns0 (0%).
• If the selected point is the end point on the linestring, function returns 1(100%).

NULL and error handling:

• ReturnsNULL if any input argument isNULL.
• Returns an error iflinestring_geography isn't a linestring or ifpoint_geography isn't a point. Use theSAFE prefixto obtainNULL for invalid input instead of an error.

Return Type

FLOAT64

Examples

WITHgeosAS(SELECTST_GEOGPOINT(0,0)ASpointUNIONALLSELECTST_GEOGPOINT(1,0)UNIONALLSELECTST_GEOGPOINT(1,1)UNIONALLSELECTST_GEOGPOINT(2,2)UNIONALLSELECTST_GEOGPOINT(3,3)UNIONALLSELECTST_GEOGPOINT(4,4)UNIONALLSELECTST_GEOGPOINT(5,5)UNIONALLSELECTST_GEOGPOINT(6,5)UNIONALLSELECTNULL)SELECTpointASinput_point,ST_LINELOCATEPOINT(ST_GEOGFROMTEXT('LINESTRING(1 1, 5 5)'),point)ASpercentage_from_beginningFROMgeos/*-------------+---------------------------+ | input_point | percentage_from_beginning | +-------------+---------------------------+ | POINT(0 0)  | 0                         | | POINT(1 0)  | 0                         | | POINT(1 1)  | 0                         | | POINT(2 2)  | 0.25015214685147907       | | POINT(3 3)  | 0.5002284283637185        | | POINT(4 4)  | 0.7501905913884388        | | POINT(5 5)  | 1                         | | POINT(6 5)  | 1                         | | NULL        | NULL                      | +-------------+---------------------------*/

ST_LINESUBSTRING

ST_LINESUBSTRING(linestring_geography,start_fraction,end_fraction);

Description

Gets a segment of a linestring at a specific starting and ending fraction.

Definitions

• linestring_geography: The LineStringGEOGRAPHY value that represents thelinestring from which to extract a segment.
• start_fraction:FLOAT64 value that representsthe starting fraction of the total length oflinestring_geography.This must be an inclusive value between 0 and 1 (0-100%).
• end_fraction:FLOAT64 value that representsthe ending fraction of the total length oflinestring_geography.This must be an inclusive value between 0 and 1 (0-100%).

Details

end_fraction must be greater than or equal tostart_fraction.

Ifstart_fraction andend_fraction are equal, a linestring with onlyone point is produced.

Return type

• LineStringGEOGRAPHY if the resulting geography has more than one point.
• PointGEOGRAPHY if the resulting geography has only one point.

Example

The following query returns the second half of the linestring:

WITHdataAS(SELECTST_GEOGFROMTEXT('LINESTRING(20 70, 70 60, 10 70, 70 70)')ASgeo1)SELECTST_LINESUBSTRING(geo1,0.5,1)ASsegmentFROMdata;/*-------------------------------------------------------------+ | segment                                                     | +-------------------------------------------------------------+ | LINESTRING(49.4760661523471 67.2419539103851, 10 70, 70 70) | +-------------------------------------------------------------*/

The following query returns a linestring that only contains one point:

WITHdataAS(SELECTST_GEOGFROMTEXT('LINESTRING(20 70, 70 60, 10 70, 70 70)')ASgeo1)SELECTST_LINESUBSTRING(geo1,0.5,0.5)ASsegmentFROMdata;/*------------------------------------------+ | segment                                  | +------------------------------------------+ | POINT(49.4760661523471 67.2419539103851) | +------------------------------------------*/

ST_MAKELINE

ST_MAKELINE(geography_1,geography_2)

ST_MAKELINE(array_of_geography)

Description

Creates aGEOGRAPHY with a single linestring byconcatenating the point or line vertices of each of the inputGEOGRAPHYs in the order they are given.

ST_MAKELINE comes in two variants. For the first variant, input must be twoGEOGRAPHYs. For the second, input must be anARRAY of typeGEOGRAPHY. Ineither variant, each inputGEOGRAPHY must consist of one of the followingvalues:

• Exactly one point.
• Exactly one linestring.

For the first variant ofST_MAKELINE, if either inputGEOGRAPHY isNULL,ST_MAKELINE returnsNULL. For the second variant, if inputARRAY or anyelement in the inputARRAY isNULL,ST_MAKELINE returnsNULL.

Constraints

Every edge must span strictly less than 180 degrees.

NOTE: The GoogleSQL snapping process may discard sufficiently shortedges and snap the two endpoints together. For instance, if two inputGEOGRAPHYs each contain a point and the two points are separated by a distanceless than the snap radius, the points will be snapped together. In such a casethe result will be aGEOGRAPHY with exactly one point.

Return type

LineStringGEOGRAPHY

ST_MAKEPOLYGON

ST_MAKEPOLYGON(polygon_shell[,array_of_polygon_holes])

Description

Creates aGEOGRAPHY containing a single polygonfrom linestring inputs, where each input linestring is used to construct apolygon ring.

ST_MAKEPOLYGON comes in two variants. For the first variant, the inputlinestring is provided by a singleGEOGRAPHY containing exactly onelinestring. For the second variant, the input consists of a singleGEOGRAPHYand an array ofGEOGRAPHYs, each containing exactly one linestring.

The firstGEOGRAPHY in either variant is used to construct the polygon shell.AdditionalGEOGRAPHYs provided in the inputARRAY specify a polygon hole.For every inputGEOGRAPHY containing exactly one linestring, the followingmust 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 bethe same. If the first and last vertex differ, the function constructs afinal edge from the first vertex to the last.

For the first variant ofST_MAKEPOLYGON, if either inputGEOGRAPHY isNULL,ST_MAKEPOLYGON returnsNULL. For the second variant, ifinputARRAY or any element in theARRAY isNULL,ST_MAKEPOLYGON returnsNULL.

NOTE:ST_MAKEPOLYGON accepts an emptyGEOGRAPHY as input.ST_MAKEPOLYGONinterprets an emptyGEOGRAPHY as having an empty linestring, which willcreate a full loop: that is, a polygon that covers the entire Earth.

Constraints

Together, the input rings must form a valid polygon:

• The polygon shell must cover each of the polygon holes.
• There can be only one polygon shell (which has to be the first input ring).This implies that polygon holes can't be nested.
• Polygon rings may only intersect in a vertex on the boundary of both rings.

Every edge must span strictly less than 180 degrees.

Each polygon ring divides the sphere into two regions. The first input linestingtoST_MAKEPOLYGON forms the polygon shell, and the interior is chosen to bethe smaller of the two regions. Each subsequent input linestring specifies apolygon hole, so the interior of the polygon is already well-defined. In orderto define a polygon shell such that the interior of the polygon is the larger ofthe two regions, seeST_MAKEPOLYGONORIENTED.

NOTE: The GoogleSQL snapping process may discard sufficientlyshort edges and snap the two endpoints together. Hence, when vertices aresnapped together, it's possible that a polygon hole that's sufficiently smallmay disappear, or the outputGEOGRAPHY may contain only a line or apoint.

Return type

GEOGRAPHY

ST_MAKEPOLYGONORIENTED

ST_MAKEPOLYGONORIENTED(array_of_geography)

Description

LikeST_MAKEPOLYGON, but the vertex ordering of each input linestringdetermines the orientation of each polygon ring. The orientation of a polygonring defines the interior of the polygon as follows: if someone walks along theboundary of the polygon in the order of the input vertices, the interior of thepolygon is on the left. This applies for each polygon ring provided.

This variant of the polygon constructor is more flexible sinceST_MAKEPOLYGONORIENTED can construct a polygon such that the interior is oneither side of the polygon ring. However, proper orientation of polygon rings iscritical in order to construct the desired polygon.

If the inputARRAY or any element in theARRAY isNULL,ST_MAKEPOLYGONORIENTED returnsNULL.

NOTE: The input argument forST_MAKEPOLYGONORIENTED may contain an emptyGEOGRAPHY.ST_MAKEPOLYGONORIENTED interprets an emptyGEOGRAPHY as havingan empty linestring, which will create a full loop: that is, a polygon thatcovers the entire Earth.

Constraints

Together, the input rings must form a valid polygon:

• The polygon shell must cover each of the polygon holes.
• There must be only one polygon shell, which must to be the first input ring.This implies that polygon holes can't be nested.
• Polygon rings may only intersect in a vertex on the boundary of both rings.

Every edge must span strictly less than 180 degrees.

ST_MAKEPOLYGONORIENTED relies on the ordering of the input vertices of eachlinestring to determine the orientation of the polygon. This applies to thepolygon shell and any polygon holes.ST_MAKEPOLYGONORIENTED expects allpolygon holes to have the opposite orientation of the shell. SeeST_MAKEPOLYGON for an alternate polygon constructor, andother constraints on building a valid polygon.

NOTE: Due to the GoogleSQL snapping process, edges with a sufficientlyshort length will be discarded and the two endpoints will be snapped to a singlepoint. Therefore, it's possible that vertices in a linestring may be snappedtogether such that one or more edge disappears. Hence, it's possible that apolygon hole that's sufficiently small may disappear, or the resultingGEOGRAPHY 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-emptyGEOGRAPHYs; that is, the distance between twovertices where the first vertex is in the firstGEOGRAPHY, and the second vertex is in the secondGEOGRAPHY. Ifgeography_1 andgeography_2 are thesameGEOGRAPHY, the function returns the distancebetween the two most distant vertices in thatGEOGRAPHY.

If either of the inputGEOGRAPHYs is empty,ST_MAXDISTANCE returnsNULL.

The optionaluse_spheroid parameter determines how this function measuresdistance. Ifuse_spheroid isFALSE, the function measures distance on thesurface of a perfect sphere.

Theuse_spheroid parameter currently only supportsthe valueFALSE. The default value ofuse_spheroid isFALSE.

Return type

FLOAT64

ST_NPOINTS

ST_NPOINTS(geography_expression)

Description

An alias ofST_NUMPOINTS.

ST_NUMGEOMETRIES

ST_NUMGEOMETRIES(geography_expression)

Description

Returns the number of geometries in the inputGEOGRAPHY. For a single point,linestring, or polygon,ST_NUMGEOMETRIES returns1. For any collection ofgeometries,ST_NUMGEOMETRIES returns the number of geometries making up thecollection.ST_NUMGEOMETRIES returns0 if the input is the emptyGEOGRAPHY.

Return type

INT64

Example

The following example computesST_NUMGEOMETRIES for a single point geography,two collections, and an empty geography.

WITHexampleAS(SELECTST_GEOGFROMTEXT('POINT(5 0)')ASgeographyUNIONALLSELECTST_GEOGFROMTEXT('MULTIPOINT(0 1, 4 3, 2 6)')ASgeographyUNIONALLSELECTST_GEOGFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(1 2, 2 1))')ASgeographyUNIONALLSELECTST_GEOGFROMTEXT('GEOMETRYCOLLECTION EMPTY'))SELECTgeography,ST_NUMGEOMETRIES(geography)ASnum_geometries,FROMexample;/*------------------------------------------------------+----------------+ | geography                                            | num_geometries | +------------------------------------------------------+----------------+ | POINT(5 0)                                           | 1              | | MULTIPOINT(0 1, 4 3, 2 6)                            | 3              | | GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(1 2, 2 1)) | 2              | | GEOMETRYCOLLECTION EMPTY                             | 0              | +------------------------------------------------------+----------------*/

ST_NUMPOINTS

ST_NUMPOINTS(geography_expression)

Description

Returns the number of vertices in the inputGEOGRAPHY. This includes the number of points, thenumber of linestring vertices, and the number of polygon vertices.

NOTE: The first and last vertex of a polygon ring are counted as distinctvertices.

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

Ifgeography_expression is a point or a line, returns zero. Ifgeography_expression is a collection, returns the perimeter of the polygonsin the collection; if the collection doesn't contain polygons, returns zero.

The optionaluse_spheroid parameter determines how this function measuresdistance. Ifuse_spheroid isFALSE, the function measures distance on thesurface of a perfect sphere.

Theuse_spheroid parameter currently only supportsthe valueFALSE. The default value ofuse_spheroid isFALSE.

Return type

FLOAT64

ST_POINTN

ST_POINTN(linestring_geography,index)

Description

Returns the Nth point of a linestring geography as a point geography, where N isthe index. The index is 1-based. Negative values are counted backwards from theend of the linestring, so that -1 is the last point. Returns an error if theinput isn't a linestring, if the input is empty, or if there is no vertex atthe given index. Use theSAFE prefix to obtainNULL for invalid inputinstead of an error.

Return Type

PointGEOGRAPHY

Example

The following example usesST_POINTN,ST_STARTPOINT andST_ENDPOINT to extract points from a linestring.

WITHlinestringAS(SELECTST_GEOGFROMTEXT('LINESTRING(1 1, 2 1, 3 2, 3 3)')g)SELECTST_POINTN(g,1)ASfirst,ST_POINTN(g,-1)ASlast,ST_POINTN(g,2)ASsecond,ST_POINTN(g,-2)ASsecond_to_lastFROMlinestring;/*--------------+--------------+--------------+----------------+ | first        | last         | second       | second_to_last | +--------------+--------------+--------------+----------------+ | POINT(1 1)   | POINT(3 3)   | POINT(2 1)   | POINT(3 2)     | +--------------+--------------+--------------+----------------*/

ST_REGIONSTATS

ST_REGIONSTATS(geography,raster_id[,[band=>]value][,include=>value][,options=>value])

Description

Returns statistics summarizing the pixel values of the raster image referencedbyraster_id that intersect withgeography. The statistics include thecount, minimum, maximum, sum, standard deviation, mean, and area of the validpixels of the raster band namedband_name. Google Earth Engine computesthe results of the function call.

• geography: AGEOGRAPHY value to intersect with the raster image.

• raster_id: A string that identifies a raster image. Thefollowing formats are supported:

  • A URI from an image table provided by Google Earth Enginein BigQuery sharing (formerly Analytics Hub).
  • A URI for a readable GeoTIFF raster file.
  • A Google Earth Engine asset path that references public catalog dataor project-owned assets with read access.

  For more information about how to find and format each typeof input, seeFind raster data.

• band_name: A string in one of the following formats:

  • A single band within the raster image specified byraster_id.
  • A formula to compute a value from the available bands in the rasterimage. The formula uses the Google Earth Engineimage expression syntax.

  Bands can be referenced by their name,band_name, in expressions. Ifyou don't specify a band, the first band of the image is used.

• include: An optional string formula that uses the Google Earth Engineimage expression syntax to compute a pixel weight.The formula should return values from 0 to 1. Values outside this range areset to the nearest limit, either 0 or 1. A value of 0 means thatthe pixel isinvalid and it's excluded from analysis.A positive value means that a pixel isvalid.Values between 0 and 1 representproportional weights for calculations, such as weightedmeans. For more information, seePixel weights.

• options: A case-sensitiveJSON value that specifies options as key valuepairs, such asJSON '{"scale": 10.0}'.

  The following options are supported:

  • "scale": The scale in meters at which raster processing should beperformed. By default, the scale is determined from thedataset. For more information, seePixel size and scale of analysis.

You can only call this function in theUS,us-central1,andus-central2 regions.

For more information about raster data and how tocall this function, seeWork with raster data.

Return type

STRUCT<count,minFLOAT64,maxFLOAT64,stdDevFLOAT64,sumFLOAT64,meanFLOAT64,areaFLOAT64>

Return values:

• count: The number of pixels that intersect withgeography, includingpartially intersecting pixels.
• min: The minimum band value of the valid pixels that intersect withgeography.
• max: The maximum band value of the valid pixels that intersect withgeography.
• stdDev: The weighted standard deviation of the band values of the pixelsthat intersect withgeography.
• sum: The weighted sum of the band values of the pixels that intersect withgeography.
• mean: The weighted mean of the band values of the pixels that intersectwithgeography.

• area: The sum of the area of valid pixels, or parts of valid pixels,that intersectgeography.

  • For point geometries, the area of the entire intersecting pixel is used.
  • For polygon geometries, partial intersections are weighted by theproportional overlap of the polygon with the pixel.
  • Thearea value might differ from the area returned by theST_AREAfunction. The difference is typically by less than 1% but increases forpolygons that are smaller than the pixel size in the raster image.

If no valid pixels intersectgeography, then the function returns 0for all statistics.

ST_SIMPLIFY

ST_SIMPLIFY(geography,tolerance_meters)

Description

Returns a simplified version ofgeography, the given inputGEOGRAPHY. The inputGEOGRAPHY is simplified by replacing nearly straightchains of short edges with a single long edge. The inputgeography will notchange by more than the tolerance specified bytolerance_meters. Thus,simplified edges are guaranteed to pass withintolerance_meters of theoriginal positions of all vertices that were removed from that edge. The giventolerance_meters is in meters on the surface of the Earth.

Note thatST_SIMPLIFY preserves topological relationships, which means thatno new crossing edges will be created and the output will be valid. For a largeenough tolerance, adjacent shapes may collapse into a single object, or a shapecould be simplified to a shape with a smaller dimension.

Constraints

ForST_SIMPLIFY to have any effect,tolerance_meters must be non-zero.

ST_SIMPLIFY returns an error if the tolerance specified bytolerance_metersis one of the following:

• A negative tolerance.
• Greater than ~7800 kilometers.

Return type

GEOGRAPHY

Examples

The following example shows howST_SIMPLIFY simplifies the input lineGEOGRAPHY by removing intermediate vertices.

WITHexampleAS(SELECTST_GEOGFROMTEXT('LINESTRING(0 0, 0.05 0, 0.1 0, 0.15 0, 2 0)')ASline)SELECTlineASoriginal_line,ST_SIMPLIFY(line,1)ASsimplified_lineFROMexample;/*---------------------------------------------+----------------------+ |                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 ofST_SIMPLIFY can have alower dimension than the original shape.

WITHexampleAS(SELECTST_GEOGFROMTEXT('POLYGON((0 0, 0.1 0, 0.1 0.1, 0 0))')ASpolygon,tAStoleranceFROMUNNEST([1000,10000,100000])ASt)SELECTpolygonASoriginal_triangle,toleranceAStolerance_meters,ST_SIMPLIFY(polygon,tolerance)ASsimplified_resultFROMexample/*-------------------------------------+------------------+-------------------------------------+ |          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 inputGEOGRAPHY, where each vertex hasbeen snapped to a longitude/latitude grid. The grid size is determined by thegrid_size parameter which is given in degrees.

Constraints

Arbitrary grid sizes aren't supported. Thegrid_size parameter is rounded sothat it's of the form10^n, where-10 < n < 0.

Return type

GEOGRAPHY

ST_STARTPOINT

ST_STARTPOINT(linestring_geography)

Description

Returns the first point of a linestring geography as a point geography. Returnsan error if the input isn't a linestring or if the input is empty. Use theSAFE prefix to obtainNULL for invalid input instead of an error.

Return Type

PointGEOGRAPHY

Example

SELECTST_STARTPOINT(ST_GEOGFROMTEXT('LINESTRING(1 1, 2 1, 3 2, 3 3)'))first/*--------------+ | first        | +--------------+ | POINT(1 1)   | +--------------*/

ST_TOUCHES

ST_TOUCHES(geography_1,geography_2)

Description

ReturnsTRUE provided the following two conditions are satisfied:

1. geography_1 intersectsgeography_2.
2. The interior ofgeography_1 and the interior ofgeography_2 aredisjoint.

Return type

BOOL

ST_UNION

ST_UNION(geography_1,geography_2)

ST_UNION(array_of_geography)

Description

Returns aGEOGRAPHY that represents the point setunion of all inputGEOGRAPHYs.

ST_UNION comes in two variants. For the first variant, input must be twoGEOGRAPHYs. For the second, the input is anARRAY of typeGEOGRAPHY.

For the first variant ofST_UNION, if an inputGEOGRAPHY isNULL,ST_UNION returnsNULL.For the second variant, if the inputARRAY valueisNULL,ST_UNION returnsNULL.For a non-NULL inputARRAY, the union is computedandNULL elements are ignored so that they don't affect the output.

SeeST_UNION_AGG for the aggregate version ofST_UNION.

Return type

GEOGRAPHY

Example

SELECTST_UNION(ST_GEOGFROMTEXT('LINESTRING(-122.12 47.67, -122.19 47.69)'),ST_GEOGFROMTEXT('LINESTRING(-122.12 47.67, -100.19 47.69)'))ASresults/*---------------------------------------------------------+ | results                                                 | +---------------------------------------------------------+ | LINESTRING(-100.19 47.69, -122.12 47.67, -122.19 47.69) | +---------------------------------------------------------*/

ST_UNION_AGG

ST_UNION_AGG(geography)

Description

Returns aGEOGRAPHY that represents the point setunion of all inputGEOGRAPHYs.

ST_UNION_AGG ignoresNULL inputGEOGRAPHY values.

SeeST_UNION for the non-aggregate version ofST_UNION_AGG.

Return type

GEOGRAPHY

Example

SELECTST_UNION_AGG(items)ASresultsFROMUNNEST([ST_GEOGFROMTEXT('LINESTRING(-122.12 47.67, -122.19 47.69)'),ST_GEOGFROMTEXT('LINESTRING(-122.12 47.67, -100.19 47.69)'),ST_GEOGFROMTEXT('LINESTRING(-122.12 47.67, -122.19 47.69)')])asitems;/*---------------------------------------------------------+ | results                                                 | +---------------------------------------------------------+ | LINESTRING(-100.19 47.69, -122.12 47.67, -122.19 47.69) | +---------------------------------------------------------*/

ST_WITHIN

ST_WITHIN(geography_1,geography_2)

Description

ReturnsTRUE if no point ofgeography_1 is outside ofgeography_2 andthe interiors ofgeography_1 andgeography_2 intersect.

Given two geographiesa andb,ST_WITHIN(a, b) returns the same resultasST_CONTAINS(b, a). Note the opposite order of arguments.

Return type

BOOL

ST_X

ST_X(point_geography_expression)

Description

Returns the longitude in degrees of the single-point inputGEOGRAPHY.

For any inputGEOGRAPHY that isn't a single point,including an emptyGEOGRAPHY,ST_X returns anerror. Use theSAFE. prefix to obtainNULL.

Return type

FLOAT64

Example

The following example usesST_X andST_Y to extract coordinates fromsingle-point geographies.

WITHpointsAS(SELECTST_GEOGPOINT(i,i+1)ASpFROMUNNEST([0,5,12])ASi)SELECTp,ST_X(p)aslongitude,ST_Y(p)aslatitudeFROMpoints;/*--------------+-----------+----------+ | 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(point_geography_expression)

Description

Returns the latitude in degrees of the single-point inputGEOGRAPHY.

For any inputGEOGRAPHY that isn't a single point,including an emptyGEOGRAPHY,ST_Y returns anerror. Use theSAFE. prefix to returnNULL instead.

Return type

FLOAT64

Example

SeeST_X for example usage.