Analytics Toolbox for BigQuery

transformations

This module contains functions that compute geometric constructions, or alter geometry size or shape.

ST_BUFFER

Description

Calculates a Geography buffer for input features for a given radius, i.e. the area within the given distance of the input. Units supported are miles, kilometers, and degrees.

• geog: GEOGRAPHY input to be buffered.
• radius: FLOAT64 distance to draw the buffer (negative values are allowed).
• units: STRING|NULL units of length, the supported options are: miles, kilometers, and degrees. If NULLthe default value kilometers is used.
• steps: INT64|NULL number of segments used to approximate a quarter circle. If NULL the default value 8 is used.

Return type

GEOGRAPHY

Example

 1 2 3 4 5 6 7 SELECT `carto-os`.carto.ST_BUFFER( ST_GEOGPOINT(-74.00, 40.7128), 1, "kilometers", 10 ); -- POLYGON((-73.9881354374691 40.7127993926494 ...

ST_CENTERMEAN

Description

Takes a Feature or FeatureCollection and returns the mean center (average of its vertices).

• geog: GEOGRAPHY feature for which to compute the center.

Return type

GEOGRAPHY

Example

 1 2 3 4 SELECT `carto-os`.carto.ST_CENTERMEAN( ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))") ); -- POINT(25.3890912155939 29.7916831655627)

ST_CENTERMEDIAN

Description

Takes a FeatureCollection of points and computes the median center. The median center is understood as the point that requires the least total travel from all other points.

• geog: GEOGRAPHY feature for which to compute the center.

Return type

GEOGRAPHY

Example

 1 2 3 4 SELECT `carto-os`.carto.ST_CENTERMEDIAN( ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))") ); -- POINT(25.3783930513609 29.8376035441371)

ST_CENTEROFMASS

Description

Takes any Feature or a FeatureCollection and returns its center of mass (also known as centroid).

• geog: GEOGRAPHY feature to be centered.

Return type

GEOGRAPHY

Example

 1 2 3 4 SELECT `carto-os`.carto.ST_CENTEROFMASS( ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))") ); -- POINT(25.1730977433239 27.2789529273059)

ST_CONCAVEHULL

Description

Takes a set of points and returns a concave hull Polygon or MultiPolygon. In case that a single or a couple of points are passed as input, the function will return that point or a segment respectively.

• geog: ARRAY<GEOGRAPHY> input points.
• maxEdge: FLOAT64|NULL the length (in ‘units’) of an edge necessary for part of the hull to become concave. If NULLthe default value infinity is used.
• units: STRING|NULL units of length, the supported options are: miles, kilometers, degrees or radians. If NULLthe default value kilometers is used.

Return type

GEOGRAPHY

Example

 1 2 3 4 5 6 7 8 9 10 11 SELECT `carto-os`.carto.ST_CONCAVEHULL( [ ST_GEOGPOINT(-75.833, 39.284), ST_GEOGPOINT(-75.6, 39.984), ST_GEOGPOINT(-75.221, 39.125), ST_GEOGPOINT(-75.521, 39.325) ], 100, 'kilometers' ); -- POLYGON((-75.68 39.24425, -75.527 39.2045 ...
 1 2 3 4 5 6 7 SELECT `carto-os`.carto.ST_CONCAVEHULL( [ ST_GEOGPOINT(-75.833, 39.284) ], 100, 'kilometers' ); -- POINT(-75.833 39.284)

ST_DESTINATION

Description

Takes a Point and calculates the location of a destination point given a distance in degrees, radians, miles, or kilometers; and a bearing in degrees. This uses the Haversine formula to account for global curvature.

• origin: GEOGRAPHY starting point.
• distance: FLOAT64 distance from the origin point in the units specified.
• bearing: FLOAT64 counter-clockwise angle from East, ranging from -180 to 180 (e.g. 0 is East, 90 is North, 180 is West, -90 is South).
• units: STRING|NULL units of length, the supported options are: miles, kilometers, degrees or radians. If NULLthe default value kilometers is used.

Return type

GEOGRAPHY

Example

 1 2 3 4 5 6 7 SELECT `carto-os`.carto.ST_DESTINATION( ST_GEOGPOINT(-3.70325,40.4167), 10, 45, "miles" ); -- POINT(-3.56862505487045 40.5189626777536)

ST_GREATCIRCLE

Description

Calculate great circle routes as LineString or MultiLineString. If the start and end points span the antimeridian, the resulting feature will be split into a MultiLineString.

• startPoint: GEOGRAPHY source point feature.
• endPoint: GEOGRAPHY destination point feature.
• npoints: INT64|NULL number of points. If NULL the default value 100 is used.

Return type

GEOGRAPHY

Example

 1 2 SELECT `carto-os`.carto.ST_GREATCIRCLE(ST_GEOGPOINT(-3.70325,40.4167), ST_GEOGPOINT(-73.9385,40.6643), 20); -- LINESTRING(-3.70325 40.4167 ...

ST_LINE_INTERPOLATE_POINT

Description

Takes a LineString and returns a Point at a specified distance along the line.

• geog: GEOGRAPHY input line.
• distance: FLOAT64 distance along the line.
• units: STRING|NULL units of length, the supported options are: miles, kilometers, degrees and radians. If NULLthe default value kilometers is used.

Return type

GEOGRAPHY

Example

 1 2 SELECT `carto-os`.carto.ST_LINE_INTERPOLATE_POINT(ST_GEOGFROMTEXT("LINESTRING (-76.091308 18.427501,-76.695556 18.729501,-76.552734 19.40443,-74.61914 19.134789,-73.652343 20.07657,-73.157958 20.210656)"), 250, 'miles'); -- POINT(-74.297592068938 19.4498107103156)

This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.