Comment on page
transformations
CORE
This module contains functions that compute geometric constructions, or alter geometry size or shape.
ST_ANTIMERIDIANSAFEGEOM(geom)
Description
If geom spans the antimeridian, attempt to convert the
Geometry
into an equivalent form that is “antimeridian-safe” (i.e. the output Geometry
is covered by BOX(-180 -90, 180 90)
). In certain circumstances, this method may fail, in which case the input Geometry
will be returned and an error will be logged.geom
:Geometry
input geom.
Return type
Geometry
Example
1
WITH t AS (
2
SELECT carto.ST_MAKEBBOX(178, 0, 190, 5) AS geom
3
)
4
SELECT carto.ST_ASTEXT(carto.ST_ANTIMERIDIANSAFEGEOM(geom)) FROM t;
5
-- MULTIPOLYGON (((-180 0, -180 5, -170 5, -170 0, -180 0)), ((180 5, 180 0, 178 0, 178 5, 180 5)))
ST_BOUNDARY(geom)
Description
Returns the boundary, or an empty
Geometry
of appropriate dimension, if geom is empty.geom
:Geometry
input geom.
Return type
Geometry
Example
1
WITH t AS (
2
SELECT carto.ST_MAKEBBOX(0, 0, 2, 2) AS geom
3
)
4
SELECT carto.ST_ASTEXT(carto.ST_BOUNDARY(geom)) FROM t;
5
-- LINESTRING (0 0, 0 2, 2 2, 2 0, 0 0)
ST_BUFFERPOINT(point, radius)
Description
Returns a
Geometry
covering all points within a given radius of Point point, where radius is given in meters.Returns the boundary, or an empty
Geometry
of appropriate dimension, if geom is empty.point
:Point
Center of the buffer.buffer
:Double
radius in meters.
Return type
Geometry
Example
1
SELECT carto.ST_ASTEXT(carto.ST_BUFFERPOINT(carto.ST_POINT(0, 0), 1));;
2
-- POLYGON ((0.000009 0, 0.000009 0.0000006, 0.0000089 0.0000011, 0.0000088 0.0000017, ...
ST_CENTROID(geom)
Description
Returns the geometric center of a geometry.
geom
:Geometry
input geom.
Return type
Point
Example
1
WITH t AS (
2
SELECT carto.ST_MAKEBBOX(0, 0, 2, 2) AS geom
3
)
4
SELECT carto.ST_ASTEXT(carto.ST_CENTROID(geom)) FROM t;
5
-- POINT (1 1)
ST_CLOSESTPOINT(geomA, geomB)
Description
Returns the
Point
on a that is closest to b. This is the first Point
of the shortest line.geomA
:Geometry
input geom A.geomB
:Geometry
input geom B.
Return type
Point
Example
1
WITH t AS (
2
SELECT carto.ST_GEOMFROMWKT("LINESTRING (3 1, 1 3)") AS geomA,
3
carto.ST_POINT(0, 0) AS geomb
4
)
5
SELECT carto.ST_ASTEXT(carto.ST_CLOSESTPOINT(geomA, geomB)) FROM t;
6
-- POINT (2 2)
ST_CONVEXHULL(geom)
Description
Aggregate function. The convex hull of a
Geometry
represents the minimum convex Geometry
that encloses all geometries geom in the aggregated rows.geom
:Geometry
input geom.
Return type
Geometry
Example
1
WITH t AS (
2
SELECT carto.ST_GEOMFROMWKT(
3
'GEOMETRYCOLLECTION(LINESTRING(1 1, 3 5),POLYGON((-1 -1, -1 -5, -5 -5, -5 -1, -1 -1)))'
4
) AS geom
5
)
6
SELECT carto.ST_ASTEXT(carto.ST_CONVEXHULL(geom)) FROM t;
7
-- POLYGON ((-5 -5, -5 -1, 3 5, -1 -5, -5 -5))
ST_DIFFERENCE(geomA, geomB)
Description
Return the part of geomA that does not intersect with geomB.
geomA
:Geometry
input geom A.geomB
:Geometry
input geom B.
Return type
Geometry
Example
1
WITH t AS (
2
SELECT carto.ST_MAKEBBOX(0, 0, 2, 2) AS geomA,
3
carto.ST_MAKEBBOX(1, 1, 3, 3) AS geomB
4
)
5
SELECT carto.ST_ASTEXT(carto.ST_DIFFERENCE(geomA, geomB)) AS difference FROM t;
6
-- POLYGON ((0 0, 0 2, 1 2, 1 1, 2 1, 2 0, 0 0))
ST_EXTERIORRING(geom)
Description
Returns a
LineString
representing the exterior ring of the geometry; returns null if the Geometry
is not a Polygon
.geom
:Geometry
input geom.
Return type
LineString
Example
1
WITH t AS (
2
SELECT carto.ST_MAKEBBOX(0, 0, 1, 1) AS geom
3
)
4
SELECT carto.ST_ASTEXT(carto.ST_EXTERIORRING(geom)) FROM t;
5
-- LINESTRING (0 0, 0 1, 1 1, 1 0, 0 0)
ST_IDLSAFEGEOM(geom)
Description
Alias of
st_antimeridianSafeGeom
.geom
:Geometry
input geom.
Return type
Geometry
Example
1
WITH t AS (
2
SELECT carto.ST_MAKEBBOX(178, 0, 190, 5) AS geom
3
)
4
SELECT carto.ST_ASTEXT(carto.ST_IDLSAFEGEOM(geom)) AS geom FROM t;
5
-- MULTIPOLYGON (((-180 0, -180 5, -170 5, -170 0, -180 0)), ((180 5, 180 0, 178 0, 178 5, 180 5)))
ST_INTERIORRINGN(geom, n)
Description
Returns a
LineString
representing the exterior ring of the geometry; returns null if the Geometry
is not a Polygon
.geom
:Geometry
input geom.n
:Int
nth ring to take.
Return type
LineString
Example
1
WITH t AS (
2
SELECT carto.ST_GEOMFROMWKT("POLYGON ((10 10, 110 10, 110 110, 10 110, 10 10), (20 20, 20 30, 30 30, 30 20, 20 20), (40 20, 40 30, 50 30, 50 20, 40 20))") AS geom
3
)
4
SELECT carto.ST_ASTEXT(carto.ST_INTERIORRINGN(geom, 1)) FROM t;
5
-- LINESTRING (20 20, 20 30, 30 30, 30 20, 20 20)
ST_INTERSECTION(geomA, geomB)
Description
Returns the intersection of the input
Geometries
.geomA
:Geometry
input geom A.geomB
:Geometry
input geom B.
Return type
Geometry
Example
1
WITH t AS (
2
SELECT carto.ST_MAKEBBOX(0, 0, 2, 2) AS geomA,
3
carto.ST_MAKEBBOX(1, 1, 3, 3) AS geomB
4
)
5
SELECT carto.ST_ASTEXT(carto.ST_INTERSECTION(geomA, geomB)) AS intersection FROM t;
6
-- POLYGON ((1 2, 2 2, 2 1, 1 1, 1 2))
ST_SIMPLIFY(geom, tolerance)
Description
Returns a simplified version of the given
Geometry
using the Douglas-Peucker algorithm. This function does not preserve topology - e.g. polygons can be split, collapse to lines or disappear holes can be created or disappear, and lines can cross. To simplify geometry while preserving topology use ST_SIMPLIFYPRESERVETOPOLOGY.geom
:Geometry
input geom.tolerance
:Double
input distance tolerance. double
Return type
Geometry
Example
1
WITH t AS (
2
SELECT carto.ST_BUFFERPOINT(carto.ST_POINT(0, 0), 10) AS geom
3
)
4
SELECT
5
carto.ST_ASTEXT(carto.ST_SIMPLIFY(geom, 0.00001)) AS simplifiedGeom,
6
carto.ST_NUMPOINTS(carto.ST_SIMPLIFY(geom, 0.00001)) AS simplifiedNumpoints,
7
carto.ST_NUMPOINTS(geom) AS numPoints
8
FROM t;
9
-- POLYGON ((0.0000899 0, 0.0000656 0.0000616, 0 0.0000899, -0.0000616 0.0000656, -0.0000899 0, -0.0000656 -0.0000616, 0 -0.0000899, 0.0000616 -0.0000656, 0.0000899 0)) | 9 | 101
ST_SIMPLIFYPRESERVETOPOLOGY(geom, tolerance)
Description
Simplifies a
Geometry
and ensures that the result is a valid geometry having the same dimension and number of components as the input, and with the components having the same topological relationship.geom
:Geometry
input geom.tolerance
:Double
input distance tolerance. double
Return type
Geometry
Example
1
WITH t AS (
2
SELECT carto.ST_BUFFERPOINT(carto.ST_POINT(0, 0), 10) AS geom
3
)
4
SELECT
5
carto.ST_ASTEXT(carto.ST_SIMPLIFYPRESERVETOPOLOGY(geom, 1)) AS simplifiedGeom,
6
carto.ST_NUMPOINTS(carto.ST_SIMPLIFYPRESERVETOPOLOGY(geom, 1)) AS simplifiedNumpoints,
7
carto.ST_NUMPOINTS(geom) AS numPoints
8
FROM t;
9
-- POLYGON ((0.0000899 0, 0 0.0000899, -0.0000899 0, 0 -0.0000899, 0.0000899 0)) | 5 | 101
ST_TRANSLATE(geom, deltaX, deltaY)
Description
Returns the
Geometry
produced when geom is translated by deltaX and deltaY.geom
:Geometry
input geom.deltaX
:Double
distance x to be tralslated.deltaY
:Double
distance y to be tralslated.
Return type
Geometry
Example
1
WITH t AS (
2
SELECT carto.ST_POINT(0, 0) AS point
3
)
4
SELECT carto.ST_ASTEXT(carto.ST_TRANSLATE(point, 1, 2)) FROM t;
5
-- POINT (1 2)
Last modified 8mo ago