transformations

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

ST_BUFFER

ST_BUFFER(geog, distance [, segments])

Description

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

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

Example

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

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

ST_CENTERMEAN

ST_CENTERMEAN(geog)

Description

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

geom: GEOGRAPHY for which to compute the mean center.

Return type

GEOGRAPHY

Example

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

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.

geog: GEOGRAPHY for which to compute the center.

Return type

GEOGRAPHY

Example

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

ST_CENTEROFMASS(geog)

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

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" }

Additional examples

Computing US airport connections and route interpolations

ST_CONCAVEHULL

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.

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

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" }

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 ], ...

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 ], ...

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):

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

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 to aggregate them into a collection.

geog: GEOGRAPHY input to compute the convex hull.

Return type

GEOGRAPHY

Examples

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" }

SELECT CARTO.CARTO.ST_CONVEXHULL(ST_COLLECT(geog))
FROM <database>.<schema>.<table>;

warning

The aggregate function ST_COLLECT has an output limit of 16 MB. This is equivalent, approximately, to 300K points.

ST_DESTINATION

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.

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 NULLthe default value kilometers is used.

Return type

GEOGRAPHY

Examples

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

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

ST_GREATCIRCLE

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.

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

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

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

Additional examples

Computing US airport connections and route interpolations

ST_LINE_INTERPOLATE_POINT

ST_LINE_INTERPOLATE_POINT(geog, distance [, units])

Description

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

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

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" }

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" }

Additional examples

Computing US airport connections and route interpolations

ST_POINTONSURFACE

ST_POINTONSURFACE(geog)

Description

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

geog: GEOGRAPHY feature to be centered.

Return type

GEOGRAPHY

Example

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)

Previoustiler NextGuides

Last updated 1 year ago

Was this helpful?