MSQL Reference – Spatial Functions

The following are the built-in functions for testing spatial predicates. They are divided into two sections:

Spatial Relationships - Functions for testing various spatial relationships, as defined in the OpenGIS - Simple Features Common Architecture specification (plus some extras).
Distance Predicates - Functions for testing distance predicates, such as ST_DWITHIN, ST_DFULLYWITIN.

Spatial Relationships

fn ST_CONTAINS

ST_CONTAINS(g1: anygeometry, g2: anygeometry) -> bool

Returns true if geometry g1 contains the geometry g2.

Null handling

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

Notes

More precisely, the function returns true if and only if no points of g2 lie in the exterior of g1, and at least one point of the interior of g2 lies in the interior of g1.

According to this definition, a geometry g does not contain its boundary, but it does contain itself.

A (multi)linestring or a (multi)point can never contain a (multi)polygon, but a (multi)point can contain a (multi)linestring when the latter has zero length (e.g. ST_CONTAINS(POINT '(10 20)', LINESTRING '(10 20, 10 20)') = true).

ST_CONTAINS(g1, g2) is equivalent to ST_WITHIN(g2, g1).

Examples

SELECT ST_CONTAINS(polygon '((0 0, 20 0, 20 20, 0 20, 0 0))', linestring '(5 0, 15 0, 15 10)');
  --> true

SELECT ST_CONTAINS(polygon '((0 0, 20 0, 20 20, 0 20, 0 0), (5 5, 10 10, 15 5, 5 5))', multipoint '(2 2, 10 8)');
  --> false

fn ST_CONTAINSPROPERLY

ST_CONTAINSPROPERLY(g1: anygeometry, g2: anygeometry) -> bool

Returns true if g2 intersects the interior of g1 but not the boundary (or exterior).

Null handling

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

Notes

A geometry g does not contain properly itself, but does contain itself (see ST_CONTAINS).

A (multi)linestring or a (multi)point can never properly contain a (multi)polygon, but a (multi)point can properly contain a (multi)linestring when the latter has zero length (e.g. ST_CONTAINSPROPERLY(POINT '(10 20)', LINESTRING '(10 20, 10 20)') = true).

Examples

SELECT ST_CONTAINSPROPERLY(polygon '((0 0, 20 0, 20 20, 0 20, 0 0), (5 5, 15 5, 10 10, 5 5))', multipoint '(2 2, 4 8, 15 15)');
  --> true

SELECT ST_CONTAINSPROPERLY(polygon '((0 0, 20 0, 20 20, 0 20, 0 0))', linestring '(5 0, 15 0, 15 10)');
  --> false

fn ST_COVERS

ST_COVERS(g1: anygeometry, g2: anygeometry) -> bool

Returns true if no point in geometry g2 is outside geometry g1.

Null handling

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

Notes

If either geometry is empty, the predicate returns false.

A (multi)linestring or a (multi)point can never cover a (multi)polygon, but a (multi)point can cover a (multi)linestring when the latter has zero length (e.g. ST_COVERS(POINT '(10 20)', LINESTRING '(10 20, 10 20)') = true).

This function is very similar to ST_CONTAINS, with the difference that when g2 lays completely on the boundary of g1, ST_CONTAINS returns false and ST_COVERS returns true.

ST_COVERS(g1, g2) is equivalent to ST_COVEREDBY(g2, g1).

Examples

SELECT ST_COVERS(polygon '((0 0, 20 0, 20 20, 0 20, 0 0))', point '(10 0)');
  --> true

SELECT ST_COVERS(polygon '((0 0, 20 0, 20 20, 0 20, 0 0), (5 5, 15 5, 10 10, 5 5))', linestring '(5 0, 10 0, 10 20)');
  --> false

fn ST_COVEREDBY

ST_COVEREDBY(g1: anygeometry, g2: anygeometry) -> bool

Returns true if no point in geometry g1 is outside geometry g2.

Null handling

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

Notes

If either geometry is empty, the predicate returns false.

A (multi)polygon can never be covered by a (multi)linestring or a (multi)point, but a (multi)linestring can be covered by a (multi)point when the former has zero length (e.g. ST_COVEREDBY(LINESTRING '(10 20, 10 20)', POINT '(10 20)') = true).

ST_COVEREDBY(g1, g2) is equivalent to ST_COVERS(g2, g1).

Examples

SELECT ST_COVEREDBY(point '(10 0)', polygon '((0 0, 20 0, 20 20, 0 20, 0 0))');
  --> true

SELECT ST_COVEREDBY(linestring '(5 0, 10 0, 10 20)', polygon '((0 0, 20 0, 20 20, 0 20, 0 0), (5 5, 15 5, 10 10, 5 5))');
  --> false

fn ST_CROSSES

ST_CROSSES(g1: anygeometry, g2: anygeometry) -> bool

Returns true if the supplied geometries spatially cross, i.e. they have some, but not all, interior points in common.

Null handling

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

Notes

More precisely, for ST_Crossess to return true, the intersection of g1 and g2 must not be empty, must not be equal to either g1 or g2, and must have a dimensionality less than the maximum dimensionality of the two input geometries.

If the input geometries are both (multi)point or both (multi)polygon geometries, the function will always return false.
It will also always return false if either of the arguments is a point (as the point cannot cross anything).

Examples

SELECT ST_CROSSES(linestring '(0 0, 10 0, 20 10)', linestring '(0 10, 20 0)');
  --> true

SELECT ST_CROSSES(multipoint '(10 10, 20 20)', polygon '((0 0, 20 0, 20 20, 0 20, 0 0))');
  --> false

fn ST_DISJOINT

ST_DISJOINT(g1: anygeometry, g2: anygeometry) -> bool

Returns true if the given geometries do not spatially intersect, i.e. if they do not share any space together.

Null handling

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

Notes

If any of the ST_OVERLAPS, ST_TOUCHES, ST_WITHIN is true, ST_DISJOINT will neccessarily return false. The contrary also holds: if ST_DISJOINT is true, all of them will be false.

ST_DISJOINT(g1, g2) is equivalent to NOT ST_INTERSECTS(g1, g2).

Examples

SELECT ST_DISJOINT(polygon '((0 0, 20 0, 20 20, 0 20, 0 0))', point '(30 40)');
  --> true

SELECT ST_DISJOINT(point '(0 0)', linestring '(0 0, 10 10)');
  --> false

fn ST_EQUALS

ST_EQUALS(g1: anygeometry, g2: anygeometry) -> bool

Returns true if the given geometries represent the same geometry, i.e. they include the same point set.

Null handling

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

Notes

Ordering of points is ignored in the comparison.

ST_EQUALS(g1, g2) is equivalent to ST_WITHIN(g1, g2) AND ST_WITHIN(g2, g1), i.e. two geometries are equal if and only if they are both contained within each other.

A multi-geometry containing only one element is treated by ST_EQUALS as equal to that element, e.g. ST_EQUALS(MULTIPOINT '(10 20)', POINT '(10 20)') = true.

Examples

SELECT ST_EQUALS(linestring '(0 0, 5 5, 10 10)', linestring '(10 10, 0 0)');
  --> true

SELECT ST_EQUALS(linestring '(0 0, 20 0, 20 20, 0 20, 0 0)', linestring '(0 0, 20 20, 20 0, 0 20, 0 0)');
  --> false

See also

ST_CONTAINS ST_WITHIN ~= (EQUALS)

fn ST_INTERSECTS

ST_INTERSECTS(g1: anygeometry, g2: anygeometry) -> bool

Returns true if the given geometries spatially intersect, i.e. share any portion of space.

Null handling

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

Notes

If any of the ST_OVERLAPS, ST_TOUCHES, ST_WITHIN is true, ST_INTERSECTS will also be true. The contrary also holds: if ST_INTERSECTS is false, all of them will be false.

ST_INTERSECTS(g1, g2) is equivalent to NOT ST_DISJOINT(g1, g2).

Examples

SELECT ST_INTERSECTS(linestring '(0 0, 10 10)', point '(5 5)');
  --> true

SELECT ST_INTERSECTS(polygon '((0 0, 20 0, 20 20, 0 20, 0 0))', point '(30 40)');
  --> false

fn ST_OVERLAPS

ST_OVERLAPS(g1: anygeometry, g2: anygeometry) -> bool

Returns true if the given geometries share space, are of the same dimension, but are not completely contained by each other.

Null handling

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

Examples

SELECT ST_OVERLAPS(linestring '(0 10, 0 0, 10 0, 10 10)', linestring '(0 -10, 0 0, 10 0, 10 -10)');
  --> true

SELECT ST_OVERLAPS(polygon '((0 0, 20 0, 20 20, 0 20, 0 0))', linestring '(10 10, 30 30)');
  --> false

fn ST_TOUCHES

ST_TOUCHES(g1: anygeometry, g2: anygeometry) -> bool

Returns true if the given geometries have at least one point in common, but their interiors do not intersect.

Null handling

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

Notes

If both arguments are (multi)point geometries (i.e. have a coordinate dimension of 0), the function will always return false.

Examples

SELECT ST_TOUCHES(polygon '((0 0, 20 0, 20 20, 0 20, 0 0))', polygon '((20 5, 30 5, 30 15, 20 15, 20 5))');
  --> true

SELECT ST_TOUCHES(polygon '((0 0, 20 0, 20 20, 0 20, 0 0))', linestring '(10 10, 30 30)');
  --> false

fn ST_WITHIN

ST_WITHIN(g1: anygeometry, g2: anygeometry) -> bool

Returns true if the geometry g1 is completely inside geometry g2.

Null handling

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

Notes

A (multi)polygon can never be contained within a (multi)linestring or a (multi)point, but a (multi)linestring can be contained within a (multi)point when the former has zero length (e.g. ST_WITHIN(LINESTRING '(10 20, 10 20)', POINT '(10 20)') = true).

ST_WITHIN(g1, g2) is equivalent to ST_CONTAINS(g2, g1).

Examples

SELECT ST_WITHIN(linestring '(5 0, 15 0, 15 10)', polygon '((0 0, 20 0, 20 20, 0 20, 0 0))');
  --> true

SELECT ST_WITHIN(multipoint '(10 0, 0 10, 20 20)', polygon '((0 0, 20 0, 20 20, 0 20, 0 0), (5 5, 15 5, 10 10, 5 5))');
  --> false

fn ST_RELATE

ST_RELATE(g1: anygeometry, g2: anygeometry, pattern: text) -> bool

Determines whether the DE-9IM intersection matrix of the two geometries satisfies the given pattern.

Arguments

The pattern argument is a DE-9IM intersection matrix pattern. It is a 9-character string, and each character can be one of {0, 1, 2, T, F, *}.

Null handling

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

Error handling

If an invalid pattern argument is given, the function will return an error: Invalid DE-9IM intersection matrix pattern.

Notes

This function can be used for checking complex spatial relationships which are not covered by built-in predicates (i.e. those described above).
It is especially useful for testing compound checks of intersection, crosses, etc. in a single step.

Examples

SELECT ST_RELATE(point '(10 20)', polygon '((-10 0, 30 0, 30 40, -10 40, -10 0))', '0F*FFF212');
  --> true

SELECT ST_RELATE(polygon '((10 10, 40 10, 40 50, 10 50, 10 10))', polygon '((30 20, 60 20, 60 60, 30 60, 30 20))', '112101212');
  --> false

fn ST_RELATION

ST_RELATION(g1: anygeometry, g2: anygeometry) -> text

Calculates the DE-9IM intersection matrix for the given pair of geometries and returns it.

Null handling

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

Notes

The result of the function is always a 9-character string representing the DE-9IM intersection matrix of the input geometries. Each character of the matrix representation is one of {0, 1, 2, T, F, *}.

Examples

SELECT ST_RELATION(linestring '(10 20, 30 40)', linestring '(50 60, 70 80)');
  --> 'FF1FF0102'

See also

ST_RELATE <?> (RELATION)

Distance Predicates

fn ST_POINTINSIDECIRCLE

ST_POINTINSIDECIRCLE(pt: point, cx: float64, cy: float64, r: float64) -> bool

Returns true if a given point geometry is located inside a circle with center (cx, cy) and radius r, false otherwise.

Arguments

The units of radius r are meters. If the distance 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.

Null handling

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

Notes

ST_POINTINSIDECIRCLE(pt, cx, cy, r) is equivalent to ST_DISTANCE(pt, ST_POINT(cx, cy)) <= r.

Examples

SELECT ST_POINTINSIDECIRCLE(point '(300 400)', 0, 0, 75);
  --> true

SELECT ST_POINTINSIDECIRCLE(point '(400 400)', 0, 0, 75);
  --> false

See also

ST_DWITHIN ST_DISTANCE

fn ST_DWITHIN

ST_DWITHIN(g1: anygeometry, g2: anygeometry, dist: float64) -> bool

Returns true if the two geometries are within the specified distance of one another.

Arguments

The units of dist are meters. If the distance 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.

Null handling

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

Notes

ST_DWITHIN(g1, g2, dist) is equivalent to ST_DISTANCE(g1, g2) <= dist.

Examples

SELECT ST_DWITHIN(linestring '(0 0, 1000 1000)', point '(1000 0)', 125);
  --> true

SELECT ST_DWITHIN(linestring '(0 0, 1000 1000)', point '(1000 0)', 100);
  --> false

See also

ST_DISTANCE ST_DFULLYWITHIN

fn ST_DFULLYWITHIN

ST_DFULLYWITHIN(g1: anygeometry, g2: anygeometry, dist: float64) -> bool

Returns true if the two geometries are fully within the specified distance of one another.

Arguments

Null handling

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

Notes

ST_DFULLYWITHIN(g1, g2, dist) is equivalent to ST_MAXDISTANCE(g1, g2) <= dist.

Examples

SELECT ST_DFULLYWITHIN(linestring '(0 0, 1000 1000)', point '(1000 0)', 200);
  --> true

SELECT ST_DFULLYWITHIN(linestring '(0 0, 1000 1000)', point '(1000 0)', 100);
  --> false

See also

ST_MAXDISTANCE ST_DWITHIN