K
Comment on page

# transformations

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

### ST_ANTIMERIDIANSAFEGEOM

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

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

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

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

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

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

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

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

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

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

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

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

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

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)