# transformations

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

## ST\_BUFFER <a href="#st_buffer" id="st_buffer"></a>

```sql
ST_BUFFER(geog, radius, units, steps)
```

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

**Input parameters**

* `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 `NULL`the 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**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.ST_BUFFER(
  ST_GEOGPOINT(-74.00, 40.7128),
  1,
  "kilometers",
  10
);
-- POLYGON((-73.9881354374691 40.7127993926494 ...
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.ST_BUFFER(
  ST_GEOGPOINT(-74.00, 40.7128),
  1,
  "kilometers",
  10
);
-- POLYGON((-73.9881354374691 40.7127993926494 ...
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.ST_BUFFER(
  ST_GEOGPOINT(-74.00, 40.7128),
  1,
  "kilometers",
  10
);
-- POLYGON((-73.9881354374691 40.7127993926494 ...
```

{% endtab %}
{% endtabs %}

{% hint style="info" %}
**Additional examples**

* [Bikeshare stations within a San Francisco buffer](https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-bigquery/step-by-step-tutorials/bikeshare-stations-within-a-san-francisco-buffer)
  {% endhint %}

## ST\_CENTERMEAN <a href="#st_centermean" id="st_centermean"></a>

```sql
ST_CENTERMEAN(geog)
```

**Description**

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

**Input parameters**

* `geog`: `GEOGRAPHY` feature for which to compute the center.

**Return type**

`GEOGRAPHY`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.ST_CENTERMEAN(
  ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))")
);
-- POINT(25.3890912155939 29.7916831655627)
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.ST_CENTERMEAN(
  ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))")
);
-- POINT(25.3890912155939 29.7916831655627)
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.ST_CENTERMEAN(
  ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))")
);
-- POINT(25.3890912155939 29.7916831655627)
```

{% endtab %}
{% endtabs %}

{% hint style="info" %}
**Additional examples**

* [New police stations based on Chicago crime location clusters](https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-bigquery/step-by-step-tutorials/new-police-stations-based-on-chicago-crime-location-clusters)
  {% endhint %}

## ST\_CENTERMEDIAN <a href="#st_centermedian" id="st_centermedian"></a>

```sql
ST_CENTERMEDIAN(geog)
```

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

**Input parameters**

* `geog`: `GEOGRAPHY` feature for which to compute the center.

**Return type**

`GEOGRAPHY`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.ST_CENTERMEDIAN(
  ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))")
);
-- POINT(25.3783930513609 29.8376035441371)
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.ST_CENTERMEDIAN(
  ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))")
);
-- POINT(25.3783930513609 29.8376035441371)
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.ST_CENTERMEDIAN(
  ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))")
);
-- POINT(25.3783930513609 29.8376035441371)
```

{% endtab %}
{% endtabs %}

## ST\_CENTEROFMASS <a href="#st_centerofmass" id="st_centerofmass"></a>

```sql
ST_CENTEROFMASS(geog)
```

**Description**

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

**Input parameters**

* `geog`: `GEOGRAPHY` feature to be centered.

**Return type**

`GEOGRAPHY`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.ST_CENTEROFMASS(
  ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))")
);
-- POINT(25.1730977433239 27.2789529273059)
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.ST_CENTEROFMASS(
  ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))")
);
-- POINT(25.1730977433239 27.2789529273059)
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.ST_CENTEROFMASS(
  ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))")
);
-- POINT(25.1730977433239 27.2789529273059)
```

{% endtab %}
{% endtabs %}

## ST\_CONCAVEHULL <a href="#st_concavehull" id="st_concavehull"></a>

```sql
ST_CONCAVEHULL(geog, maxEdge, units)
```

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

**Input parameters**

* `geog`: `ARRAY<GEOGRAPHY>` input points.
* `maxEdge`: `FLOAT64`|`NULL` the maximum length allowed for an edge of the concave hull. Higher `maxEdge` values will produce more convex-like hulls. If `NULL`, the default value `infinity` is used and it would be equivalent to a Convex Hull.
* `units`: `STRING`|`NULL` units of length, the supported options are: miles, kilometers, degrees or radians. If `NULL`the default value `kilometers` is used.

**Return type**

`GEOGRAPHY`

**Examples**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.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 ...
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.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 ...
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT 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 ...
```

{% endtab %}
{% endtabs %}

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.ST_CONCAVEHULL(
  [
    ST_GEOGPOINT(-75.833, 39.284)
  ],
  100, 'kilometers'
);
-- POINT(-75.833 39.284)
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.ST_CONCAVEHULL(
  [
    ST_GEOGPOINT(-75.833, 39.284)
  ],
  100, 'kilometers'
);
-- POINT(-75.833 39.284)
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.ST_CONCAVEHULL(
  [
    ST_GEOGPOINT(-75.833, 39.284)
  ],
  100, 'kilometers'
);
-- POINT(-75.833 39.284)
```

{% endtab %}
{% endtabs %}

## ST\_DESTINATION <a href="#st_destination" id="st_destination"></a>

```sql
ST_DESTINATION(startPoint, distance, bearing, units)
```

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

**Input parameters**

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

**Return type**

`GEOGRAPHY`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.ST_DESTINATION(
  ST_GEOGPOINT(-3.70325,40.4167),
  10,
  45,
  "miles"
);
-- POINT(-3.56862505487045 40.5189626777536)
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.ST_DESTINATION(
  ST_GEOGPOINT(-3.70325,40.4167),
  10,
  45,
  "miles"
);
-- POINT(-3.56862505487045 40.5189626777536)
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.ST_DESTINATION(
  ST_GEOGPOINT(-3.70325,40.4167),
  10,
  45,
  "miles"
);
-- POINT(-3.56862505487045 40.5189626777536)
```

{% endtab %}
{% endtabs %}

## ST\_GREATCIRCLE <a href="#st_greatcircle" id="st_greatcircle"></a>

```sql
ST_GREATCIRCLE(startPoint, endPoint, npoints)
```

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

**Input parameters**

* `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**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.ST_GREATCIRCLE(ST_GEOGPOINT(-3.70325,40.4167), ST_GEOGPOINT(-73.9385,40.6643), 20);
-- LINESTRING(-3.70325 40.4167 ...
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.ST_GREATCIRCLE(ST_GEOGPOINT(-3.70325,40.4167), ST_GEOGPOINT(-73.9385,40.6643), 20);
-- LINESTRING(-3.70325 40.4167 ...
```

{% endtab %}

{% tab title="manual" %}

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

{% endtab %}
{% endtabs %}

{% hint style="info" %}
**Additional examples**

* [Computing US airport connections and route interpolations](https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-bigquery/step-by-step-tutorials/computing-us-airport-connections-and-route-interpolations)
  {% endhint %}

## ST\_LINE\_INTERPOLATE\_POINT <a href="#st_line_interpolate_point" id="st_line_interpolate_point"></a>

```sql
ST_LINE_INTERPOLATE_POINT(geog, distance, units)
```

**Description**

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

**Input parameters**

* `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 `NULL`the default value `kilometers` is used.

**Return type**

`GEOGRAPHY`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.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)
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.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)
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT 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)
```

{% endtab %}
{% endtabs %}

{% hint style="info" %}
**Additional examples**

* [Computing US airport connections and route interpolations](https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-bigquery/step-by-step-tutorials/computing-us-airport-connections-and-route-interpolations)
  {% endhint %}

## ST\_POINTONSURFACE <a href="#st_pointonsurface" id="st_pointonsurface"></a>

```sql
ST_POINTONSURFACE(geog)
```

**Description**

Takes any Feature or a FeatureCollection and returns a point that is granted to be inside one of the polygons.

**Input parameters**

* `geog`: `GEOGRAPHY` feature to be centered.

**Return type**

`GEOGRAPHY`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.ST_POINTONSURFACE(
  ST_GEOGFROMTEXT("POLYGON ((1.444057 38.791203 ,  1.450457 38.793763 ,  1.457178 38.792403 ,  1.458298 38.781282 ,  1.453418 38.778242 ,  1.445977 38.780482 ,  1.453498 38.781042 ,  1.456218 38.786883 ,  1.450617 38.790643 ,  1.444057 38.791203))")
);
-- POINT(1.456218 38.786883)
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.ST_POINTONSURFACE(
  ST_GEOGFROMTEXT("POLYGON ((1.444057 38.791203 ,  1.450457 38.793763 ,  1.457178 38.792403 ,  1.458298 38.781282 ,  1.453418 38.778242 ,  1.445977 38.780482 ,  1.453498 38.781042 ,  1.456218 38.786883 ,  1.450617 38.790643 ,  1.444057 38.791203))")
);
-- POINT(1.456218 38.786883)
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.ST_POINTONSURFACE(
  ST_GEOGFROMTEXT("POLYGON ((1.444057 38.791203 ,  1.450457 38.793763 ,  1.457178 38.792403 ,  1.458298 38.781282 ,  1.453418 38.778242 ,  1.445977 38.780482 ,  1.453498 38.781042 ,  1.456218 38.786883 ,  1.450617 38.790643 ,  1.444057 38.791203))")
);
-- POINT(1.456218 38.786883)
```

{% endtab %}
{% endtabs %}

<img src="https://3029946802-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FybPdpmLltPkzGFvz7m8A%2Fuploads%2Fgit-blob-1c82685e4e434438152a8e3d867df996413489fe%2Feu-flag-website.png?alt=media&#x26;token=4343f6e5-973a-4e9a-8e14-50366a086f72" alt="EU flag" data-size="line">This project has received funding from the [European Union’s Horizon 2020](https://ec.europa.eu/programmes/horizon2020/en) research and innovation programme under grant agreement No 960401.