This module contains functions that compute geometric constructions, or alter geometry size or shape.
ST_BUFFER
Copy 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. 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
carto-un carto-un-eu carto-os manual
Copy SELECT `carto-un` .carto.ST_BUFFER(
ST_GEOGPOINT( - 74 . 00 , 40 . 7128 ),
1 ,
"kilometers" ,
10
);
-- POLYGON((-73.9881354374691 40.7127993926494 ...
Copy SELECT `carto-un-eu` .carto.ST_BUFFER(
ST_GEOGPOINT( - 74 . 00 , 40 . 7128 ),
1 ,
"kilometers" ,
10
);
-- POLYGON((-73.9881354374691 40.7127993926494 ...
Copy SELECT `carto-os` .carto.ST_BUFFER(
ST_GEOGPOINT( - 74 . 00 , 40 . 7128 ),
1 ,
"kilometers" ,
10
);
-- POLYGON((-73.9881354374691 40.7127993926494 ...
Copy SELECT carto.ST_BUFFER(
ST_GEOGPOINT( - 74 . 00 , 40 . 7128 ),
1 ,
"kilometers" ,
10
);
-- POLYGON((-73.9881354374691 40.7127993926494 ...
ST_CENTERMEAN
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
Copy SELECT `carto-un` .carto.ST_CENTERMEAN(
ST_GEOGFROMTEXT( "POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))" )
);
-- POINT(25.3890912155939 29.7916831655627)
Copy 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)
Copy SELECT `carto-os` .carto.ST_CENTERMEAN(
ST_GEOGFROMTEXT( "POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))" )
);
-- POINT(25.3890912155939 29.7916831655627)
Copy SELECT carto.ST_CENTERMEAN(
ST_GEOGFROMTEXT( "POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))" )
);
-- POINT(25.3890912155939 29.7916831655627)
ST_CENTERMEDIAN
Copy 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
Copy SELECT `carto-un` .carto.ST_CENTERMEDIAN(
ST_GEOGFROMTEXT( "POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))" )
);
-- POINT(25.3783930513609 29.8376035441371)
Copy 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)
Copy SELECT `carto-os` .carto.ST_CENTERMEDIAN(
ST_GEOGFROMTEXT( "POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))" )
);
-- POINT(25.3783930513609 29.8376035441371)
Copy SELECT carto.ST_CENTERMEDIAN(
ST_GEOGFROMTEXT( "POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))" )
);
-- POINT(25.3783930513609 29.8376035441371)
ST_CENTEROFMASS
Copy 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
Copy SELECT `carto-un` .carto.ST_CENTEROFMASS(
ST_GEOGFROMTEXT( "POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))" )
);
-- POINT(25.1730977433239 27.2789529273059)
Copy 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)
Copy SELECT `carto-os` .carto.ST_CENTEROFMASS(
ST_GEOGFROMTEXT( "POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))" )
);
-- POINT(25.1730977433239 27.2789529273059)
Copy SELECT carto.ST_CENTEROFMASS(
ST_GEOGFROMTEXT( "POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))" )
);
-- POINT(25.1730977433239 27.2789529273059)
ST_CONCAVEHULL
Copy 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 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
Example
carto-un carto-un-eu carto-os manual
Copy 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 ...
Copy 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 ...
Copy 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 ...
Copy 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
Copy SELECT `carto-un` .carto.ST_CONCAVEHULL(
[
ST_GEOGPOINT( - 75 . 833 , 39 . 284 )
],
100 , 'kilometers'
);
-- POINT(-75.833 39.284)
Copy SELECT `carto-un-eu` .carto.ST_CONCAVEHULL(
[
ST_GEOGPOINT( - 75 . 833 , 39 . 284 )
],
100 , 'kilometers'
);
-- POINT(-75.833 39.284)
Copy SELECT `carto-os` .carto.ST_CONCAVEHULL(
[
ST_GEOGPOINT( - 75 . 833 , 39 . 284 )
],
100 , 'kilometers'
);
-- POINT(-75.833 39.284)
Copy SELECT carto.ST_CONCAVEHULL(
[
ST_GEOGPOINT( - 75 . 833 , 39 . 284 )
],
100 , 'kilometers'
);
-- POINT(-75.833 39.284)
ST_DESTINATION
Copy 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
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
carto-un carto-un-eu carto-os manual
Copy SELECT `carto-un` .carto.ST_DESTINATION(
ST_GEOGPOINT( - 3 . 70325 , 40 . 4167 ),
10 ,
45 ,
"miles"
);
-- POINT(-3.56862505487045 40.5189626777536)
Copy SELECT `carto-un-eu` .carto.ST_DESTINATION(
ST_GEOGPOINT( - 3 . 70325 , 40 . 4167 ),
10 ,
45 ,
"miles"
);
-- POINT(-3.56862505487045 40.5189626777536)
Copy SELECT `carto-os` .carto.ST_DESTINATION(
ST_GEOGPOINT( - 3 . 70325 , 40 . 4167 ),
10 ,
45 ,
"miles"
);
-- POINT(-3.56862505487045 40.5189626777536)
Copy SELECT carto.ST_DESTINATION(
ST_GEOGPOINT( - 3 . 70325 , 40 . 4167 ),
10 ,
45 ,
"miles"
);
-- POINT(-3.56862505487045 40.5189626777536)
ST_GREATCIRCLE
Copy 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. If NULL
the default value 100
is used.
Return type
GEOGRAPHY
Example
carto-un carto-un-eu carto-os manual
Copy SELECT `carto-un` .carto.ST_GREATCIRCLE(ST_GEOGPOINT( - 3 . 70325 , 40 . 4167 ), ST_GEOGPOINT( - 73 . 9385 , 40 . 6643 ), 20 );
-- LINESTRING(-3.70325 40.4167 ...
Copy 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 ...
Copy SELECT `carto-os` .carto.ST_GREATCIRCLE(ST_GEOGPOINT( - 3 . 70325 , 40 . 4167 ), ST_GEOGPOINT( - 73 . 9385 , 40 . 6643 ), 20 );
-- LINESTRING(-3.70325 40.4167 ...
Copy SELECT carto.ST_GREATCIRCLE(ST_GEOGPOINT( - 3 . 70325 , 40 . 4167 ), ST_GEOGPOINT( - 73 . 9385 , 40 . 6643 ), 20 );
-- LINESTRING(-3.70325 40.4167 ...
ST_LINE_INTERPOLATE_POINT
Copy 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
and radians
. If NULL
the default value kilometers
is used.
Return type
GEOGRAPHY
Example
carto-un carto-un-eu carto-os manual
Copy 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)
Copy 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)
Copy 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)
Copy 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)
ST_POINTONSURFACE
Copy 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
carto-un carto-un-eu carto-os manual
Copy 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)
Copy 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)
Copy SELECT `carto-os` .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)
Copy 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)