MSQL Reference – Spatial Functions

The following are the built-in functions for doing various kinds of processing operations and transformations over geometric objects.
They are divided into four groups:

Geometry Processing - Functions for computing buffer zones, centroids, convex hulls, etc.
Topological Set Operations - Functions for performing topological set operations (e.g. intersection, difference, union).
Affine Transformations - Functions for translating, rotating, scaling of geometric objects.
Geometry Aggregate Functions - Aggregate functions for combining multiple geometric objects into a single result.

Geometry Processing

fn ST_BUFFER

ST_BUFFER(g: [point | linestring | polygon], radius: float64) -> polygon

ST_BUFFER(g: [point | linestring | polygon], radius: float64, segs_per_quadrant: int64) -> polygon

ST_BUFFER(g: multi[point | linestring | polygon], radius: float64) -> multipolygon

ST_BUFFER(g: multi[point | linestring | polygon], radius: float64, segs_per_quadrant: int64) -> multipolygon

Returns a geometry covering all points within a given radius from the input geometry.

Arguments

The units of radius are meters. If the radius for any reason needs to be specified in Mireo World-Point coordinate units, there is a conversion function TO_METERS which converts a value in world-point coordinate units to meters.

The radius argument can be negative, in which case the input geometry is shrunk, not expanded.

The segs_per_quadrant argument (if present) determines the number of segments used to approximate a quarter-circle. If the argument is absent or equal to 0, the default value of 8 will be used.

Null handling

If any of the arguments is NULL, the function will also return NULL.

Error handling

If the segs_per_quadrant argument is negative, the function will return an error: Function argument segs_per_quadrant must not be negative..

Notes

If the buffer radius is negative, the result might become an empty [multi]polygon (e.g. ST_Buffer(POLYGON '((0 0, 10 0, 5 5, 0 0))', -100)).

Negative radius has a meaning only with [multi]polygon input geometries; in case of a [multi]point or a [multi]linestring geometry, the result will always be an empty [multi]polygon.

If the buffer radius is equals 0, the function will return the input geometry unchanged if the input is a [multi]polygon, and an empty [multi]polygon in all other cases.

Examples

SELECT ST_BUFFER(linestring '(1000 1000, 5000 10000)', 5);
  --> POLYGON((4969.34625 10013.623889,969.34625 1013.623889,967.277365 1007.38186,966.465992 1000.85615,966.943312 994.297539,968.690982 987.95807,971.64184 982.081366,975.682486 976.893265,980.657641 972.593142,986.376111 969.34625,992.61814 967.277365,999.14385 966.465992,1005.702461 966.943312,1012.04193 968.690982,1017.918634 971.64184,1023.106735 975.682486,1027.406858 980.657641,1030.65375 986.376111,5030.65375 9986.376111,5032.722635 9992.61814,5033.534008 9999.14385,5033.056688 10005.702461,5031.309018 10012.04193,5028.35816 10017.918634,5024.317514 10023.106735,5019.342359 10027.406858,5013.623889 10030.65375,5007.38186 10032.722635,5000.85615 10033.534008,4994.297539 10033.056688,4987.95807 10031.309018,4982.081366 10028.35816,4976.893265 10024.317514,4972.593142 10019.342359,4969.34625 10013.623889))

SELECT ST_BUFFER(multipoint '(0 0, 10000 20000)', 20, 1);
  --> MULTIPOLYGON(((10134.179746 20000,10000 20134.179746,9865.820254 20000,10000 19865.820254,10134.179746 20000)),((134.179746 0,0 134.179746,-134.179746 0,0 -134.179746,134.179746 0)))

See also

ST_EXPAND

fn ST_CENTROID

ST_CENTROID(g: anygeometry) -> point

Computes the geometric center of a geometry, or equivalently, the center of mass of the geometry as a point.

Null handling

If the argument is NULL, the function will also return NULL.

Notes

If the argument is an empty geometry, the function will return NULL.

Examples

SELECT ST_CENTROID(linestring '(10 20, 30 30, 40 10)');
  --> POINT(27.5 22.5)

SELECT ST_CENTROID(multipoint '(10 10, 15 20, 20 15, 40 0, 80 5)');
  --> POINT(33 10)

fn ST_CLIP

ST_CLIP(g: [multi]point, xmin: float64, ymin: float64, xmax: float64, ymax: float64) -> multipoint

ST_CLIP(g: [multi]linestring, xmin: float64, ymin: float64, xmax: float64, ymax: float64) -> multilinestring

ST_CLIP(g: [multi]polygon, xmin: float64, ymin: float64, xmax: float64, ymax: float64) -> multipolygon

Returns the portion of a geometry falling within a rectangle ((xmin, ymin), (xmax, ymax)).

Null handling

If any of the arguments is NULL, the function will also return NULL.

Notes

This function is just a shortcut for the ST_INTERSECTION function, so everything said there applies here as well.

Examples

SELECT ST_CLIP(linestring '(10 10, 30 30, 30 5, 10 5)', 0, 0, 20, 20);
  --> MULTILINESTRING((10 10,20 20),(20 5,10 5))

See also

ST_INTERSECTION

fn ST_CONVEXHULL

ST_CONVEXHULL(g: multipoint | [multi]linestring | [multi]polygon) -> polygon

Computes the convex hull of a given geometry, as a polygon geometry.

Arguments

The function doesn't allow point geometries as inputs, as their convex hull is never a valid polygon.

Null handling

If the argument is NULL, the function will also return NULL.

Notes

The convex hull is the smallest convex geometry that encloses all geometries in the input.

In some cases, the function can return an invalid polygon (e.g. when the input geometry contains fewer than 3 points, or when all the points are colinear).

If the input geometry is empty, the function will return an empty polygon.

Examples

SELECT ST_CONVEXHULL(multipoint '(10 10, 20 15, 15 20, 40 0, 35 30, 80 50, 5 40)');
  --> POLYGON((40 0,80 50,5 40,10 10,40 0))

A case of an invalid polygon (too few points in input):

SELECT ST_CONVEXHULL(linestring '(10 10, 30 30)');
  --> POLYGON((10 10,30 30,30 30,10 10))

fn ST_REVERSE

ST_REVERSE(ls: linestring) -> linestring

Returns a linestring geometry with the order of points reversed.

Null handling

If the argument is NULL, the function will also return NULL.

Examples

SELECT ST_REVERSE(linestring '(0 0, 10 20)');
  --> LINESTRING(10 20,0 0)

Topological Set Operations

fn ST_DIFFERENCE

ST_DIFFERENCE(g1: [multi]point, g2: [multi]point) -> multipoint

ST_DIFFERENCE(g1: [multi]linestring), g2: [multi]linestring -> multilinestring

ST_DIFFERENCE(g1: [multi]polygon, g2: [multi]polygon) -> multipolygon

Returns the part of geometry g1 which is does not intersect with g2.

Null handling

If any of the arguments is NULL, the function will also return NULL.

Notes

If g1 is completely contained within g2, the result is an empty set.

The order of the arguments is important - the result will always be a subset of g1.

Examples

SELECT ST_DIFFERENCE(linestring '(15 10, 25 10, 25 20)', linestring '(10 10, 30 10)');
  --> MULTILINESTRING((25 10,25 20))

SELECT ST_DIFFERENCE(linestring '(10 10, 30 10)', linestring '(15 10, 25 10, 25 20)');
  --> MULTILINESTRING((10 10,15 10),(25 10,30 10))

fn ST_INTERSECTION

ST_INTERSECTION(g1: [multi]point, g2: anygeometry) -> multipoint

ST_INTERSECTION(g1: anygeometry, g2: [multi]point) -> multipoint

ST_INTERSECTION(g1: [multi]linestring, g2: [multi]linestring | [multi]polygon) -> multilinestring

ST_INTERSECTION(g1: [multi]linestring | [multi]polygon, g2: [multi]linestring) -> multilinestring

ST_INTERSECTION(g1: [multi]polygon, g2: [multi]polygon) -> multipolygon

Returns parts of g1 which are shared (i.e. intersect) with g2.

Return type

The result will always have a dimension equal to the lower of the argument's dimensions. This means that if, for example, a polygon and a linestring touch only at a point, the function will return an empty multilinestring and not the intersection point.

Null handling

If any of the arguments is NULL, the function will also return NULL.

Notes

If the geometries are disjoint (i.e. they don't intersect), the function will return an empty collection.

The order of arguments is not important - ST_INTERSECTION(g1, g2) = ST_INTERSECTION(g2, g1).

Examples

SELECT ST_INTERSECTION(linestring '(0 0, 20 0, 20 10, 10 10)', linestring '(20 -10, 20 30)');
  --> MULTILINESTRING((20 0,20 10))

SELECT ST_INTERSECTION(
    polygon '((0 10, 60 10, 60 40, 0 40, 0 10), (10 20, 10 30, 50 30, 50 20, 10 20))', 
    polygon '((20 0, 40 0, 40 50, 20 50, 20 0))');

--> MULTIPOLYGON(((40 10,40 20,20 20,20 10,40 10)),((20 40,20 30,40 30,40 40,20 40)))

fn ST_SYMDIFFERENCE

ST_SYMDIFFERENCE(g1: [multi]point, g2: [multi]point) -> multipoint

ST_SYMDIFFERENCE(g1: [multi]linestring), g2: [multi]linestring -> multilinestring

ST_SYMDIFFERENCE(g1: [multi]polygon, g2: [multi]polygon) -> multipolygon

Returns parts of geometries g1 and g2 which do not intersect.

Null handling

If any of the arguments is NULL, the function will also return NULL.

Notes

The order of the arguments is not important - ST_SYMDIFFERENCE(g1, g2) = ST_SYMDIFFERENCE(g2, g1).

Examples

SELECT ST_SYMDIFFERENCE(linestring '(15 10, 25 10, 25 20)', linestring '(10 10, 30 10)');
  --> MULTILINESTRING((25 10,25 20),(10 10,15 10),(25 10,30 10))

SELECT ST_SYMDIFFERENCE(linestring '(10 10, 30 10)', linestring '(15 10, 25 10, 25 20)');
  --> MULTILINESTRING((10 10,15 10),(25 10,30 10),(25 10,25 20))

See also

ST_DIFFERENCE ST_INTERSECTION ST_UNION

fn ST_UNION

ST_UNION(g1: [multi]point, g2: [multi]point) -> multipoint

ST_UNION(g1: [multi]linestring), g2: [multi]linestring -> multilinestring

ST_UNION(g1: [multi]polygon, g2: [multi]polygon) -> multipolygon

Returns a union of two geometries as a corresponding multigeometry.

Null handling

If any of the arguments is NULL, the function will also return NULL.

Examples

SELECT ST_UNION(multipoint '(10 10, 30 50, 0 20)', multipoint '(20 10, 20 50, 100 15, 0 20)');
  --> MULTIPOINT((0 20),(10 10),(20 10),(20 50),(30 50),(100 15))

SELECT ST_UNION(
    linestring '(10 10, 30 20, 30 50)',
    multilinestring '((10 30, 10 20, 20 15, 30 20, 30 10), (30 30, 30 50))');

--> MULTILINESTRING((10 10,20 15),(20 15,30 20),(30 20,30 30),(30 30,30 50),(10 30,10 20,20 15),(30 20,30 10))

SELECT ST_UNION(polygon '((0 0, 20 0, 20 20, 0 20, 0 0))', polygon '((10 10, 50 10, 30 50, 10 10))');
  --> MULTIPOLYGON(((20 10,50 10,30 50,15 20,0 20,0 0,20 0,20 10)))

Affine transformations

fn ST_AFFINE

ST_AFFINE(g: anygeometry, a: float64, b: float64, c: float64, d: float64, xoff: float64, yoff: float64) -> anygeometry

Applies a 2D affine transformation to the given geometry and returns the result.

Arguments

Units of xoff and yoff are those of the Mireo World-point coordinate system. If these values need to be specified in meters, there is a conversion function TO_WORLD which converts a value in meters to world-point coordinate units.

Return type

The result is always of the same type as the first argument.

Null handling

If any of the arguments is NULL, the function will also return NULL.

Notes

The coordinates of individual vertices are transformed according to the following formulas:

x' = a*x + b*y + xoff
y' = c*x + d*y + yoff

When [multi]polygon objects are transformed, the function will ensure that the result object retains the expected ring orientations (i.e. the outer polygon rings will always be counter-clockwise-oriented and the inner rings clockwise-oriented).

Examples

SELECT ST_AFFINE(point '(10 20)', 2, 0.5, 1, 3, 5, 10);
  --> POINT(35 80)

SELECT ST_AFFINE(polygon '((0 0, 20 0, 20 20, 0 20, 0 0), (5 5, 10 10, 15 5, 5 5))',
    COS(PI()), -SIN(PI()), SIN(PI()), COS(PI()), 10, 20);

--> POLYGON((10 20,-10 20,-10 0,10 0,10 20),(5 15,0 10,-5 15,5 15))

fn ST_ROTATE

ST_ROTATE(g: anygeometry, angle: float64) -> anygeometry

ST_ROTATE(g: anygeometry, angle: float64, origX: float64, origY: float64) -> anygeometry

Rotates a geometry counter-clockwise by a given angle (in radians) about the origin point.

Arguments

The default origin (if not specified) is the point (0, 0).

Return type

The result is always of the same type as the first argument.

Null handling

If any of the arguments is NULL, the function will also return NULL.

Notes

ST_ROTATE(g, angle) is just a shortcut for ST_AFFINE(g, COS(angle), -SIN(angle), SIN(angle), COS(angle), 0, 0).

Examples

SELECT ST_ROTATE(point '(20 30)', PI()/2, 10, 10);
  --> POINT(-10 20)

SELECT ST_ROTATE(polygon '((0 0, 20 0, 20 20, 0 20, 0 0), (5 5, 10 10, 15 5, 5 5))', -PI()/2);
  --> POLYGON((0 0,0 -20,20 -20,20 0,0 0),(5 -5,10 -10,5 -15,5 -5))

fn ST_SCALE

ST_SCALE(g: anygeometry, sx: float64, sy: float64) -> anygeometry

ST_SCALE(g: anygeometry, sx: float64, sy: float64, origX: float64, origY: float64) -> anygeometry

Scales a geometry by sx and sy factors in x- and y-direction, respectively, relative to the given origin.

Arguments

The default origin (if not specified) is the point (0, 0).

Return type

The result is always of the same type as the first argument.

Null handling

If any of the arguments is NULL, the function will also return NULL.

Notes

ST_SCALE(g, sx, sy) is just a shortcut for ST_AFFINE(g, sx, 0, 0, sy, 0, 0).

Examples

SELECT ST_SCALE(point '(10 20)', 2, 3);
  --> POINT(20 60)

SELECT ST_SCALE(polygon '((0 0, 20 0, 20 20, 0 20, 0 0), (5 5, 10 10, 15 5, 5 5))', 3, 0.5);
  --> POLYGON((0 0,60 0,60 10,0 10,0 0),(15 2.5,30 5,45 2.5,15 2.5))

SELECT ST_SCALE(point '(10 20)', 2, 2, 10, 10);
  --> POINT(10 30)

fn ST_TRANSLATE

ST_TRANSLATE(g: anygeometry, dx: float64, dy: float64) -> anygeometry

Translates a geometry by dx and dy units in x- and y-direction, respectively.

Arguments

Units of dx and dy are those of the Mireo World-point coordinate system. If the translation distance needs to be set in meters, there is a conversion function TO_WORLD which converts a value in meters to world-point coordinate units.

Return type

The result is always of the same type as the first argument.

Null handling

If any of the arguments is NULL, the function will also return NULL.

Notes

ST_TRANSLATE(g, dx, dy) is just a shortcut for ST_AFFINE(g, 1, 0, 0, 1, dx, dy).

Examples

SELECT ST_TRANSLATE(point '(10 20)', 100, -50);
  --> POINT(110 -30)

SELECT ST_TRANSLATE(polygon '((0 0, 20 0, 20 20, 0 20, 0 0), (5 5, 10 10, 15 5, 5 5))', 30, 40);
  --> POLYGON((30 40,50 40,50 60,30 60,30 40),(35 45,40 50,45 45,35 45))

fn ST_TRANSSCALE

ST_TRANSSCALE(g: anygeometry, dx: float64, dy: float64, sx: float64, sy: float64) -> anygeometry

Translates a geometry by dx and dy units, then scales it by sx and sy factors in x- and y-direction, respectively.

Arguments

Return type

The result is always of the same type as the first argument.

Null handling

If any of the arguments is NULL, the function will also return NULL.

Notes

ST_TRANSSCALE(g, dx, dy, sx, sy) is just a shortcut for ST_AFFINE(g, sx, 0, 0, sy, sx*dx, sy*dy).

Examples

SELECT ST_TRANSSCALE(point '(20 40)', 100, -100, 0.25, 0.25);
  --> POINT(30 -15)

SELECT ST_TRANSSCALE(polygon '((0 0, 20 0, 20 20, 0 20, 0 0), (5 5, 10 10, 15 5, 5 5))', 30, 40, 0.5, 2.0);
  --> POLYGON((15 80,25 80,25 120,15 120,15 80),(17.5 90,20 100,22.5 90,17.5 90))