All pages
Powered by GitBook
Couldn't generate the PDF for 180 pages, generation stopped at 100.
Extend with 50 more pages.
1 of 100

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Geometry Constructors

Learn about geometry constructors in MariaDB Server. This section details SQL functions for creating spatial data types like points, lines, and polygons, enabling geospatial data management.

Geometry Statements

Learn about geometry constructors in MariaDB Server. This section details SQL functions for creating spatial data types like points, lines, and polygons, enabling geospatial data management.

Geometry Properties

Learn about geometry properties. This section details SQL functions for retrieving attributes of spatial objects, such as area, length, and bounding box, essential for geospatial analysis.

Geometry Relations

Learn about geometry relations in MariaDB Server. This section details SQL functions for determining spatial relationships between geometric objects, such as ST_Intersects and ST_Contains.

MBR (Minimum Bounding Rectangle)

Learn about Minimum Bounding Rectangles (MBR) in MariaDB Server. This section details how to calculate and use MBRs for spatial indexing and efficient querying of geometric data.

ST_INTERSECTION

Returns a geometry representing the point set intersection of two geometries. It outputs the spatial region shared by both input objects.

Syntax

ST_INTERSECTION(g1,g2)

Description

Returns a geometry that is the intersection, or shared portion, of geometry g1 and geometry g2.

Examples

This page is licensed: CC BY-SA / Gnu FDL

ST_POINTONSURFACE

Returns a Point guaranteed to lie on the surface of the given geometry. This standard function finds a representative point strictly within the geometry's area.

Syntax

ST_PointOnSurface(g)
PointOnSurface(g)

Description

Given a geometry, returns a POINT guaranteed to intersect a surface. However, see MDEV-7514.

ST_PointOnSurface() and PointOnSurface() are synonyms.

This page is licensed: CC BY-SA / Gnu FDL

ST_AsGeoJSON

Generates a GeoJSON object from a given geometry. This function converts internal geometry data into the standard JSON-based format for web mapping.

Syntax

ST_AsGeoJSON(g[, max_decimals[, options]])

Description

Returns the given geometry g as a GeoJSON element. The optional max_decimals limits the maximum number of decimals displayed.

The optional options flag can be set to 1 to add a bounding box to the output.

Examples

See Also

This page is licensed: CC BY-SA / Gnu FDL

ENVELOPE

Synonym for ST_ENVELOPE. Returns the Minimum Bounding Rectangle (MBR) for the given geometry as a Polygon.

A synonym for ST_ENVELOPE.

This page is licensed: CC BY-SA / Gnu FDL

GeometryN

Synonym for ST_GEOMETRYN. Returns the N-th geometry from a GeometryCollection, numbered starting from 1.

A synonym for ST_GeometryN.

This page is licensed: CC BY-SA / Gnu FDL

GeometryType

Synonym for ST_GEOMETRYTYPE. Returns the name of the geometry type (e.g., 'POINT') of the geometry instance.

A synonym for ST_GeometryType.

This page is licensed: CC BY-SA / Gnu FDL

ST_IsSimple

Checks if the geometry is simple (no self-intersections or anomalous points). Returns 1 if simple, 0 otherwise.

Syntax

ST_IsSimple(g)
IsSimple(g)

Description

Returns true if the given Geometry has no anomalous geometric points, false if it does, or NULL if given a NULL value.

ST_IsSimple() and IsSimple() are synonyms.

Examples

A POINT is always simple.

This page is licensed: CC BY-SA / Gnu FDL

BUFFER

Returns a geometry representing all points within a given distance of the geometry value. This function calculates a buffer zone around the spatial object.

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

GEOMETRYCOLLECTION

Constructs a GeometryCollection value from a list of WKB arguments. This function creates a collection containing multiple geometry objects.

Syntax

Description

Constructs a GeometryCollection. If any argument is not a well-formed WKB representation of a geometry, the return value is NULL

MULTILINESTRING

Constructs a MultiLineString value from a list of WKB LineString arguments. This function creates a collection of multiple line strings.

Syntax

Description

Constructs a WKB MultiLineString value using arguments. If any argument is not a WKB LineString, the return value is

MULTIPOINT

Constructs a MultiPoint value from a list of WKB Point arguments. It creates a geometry collection consisting of multiple individual points.

Syntax

Description

Constructs a MultiPoint value using WKB arguments. If any argument is not a WKB Point, the return value is

CONVEXHULL

Returns a geometry representing the convex hull of the given geometry value. It computes the smallest convex polygon that contains the entire geometry.

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

NumGeometries

Synonym for ST_NUMGEOMETRIES. Returns the number of geometries contained in a GeometryCollection.

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

IsClosed

Synonym for ST_ISCLOSED. Checks if a LineString's start and end points are the same (closed).

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

ST_RELATE

Tests if two geometries are spatially related according to a given DE-9IM intersection matrix pattern.

Syntax

Description

Returns true if Geometry g1 is spatially related to Geometry

ST_DISTANCE

Calculates the shortest distance between two geometries. Returns the minimum Euclidean distance between any two points in the geometries.

Syntax

Description

Returns the distance between two geometries, or null if not given valid inputs.

ST_ISCLOSED

Returns 1 if the LineString is closed (start and end points are the same), 0 if not, and NULL if the argument is NULL.

Syntax

Description

Returns 1 if a given start and end points are the same, or 0 if they are not the same.

IsSimple

Synonym for ST_ISSIMPLE. Checks if a geometry is simple, meaning it has no anomalous geometric points like self-intersections.

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

ST_SRID

Returns the Spatial Reference Identifier (SRID) for the geometry. This integer represents the coordinate system used.

Syntax

Description

Returns an integer indicating the Spatial Reference System ID for the geometry value g.

ST_DIFFERENCE

Returns a geometry representing the difference between two geometries. The result contains points from the first geometry that are not in the second.

Syntax

Description

Returns a geometry representing the point set difference of the given geometry values.

ST_GEOMETRYTYPE

Returns the name of the geometry type (e.g., 'POINT', 'LINESTRING', 'POLYGON') for the given geometry instance.

Syntax

Description

Returns as a string the name of the geometry type of which the geometry instance g

SRID

Synonym for ST_SRID. Returns the Spatial Reference Identifier (SRID) integer associated with the geometry.

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

PointOnSurface

Returns a Point which is guaranteed to lie on the surface of the given geometry. This is useful for labeling or finding a representative point inside a polygon.

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

ENDPOINT

Synonym for ST_ENDPOINT. Returns the last point of a LineString geometry.

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

IsEmpty

Synonym for ST_ISEMPTY. Checks if a geometry is empty (contains no point sets). Returns 1 if empty, 0 otherwise.

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

ST_NUMGEOMETRIES

Returns the number of geometries in a GeometryCollection. Returns NULL if the argument is not a GeometryCollection.

Syntax

Description

Returns the number of geometries in the GeometryCollection gc

OVERLAPS

Synonym for ST_OVERLAPS. Checks if two geometries overlap, sharing some but not all points, and having the same dimension.

Syntax

Description

Returns 1 or

LineString Properties

Learn about LINESTRING properties in MariaDB Server. This section details SQL functions for retrieving attributes of linear spatial objects, such as length, number of points, and start/end points.

Miscellaneous GIS functions

Explore miscellaneous GIS functions in MariaDB Server. This section details various SQL functions that support geographic information system operations and spatial data analysis.

Point Properties

Learn about POINT properties in MariaDB Server. This section details SQL functions for retrieving attributes of point spatial objects, such as their X and Y coordinates.

Polygon Properties

Learn about POLYGON properties in MariaDB Server. This section details SQL functions for retrieving attributes of polygonal spatial objects, such as area, perimeter, and the number of rings.

.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

GeometryCollection(g1,g2,...)
WKB
CREATE TABLE gis_geometrycollection  (g GEOMETRYCOLLECTION);
SHOW FIELDS FROM gis_geometrycollection;
INSERT INTO gis_geometrycollection VALUES
    (GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
    (GeometryFromWKB(AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))))),
    (GeomFromText('GeometryCollection()')),
    (GeomFromText('GeometryCollection EMPTY'));
NULL
.

Example

MultiLineString_Example:

This page is licensed: GPLv2, originally from fill_help_tables.sql

MultiLineString(ls1,ls2,...)
WKB
LineString
CREATE TABLE gis_multi_line (g MULTILINESTRING);
INSERT INTO gis_multi_line VALUES
 (MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
 (MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
 (MLineFromWKB(AsWKB(MultiLineString(LineString(Point(1, 2), 
    Point(3, 5)), LineString(Point(2, 5),Point(5, 8),Point(21, 7))))));
NULL
.

Examples

MultiPoint_Example:

This page is licensed: GPLv2, originally from fill_help_tables.sql

MultiPoint(pt1,pt2,...)
WKB
Point
SET @g = ST_GEOMFROMTEXT('MultiPoint( 1 1, 2 2, 5 3, 7 2, 9 3, 8 4, 6 6, 6 9, 4 9, 1 5 )');

CREATE TABLE gis_multi_point (g MULTIPOINT);
INSERT INTO gis_multi_point VALUES
    (MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
    (MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
    (MPointFromWKB(AsWKB(MultiPoint(Point(3, 6), Point(4, 10)))));
g2
by testing for intersections between the interior, boundary and exterior of the two geometries as specified by the values in intersection matrix pattern
i
.

This page is licensed: CC BY-SA / Gnu FDL

ST_Relate(g1, g2, i)
Example

This page is licensed: CC BY-SA / Gnu FDL

ST_DISTANCE(g1,g2)
SELECT ST_Distance(POINT(1,2),POINT(2,2));
+------------------------------------+
| ST_Distance(POINT(1,2),POINT(2,2)) |
+------------------------------------+
|                                  1 |
+------------------------------------+
ST_IsClosed() and IsClosed() are synonyms.

Examples

This page is licensed: CC BY-SA / Gnu FDL

ST_IsClosed(g)
IsClosed(g)
LINESTRING's
SET @ls = 'LineString(0 0, 0 4, 4 4, 0 0)';
SELECT ST_ISCLOSED(GEOMFROMTEXT(@ls));
+--------------------------------+
| ST_ISCLOSED(GEOMFROMTEXT(@ls)) |
+--------------------------------+
|                              1 |
+--------------------------------+

SET @ls = 'LineString(0 0, 0 4, 4 4, 0 1)';
SELECT ST_ISCLOSED(GEOMFROMTEXT(@ls));
+--------------------------------+
| ST_ISCLOSED(GEOMFROMTEXT(@ls)) |
+--------------------------------+
|                              0 |
+--------------------------------+
In MariaDB, the SRID value is just an integer associated with the geometry value. All calculations are done assuming Euclidean (planar) geometry.

ST_SRID() and SRID() are synonyms.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

ST_SRID(g)
SRID(g)
SELECT SRID(GeomFromText('LineString(1 1,2 2)',101));
+-----------------------------------------------+
| SRID(GeomFromText('LineString(1 1,2 2)',101)) |
+-----------------------------------------------+
|                                           101 |
+-----------------------------------------------+
Example

This page is licensed: CC BY-SA / Gnu FDL

ST_DIFFERENCE(g1,g2)
SET @g1 = POINT(10,10), @g2 = POINT(20,20);

SELECT ST_AsText(ST_Difference(@g1, @g2));
+------------------------------------+
| ST_AsText(ST_Difference(@g1, @g2)) |
+------------------------------------+
| POINT(10 10)                       |
+------------------------------------+
is a member. The name corresponds to one of the instantiable Geometry subclasses.

ST_GeometryType() and GeometryType() are synonyms.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

ST_GeometryType(g)
GeometryType(g)
SELECT GeometryType(GeomFromText('POINT(1 1)'));
+------------------------------------------+
| GeometryType(GeomFromText('POINT(1 1)')) |
+------------------------------------------+
| POINT                                    |
+------------------------------------------+
.

ST_NumGeometries() and NumGeometries() are synonyms.

Example

This page is licensed: CC BY-SA / Gnu FDL

ST_NumGeometries(gc)
NumGeometries(gc)
SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))';

SELECT NUMGEOMETRIES(GeomFromText(@gc));
+----------------------------------+
| NUMGEOMETRIES(GeomFromText(@gc)) |
+----------------------------------+
|                                2 |
+----------------------------------+
0
to indicate whether
g1
spatially overlaps
g2
. The term spatially overlaps is used if two geometries of equal dimensions intersect and their intersection results in a geometry of the same dimension but not equal to either of the given geometries.

OVERLAPS() is based on the original MySQL implementation and uses object bounding rectangles, while ST_OVERLAPS() uses object shapes.

This page is licensed: GPLv2, originally from fill_help_tables.sql

OVERLAPS(g1,g2)
SET @g1 = ST_GEOMFROMTEXT('POINT(2 1)');

SET @g2 = ST_GEOMFROMTEXT('LINESTRING(2 1, 0 2)');

SELECT ASTEXT(ST_INTERSECTION(@g1,@g2));
+----------------------------------+
| ASTEXT(ST_INTERSECTION(@g1,@g2)) |
+----------------------------------+
| POINT(2 1)                       |
+----------------------------------+
SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(5.3 7.2)'));
+-------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(5.3 7.2)')) |
+-------------------------------------------------+
| {"type": "Point", "coordinates": [5.3, 7.2]}    |
+-------------------------------------------------+
ST_GeomFromGeoJSON
SET @g = 'Point(1 2)';

SELECT ST_ISSIMPLE(GEOMFROMTEXT(@g));
+-------------------------------+
| ST_ISSIMPLE(GEOMFROMTEXT(@g)) |
+-------------------------------+
|                             1 |
+-------------------------------+
ST_BUFFER
ST_CONVEXHULL
ST_NumGeometries
ST_IsClosed
ST_IsSImple
ST_SRID
ST_PointOnSurface
ST_ENDPOINT
ST_IsEmpty

POLYGON

Constructs a Polygon value from a list of WKB LineString arguments. It defines a filled shape using an outer ring and optional inner rings (holes).

Syntax

Polygon(ls1,ls2,...)

Description

Constructs a WKB Polygon value from a number of WKB LineString arguments. If any argument does not represent the WKB of a LinearRing (that is, not a closed and simple LineString) the return value is NULL.

Note that according to the OpenGIS standard, a POLYGON should have exactly one ExteriorRing and all other rings should lie within that ExteriorRing and thus be the InteriorRings. Practically, however, some systems, including MariaDB's, permit polygons to have several 'ExteriorRings'. In the case of there being multiple, non-overlapping exterior rings will return 1.

Examples

Non-overlapping 'polygon':

Polygon_Example:

This page is licensed: GPLv2, originally from

ST_GeomFromGeoJSON

Parses a GeoJSON string and returns a geometry. This function converts a standard GeoJSON representation into a MariaDB geometry value.

Syntax

ST_GeomFromGeoJSON(g[, option])

Description

Given a GeoJSON input g, returns a geometry object. The option specifies what to do if g contains geometries with coordinate dimensions higher than 2.

Option
Description

Examples

See Also

This page is licensed: CC BY-SA / Gnu FDL

ST_UNION

Returns a geometry representing the point set union of two geometries. This function merges multiple spatial objects into a single geometry.

Syntax

ST_UNION(g1,g2)

Description

Returns a geometry that is the union of the geometry g1 and geometry g2.

Examples

This page is licensed: CC BY-SA / Gnu FDL

LINESTRING

Constructs a LineString value from a number of Point values. It creates a linear geometry connecting the specified coordinate points.

Syntax

LineString(pt1,pt2,...)

Description

WKB LineString from WKB Point coordinate data. Constructs a WKB LineString value from a number of WKB Point arguments. If any argument is not a WKB Point, the return value isNULL. If the number of arguments is less than two, the return value is NULL.

Examples

Linestring_Example:

This page is licensed: GPLv2, originally from

DISJOINT

Synonym for ST_DISJOINT. Checks if two geometries are disjoint, meaning they have no points in common.

Syntax

Disjoint(g1,g2)

Description

Returns 1 or 0 to indicate whether g1 is spatially disjoint from (does not intersect) g2.

DISJOINT() tests the opposite relationship to .

DISJOINT() is based on the original MySQL implementation and uses object bounding rectangles, while uses object shapes.

This page is licensed: GPLv2, originally from

BOUNDARY

Synonym for ST_BOUNDARY. Returns a geometry representing the closure of the combinatorial boundary of the geometry value.

A synonym for ST_BOUNDARY.

This page is licensed: CC BY-SA / Gnu FDL

ST_SYMDIFFERENCE

Returns a geometry representing the point set symmetric difference of two geometries. It outputs the spatial area present in either geometry but not in both.

Syntax

ST_SYMDIFFERENCE(g1,g2)

Description

Returns a geometry that represents the portions of geometry g1 and geometry g2 that don't intersect.

Examples

This page is licensed: CC BY-SA / Gnu FDL

WITHIN

Synonym for ST_WITHIN. Checks if the first geometry is completely enclosed by the second geometry.

Syntax

Within(g1,g2)

Description

Returns 1 or 0 to indicate whether g1 is spatially within g2. This tests the opposite relationship as .

WITHIN() is based on the original MySQL implementation, and uses object bounding rectangles, while uses object shapes.

Examples

This page is licensed: GPLv2, originally from

GLENGTH

Synonym for ST_LENGTH. Calculates the length of a LineString or MultiLineString in its associated spatial reference units.

Syntax

GLength(ls)

Description

Returns as a double-precision number the length of the LineString value ls in its associated spatial reference.

Examples

See Also

  • is the OpenGIS equivalent.

This page is licensed: GPLv2, originally from

ST_TOUCHES

Checks if two geometries touch. Returns 1 if they intersect only at their boundaries and not their interiors.

Syntax

ST_TOUCHES(g1,g2)

Description

Returns 1 or 0 to indicate whether geometry g1 spatially touches geometry g2. Two geometries spatially touch if the interiors of the geometries do not intersect, but the boundary of one of the geometries intersects either the boundary or the interior of the other.

ST_TOUCHES() uses object shapes, while , based on the original MySQL implementation, uses object bounding rectangles.

Examples

This page is licensed: CC BY-SA / Gnu FDL

ST_OVERLAPS

Checks if two geometries overlap. Returns 1 if they share space but neither completely contains the other, and they have the same dimension.

Syntax

ST_OVERLAPS(g1,g2)

Description

Returns 1 or 0 to indicate whether geometry g1 spatially overlaps geometry g2.

The term spatially overlaps is used if two geometries of equal dimensions intersect and their intersection results in a geometry of the same dimension but not equal to either of the given geometries.

ST_OVERLAPS() uses object shapes, while , based on the original MySQL implementation, uses object bounding rectangles.

This page is licensed: CC BY-SA / Gnu FDL

ST_CONVEXHULL

Returns a geometry representing the convex hull of the given geometry. This standard function computes the smallest convex polygon enclosing the geometry.

Syntax

ST_ConvexHull(g)
ConvexHull(g)

Description

Given a geometry, returns a geometry that is the minimum convex geometry enclosing all geometries within the set. Returns NULL if the geometry value is NULL or an empty value.

ST_ConvexHull() and ConvexHull() are synonyms.

Examples

The ConvexHull of a single point is simply the single point:

This page is licensed: CC BY-SA / Gnu FDL

ST_DISJOINT

Checks if two geometries are disjoint. Returns 1 if the geometries share no points, 0 otherwise.

Syntax

ST_DISJOINT(g1,g2)

Description

Returns 1 or 0 to indicate whether geometry g1 is spatially disjoint from (does not intersect with) geometry g2.

ST_DISJOINT() uses object shapes, while , based on the original MySQL implementation, uses object bounding rectangles.

ST_DISJOINT() tests the opposite relationship to .

Examples

This page is licensed: CC BY-SA / Gnu FDL

ST_INTERSECTS

Checks if two geometries intersect. Returns 1 if the geometries share any portion of space (interior or boundary), 0 otherwise.

Syntax

ST_INTERSECTS(g1,g2)

Description

Returns 1 or 0 to indicate whether geometry g1 spatially intersects geometry g2.

ST_INTERSECTS() uses object shapes, while , based on the original MySQL implementation, uses object bounding rectangles.

ST_INTERSECTS() tests the opposite relationship to .

Examples

This page is licensed: CC BY-SA / Gnu FDL

ST_ENVELOPE

Returns the Minimum Bounding Rectangle (MBR) for the geometry value. The result is returned as a Polygon defined by the corner points.

Syntax

ST_ENVELOPE(g)
ENVELOPE(g)

Description

Returns the Minimum Bounding Rectangle (MBR) for the geometry value g. The result is returned as a Polygon value.

The polygon is defined by the corner points of the bounding box:

ST_ENVELOPE() and ENVELOPE() are synonyms.

Examples

This page is licensed: GPLv2, originally from

ST_WITHIN

Checks if geometry A is within geometry B. Returns 1 if A is completely inside B, 0 otherwise. This is the inverse of ST_CONTAINS.

Syntax

ST_WITHIN(g1,g2)

Description

Returns 1 or 0 to indicate whether geometry g1 is spatially within geometry g2.

This tests the opposite relationship as .

ST_WITHIN() uses object shapes, while , based on the original MySQL implementation, uses object bounding rectangles.

Examples

This page is licensed: CC BY-SA / Gnu FDL

ST_NUMPOINTS

Returns the count of Points in a LineString. This function calculates the total number of vertices defining the line.

Syntax

ST_NumPoints(ls)
NumPoints(ls)

Description

Returns the number of Point objects in the LineString value ls.

ST_NumPoints() and NumPoints() are synonyms.

Examples

This page is licensed: GPLv2, originally from

MBR Definition

Understand Minimum Bounding Rectangles. An MBR is the smallest rectangle that completely encloses a geometry, defined by its minimum and maximum X and Y coordinates.

Description

The MBR (Minimum Bounding Rectangle), or Envelope is the bounding geometry, formed by the minimum and maximum (X,Y) coordinates:

Examples

((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))

This page is licensed: GPLv2, originally from

EQUALS

Synonym for ST_EQUALS. Checks if two geometries are spatially equal, meaning they contain exactly the same point set.

Syntax

MBREQUALS(g1,g2)

Description

Returns 1 or 0 to indicate whether g1 is spatially equal to g2.

EQUALS() is based on the original MySQL implementation and uses object bounding rectangles, while uses object shapes.

MBREQUALS is a synonym for Equals.

This page is licensed: GPLv2, originally from

STARTPOINT

Synonym for ST_STARTPOINT. Returns the first point of a LineString geometry.

A synonym for ST_STARTPOINT.

This page is licensed: CC BY-SA / Gnu FDL

DIMENSION

Synonym for ST_DIMENSION. Returns the inherent dimension of a geometry object (0 for Point, 1 for LineString, 2 for Polygon).

A synonym for ST_DIMENSION.

This page is licensed: CC BY-SA / Gnu FDL

PointN

Synonym for ST_POINTN. Returns the N-th point in a LineString geometry, where N is a 1-based index.

A synonym for ST_PointN.

This page is licensed: CC BY-SA / Gnu FDL

ST_IsRing

Checks if the LineString is a ring. Returns 1 if the LineString is both closed (start equals end) and simple (no self-intersection).

Syntax

ST_IsRing(g)
IsRing(g)

Description

Returns true if a given LINESTRING is a ring, that is, both ST_IsClosed and ST_IsSimple. A simple curve does not pass through the same point more than once. However, see .

St_IsRing() and IsRing() are synonyms.

This page is licensed: CC BY-SA / Gnu FDL

CONTAINS

Synonym for ST_CONTAINS. Checks if the first geometry completely contains the second geometry, with no points of the second geometry outside the first.

Syntax

Contains(g1,g2)

Description

Returns 1 or 0 to indicate whether a geometry g1 completely contains geometry g2. CONTAINS() is based on the original MySQL implementation and uses object bounding rectangles, while uses object shapes.

This tests the opposite relationship to .

This page is licensed: GPLv2, originally from

TOUCHES

Synonym for ST_TOUCHES. Checks if two geometries touch at their boundaries without their interiors intersecting.

Syntax

Touches(g1,g2)

Description

Returns 1 or 0 to indicate whether g1 spatially touches g2. Two geometries spatially touch if the interiors of the geometries do not intersect, but the boundary of one of the geometries intersects either the boundary or the interior of the other.

TOUCHES() is based on the original MySQL implementation and uses object bounding rectangles, while uses object shapes.

This page is licensed: GPLv2, originally from

ST_ISEMPTY

Checks if a geometry is empty. Returns 1 if the geometry contains no points, and 0 otherwise.

Syntax

ST_IsEmpty(g)
IsEmpty(g)

Description

IsEmpty is a function defined by the OpenGIS specification, but is not fully implemented by MariaDB or MySQL.

Since MariaDB and MySQL do not support GIS EMPTY values such as POINT EMPTY, as implemented it simply returns 1 if the geometry value g is invalid, 0 if it is valid, and NULL if the argument is NULL.

ST_IsEmpty() and IsEmpty() are synonyms.

This page is licensed: GPLv2, originally from

Y

Synonym for ST_Y. Returns the Y-coordinate value of a Point geometry as a double-precision number.

A synonym for ST_Y.

This page is licensed: CC BY-SA / Gnu FDL

ST_X

Returns the X-coordinate of a Point geometry. This function extracts the horizontal coordinate value as a double-precision number.

Syntax

ST_X(p)
X(p)

Description

Returns the X-coordinate value for the point p as a double-precision number.

ST_X() and X() are synonyms.

Examples

This page is licensed: GPLv2, originally from

IsRing

Synonym for ST_ISRING. Checks if a LineString is a ring, meaning it is both closed and simple.

A synonym for ST_IsRing.

This page is licensed: CC BY-SA / Gnu FDL

X

Synonym for ST_X. Returns the X-coordinate value of a Point geometry as a double-precision number.

A synonym for ST_X.

This page is licensed: CC BY-SA / Gnu FDL

ST_Y

Returns the Y-coordinate of a Point geometry. This function extracts the vertical coordinate value as a double-precision number.

Syntax

ST_Y(p)
Y(p)

Description

Returns the Y-coordinate value for the point p as a double-precision number.

ST_Y() and Y() are synonyms.

Examples

This page is licensed: GPLv2, originally from

POINT

Constructs a Point value using its coordinates. This basic constructor creates a point geometry from given X and Y (and optionally Z/M) values.

Syntax

Description

Constructs a Point using the given coordinates.

MULTIPOLYGON

Constructs a MultiPolygon value from a list of WKB Polygon arguments. This function creates a geometry collection containing multiple polygons.

Syntax

Description

Constructs a MultiPolygon value from a set of WKB arguments. If any argument is not a WKB Polygon, the return value is

ST_DISTANCE_SPHERE

Calculates the spherical distance between two points or geometries. Uses a spherical earth model to determine the minimum distance in meters.

Syntax

Description

Returns the spherical distance in meters between two geometries (point or multipoint) on a sphere. The optional radius r is in meters, must be positive, and defaults to the Earth's radius (6370986 meters) if not specified. If either of the two geometries are not valid,

ST_GEOMETRYN

Returns the N-th geometry in a GeometryCollection. Geometries are numbered beginning with 1.

Syntax

Description

Returns the N-th geometry in the GeometryCollection

ST_CROSSES

Checks if two geometries cross. Returns 1 if they intersect but one does not cover the other, and the intersection dimension is less than the maximum dimension.

Syntax

Description

Returns 1

ST_DIMENSION

Returns the inherent dimension of the geometry value. Returns 0 for points, 1 for linear geometries, and 2 for surface geometries.

Syntax

Description

Returns the inherent dimension of the geometry value

ST_BUFFER

Returns a geometry representing all points within a given distance of the geometry. This standard-compliant function creates a buffer zone around the object.

Syntax

Description

Returns a geometry that represents all points whose distance from geometry

MBROverlaps

Check if two MBRs overlap. Returns 1 if the MBRs intersect but neither completely contains the other, and they have the same dimension.

Syntax

Description

Returns 1 or 0 to indicate whether the of the two geometries g1

ST_STARTPOINT

Returns the start Point of a LineString. This function retrieves the initial coordinate in the linear geometry sequence.

Syntax

Description

Returns the that is the start point of the value ls

MBRIntersects

Check if two MBRs intersect. Returns 1 if the Minimum Bounding Rectangles of the geometries share any portion of space, including boundaries.

Syntax

Description

Returns 1 or 0 to indicate whether the of the two geometries g1 and g2 intersect.

INTERSECTS

Synonym for ST_INTERSECTS. Checks if two geometries intersect, meaning they share at least one common point.

Syntax

Description

Returns 1 or

ST_POINTN

Returns the N-th Point in a LineString. This function retrieves a specific point from the sequence based on its 1-based index.

Syntax

Description

Returns the N-th in the value ls

NumPoints

Synonym for ST_NUMPOINTS. Returns the number of points in a LineString geometry.

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

ST_BOUNDARY

Returns the combinatorial boundary of a geometry. For a Polygon, this is the line string defining the boundary.

Syntax

Description

Returns a geometry that is the closure of the combinatorial boundary of the geometry value

CROSSES

Synonym for ST_CROSSES. Checks if two geometries cross, meaning they share some interior points but not all, and the intersection has a lower dimension.

Syntax

Description

Returns 1

ST_ENDPOINT

Returns the end Point of a LineString. This function retrieves the final coordinate in the linear geometry sequence.

Syntax

Description

Returns the that is the endpoint of the value ls

MBRCoveredBy

Check if one MBR is covered by another. Returns 1 if the MBR of the first geometry is entirely contained within the MBR of the second geometry.

MBRCoveredBy is available from .

Syntax

MBRTouches

Check if two MBRs touch. Returns 1 if the MBRs intersect only at their boundaries and do not share any interior points.

Syntax

Description

Returns 1 or 0 to indicate whether the of the two geometries g1

ST_IsValid

Check if a geometry is valid. This function returns 1 if the geometry complies with OGC specifications (e.g., no self-intersections), 0 otherwise.

ST_IsValid is available from .

Syntax

ST_Validate

Validate and optionally return a geometry. This function checks if a geometry is valid according to OGC rules; it returns the geometry if valid, or NULL if not.

ST_Validate is available from .

Syntax

ST_PointFromGeoHash

Create a Point geometry from a Geohash. This function decodes a Geohash string into a Point object representing the location's center.

ST_PointFromGeoHash is available from .

Syntax

ST_Simplify

Simplify a geometry using the Douglas-Peucker algorithm. This function reduces the number of vertices in a geometry while preserving its general shape, useful for rendering maps.

ST_Simplify is available from .

Syntax

MBREqual

Check if two MBRs are identical. Returns 1 if the Minimum Bounding Rectangles of both geometries share the exact same coordinates.

Syntax

Description

Returns 1 or 0 to indicate whether the of the two geometries g1 and g2 are the same.

ST_GeoHash

Generate a Geohash string from a point or coordinates. This function encodes spatial locations into short, alphanumeric strings for efficient indexing and proximity searches.

ST_GeoHash is available from .

Syntax

CREATE TABLE geomcoll_example (
  g GEOMETRYCOLLECTION
);
INSERT INTO geomcoll_example VALUES
  (ST_GeomCollFromText(
    'GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 1, 0 2),
    POINT(1 0))')),
  (GeometryCollection(
    Point(0, 0),
    LineString(Point(0, 1), Point(0, 2))));
SELECT ST_AsWKT(g) FROM geomcoll_example;

+---------------------------------------------------------------+
| ST_AsWKT(g)                                                   |
+---------------------------------------------------------------+
| GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 1,0 2),POINT(1 0)) |
| GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 1,0 2))            |
+---------------------------------------------------------------+
CREATE TABLE mlstr_example (
  m MULTILINESTRING
);
INSERT INTO mlstr_example VALUES
  (ST_MultiLineStringFromText(
    'MULTILINESTRING((0 40, 0 20, 6 30, 12 20, 12 40),
    (15 40, 15 20, 25 20, 30 25, 30 35, 25 40, 15 40))')),
  (ST_MLineFromText('MULTILINESTRING((0 0, 1 1, 2 2))')),
  (MultiLineString(
    LineString(Point(0, 40), Point(0, 20)),
    LineString(Point(6, 30), Point(12, 20), Point(12, 40))));
INSERT INTO mlstr_example VALUES
  (MultiLineStringFromText(
    'MULTILINESTRING((0 40, 0 20, 6 30, 12 20, 12 40),
    (15 40, 15 20, 25 20, 30 25, 30 35, 25 40, 15 40))')),
  (MLineFromText('MULTILINESTRING((0 0, 1 1, 2 2))')),
  (MultiLineString(
    LineString(Point(0, 40), Point(0, 20)),
    LineString(Point(6, 30), Point(12, 20), Point(12, 40))));
SELECT ST_AsWKT(m) FROM mlstr_example;
+-------------------------------------------------------------------------------------------+
| ST_AsWKT(m)                                                                               |
+-------------------------------------------------------------------------------------------+
| MULTILINESTRING((0 40,0 20,6 30,12 20,12 40),(15 40,15 20,25 20,30 25,30 35,25 40,15 40)) |
| MULTILINESTRING((0 0,1 1,2 2))                                                            |
| MULTILINESTRING((0 40,0 20),(6 30,12 20,12 40))                                           |
+-------------------------------------------------------------------------------------------+
CREATE TABLE multipoint_example (
  m MULTIPOINT
);
INSERT INTO multipoint_example VALUES
  (ST_MultiPointFromText('MULTIPOINT(0 0, 1 0, 1 1, 0 1)')),
  (ST_MPointFromText('MULTIPOINT(1 1, 2 2, 3 3, 4 4)')),
  (MultiPoint(Point(0, 0), Point(1, 1)));
INSERT INTO multipoint_example VALUES
  (MultiPointFromText('MULTIPOINT(0 0, 1 0, 1 1, 0 1)')),
  (MPointFromText('MULTIPOINT(1 1, 2 2, 3 3, 4 4)')),
  (MultiPoint(Point(0, 0), Point(1, 1)));
SELECT ST_AsWKT(m) FROM multipoint_example;
+-----------------------------+
| ST_AsWKT(m)                 |
+-----------------------------+
| MULTIPOINT(0 0,1 0,1 1,0 1) |
| MULTIPOINT(1 1,2 2,3 3,4 4) |
| MULTIPOINT(0 0,1 1)         |
+-----------------------------+
Examples

Point_Example:

This page is licensed: GPLv2, originally from fill_help_tables.sql

Point(x,y)
WKB
SET @g = ST_GEOMFROMTEXT('Point(1 1)');

CREATE TABLE gis_point  (g POINT);
INSERT INTO gis_point VALUES
    (PointFromText('POINT(10 10)')),
    (PointFromText('POINT(20 10)')),
    (PointFromText('POINT(20 20)')),
    (PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
NULL
.

Example

MultiPolygon_Example:

This page is licensed: GPLv2, originally from fill_help_tables.sql

MultiPolygon(poly1,poly2,...)
WKB
Polygon
CREATE TABLE multipolygon_example (
  m MULTIPOLYGON
);
NULL
is returned.

Example

This page is licensed: CC BY-SA / Gnu FDL

ST_DISTANCE_SPHERE(g1,g2,[r])
SET @zenica   = ST_GeomFromText('POINT(17.907743 44.203438)');
SET @sarajevo = ST_GeomFromText('POINT(18.413076 43.856258)');
SELECT ST_Distance_Sphere(@zenica, @sarajevo);
+----------------------------------------+
| ST_Distance_Sphere(@zenica, @sarajevo) |
+----------------------------------------+
|                      55878.59337591705 |
+----------------------------------------+

SELECT ST_Distance_Sphere(@zenica, @sarajevo, 6370986);
+-------------------------------------------------+
| ST_Distance_Sphere(@zenica, @sarajevo, 6370986) |
+-------------------------------------------------+
|                               55878.59337591705 |
+-------------------------------------------------+

SELECT ST_Distance_Sphere(@zenica, @sarajevo, 200);    
+---------------------------------------------+
| ST_Distance_Sphere(@zenica, @sarajevo, 200) |
+---------------------------------------------+
|                           1.754158410516584 |
+---------------------------------------------+
gc
.
Geometries are numbered beginning with 1.

ST_GeometryN() and GeometryN() are synonyms.

Example

This page is licensed: CC BY-SA / Gnu FDL

ST_GeometryN(gc,N)
GeometryN(gc,N)
SET @gc = 'GeometryCollection(Point(1 1),LineString(12 14, 9 11))';

SELECT AsText(GeometryN(GeomFromText(@gc),1));
+----------------------------------------+
| AsText(GeometryN(GeomFromText(@gc),1)) |
+----------------------------------------+
| POINT(1 1)                             |
+----------------------------------------+
if geometry
g1
spatially crosses geometry
g2
. Returns
NULL
if
g1
is a
or a
, or if g2 is a
or a
. Otherwise, returns 0.

The term spatially crosses denotes a spatial relation between two given geometries that has the following properties:

  • The two geometries intersect.

  • Their intersection results in a geometry that has a dimension that is one less than the maximum dimension of the two given geometries.

  • Their intersection is not equal to either of the two given geometries.

ST_CROSSES() uses object shapes, while CROSSES(), based on the original MySQL implementation, uses object bounding rectangles.

Examples

This page is licensed: CC BY-SA / Gnu FDL

Polygon
MultiPolygon
Point
MultiPoint
g
. The result can be
Dimension
Definition

-1

empty geometry

0

geometry with no length or area

1

geometry with no area but nonzero length

2

geometry with nonzero area

ST_Dimension() and Dimension() are synonyms.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

g1
is less than or equal to distance, or radius,
r
.

Uses for this function could include creating for example a new geometry representing a buffer zone around an island.

BUFFER() is a synonym.

Examples

Determining whether a point is within a buffer zone:

This page is licensed: CC BY-SA / Gnu FDL

ST_BUFFER(g1,r)
BUFFER(g1,r)
SET @g1 = ST_GEOMFROMTEXT('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))');

SET @g2 = ST_GEOMFROMTEXT('POINT(8 8)');

SELECT ST_WITHIN(@g2,ST_BUFFER(@g1,5));
+---------------------------------+
| ST_WITHIN(@g2,ST_BUFFER(@g1,5)) |
+---------------------------------+
|                               1 |
+---------------------------------+

SELECT ST_WITHIN(@g2,ST_BUFFER(@g1,1));
+---------------------------------+
| ST_WITHIN(@g2,ST_BUFFER(@g1,1)) |
+---------------------------------+
|                               0 |
+---------------------------------+
and
g2
overlap. The term spatially overlaps is used if two geometries intersect and their intersection results in a geometry of the same dimension but not equal to either of the given geometries.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

MBROverlaps(g1,g2)
Minimum Bounding Rectangles
.

ST_StartPoint() and StartPoint() are synonyms.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

ST_StartPoint(ls)
StartPoint(ls)
Point
LineString
Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

MBRIntersects(g1,g2)
Minimum Bounding Rectangles
0
to indicate whether geometry
g1
spatially intersects geometry
g2
.

INTERSECTS() is based on the original MySQL implementation and uses object bounding rectangles, while ST_INTERSECTS() uses object shapes.

INTERSECTS() tests the opposite relationship to DISJOINT().

This page is licensed: GPLv2, originally from fill_help_tables.sql

INTERSECTS(g1,g2)
. Points are numbered beginning with
1
.

ST_PointN() and PointN() are synonyms.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

ST_PointN(ls,N)
PointN(ls,N)
Point
LineString
g
.

BOUNDARY() is a synonym.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

ST_BOUNDARY(g)
BOUNDARY(g)
SELECT ST_AsText(ST_Boundary(ST_GeomFromText('LINESTRING(3 3,0 0, -3 3)')));
+----------------------------------------------------------------------+
| ST_AsText(ST_Boundary(ST_GeomFromText('LINESTRING(3 3,0 0, -3 3)'))) |
+----------------------------------------------------------------------+
| MULTIPOINT(3 3,-3 3)                                                 |
+----------------------------------------------------------------------+

SELECT ST_AsText(ST_Boundary(ST_GeomFromText('POLYGON((3 3,0 0, -3 3, 3 3))')));
+--------------------------------------------------------------------------+
| ST_AsText(ST_Boundary(ST_GeomFromText('POLYGON((3 3,0 0, -3 3, 3 3))'))) |
+--------------------------------------------------------------------------+
| LINESTRING(3 3,0 0,-3 3,3 3)                                             |
+--------------------------------------------------------------------------+
if
g1
spatially crosses
g2
. Returns
NULL
if
g1
is a
or a
, or if g2 is a
or a
. Otherwise, returns 0.

The term spatially crosses denotes a spatial relation between two given geometries that has the following properties:

  • The two geometries intersect.

  • Their intersection results in a geometry that has a dimension that is one less than the maximum dimension of the two given geometries.

  • Their intersection is not equal to either of the two given geometries.

CROSSES() is based on the original MySQL implementation, and uses object bounding rectangles, while ST_CROSSES() uses object shapes.

This page is licensed: GPLv2, originally from fill_help_tables.sql

Polygon
MultiPolygon
Point
MultiPoint
.

ST_EndPoint() and EndPoint() are synonyms.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

ST_EndPoint(ls)
EndPoint(ls)
Point
LineString
Description

Returns 1 if the minimum bounding rectangle of g1 is covered by the minimum bounding rectangle of g2, otherwise 0.

Returns NULL If any argument is NULL, or an argument is an empty geometry.

Examples

See Also

This page is licensed: CC BY-SA / Gnu FDL

MBRCoveredBy(g1, g2)
MariaDB 12.0
and
g2
touch. Two geometries spatially touch if the interiors of the geometries do not intersect, but the boundary of one of the geometries intersects either the boundary or the interior of the other.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

MBRTouches(g1,g2)
Minimum Bounding Rectangles
Description

Given a geometry input, returns 1 if the argument is geometrically valid according to the OGC specifications, 0 if the argument is not geometrically valid.

Unlike ST_Validate, requires valid GIS data, or ERROR 3037 (22023): Invalid GIS data provided to function st_isvalid is returned.

Examples

A POINT requires both x and y co-ordinates:

See Also

  • Geometry Constructors

  • ST_AsText

This page is licensed: CC BY-SA / Gnu FDL

MariaDB 12.0
Description

The function checks that a given geometry is compliant with the Well-Known Binary (WKB) format and Spatial Reference System Identifier (SRID) syntax, and is geometrically valid.

It returns the geometry if it's valid, or NULL if not.

The function is useful to filter out invalid geometry data.

Examples

A POINT requires both x and y co-ordinates:

See Also

  • Geometry Constructors

  • ST_AsText

This page is licensed: CC BY-SA / Gnu FDL

MariaDB 12.0
Description

Takes a given geohash string and returns a point where the x is the longitude and the y is the latitude.

The latitude is returned as a numeric value in the interval [180, -180]. The longitude is returned as a numeric value in the interval [90, -90]. If the argument is NULL, the return value is NULL. If the argument is invalid, an ER_GIS_INVALID_DATA is thrown.

Examples

See Also

  • ST_GeoHash

This page is licensed: CC BY-SA / Gnu FDL

MariaDB 12.0
Description

Takes as input a geometry (g) and a double (max_distance). It applies the Ramer–Douglas–Peucker algorithm on g and returns the resulting geometry.

The goal of the Douglas-Peucker algorithm is to provide generalized simplifications by returning a geometry that is similar to g but uses only a subset of points. To perform the simplification, all the vertices that are shorter than max_distance are removed.

The algorithm may produce self-intersections and therefore result in invalid geometries. ST_IsValid can be used to test validity of the result.

If the max_distance is not positive or is NULL, an ER_WRONG_ARGUMENT will occur.

Examples

See Also

  • Geometry Constructors

  • ST_AsText

  • ST_GeomFromText

This page is licensed: CC BY-SA / Gnu FDL

MariaDB 12.0
MBREquals is a synonym.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

MBREqual(g1,g2)
MBREquals(g1,g2)
Minimum Bounding Rectangles
Description

Returns the geohash corresponding to the input values, or NULL if any argument is NULL. Geohashes encode latitude and longitude coordinates into a text string made up only of numeric and lowercase latin letter characters.

The longitude parameter is a numeric value in the interval [180, -180]. latitude is a numeric value in the interval [90, -90].

In the case of point, the x coordinate is treated as the latitude and the y coordinate is treated as the latitude. The same constraints apply.

The max_length parameter is the upper limit on the resulting string size and cannot exceed 100.

The ST_LatFromGeoHash function decodes a given geohash and returns the latitude.

Examples

See Also

  • ST_LatFromGeoHash

  • Geometry Constructors

  • ST_GeomFromText

This page is licensed: CC BY-SA / Gnu FDL

MariaDB 12.0
SET @g = ST_GEOMFROMTEXT('POLYGON((1 1,1 5,4 9,6 9,9 3,7 2,1 1))');

CREATE TABLE gis_polygon   (g POLYGON);
INSERT INTO gis_polygon VALUES
    (PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
    (PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
    (PolyFromWKB(AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))));
ST_NUMINTERIORRINGS()
fill_help_tables.sql

1

Return an error (the default)

2 - 4

The document is accepted, but the coordinates for higher coordinate dimensions are stripped off.

ST_AsGeoJSON
SET @g1 = GEOMFROMTEXT('POINT (0 2)');

SET @g2 = GEOMFROMTEXT('POINT (2 0)');

SELECT ASTEXT(ST_UNION(@g1,@g2));
+---------------------------+
| ASTEXT(ST_UNION(@g1,@g2)) |
+---------------------------+
| MULTIPOINT(2 0,0 2)       |
+---------------------------+
SET @g1 = GEOMFROMTEXT('POLYGON((0 0,0 3,3 3,3 0,0 0))');

SET @g2 = GEOMFROMTEXT('POLYGON((2 2,4 2,4 4,2 4,2 2))');

SELECT ASTEXT(ST_UNION(@g1,@g2));
+------------------------------------------------+
| ASTEXT(ST_UNION(@g1,@g2))                      |
+------------------------------------------------+
| POLYGON((0 0,0 3,2 3,2 4,4 4,4 2,3 2,3 0,0 0)) |
+------------------------------------------------+
SET @ls = 'LineString(1 1,2 2,3 3)';

SELECT AsText(EndPoint(GeomFromText(@ls)));
+-------------------------------------+
| AsText(EndPoint(GeomFromText(@ls))) |
+-------------------------------------+
| POINT(3 3)                          |
+-------------------------------------+

CREATE TABLE gis_line  (g LINESTRING);
INSERT INTO gis_line VALUES
    (LineFromText('LINESTRING(0 0,0 10,10 0)')),
    (LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
    (LineStringFromWKB(AsWKB(LineString(Point(10, 10), Point(40, 10)))));
Point
fill_help_tables.sql
SET @g1 = ST_GEOMFROMTEXT('LINESTRING(10 20, 10 40)');

SET @g2 = ST_GEOMFROMTEXT('LINESTRING(10 15, 10 25)');

SELECT ASTEXT(ST_SYMDIFFERENCE(@g1,@g2));
+----------------------------------------------+
| ASTEXT(ST_SYMDIFFERENCE(@g1,@g2))            |
+----------------------------------------------+
| MULTILINESTRING((10 15,10 20),(10 25,10 40)) |
+----------------------------------------------+

SET @g2 = ST_GeomFromText('LINESTRING(10 20, 10 41)');

SELECT ASTEXT(ST_SYMDIFFERENCE(@g1,@g2));
+-----------------------------------+
| ASTEXT(ST_SYMDIFFERENCE(@g1,@g2)) |
+-----------------------------------+
| LINESTRING(10 40,10 41)           |
+-----------------------------------+
SET @g1 = GEOMFROMTEXT('POINT(174 149)');
SET @g2 = GEOMFROMTEXT('POINT(176 151)');
SET @g3 = GEOMFROMTEXT('POLYGON((175 150, 20 40, 50 60, 125 100, 175 150))');

SELECT within(@g1,@g3);
+-----------------+
| within(@g1,@g3) |
+-----------------+
|               1 |
+-----------------+

SELECT within(@g2,@g3);
+-----------------+
| within(@g2,@g3) |
+-----------------+
|               0 |
+-----------------+
Contains()
ST_WITHIN()
fill_help_tables.sql
SET @ls = 'LineString(1 1,2 2,3 3)';

SELECT GLength(GeomFromText(@ls));
+----------------------------+
| GLength(GeomFromText(@ls)) |
+----------------------------+
|           2.82842712474619 |
+----------------------------+
ST_LENGTH()
fill_help_tables.sql
SET @g1 = ST_GEOMFROMTEXT('POINT(2 0)');

SET @g2 = ST_GEOMFROMTEXT('LINESTRING(2 0, 0 2)');

SELECT ST_TOUCHES(@g1,@g2);
+---------------------+
| ST_TOUCHES(@g1,@g2) |
+---------------------+
|                   1 |
+---------------------+

SET @g1 = ST_GEOMFROMTEXT('POINT(2 1)');

SELECT ST_TOUCHES(@g1,@g2);
+---------------------+
| ST_TOUCHES(@g1,@g2) |
+---------------------+
|                   0 |
+---------------------+
TOUCHES()
SET @g = ST_GEOMFROMTEXT('Point(0 0)');

SELECT ST_ASTEXT(ST_CONVEXHULL(@g));
+------------------------------+
| ST_ASTEXT(ST_CONVEXHULL(@g)) |
+------------------------------+
| POINT(0 0)                   |
+------------------------------+
SET @g = ST_GEOMFROMTEXT('MultiPoint(0 0, 1 2, 2 3)');

SELECT ST_ASTEXT(ST_CONVEXHULL(@g));
+------------------------------+
| ST_ASTEXT(ST_CONVEXHULL(@g)) |
+------------------------------+
| POLYGON((0 0,1 2,2 3,0 0))   |
+------------------------------+
SET @g1 = ST_GEOMFROMTEXT('POINT(0 0)');

SET @g2 = ST_GEOMFROMTEXT('LINESTRING(2 0, 0 2)');

SELECT ST_DISJOINT(@g1,@g2);
+----------------------+
| ST_DISJOINT(@g1,@g2) |
+----------------------+
|                    1 |
+----------------------+

SET @g2 = ST_GEOMFROMTEXT('LINESTRING(0 0, 0 2)');

SELECT ST_DISJOINT(@g1,@g2);
+----------------------+
| ST_DISJOINT(@g1,@g2) |
+----------------------+
|                    0 |
+----------------------+
DISJOINT()
ST_INTERSECTS()
SET @g1 = ST_GEOMFROMTEXT('POINT(0 0)');

SET @g2 = ST_GEOMFROMTEXT('LINESTRING(0 0, 0 2)');

SELECT ST_INTERSECTS(@g1,@g2);
+------------------------+
| ST_INTERSECTS(@g1,@g2) |
+------------------------+
|                      1 |
+------------------------+
INTERSECTS()
ST_DISJOINT()
POLYGON((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))
SELECT AsText(ST_ENVELOPE(GeomFromText('LineString(1 1,4 4)')));
+----------------------------------------------------------+
| AsText(ST_ENVELOPE(GeomFromText('LineString(1 1,4 4)'))) |
+----------------------------------------------------------+
| POLYGON((1 1,4 1,4 4,1 4,1 1))                           |
+----------------------------------------------------------+
fill_help_tables.sql
SET @g1 = ST_GEOMFROMTEXT('POINT(174 149)');

SET @g2 = ST_GEOMFROMTEXT('POLYGON((175 150, 20 40, 50 60, 125 100, 175 150))');

SELECT ST_WITHIN(@g1,@g2);
+--------------------+
| ST_WITHIN(@g1,@g2) |
+--------------------+
|                  1 |
+--------------------+

SET @g1 = ST_GEOMFROMTEXT('POINT(176 151)');

SELECT ST_WITHIN(@g1,@g2);
+--------------------+
| ST_WITHIN(@g1,@g2) |
+--------------------+
|                  0 |
+--------------------+
ST_CONTAINS()
WITHIN()
SET @ls = 'LineString(1 1,2 2,3 3)';

SELECT NumPoints(GeomFromText(@ls));
+------------------------------+
| NumPoints(GeomFromText(@ls)) |
+------------------------------+
|                            3 |
+------------------------------+
fill_help_tables.sql
SET @pt = 'Point(56.7 53.34)';

SELECT X(GeomFromText(@pt));
+----------------------+
| X(GeomFromText(@pt)) |
+----------------------+
|                 56.7 |
+----------------------+
fill_help_tables.sql
SET @pt = 'Point(56.7 53.34)';

SELECT Y(GeomFromText(@pt));
+----------------------+
| Y(GeomFromText(@pt)) |
+----------------------+
|                53.34 |
+----------------------+
fill_help_tables.sql
INTERSECTS()
ST_DISJOINT()
fill_help_tables.sql
OVERLAPS()
fill_help_tables.sql
ST_EQUALS()
fill_help_tables.sql
MDEV-7510
ST_CONTAINS()
Within()
fill_help_tables.sql
ST_TOUCHES()
fill_help_tables.sql
fill_help_tables.sql
ST_NumPoints

ST_LatFromGeoHash

Decode a Geohash to retrieve the latitude. This function returns the latitude coordinate (Y-axis) from a given Geohash string.

ST_LatFromGeoHash is available from MariaDB 12.0.

Syntax

ST_LatFromGeoHash(geohash)

Description

Decodes a given geohash string and returns the latitude in the interval [90, -90].

If the argument is NULL, the return value is NULL. If the argument is invalid, an ER_INCORRECT_TYPE error is thrown.

The function can be used to generate geohashes.

Examples

See Also

This page is licensed: CC BY-SA / Gnu FDL

ST_LENGTH

Returns the length of a linear geometry. Calculates the total length of a LineString or MultiLineString in its associated spatial reference units.

Syntax

ST_LENGTH(ls)

Description

Returns as a double-precision number the length of the LineString value ls in its associated spatial reference.

Examples

This page is licensed: CC BY-SA / Gnu FDL

MBRDisjoint

Check if two MBRs are disjoint. Returns 1 if the Minimum Bounding Rectangles of the two geometries do not intersect or touch at all.

Syntax

MBRDisjoint(g1,g2)

Description

Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 are disjoint. Two geometries are disjoint if they do not intersect, that is touch or overlap.

Examples

This page is licensed: GPLv2, originally from

ST_LongFromGeoHash

Decode a Geohash to retrieve the longitude. This function returns the longitude coordinate (X-axis) from a given Geohash string.

ST_LongFromGeoHash is available from MariaDB 12.0.

Syntax

Description

Decodes a given geohash string and returns the longitude in the interval [180, -180].

If the argument is NULL, the return value is NULL. If the argument is invalid, an ER_INCORRECT_TYPE error is thrown.

The function can be used to generate geohashes.

Examples

See Also

This page is licensed: CC BY-SA / Gnu FDL

MBRWithin

Check if one MBR is within another. Returns 1 if the MBR of the first geometry is completely enclosed by the MBR of the second geometry.

Syntax

MBRWithin(g1,g2)

Description

Returns 1 or 0 to indicate whether the Minimum Bounding Rectangle of g1 is within the Minimum Bounding Rectangle of g2. This tests the opposite relationship as MBRContains().

Examples

This page is licensed: GPLv2, originally from

ST_CONTAINS

Checks if geometry A contains geometry B. Returns 1 if B is completely inside A, 0 otherwise.

Syntax

ST_CONTAINS(g1,g2)

Description

Returns 1 or 0 to indicate whether a geometry g1 completely contains geometry g2.

ST_CONTAINS() uses object shapes, while , based on the original MySQL implementation, uses object bounding rectangles.

ST_CONTAINS tests the opposite relationship to .

Examples

This page is licensed: CC BY-SA / Gnu FDL

ST_Collect

Aggregate multiple geometries into a collection. This function creates a MultiPoint, MultiLineString, MultiPolygon, or GeometryCollection from a set of geometry arguments.

ST_Collect is available from MariaDB 12.0.

Syntax

Description

ST_Collect is an aggregate function that can also be used as a window function.

Given multiple geometries, returns the aggregation of the distinct geometry arguments. This function also supports the DISTINCT option. If DISTINCT is used, it returns the aggregation of the distinct geometry arguments.

The resulting value type is chosen using the following policy:

  • If all arguments are values, the result is a value.

  • If all arguments are LineString values, the result is a MultiLineString value.

  • If all arguments are Polygon values, the result is a MultiPolygon value.

  • Otherwise, the result is a GeometryCollection value.

If there are multiple geometry arguments and those arguments are in the same spatial reference system (SRS), the return value is in that SRS. If those arguments are not in the same SRS, an ER_GIS_DIFFERENT_SRIDS_AGGREGATION error occurs.

Examples

Multiple geometries aggregated into a geometry:

See Also

This page is licensed: CC BY-SA / Gnu FDL

MBRContains

Check if one MBR contains another. Returns 1 if the Minimum Bounding Rectangle of the first geometry completely encloses the MBR of the second geometry.

Syntax

Description

Returns 1 or 0 to indicate whether the of g1 contains the Minimum Bounding Rectangle of g2. This tests the opposite relationship as

ST_EQUALS

Returns a geometry representing the difference between two geometries. The result contains points from the first geometry that are not in the second.

Syntax

Description

Returns 1

CREATE TABLE point_example (
  p POINT
);
INSERT INTO point_example VALUES
  (ST_PointFromText('POINT(1 1)')),
  (ST_PointFromText('POINT(2 2)')),
  (Point(3, 3)),
  (Point(4, 4));
SELECT ST_AsWKT(p) FROM point_example;
+-------------+
| ST_AsWKT(p) |
+-------------+
| POINT(1 1)  |
| POINT(2 2)  |
| POINT(3 3)  |
| POINT(4 4)  |
+-------------+
CREATE TABLE gis_multi_polygon  (g MULTIPOLYGON);
INSERT INTO gis_multi_polygon VALUES
    (MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),
     ((59 18,67 18,67 13,59 13,59 18)))')),
    (MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),
        ((59 18,67 18,67 13,59 13,59 18)))')),
    (MPolyFromWKB(AsWKB(MultiPolygon(Polygon(LineString(
       Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))));
INSERT INTO multipolygon_example VALUES
  (ST_MultiPolygonFromText(
    'MULTIPOLYGON(((0 40, 0 20, 6 30, 12 20, 12 40, 0 40),
    (15 40, 15 20, 25 20, 30 25, 30 35, 25 40, 15 40)))')),
  (ST_MPolyFromText(
    'MULTIPOLYGON(((-5 45, 35 45, 35 15, -5 15, -5 45),
    (0 40, 0 20, 6 30, 12 20, 12 40, 0 40),
    (15 40, 15 20, 25 20, 30 25, 30 35, 25 40, 15 40)))')),
  (MultiPolygon(Polygon(LineString(Point(0, 0), Point(0, 1),
     Point(1, 1), Point(1, 0), Point(0, 0)))));
SELECT ST_AsWKT(m) FROM multipolygon_example;
+-------------------------------------------------------------------------------------------------------------------------------+
| ST_AsWKT(m)                                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------+
| MULTIPOLYGON(((0 40,0 20,6 30,12 20,12 40,0 40),(15 40,15 20,25 20,30 25,30 35,25 40,15 40)))                                 |
| MULTIPOLYGON(((-5 45,35 45,35 15,-5 15,-5 45),(0 40,0 20,6 30,12 20,12 40,0 40),(15 40,15 20,25 20,30 25,30 35,25 40,15 40))) |
| MULTIPOLYGON(((0 0,0 1,1 1,1 0,0 0)))                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------+
ST_CROSSES(g1,g2)
SET @g1 = ST_GEOMFROMTEXT('LINESTRING(174 149, 176 151)');

SET @g2 = ST_GEOMFROMTEXT('POLYGON((175 150, 20 40, 50 60, 125 100, 175 150))');

SELECT ST_CROSSES(@g1,@g2);
+---------------------+
| ST_CROSSES(@g1,@g2) |
+---------------------+
|                   1 |
+---------------------+

SET @g1 = ST_GEOMFROMTEXT('LINESTRING(176 149, 176 151)');

SELECT ST_CROSSES(@g1,@g2);
+---------------------+
| ST_CROSSES(@g1,@g2) |
+---------------------+
|                   0 |
+---------------------+
ST_Dimension(g)
Dimension(g)
SELECT Dimension(GeomFromText('LineString(1 1,2 2)'));
+------------------------------------------------+
| Dimension(GeomFromText('LineString(1 1,2 2)')) |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+
SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GeomFromText('Polygon((4 4,4 7,7 7,7 4,4 4))');
SELECT mbroverlaps(@g1,@g2);
+----------------------+
| mbroverlaps(@g1,@g2) |
+----------------------+
|                    0 |
+----------------------+

SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GeomFromText('Polygon((3 3,3 6,6 6,6 3,3 3))');
SELECT mbroverlaps(@g1,@g2);
+----------------------+
| mbroverlaps(@g1,@g2) |
+----------------------+
|                    0 |
+----------------------+

SET @g1 = GeomFromText('Polygon((0 0,0 4,4 4,4 0,0 0))');
SET @g2 = GeomFromText('Polygon((3 3,3 6,6 6,6 3,3 3))');
SELECT mbroverlaps(@g1,@g2);
+----------------------+
| mbroverlaps(@g1,@g2) |
+----------------------+
|                    1 |
+----------------------+
SET @ls = 'LineString(1 1,2 2,3 3)';

SELECT AsText(StartPoint(GeomFromText(@ls)));
+---------------------------------------+
| AsText(StartPoint(GeomFromText(@ls))) |
+---------------------------------------+
| POINT(1 1)                            |
+---------------------------------------+
SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GeomFromText('Polygon((3 3,3 6,6 6,6 3,3 3))');
SELECT mbrintersects(@g1,@g2);
+------------------------+
| mbrintersects(@g1,@g2) |
+------------------------+
|                      1 |
+------------------------+

SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GeomFromText('Polygon((4 4,4 7,7 7,7 4,4 4))');
SELECT mbrintersects(@g1,@g2);
+------------------------+
| mbrintersects(@g1,@g2) |
+------------------------+
|                      0 |
+------------------------+
SET @ls = 'LineString(1 1,2 2,3 3)';

SELECT AsText(PointN(GeomFromText(@ls),2));
+-------------------------------------+
| AsText(PointN(GeomFromText(@ls),2)) |
+-------------------------------------+
| POINT(2 2)                          |
+-------------------------------------+
Crosses(g1,g2)
SET @ls = 'LineString(1 1,2 2,3 3)';

SELECT AsText(EndPoint(GeomFromText(@ls)));
+-------------------------------------+
| AsText(EndPoint(GeomFromText(@ls))) |
+-------------------------------------+
| POINT(3 3)                          |
+-------------------------------------+
SET @g1a = ST_GeomFromText('Point(5 6)');
SET @g1b = ST_GeomFromText('Point(5 11)');
SET @g2 = ST_GeomFromText('Polygon((0 0,0 10,10 10,10 0,0 0))');

SELECT MBRCoveredby(@g1a,@g2), MBRCoveredby(@g1b,@g2);
+------------------------+------------------------+
| MBRCoveredby(@g1a,@g2) | MBRCoveredby(@g1b,@g2) |
+------------------------+------------------------+
|                      1 |                      0 |
+------------------------+------------------------+
SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GeomFromText('Polygon((4 4,4 7,7 7,7 4,4 4))');
SELECT mbrtouches(@g1,@g2);
+---------------------+
| mbrtouches(@g1,@g2) |
+---------------------+
|                   0 |
+---------------------+

SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GeomFromText('Polygon((3 3,3 6,6 6,6 3,3 3))');
SELECT mbrtouches(@g1,@g2);
+---------------------+
| mbrtouches(@g1,@g2) |
+---------------------+
|                   1 |
+---------------------+

SET @g1 = GeomFromText('Polygon((0 0,0 4,4 4,4 0,0 0))');
SET @g2 = GeomFromText('Polygon((3 3,3 6,6 6,6 3,3 3))');
SELECT mbrtouches(@g1,@g2);
+---------------------+
| mbrtouches(@g1,@g2) |
+---------------------+
|                   0 |
+---------------------+
ST_IsValid(g)
SELECT ST_IsValid(ST_GeomFromText('LINESTRING (0 0, 1 1)'));
+------------------------------------------------------+
| ST_IsValid(ST_GeomFromText('LINESTRING (0 0, 1 1)')) |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+

SELECT ST_IsValid(ST_GeomFromText('LINESTRING (0 0, 0 0)'));
+------------------------------------------------------+
| ST_IsValid(ST_GeomFromText('LINESTRING (0 0, 0 0)')) |
+------------------------------------------------------+
|                                                    0 |
+------------------------------------------------------+
SELECT ST_IsValid(ST_GeomFromText('POINT (0)'));  
ERROR 3037 (22023): Invalid GIS data provided to function st_isvalid.
ST_Validate(g)
SELECT ST_ASTEXT(ST_VALIDATE(ST_GeomFromText('POINT(1 0)')));          
+-------------------------------------------------------+
| ST_ASTEXT(ST_VALIDATE(ST_GeomFromText('POINT(1 0)'))) |
+-------------------------------------------------------+
| POINT(1 0)                                            |
+-------------------------------------------------------+

SELECT ST_ASTEXT(ST_VALIDATE(ST_GeomFromText('POINT(1)')));  
+-----------------------------------------------------+
| ST_ASTEXT(ST_VALIDATE(ST_GeomFromText('POINT(1)'))) |
+-----------------------------------------------------+
| NULL                                                |
+-----------------------------------------------------+
ST_PointFromGeoHash(geohash, srid)
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("s00twy01mtw037m",0));
+-----------------------------------------------------+
| ST_ASTEXT(ST_POINTFROMGEOHASH("s00twy01mtw037m",0)) |
+-----------------------------------------------------+
| POINT(1 1)                                          |
+-----------------------------------------------------+

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(180,90,20),0));
+---------------------------------------------------------+
| ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(180,90,20),0)) |
+---------------------------------------------------------+
| POINT(180 90)                                           |
+---------------------------------------------------------+
ST_Simplify(g, max_distance)
SELECT ST_AsText(ST_Simplify(ST_GeomFromText('LINESTRING(0 0,0 2,2 2,2 4,4 4,4 6,6 6)'), 0.5));
+-----------------------------------------------------------------------------------------+
| ST_AsText(ST_Simplify(ST_GeomFromText('LINESTRING(0 0,0 2,2 2,2 4,4 4,4 6,6 6)'), 0.5)) |
+-----------------------------------------------------------------------------------------+
| LINESTRING(0 0,0 2,2 2,2 4,4 4,4 6,6 6)                                                 |
+-----------------------------------------------------------------------------------------+

SELECT ST_AsText(ST_Simplify(ST_GeomFromText('LINESTRING(0 0,0 2,2 2,2 4,4 4,4 6,6 6)'), 1));  
+---------------------------------------------------------------------------------------+
| ST_AsText(ST_Simplify(ST_GeomFromText('LINESTRING(0 0,0 2,2 2,2 4,4 4,4 6,6 6)'), 1)) |
+---------------------------------------------------------------------------------------+
| LINESTRING(0 0,0 2,2 2,2 4,6 6)                                                       |
+---------------------------------------------------------------------------------------+
SET @g1=GEOMFROMTEXT('LINESTRING(0 0, 1 2)');
SET @g2=GEOMFROMTEXT('POLYGON((0 0, 0 2, 1 2, 1 0, 0 0))');
SELECT MbrEqual(@g1,@g2);
+-------------------+
| MbrEqual(@g1,@g2) |
+-------------------+
|                 1 |
+-------------------+

SET @g1=GEOMFROMTEXT('LINESTRING(0 0, 1 3)');
SET @g2=GEOMFROMTEXT('POLYGON((0 0, 0 2, 1 4, 1 0, 0 0))');
SELECT MbrEqual(@g1,@g2);
+-------------------+
| MbrEqual(@g1,@g2) |
+-------------------+
|                 0 |
+-------------------+
ST_GeoHash(longitude, latitude, max_length)
ST_GeoHash(point, max_length)
SELECT ST_GeoHash(ST_GeomFromText('POINT(1 1)'),15), ST_GeoHash(0,30,15);
+----------------------------------------------+---------------------+
| ST_GeoHash(ST_GeomFromText('POINT(1 1)'),15) | ST_GeoHash(0,30,15) |
+----------------------------------------------+---------------------+
| s00twy01mtw037m                              | sj248j248j248j2     |
+----------------------------------------------+---------------------+
SELECT ST_NumInteriorRings(ST_PolyFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),
  (-1 -1,-5 -1,-5 -5,-1 -5,-1 -1))')) AS NumInteriorRings;
+------------------+
| NumInteriorRings |
+------------------+
|                1 |
+------------------+
CREATE TABLE polygon_example (
  p POLYGON
);
INSERT INTO polygon_example VALUES
  (ST_PolygonFromText('POLYGON((0 40, 0 20, 6 30, 12 20, 12 40, 0 40))')),
  (ST_PolyFromText('POLYGON((15 40, 15 20, 25 20, 30 25, 30 35, 25 40, 15 40))')),
  (Polygon(LineString(Point(0, 0), Point(0, 1), Point(1, 1),
  Point(1, 0), Point(0, 0))));
SELECT ST_AsWKT(p) FROM polygon_example;
+------------------------------------------------------+
| ST_AsWKT(p)                                          |
+------------------------------------------------------+
| POLYGON((0 40,0 20,6 30,12 20,12 40,0 40))           |
| POLYGON((15 40,15 20,25 20,30 25,30 35,25 40,15 40)) |
| POLYGON((0 0,0 1,1 1,1 0,0 0))                       |
+------------------------------------------------------+
SET @j = '{ "type": "Point", "coordinates": [5.3, 15.0]}';

SELECT ST_AsText(ST_GeomFromGeoJSON(@j));
+-----------------------------------+
| ST_AsText(ST_GeomFromGeoJSON(@j)) |
+-----------------------------------+
| POINT(5.3 15)                     |
+-----------------------------------+
CREATE TABLE linestring_example (
  g LINESTRING
);
INSERT INTO linestring_example VALUES
  (ST_LineFromText('LINESTRING(0 0, 1 1, 2 2)')),
  (ST_LineStringFromText('LINESTRING(10 10, 20 10, 20 20, 10 20, 10 10)')),
  (LineString(Point(10, 10), Point(40, 10)));
SELECT ST_AsWKT(g) FROM linestring_example;
<</code>>

<<sql>>
+-------------------------------------------+
| ST_AsWKT(g)                               |
+-------------------------------------------+
| LINESTRING(0 0,1 1,2 2)                   |
| LINESTRING(10 10,20 10,20 20,10 20,10 10) |
| LINESTRING(10 10,40 10)                   |
+-------------------------------------------+
SET @g = ST_GEOMFROMTEXT('MultiPoint( 1 1, 2 2, 5 3, 7 2, 9 3, 8 4, 6 6, 6 9, 4 9, 1 5 )');

SELECT ST_ASTEXT(ST_CONVEXHULL(@g));
+----------------------------------------+
| ST_ASTEXT(ST_CONVEXHULL(@g))           |
+----------------------------------------+
| POLYGON((1 1,1 5,4 9,6 9,9 3,7 2,1 1)) |
+----------------------------------------+
SET @g2 = ST_GEOMFROMTEXT('LINESTRING(2 0, 0 2)');

SELECT ST_INTERSECTS(@g1,@g2);
+------------------------+
| ST_INTERSECTS(@g1,@g2) |
+------------------------+
|                      0 |
+------------------------+
ST_LongFromGeoHash(geohash)
ST_Collect(g)
ST_GeoHash
ST_GeoHash
SET @ls = 'LineString(1 1,2 2,3 3)';

SELECT ST_LENGTH(ST_GeomFromText(@ls));
+---------------------------------+
| ST_LENGTH(ST_GeomFromText(@ls)) |
+---------------------------------+
|                2.82842712474619 |
+---------------------------------+
SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GeomFromText('Polygon((4 4,4 7,7 7,7 4,4 4))');
SELECTmbrdisjoint(@g1,@g2);
+----------------------+
| mbrdisjoint(@g1,@g2) |
+----------------------+
|                    1 |
+----------------------+

SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GeomFromText('Polygon((3 3,3 6,6 6,6 3,3 3))');
SELECT mbrdisjoint(@g1,@g2);
+----------------------+
| mbrdisjoint(@g1,@g2) |
+----------------------+
|                    0 |
+----------------------+
fill_help_tables.sql
ST_GeoHash
ST_GeoHash
ST_LatFromGeoHash
SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GeomFromText('Polygon((0 0,0 5,5 5,5 0,0 0))');
SELECT MBRWithin(@g1,@g2), MBRWithin(@g2,@g1);
+--------------------+--------------------+
| MBRWithin(@g1,@g2) | MBRWithin(@g2,@g1) |
+--------------------+--------------------+
|                  1 |                  0 |
+--------------------+--------------------+
fill_help_tables.sql
SET @g1 = ST_GEOMFROMTEXT('POLYGON((175 150, 20 40, 50 60, 125 100, 175 150))');

SET @g2 = ST_GEOMFROMTEXT('POINT(174 149)');

SELECT ST_CONTAINS(@g1,@g2);
+----------------------+
| ST_CONTAINS(@g1,@g2) |
+----------------------+
|                    1 |
+----------------------+

SET @g2 = ST_GEOMFROMTEXT('POINT(175 151)');

SELECT ST_CONTAINS(@g1,@g2);
+----------------------+
| ST_CONTAINS(@g1,@g2) |
+----------------------+
|                    0 |
+----------------------+
CONTAINS()
ST_WITHIN()
Point
MultiPoint
Point
MultiPoint
Geometry Constructors
ST_AsText
.

Examples

See Also

  • MBRWithin

This page is licensed: GPLv2, originally from fill_help_tables.sql

MBRContains(g1,g2)
Minimum Bounding Rectangle
MBRWithin()
or
0
to indicate whether geometry
g1
is spatially equal to geometry
g2
.

ST_EQUALS() uses object shapes, while EQUALS(), based on the original MySQL implementation, uses object bounding rectangles.

Examples

This page is licensed: CC BY-SA / Gnu FDL

ST_EQUALS(g1,g2)
SET @g1 = ST_GEOMFROMTEXT('LINESTRING(174 149, 176 151)');

SET @g2 = ST_GEOMFROMTEXT('LINESTRING(176 151, 174 149)');

SELECT ST_EQUALS(@g1,@g2);
+--------------------+
| ST_EQUALS(@g1,@g2) |
+--------------------+
|                  1 |
+--------------------+
SELECT ST_LatFromGeoHash('zzzzzzzzz'), ST_LatFromGeoHash('xvrfxvrfxvrfxvr');
+--------------------------------+--------------------------------------+
| ST_LatFromGeoHash('zzzzzzzzz') | ST_LatFromGeoHash('xvrfxvrfxvrfxvr') |
+--------------------------------+--------------------------------------+
|                             90 |                                   30 |
+--------------------------------+--------------------------------------+
SELECT ST_LongFromGeoHash('zzzzzzzzz'), ST_LongFromGeoHash('sj248j248j248j2');           
+---------------------------------+---------------------------------------+
| ST_LongFromGeoHash('zzzzzzzzz') | ST_LongFromGeoHash('sj248j248j248j2') |
+---------------------------------+---------------------------------------+
|                             180 |                                     0 |
+---------------------------------+---------------------------------------+
CREATE OR REPLACE TABLE t1 ( running_number INTEGER NOT NULL
  AUTO_INCREMENT, grouping_condition INTEGER, location GEOMETRY , PRIMARY KEY (
  running_number));

INSERT INTO t1 ( grouping_condition, location ) VALUES
  ( 0,ST_GEOMFROMTEXT('POINT(0 0)',4326)),
  ( 1,ST_GEOMFROMTEXT('POINT(0 0)',4326)),
  ( 0,ST_GEOMFROMTEXT('POINT(1 0)',4326)),
  ( 1,ST_GEOMFROMTEXT('POINT(2 0)',4326)),
  ( 0,ST_GEOMFROMTEXT('POINT(3 0)',4326));

SELECT ST_EQUALS( (SELECT ST_COLLECT( location ) AS t FROM t1), 
  ST_GEOMFROMTEXT('MULTIPOINT(0 0,0 0,1 0,2 0,3 0) ',4326)) AS equals;
+--------+
| equals |
+--------+
|      1 |
+--------+
SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');

SET @g2 = GeomFromText('Point(1 1)');

SELECT MBRContains(@g1,@g2), MBRContains(@g2,@g1);
+----------------------+----------------------+
| MBRContains(@g1,@g2) | MBRContains(@g2,@g1) |
+----------------------+----------------------+
|                    1 |                    0 |
+----------------------+----------------------+
SET @g1 = ST_GEOMFROMTEXT('POINT(0 2)');

SET @g1 = ST_GEOMFROMTEXT('POINT(2 0)');

SELECT ST_EQUALS(@g1,@g2);
+--------------------+
| ST_EQUALS(@g1,@g2) |
+--------------------+
|                  0 |
+--------------------+