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 is8
.
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" }
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. HighermaxEdge
values will produce more convex-like hulls. If not provided, the default valueinfinity
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 defaultunits
iskilometers
.
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
orradians
. IfNULL
the default valuekilometers
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 defaultnpoints
is100
.
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 ], ...
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
andradians
. By defaultunits
iskilometers
.
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" }
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)
Last updated
Was this helpful?