transformations
CORE
This module contains functions that compute geometric constructions, or alter geometry size or shape.
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.
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. IfNULL
the default valuekilometers
is used.steps
:INT64
|NULL
number of segments used to approximate a quarter circle. IfNULL
the default value8
is used.
Return type
GEOGRAPHY
Example
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.ST_BUFFER(
ST_GEOGPOINT(-74.00, 40.7128),
1,
"kilometers",
10
);
-- POLYGON((-73.9881354374691 40.7127993926494 ...
SELECT `carto-un-eu`.carto.ST_BUFFER(
ST_GEOGPOINT(-74.00, 40.7128),
1,
"kilometers",
10
);
-- POLYGON((-73.9881354374691 40.7127993926494 ...
SELECT `carto-os`.carto.ST_BUFFER(
ST_GEOGPOINT(-74.00, 40.7128),
1,
"kilometers",
10
);
-- POLYGON((-73.9881354374691 40.7127993926494 ...
SELECT carto.ST_BUFFER(
ST_GEOGPOINT(-74.00, 40.7128),
1,
"kilometers",
10
);
-- POLYGON((-73.9881354374691 40.7127993926494 ...
Additional examples
ST_CENTERMEAN(geog)
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
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.ST_CENTERMEAN(
ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))")
);
-- POINT(25.3890912155939 29.7916831655627)
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)
SELECT `carto-os`.carto.ST_CENTERMEAN(
ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))")
);
-- POINT(25.3890912155939 29.7916831655627)
SELECT carto.ST_CENTERMEAN(
ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))")
);
-- POINT(25.3890912155939 29.7916831655627)
Additional examples
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
feature for which to compute the center.
Return type
GEOGRAPHY
Example
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.ST_CENTERMEDIAN(
ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))")
);
-- POINT(25.3783930513609 29.8376035441371)
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)
SELECT `carto-os`.carto.ST_CENTERMEDIAN(
ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))")
);
-- POINT(25.3783930513609 29.8376035441371)
SELECT carto.ST_CENTERMEDIAN(
ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))")
);
-- POINT(25.3783930513609 29.8376035441371)
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
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.ST_CENTEROFMASS(
ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))")
);
-- POINT(25.1730977433239 27.2789529273059)
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)
SELECT `carto-os`.carto.ST_CENTEROFMASS(
ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))")
);
-- POINT(25.1730977433239 27.2789529273059)
SELECT carto.ST_CENTEROFMASS(
ST_GEOGFROMTEXT("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))")
);
-- POINT(25.1730977433239 27.2789529273059)
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.
geog
:ARRAY<GEOGRAPHY>
input points.maxEdge
:FLOAT64
|NULL
the length (in 'units') of an edge necessary for part of the hull to become concave. IfNULL
the default valueinfinity
is used.units
:STRING
|NULL
units of length, the supported options are: miles, kilometers, degrees or radians. IfNULL
the default valuekilometers
is used.
Return type
GEOGRAPHY
Example
carto-un
carto-un-eu
carto-os
manual
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 ...
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 ...
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 ...
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 ...
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.ST_CONCAVEHULL(
[
ST_GEOGPOINT(-75.833, 39.284)
],
100, 'kilometers'
);
-- POINT(-75.833 39.284)
SELECT `carto-un-eu`.carto.ST_CONCAVEHULL(
[
ST_GEOGPOINT(-75.833, 39.284)
],
100, 'kilometers'
);
-- POINT(-75.833 39.284)
SELECT `carto-os`.carto.ST_CONCAVEHULL(
[
ST_GEOGPOINT(-75.833, 39.284)
],
100, 'kilometers'
);
-- POINT(-75.833 39.284)
SELECT carto.ST_CONCAVEHULL(
[
ST_GEOGPOINT(-75.833, 39.284)
],
100, 'kilometers'
);
-- POINT(-75.833 39.284)
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
: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
orradians
. IfNULL
the default valuekilometers
is used.
Return type
GEOGRAPHY
Example
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.ST_DESTINATION(
ST_GEOGPOINT(-3.70325,40.4167),
10,
45,
"miles"
);
-- POINT(-3.56862505487045 40.5189626777536)
SELECT `carto-un-eu`.carto.ST_DESTINATION(
ST_GEOGPOINT(-3.70325,40.4167),
10,
45,
"miles"
);
-- POINT(-3.56862505487045 40.5189626777536)
SELECT `carto-os`.carto.ST_DESTINATION(
ST_GEOGPOINT(-3.70325,40.4167),
10,
45,
"miles"
);
-- POINT(-3.56862505487045 40.5189626777536)
SELECT carto.ST_DESTINATION(
ST_GEOGPOINT(-3.70325,40.4167),
10,
45,
"miles"
);
-- POINT(-3.56862505487045 40.5189626777536)
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
:INT64
|NULL
number of points. IfNULL
the default value100
is used.
Return type
GEOGRAPHY
Example
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.ST_GREATCIRCLE(ST_GEOGPOINT(-3.70325,40.4167), ST_GEOGPOINT(-73.9385,40.6643), 20);
-- LINESTRING(-3.70325 40.4167 ...
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 ...
SELECT `carto-os`.carto.ST_GREATCIRCLE(ST_GEOGPOINT(-3.70325,40.4167), ST_GEOGPOINT(-73.9385,40.6643), 20);
-- LINESTRING(-3.70325 40.4167 ...
SELECT carto.ST_GREATCIRCLE(ST_GEOGPOINT(-3.70325,40.4167), ST_GEOGPOINT(-73.9385,40.6643), 20);
-- LINESTRING(-3.70325 40.4167 ...
Additional examples
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
:FLOAT64
distance along the line.units
:STRING
|NULL
units of length, the supported options are:miles
,kilometers
,degrees
andradians
. IfNULL
the default valuekilometers
is used.
Return type
GEOGRAPHY
Example
carto-un
carto-un-eu
carto-os
manual
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)
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)
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)
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)
Additional examples
This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.

Last modified 5mo ago