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
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
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)
tiler
Guides
Last modified 8mo ago