MSQL Reference – Spatial Functions

The following are the built-in functions for accessing and checking various properties of geometric objects.
They are divided into several sections:

Generic Accessors - Functions for accessing and checking generic properties common to all kinds of geometries.
Point Accessors - Functions for accessing properties specific to point objects.
Linestring Accessors - Functions for accessing properties specific to linestring objects.
Polygon Accessors - Functions for accessing properties specific to polygon objects.
Multi-geometry Accessors - Functions for accessing properties specific to multi* objects.
Bounding-box Functions - Functions for dealing with bounding-boxes of geometric objects.

Generic Accessors

fn ST_BOUNDARY

ST_BOUNDARY(g: [multi]linestring) -> multipoint

ST_BOUNDARY(g: [multi]polygon) -> multilinestring

Returns the boundary of the given linestring/multilinestring or polygon/multipolygon geometry.

Null handling

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

Notes

The boundary of an open linestring are its start- and end-point. The boundary of a closed linestring is an empty set.

If the input geometry is empty, the output will also be an empty geometry of a corresponding type.

For multilinestring geometries, the boundary is calculated by applying the mod 2 union rule:
A point is in the boundary of a multilinestring if it is in the boundaries of an odd number of elements of the multilinestring.

Examples

SELECT ST_BOUNDARY(linestring '(100 150,50 60, 70 80, 160 170)');
  --> MULTIPOINT((100 150),(160 170))

SELECT ST_BOUNDARY(polygon '((10 10, -10 10, 0 0, 10 10))');
  --> MULTILINESTRING((10 10,-10 10,0 0,10 10))

See also

ST_ENVELOPE

fn ST_GEOMETRYTYPE

ST_GEOMETRYTYPE(g: anygeometry) -> text

Returns the type of a given geometry as text.

Null handling

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

Notes

The result will be one of: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon.

Examples

SELECT ST_GEOMETRYTYPE(point '(10 20)');
  --> 'Point'

SELECT ST_GEOMETRYTYPE(linestring '(10 20, 10 40)');
  --> 'LineString'

fn ST_MEMSIZE

ST_MEMSIZE(g: anygeometry) -> int64

Returns the amount of memory space (in bytes) used by the input geometry.

Null handling

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

Notes

The in-memory size of spatial objects depends upon their internal MSQL representation and can change if the internal representation changes.

Examples

SELECT ST_MEMSIZE(point '(10 20)');
  --> 16

SELECT ST_MEMSIZE(multilinestring 'EMPTY');
  --> 4

fn ST_NPOINTS

ST_NPOINTS(g: anygeometry) -> int64

Returns the number of points (vertexes) in a geometric object.

Null handling

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

Examples

SELECT ST_NPOINTS(multipoint '(10 20, 30 0, 0 15)');
  --> 3

SELECT ST_NPOINTS(polygon '((0 0, 20 0, 20 20, 0 20, 0 0), (5 5, 15 5, 10 10, 5 5))');
  --> 9

fn ST_ISEMPTY

ST_ISEMPTY(g: anygeometry) -> bool

Returns true if a given geometry is empty, false otherwise.

Null handling

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

Notes

The function always returns false for a point geometry.

Examples

SELECT ST_ISEMPTY(multipoint 'EMPTY');
  --> true

SELECT ST_ISEMPTY(point '(10 20)');
  --> false

See also

ST_ISSIMPLE ST_ISVALID

fn ST_ISSIMPLE

ST_ISSIMPLE(g: anygeometry) -> bool

Returns true if the geometry has no anomalous geometric points, such as self-intersections or self-tangencies.

Null handling

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

Notes

The function always returns true if the argument is a point or a (multi)polygon.

Examples

SELECT ST_ISSIMPLE(polygon '((10 20, 30 40, 50 60, 10 20))');
  --> true

SELECT ST_ISSIMPLE(linestring '(10 10, 20 20, 20 35, 10 30, 10 20, 20 10)');
  --> false

See also

ST_ISEMPTY ST_ISVALID

fn ST_ISVALID

ST_ISVALID(g: anygeometry) -> bool

Returns true if a given geometry is well-formed, false otherwise.

Null handling

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

Notes

All point and multipoint geometries are always valid (though they might not be simple).

A linestring is invalid if it has less than two distinct vertices. A multilinestring is invalid if it contains an invalid linestring.

A polygon is valid if no two rings in the boundary (made up of an exterior ring and interior rings) cross. The boundary of a polygon may intersect at a point but only as a tangent (i.e. not on a line). A polygon may not have cut lines or spikes and the interior rings must be contained entirely within the exterior ring.

A multipolygon is valid if and only if all of its elements are valid and the interiors of no two elements intersect. The boundaries of any two elements may touch, but only at a finite number of points.

Most spatial functions rely on the assumption that geometries given to them are valid. If not, they might return either incorrect results or an error.

Examples

SELECT ST_ISVALID(polygon '((10 10, 30 20, 40 40, 20 30, 10 10))');
  --> true

SELECT ST_ISVALID(polygon '((10 10, 30 20, 20 30, 40 40, 10 10))');
  --> false

See also

ST_ISVALIDREASON ST_ISEMPTY ST_ISSIMPLE

fn ST_ISVALIDREASON

ST_ISVALIDREASON(g: anygeometry) -> text

Returns text stating that a given geometry is valid, or a reason for its invalidity.

Null handling

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

Examples

SELECT ST_ISVALIDREASON(polygon '((10 10, 30 20, 40 40, 20 30, 10 10))');
  --> 'Geometry is valid'

SELECT ST_ISVALIDREASON(polygon '((10 10, 30 20, 20 30, 40 40, 10 10))');
  --> 'Self-intersection (25, 25)'

See also

ST_ISVALID

Point Accessors

fn ST_X

ST_X(pt: point) -> float64

ST_X(pt: geogpoint) -> float64

Returns the X coordinate of a point geometry/geography.

Null handling

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

Examples

SELECT ST_X(point '(10 20)');
  --> 10.0

SELECT ST_X(geogpoint '(2.35 48.86)');
  --> 2.35

See also

ST_Y [] operator ST_XMAX ST_XMIN

fn ST_Y

ST_Y(pt: point) -> float64

ST_Y(pt: geogpoint) -> float64

Returns the Y coordinate of a point geometry/geography.

Null handling

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

Examples

SELECT ST_Y(point '(10 20)');
  --> 20.0

SELECT ST_Y(geogpoint '(2.35 48.86)');
  --> 48.86

See also

ST_X [] operator ST_YMAX ST_YMIN

Linestring Accessors

fn ST_NUMPOINTS

ST_NUMPOINTS(ls: linestring) -> int64

Returns the number of points in a linestring geometry.

Null handling

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

Notes

This function is just an alias for the more generic ST_NPOINTS which accepts any geometry, not just linestrings.

Examples

SELECT ST_NUMPOINTS(linestring '(10 10, 20 30, 45 70, 20 35)');
  --> 4

See also

ST_POINTN ST_NPOINTS

fn ST_POINTN

ST_POINTN(ls: linestring, n: int64) -> geometry

Returns the Nth point of a linestring geometry.

Arguments

The index n is 1-based. If it's is negative, the points are counted from the end of the linestring.

Null handling

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

Notes

If there are fewer than n points in a given linestring, the function will return NULL.

Examples

SELECT ST_POINTN(linestring '(10 10, 20 30, 50 65, 20 40)', 2);
  --> POINT(20 30)

SELECT ST_POINTN(linestring '(10 10, 20 30, 50 65, 20 40)', -2);
  --> POINT(50 65)

SELECT ST_POINTN(linestring '(10 10, 20 30, 50 65, 20 40)', 6);
  --> NULL

See also

ST_STARTPOINT ST_ENDPOINT ST_NUMPOINTS

fn ST_STARTPOINT

ST_STARTPOINT(ls: linestring) -> geometry

Returns the first point of a linestring geometry.

Null handling

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

Notes

If the given linestring is empty, the function will return NULL.

Examples

SELECT ST_STARTPOINT(linestring '(10 10, 20 30, 50 65, 20 40)');
  --> POINT(10 10)

SELECT ST_STARTPOINT(linestring 'EMPTY');
  --> NULL

See also

ST_ENDPOINT ST_POINTN

fn ST_ENDPOINT

ST_ENDPOINT(ls: linestring) -> geometry

Returns the last point of a linestring geometry.

Null handling

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

Notes

If the given linestring is empty, the function will return NULL.

Examples

SELECT ST_ENDPOINT(linestring '(10 10, 20 30, 50 65, 20 40)');
  --> POINT(20 40)

SELECT ST_ENDPOINT(linestring 'EMPTY');
  --> NULL

Polygon Accessors

fn ST_EXTERIORRING

ST_EXTERIORRING(py: polygon) -> linestring

Returns the exterior ring of a given polygon geometry as a linestring.

Null handling

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

Notes

If the given argument is an empty polygon, the function will return an empty linestring.

Examples

SELECT ST_EXTERIORRING(polygon '((0 0, 20 0, 20 20, 0 20, 0 0), (5 5, 10 10, 10 5, 5 5))');
  --> LINESTRING(0 0,20 0,20 20,0 20,0 0)

See also

ST_INTERIORRINGN ST_NUMINTERIORRINGS

fn ST_NUMINTERIORRINGS

ST_NUMINTERIORRINGS(py: polygon) -> int64

Returns the number of interior rings (holes) of a polygon geometry.

Null handling

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

Examples

SELECT ST_NUMINTERIORRINGS(polygon '((0 0, 0 20, 20 20, 20 0, 0 0), (5 5, 10 10, 10 5, 5 5))');
  --> 1

See also

ST_EXTERIORRING ST_INTERIORRINGN

fn ST_INTERIORRINGN

ST_INTERIORRINGN(py: polygon, n: int64) -> linestring

Returns the Nth interior ring (hole) of a given polygon geometry as a linestring.

Arguments

The index n is 1-based. If it's negative, the rings are counted from the end of the interior ring collection.

Null handling

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

Notes

If there are fewer than n elements in a collection, the function will return NULL.

Examples

SELECT ST_INTERIORRINGN(polygon '((0 0, 0 20, 20 20, 20 0, 0 0), (5 5, 10 10, 10 5, 5 5))', 1);
  --> LINESTRING(5 5,10 10,10 5,5 5)

SELECT ST_INTERIORRINGN(polygon '((0 0, 0 20, 20 20, 20 0, 0 0), (5 5, 10 10, 10 5, 5 5))', -1);
  --> LINESTRING(5 5,10 10,10 5,5 5)

SELECT ST_INTERIORRINGN(polygon '((0 0, 0 20, 20 20, 20 0, 0 0), (5 5, 10 10, 10 5, 5 5))', 3);
  --> NULL

See also

ST_EXTERIORRING ST_NUMINTERIORRINGS

Multi-geometry Accessors

fn ST_NUMGEOMETRIES

ST_NUMGEOMETRIES(g: multi[point | linestring | polygon]) -> int64

Returns the number of elements in a geometry collection.

Null handling

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

Examples

SELECT ST_NUMGEOMETRIES(multipoint '(10 20, 30 30, 50 65)');
  --> 3

See also

ST_GEOMETRYN ST_NPOINTS

fn ST_GEOMETRYN

ST_GEOMETRYN(mpt: multipoint, n: int64) -> point

ST_GEOMETRYN(mls: multilinestring, n: int64) -> linestring

ST_GEOMETRYN(mpy: multipolygon, n: int64) -> polygon

Returns the Nth geometry element of a multi-geometry.

Arguments

The index n is 1-based. If it's negative, the elements are counted from the end of the collection.

Null handling

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

Notes

If there are fewer than n elements in a collection, the function will return NULL.

Examples

SELECT ST_GEOMETRYN(multipoint '(10 10, 20 30, 45 15)', 3);
  --> POINT(45 15)

SELECT ST_GEOMETRYN(multipoint '(10 10, 20 30, 45 15)', -2);
  --> POINT(20 30)

SELECT ST_GEOMETRYN(multipoint '(10 10, 20 30, 45 15)', 7);
  --> NULL

See also

ST_NUMGEOMETRIES

Bounding-box Functions

fn ST_ENVELOPE

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

Returns a minimum bounding box for the supplied geometry, as a polygon geometry.

Arguments

The function doesn't allow point geometries as inputs, as point's bounding box is never a valid polygon.

Null handling

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

Notes

When the input geometry is empty (e.g. an empty multipoint), the function will return an empty polygon.

The function can sometimes return an invalid polygon, e.g. when an input is a horizontal or vertical linestring, or a multipoint containing only one point.

Examples

SELECT ST_ENVELOPE(linestring '(10 10, 20 20)');
  --> POLYGON((10 10,20 10,20 20,10 20,10 10))

SELECT ST_ENVELOPE(multipoint 'EMPTY');
  --> POLYGON EMPTY

A case of degenerated result:

SELECT ST_ENVELOPE(linestring '(10 10, 20 10)');
  --> POLYGON((10 10,20 10,20 10,10 10,10 10))

See also

ST_BOUNDARY ST_EXPAND

fn ST_EXPAND

ST_EXPAND(g: anygeometry, dist: float64) -> polygon

ST_EXPAND(g: anygeometry, dx: float64, dy: float64) -> polygon

Returns a geometry's envelope expanded by dist meters in both directions or dx in the x-direction and dy in the y-direction.

Arguments

Both positive and negative distances are supported.

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

Null handling

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

Notes

If the given geometry is empty, or the distances specified are negative and the envelope is shrunk to non-existence, the result will be an empty polygon.

Examples

SELECT ST_EXPAND(point '(0 0)', 10);
  --> POLYGON((-67.089871 -67.089871,67.089871 -67.089871,67.089871 67.089871,-67.089871 67.089871,-67.089871 -67.089871))

SELECT ST_EXPAND(linestring '(0 0, 100 100)', -5, 5);
  --> POLYGON((33.544935 -33.544935,66.455065 -33.544935,66.455065 133.544935,33.544935 133.544935,33.544935 -33.544935))

See also

ST_BUFFER ST_ENVELOPE

fn ST_XMAX

ST_XMAX(g: anygeometry) -> float64

Returns the X maxima of a geometry.

Null handling

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

Notes

When the input geometry is empty, the function will return NULL.

Examples

SELECT ST_XMAX(linestring '(10 10, 20 30, 50 5)');
  --> 50.0

See also

ST_XMIN ST_YMAX ST_YMIN ST_X

fn ST_XMIN

ST_XMIN(g: anygeometry) -> float64

Returns the X minima of a geometry.

Null handling

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

Notes

When the input geometry is empty, the function will return NULL.

Examples

SELECT ST_XMIN(linestring '(10 10, 20 30, 50 5)');
  --> 10.0

See also

ST_XMAX ST_YMAX ST_YMIN ST_X

fn ST_YMAX

ST_YMAX(g: anygeometry) -> float64

Returns the Y maxima of a geometry.

Null handling

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

Notes

When the input geometry is empty, the function will return NULL.

Examples

SELECT ST_YMAX(linestring '(10 10, 20 30, 50 5)');
  --> 30.0

See also

ST_YMIN ST_XMAX ST_XMIN ST_Y

fn ST_YMIN

ST_YMIN(g: anygeometry) -> float64

Returns the Y minima of a geometry.

Null handling

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

Notes

When the input geometry is empty, the function will return NULL.

Examples

SELECT ST_YMIN(linestring '(10 10, 20 30, 50 5)');
  --> 5.0

See also

ST_YMAX ST_XMAX ST_XMIN ST_Y