# 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, distance [, segments])
```

**Description**

Calculates a buffer for the input features for a given distance.

**Input parameters**

* `geog`: `GEOGRAPHY` input to be buffered.
* `distance`: `DOUBLE` distance of the buffer around the input geography. The value is in meters. Negative values are allowed.
* `segments` (optional): `INTEGER` number of segments used to approximate a quarter circle. The default value is `8`.

**Return type**

`GEOGRAPHY`

**Examples**

```sql
SELECT CARTO.CARTO.ST_BUFFER(ST_POINT(-74.00, 40.7128), 1000);
-- { "coordinates": [ [ [ -73.98813543746913, 40.712799392649444 ], ...
```

```sql
SELECT CARTO.CARTO.ST_BUFFER(ST_POINT(-74.00, 40.7128), 1000, 10);
-- { "coordinates": [ [ [ -73.98813543746913, 40.712799392649444 ], ...
```

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

* `geom`: `GEOGRAPHY` for which to compute the mean center.

**Return type**

`GEOGRAPHY`

**Example**

```sql
SELECT CARTO.CARTO.ST_CENTERMEAN(TO_GEOGRAPHY('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))'));
-- { "coordinates": [ 26, 24 ], "type": "Point" }
```

## 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` for which to compute the center.

**Return type**

`GEOGRAPHY`

**Example**

```sql
SELECT CARTO.CARTO.ST_CENTERMEDIAN(TO_GEOGRAPHY('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))'));
-- { "coordinates": [ 25, 27.5 ], "type": "Point" }
```

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

```sql
SELECT CARTO.CARTO.ST_CENTEROFMASS(TO_GEOGRAPHY('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))'));
-- { "coordinates": [ 25.454545454545453, 26.96969696969697 ], "type": "Point" }
```

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

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

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

```sql
ST_CONCAVEHULL(geojsons [, 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**

* `geojsons`: `ARRAY` array of features in GeoJSON format casted to STRING.
* `maxEdge` (optional): `DOUBLE` the maximum length allowed for an edge of the concave hull. Higher `maxEdge` values will produce more convex-like hulls. If not provided, the default value `infinity` is used and it would be equivalent to a Convex Hull.
* `units` (optional): `STRING` units of length, the supported options are: miles, kilometers, degrees or radians. By default `units` is `kilometers`.

**Return type**

`GEOGRAPHY`

**Examples**

```sql
SELECT CARTO.CARTO.ST_CONCAVEHULL(
  ARRAY_CONSTRUCT(
    ST_ASGEOJSON(ST_POINT(-75.833, 39.284))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.6, 39.984))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.221, 39.125))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.521, 39.325))::STRING
  )
);
-- { "coordinates": [ [ [ -75.221, 39.125 ], [ -75.833, 39.284 ], [ -75.6, 39.984 ], [ -75.221, 39.125 ] ] ], "type": "Polygon" }
```

```sql
SELECT CARTO.CARTO.ST_CONCAVEHULL(
  ARRAY_CONSTRUCT(
    ST_ASGEOJSON(ST_POINT(-75.833, 39.284))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.6, 39.984))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.221, 39.125))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.521, 39.325))::STRING
  ),
  100
);
-- { "coordinates": [ [ [ -75.833, 39.284 ], [ -75.6, 39.984 ], ...
```

```sql
SELECT CARTO.CARTO.ST_CONCAVEHULL(
  ARRAY_CONSTRUCT(
    ST_ASGEOJSON(ST_POINT(-75.833, 39.284))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.6, 39.984))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.221, 39.125))::STRING,
    ST_ASGEOJSON(ST_POINT(-75.521, 39.325))::STRING
  ),
  100,
  'kilometers'
);
-- { "coordinates": [ [ [ -75.833, 39.284 ], [ -75.6, 39.984 ], ...
```

```sql
SELECT CARTO.CARTO.ST_CONCAVEHULL(
  ARRAY_CONSTRUCT(
    ST_ASGEOJSON(
      ST_POINT(-75.833, 39.284))::STRING,
      ST_ASGEOJSON(ST_POINT(-75.6, 39.984))::STRING
    )
  );
--  { "coordinates": [ -75.833, 39.284 ], "type": "Point" }
```

If points are stored in a table, a query like the one below can be used (multiple polygons are generated in this case, one for each `cluster_id` value):

```sql
WITH _array AS (
    SELECT
        cluster_id,
        ARRAY_AGG(ST_ASGEOJSON(geom)::STRING) AS geomarray
    FROM mytable
    GROUP BY cluster_id
)
SELECT
    CARTO.CARTO.ST_CONCAVEHULL(geomarray) AS geom,
    cluster_id
FROM _array
```

## ST\_CONVEXHULL <a href="#st_convexhull" id="st_convexhull"></a>

```sql
ST_CONVEXHULL(geog)
```

**Description**

Computes the convex hull of the input geography. The convex hull is the smallest convex geography that covers the input. It returns NULL if there is no convex hull.

This is not an aggregate function. To compute the convex hull of a set of geography, use [ST\_COLLECT](https://docs.snowflake.com/en/sql-reference/functions/st_collect) to aggregate them into a collection.

**Input parameters**

* `geog`: `GEOGRAPHY` input to compute the convex hull.

**Return type**

`GEOGRAPHY`

**Examples**

```sql
SELECT CARTO.CARTO.ST_CONVEXHULL(
  TO_GEOGRAPHY('LINESTRING (-3.5938 41.0403, -4.4006 40.3266, -3.14655 40.1193, -3.7205 40.4743)')
);
-- { "coordinates": [ [ [ -3.14655, 40.1193 ], [ -4.4006, 40.3266 ], [ -3.5938, 41.0403 ], [ -3.14655, 40.1193 ] ] ], "type": "Polygon" }
```

```sql
SELECT CARTO.CARTO.ST_CONVEXHULL(ST_COLLECT(geog))
FROM <my-database>.<my-schema>.<my-table>;
```

{% hint style="warning" %}
**warning**

The aggregate function [ST\_COLLECT](https://docs.snowflake.com/en/sql-reference/functions/st_collect) has an output limit of 16 MB. This is equivalent, approximately, to 300K points.
{% endhint %}

## 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`: `DOUBLE` distance from the origin point in the units specified.
* `bearing`: `DOUBLE` ranging from -180 to 180 (e.g. 0 is North, 90 is East, 180 is South, -90 is West).
* `units` (optional): `STRING` units of length, the supported options are: `miles`, `kilometers`, `degrees` or `radians`. If `NULL`the default value `kilometers` is used.

**Return type**

`GEOGRAPHY`

**Examples**

```sql
SELECT CARTO.CARTO.ST_DESTINATION(ST_POINT(-3.70325,40.4167), 10, 45);
-- { "coordinates": [ -3.6196461743569053, 40.48026145975517 ], "type": "Point" }
```

```sql
SELECT CARTO.CARTO.ST_DESTINATION(ST_POINT(-3.70325,40.4167), 10, 45, 'miles');
-- { "coordinates": [ -3.56862505487045, 40.518962677753585 ], "type": "Point" }
```

## 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` (optional): `INT` number of points. By default `npoints` is `100`.

**Return type**

`GEOGRAPHY`

**Examples**

```sql
SELECT CARTO.CARTO.ST_GREATCIRCLE(ST_POINT(-3.70325,40.4167), ST_POINT(-73.9385,40.6643));
-- { "coordinates": [ [ -3.7032499999999993, 40.4167 ], ...
```

```sql
SELECT CARTO.CARTO.ST_GREATCIRCLE(ST_POINT(-3.70325,40.4167), ST_POINT(-73.9385,40.6643), 20);
-- { "coordinates": [ [ -3.7032499999999993, 40.4167 ], ...
```

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

* [Computing US airport connections and route interpolations](https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-snowflake/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`: `DOUBLE` distance along the line.
* `units` (optional): `STRING` units of length, the supported options are: `miles`, `kilometers`, `degrees` and `radians`. By default `units` is `kilometers`.

**Return type**

`GEOGRAPHY`

**Examples**

```sql
SELECT CARTO.CARTO.ST_LINE_INTERPOLATE_POINT(TO_GEOGRAPHY('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);
-- { "coordinates": [ -75.5956489839589, 19.273615818183988 ], "type": "Point" }
```

```sql
SELECT CARTO.CARTO.ST_LINE_INTERPOLATE_POINT(TO_GEOGRAPHY('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');
-- { "coordinates": [ -74.297592068938, 19.449810710315635 ], "type": "Point" }
```

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

* [Computing US airport connections and route interpolations](https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-snowflake/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**

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