1 of 100

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 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.

BUFFER

A synonym for ST_BUFFER.

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

CONVEXHULL

A synonym for ST_CONVEXHULL.

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

GEOMETRYCOLLECTION

Syntax

GeometryCollection(g1,g2,...)

Description

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

Examples

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'));

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))            |
+---------------------------------------------------------------+

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

LINESTRING

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 Point arguments is less than two, the return value is NULL.

Examples

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)))));

Linestring_Example:

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)                   |
+-------------------------------------------+

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

MULTILINESTRING

Syntax

MultiLineString(ls1,ls2,...)

Description

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

Example

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))))));

MultiLineString_Example:

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))                                           |
+-------------------------------------------------------------------------------------------+

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

MULTIPOINT

Syntax

MultiPoint(pt1,pt2,...)

Description

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

Examples

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)))));

MultiPoint_Example:

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)         |
+-----------------------------+

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

MULTIPOLYGON

Syntax

MultiPolygon(poly1,poly2,...)

Description

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

Example

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)))))));

MultiPolygon_Example:

CREATE TABLE multipolygon_example (
  m MULTIPOLYGON
);

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)))                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------+

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

POINT

Syntax

Point(x,y)

Description

Constructs a WKB Point using the given coordinates.

Examples

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)'))));

Point_Example:

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)  |
+-------------+

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

PointOnSurface

A synonym for ST_PointOnSurface.

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

POLYGON

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 ST_NUMINTERIORRINGS() will return 1.

Examples

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))))));

Non-overlapping 'polygon':

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 |
+------------------+

Polygon_Example:

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))                       |
+------------------------------------------------------+

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

ST_BUFFER

Syntax

ST_BUFFER(g1,r)
BUFFER(g1,r)

Description

Returns a geometry that represents all points whose distance from geometry 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:

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 |
+---------------------------------+

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

ST_CONVEXHULL

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:

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 @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)) |
+----------------------------------------+

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

ST_INTERSECTION

Syntax

ST_INTERSECTION(g1,g2)

Description

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

Examples

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)                       |
+----------------------------------+

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

ST_POINTONSURFACE

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_SYMDIFFERENCE

Syntax

ST_SYMDIFFERENCE(g1,g2)

Description

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

Examples

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)           |
+-----------------------------------+

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

ST_UNION

Syntax

ST_UNION(g1,g2)

Description

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

Examples

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)) |
+------------------------------------------------+

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

ST_AsGeoJSON

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

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

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

Return an error (the default)

2 - 4

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

Examples

SET @j = '{ "type": "Point", "coordinates": [5.3, 15.0]}';

SELECT ST_AsText(ST_GeomFromGeoJSON(@j));
+-----------------------------------+
| ST_AsText(ST_GeomFromGeoJSON(@j)) |
+-----------------------------------+
| POINT(5.3 15)                     |
+-----------------------------------+

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.

DIMENSION

A synonym for ST_DIMENSION.

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

BOUNDARY

A synonym for ST_BOUNDARY.

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

ENVELOPE

A synonym for ST_ENVELOPE.

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

GeometryN

A synonym for ST_GeometryN.

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

GeometryType

A synonym for ST_GeometryType.

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

IsEmpty

A synonym for ST_IsEmpty.

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

IsSimple

A synonym for ST_IsSImple.

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

NumGeometries

A synonym for ST_NumGeometries.

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

SRID

A synonym for ST_SRID.

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

IsClosed

A synonym for ST_IsClosed.

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

IsRing

A synonym for ST_IsRing.

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

ST_BOUNDARY

Syntax

ST_BOUNDARY(g)
BOUNDARY(g)

Description

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

BOUNDARY() is a synonym.

Examples

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)                                             |
+--------------------------------------------------------------------------+

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

ST_DIMENSION

Syntax

ST_Dimension(g)
Dimension(g)

Description

Returns the inherent dimension of the geometry value g. The result can be

Dimension

Definition

-1

empty geometry

geometry with no length or area

geometry with no area but nonzero length

geometry with nonzero area

ST_Dimension() and Dimension() are synonyms.

Examples

SELECT Dimension(GeomFromText('LineString(1 1,2 2)'));
+------------------------------------------------+
| Dimension(GeomFromText('LineString(1 1,2 2)')) |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+

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

ST_ENVELOPE

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:

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

ST_ENVELOPE() and ENVELOPE() are synonyms.

Examples

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))                           |
+----------------------------------------------------------+

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

ST_GEOMETRYN

Syntax

ST_GeometryN(gc,N)
GeometryN(gc,N)

Description

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

ST_GeometryN() and GeometryN() are synonyms.

Example

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)                             |
+----------------------------------------+

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

ST_GEOMETRYTYPE

Syntax

ST_GeometryType(g)
GeometryType(g)

Description

Returns as a string the name of the geometry type of which the geometry instance g is a member. The name corresponds to one of the instantiable Geometry subclasses.

ST_GeometryType() and GeometryType() are synonyms.

Examples

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

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

ST_ISCLOSED

Syntax

ST_IsClosed(g)
IsClosed(g)

Description

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

ST_IsClosed() and IsClosed() are synonyms.

Examples

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 |
+--------------------------------+

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

ST_ISEMPTY

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}_{fill_help_tables.sql}

ST_IsRing

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 MDEV-7510.

St_IsRing() and IsRing() are synonyms.

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

ST_IsSimple

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.

SET @g = 'Point(1 2)';

SELECT ST_ISSIMPLE(GEOMFROMTEXT(@g));
+-------------------------------+
| ST_ISSIMPLE(GEOMFROMTEXT(@g)) |
+-------------------------------+
|                             1 |
+-------------------------------+

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

ST_NUMGEOMETRIES

Syntax

ST_NumGeometries(gc)
NumGeometries(gc)

Description

Returns the number of geometries in the GeometryCollection gc.

ST_NumGeometries() and NumGeometries() are synonyms.

Example

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

SELECT NUMGEOMETRIES(GeomFromText(@gc));
+----------------------------------+
| NUMGEOMETRIES(GeomFromText(@gc)) |
+----------------------------------+
|                                2 |
+----------------------------------+

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

ST_RELATE

Syntax

ST_Relate(g1, g2, i)

Description

Returns true if Geometry g1 is spatially related to Geometryg2 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_SRID

Syntax

ST_SRID(g)
SRID(g)

Description

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

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

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

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

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.

CONTAINS

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 ST_CONTAINS() uses object shapes.

This tests the opposite relationship to Within().

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

CROSSES

Syntax

Crosses(g1,g2)

Description

Returns 1 if g1 spatially crosses g2. Returns NULL if g1 is a Polygon or a MultiPolygon, or if g2 is aPoint or a MultiPoint. 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}

DISJOINT

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 INTERSECTS().

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

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

EQUALS

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 ST_EQUALS() uses object shapes.

MBREQUALS is a synonym for Equals.

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

INTERSECTS

Syntax

INTERSECTS(g1,g2)

Description

Returns 1 or 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}

OVERLAPS

Syntax

OVERLAPS(g1,g2)

Description

Returns 1 or 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}

ST_CONTAINS

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 CONTAINS(), based on the original MySQL implementation, uses object bounding rectangles.

ST_CONTAINS tests the opposite relationship to ST_WITHIN().

Examples

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 |
+----------------------+

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

ST_CROSSES

Syntax

ST_CROSSES(g1,g2)

Description

Returns 1 if geometry g1 spatially crosses geometry g2. Returns NULL if g1 is a Polygon or a MultiPolygon, or if g2 is aPoint or a MultiPoint. 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

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 |
+---------------------+

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

ST_EQUALS

Syntax

ST_EQUALS(g1,g2)

Description

Returns 1 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

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 |
+--------------------+

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

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

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

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

ST_INTERSECTS

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 INTERSECTS(), based on the original MySQL implementation, uses object bounding rectangles.

ST_INTERSECTS() tests the opposite relationship to ST_DISJOINT().

Examples

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 |
+------------------------+

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

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

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

ST_OVERLAPS

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 OVERLAPS(), based on the original MySQL implementation, uses object bounding rectangles.

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

ST_TOUCHES

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 TOUCHES(), based on the original MySQL implementation, uses object bounding rectangles.

Examples

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 |
+---------------------+

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

ST_WITHIN

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_CONTAINS().

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

Examples

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 |
+--------------------+

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

ST_DIFFERENCE

Syntax

ST_DIFFERENCE(g1,g2)

Description

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

Example

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)                       |
+------------------------------------+

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

ST_DISJOINT

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 DISJOINT(), based on the original MySQL implementation, uses object bounding rectangles.

ST_DISJOINT() tests the opposite relationship to ST_INTERSECTS().

Examples

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 |
+----------------------+

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

ST_DISTANCE

Syntax

ST_DISTANCE(g1,g2)

Description

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

Example

SELECT ST_Distance(POINT(1,2),POINT(2,2));
+------------------------------------+
| ST_Distance(POINT(1,2),POINT(2,2)) |
+------------------------------------+
|                                  1 |
+------------------------------------+

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

ST_DISTANCE_SPHERE

Syntax

ST_DISTANCE_SPHERE(g1,g2,[r])

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, NULL is returned.

Example

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 |
+---------------------------------------------+

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

ST_LENGTH

Syntax

ST_LENGTH(ls)

Description

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

Examples

SET @ls = 'LineString(1 1,2 2,3 3)';

SELECT ST_LENGTH(ST_GeomFromText(@ls));
+---------------------------------+
| ST_LENGTH(ST_GeomFromText(@ls)) |
+---------------------------------+
|                2.82842712474619 |
+---------------------------------+

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

TOUCHES

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 ST_TOUCHES() uses object shapes.

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

WITHIN

Syntax

Within(g1,g2)

Description

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

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

Examples

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 |
+-----------------+

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

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.

GLENGTH

Syntax

GLength(ls)

Description

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

Examples

SET @ls = 'LineString(1 1,2 2,3 3)';

SELECT GLength(GeomFromText(@ls));
+----------------------------+
| GLength(GeomFromText(@ls)) |
+----------------------------+
|           2.82842712474619 |
+----------------------------+

ENDPOINT

A synonym for ST_ENDPOINT.

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

NumPoints

A synonym for ST_NumPoints.

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

PointN

A synonym for ST_PointN.

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

STARTPOINT

A synonym for ST_STARTPOINT.

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

ST_ENDPOINT

Syntax

ST_EndPoint(ls)
EndPoint(ls)

Description

Returns the Point that is the endpoint of theLineString value ls.

ST_EndPoint() and EndPoint() are synonyms.

Examples

SET @ls = 'LineString(1 1,2 2,3 3)';

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

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

ST_NUMPOINTS

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

SET @ls = 'LineString(1 1,2 2,3 3)';

SELECT NumPoints(GeomFromText(@ls));
+------------------------------+
| NumPoints(GeomFromText(@ls)) |
+------------------------------+
|                            3 |
+------------------------------+

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

ST_POINTN

Syntax

ST_PointN(ls,N)
PointN(ls,N)

Description

Returns the N-th Point in the LineString value ls. Points are numbered beginning with 1.

ST_PointN() and PointN() are synonyms.

Examples

SET @ls = 'LineString(1 1,2 2,3 3)';

SELECT AsText(PointN(GeomFromText(@ls),2));
+-------------------------------------+
| AsText(PointN(GeomFromText(@ls),2)) |
+-------------------------------------+
| POINT(2 2)                          |
+-------------------------------------+

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

ST_STARTPOINT

Syntax

ST_StartPoint(ls)
StartPoint(ls)

Description

Returns the Point that is the start point of theLineString value ls.

ST_StartPoint() and StartPoint() are synonyms.

Examples

SET @ls = 'LineString(1 1,2 2,3 3)';

SELECT AsText(StartPoint(GeomFromText(@ls)));
+---------------------------------------+
| AsText(StartPoint(GeomFromText(@ls))) |
+---------------------------------------+
| POINT(1 1)                            |
+---------------------------------------+

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

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.

MBR Definition

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}_{fill_help_tables.sql}

MBRContains

Syntax

MBRContains(g1,g2)

Description

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

Examples

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 |
+----------------------+----------------------+

MBRCoveredBy

MBRCoveredBy was added in MariaDB 12.0.

Syntax

MBRCoveredBy(g1, g2)

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

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 |
+------------------------+------------------------+

MBRDisjoint

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

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 |
+----------------------+

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

MBREqual

Syntax

MBREqual(g1,g2)
MBREquals(g1,g2)

Description

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

MBREquals is a synonym.

Examples

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 |
+-------------------+

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

MBRIntersects

Syntax

MBRIntersects(g1,g2)

Description

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

Examples

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 |
+------------------------+

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

MBROverlaps

Syntax

MBROverlaps(g1,g2)

Description

Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 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

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 |
+----------------------+

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

MBRTouches

Syntax

MBRTouches(g1,g2)

Description

Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 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

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 |
+---------------------+

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

MBRWithin

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

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 |
+--------------------+--------------------+

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

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.

ST_Collect

ST_Collect is available from MariaDB 12.0.

Syntax

ST_Collect(g)

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 Point values, the result is a MultiPoint 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 Point geometries aggregated into a MultiPoint geometry:

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 |
+--------+

ST_GeoHash

ST_GeoHash is available from MariaDB 12.0.

Syntax

ST_GeoHash(longitude, latitude, max_length)
ST_GeoHash(point, max_length)

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

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     |
+----------------------------------------------+---------------------+

ST_IsValid

ST_IsValid is available from MariaDB 12.0.

Syntax

ST_IsValid(g)

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

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 |
+------------------------------------------------------+

A POINT requires both x and y co-ordinates:

SELECT ST_IsValid(ST_GeomFromText('POINT (0)'));  
ERROR 3037 (22023): Invalid GIS data provided to function st_isvalid.

ST_LatFromGeoHash

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 ST_GeoHash function can be used to generate geohashes.

Examples

SELECT ST_LatFromGeoHash('zzzzzzzzz'), ST_LatFromGeoHash('xvrfxvrfxvrfxvr');
+--------------------------------+--------------------------------------+
| ST_LatFromGeoHash('zzzzzzzzz') | ST_LatFromGeoHash('xvrfxvrfxvrfxvr') |
+--------------------------------+--------------------------------------+
|                             90 |                                   30 |
+--------------------------------+--------------------------------------+

ST_LongFromGeoHash

ST_LongFromGeoHash is available from MariaDB 12.0.

Syntax

ST_LongFromGeoHash(geohash)

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 ST_GeoHash function can be used to generate geohashes.

Examples

SELECT ST_LongFromGeoHash('zzzzzzzzz'), ST_LongFromGeoHash('sj248j248j248j2');           
+---------------------------------+---------------------------------------+
| ST_LongFromGeoHash('zzzzzzzzz') | ST_LongFromGeoHash('sj248j248j248j2') |
+---------------------------------+---------------------------------------+
|                             180 |                                     0 |
+---------------------------------+---------------------------------------+

ST_PointFromGeoHash

ST_PointFromGeoHash is available from MariaDB 12.0.

Syntax

ST_PointFromGeoHash(geohash, srid)

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

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

ST_Simplify was added in MariaDB 12.0.

Syntax

ST_Simplify(g, max_distance)

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

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)                                                       |
+---------------------------------------------------------------------------------------+

ST_Validate

MariaDB startiis available from MariaDB 12.0.

Syntax

ST_Validate(g)

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:

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                                                |
+-----------------------------------------------------+

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.

X

A synonym for ST_X.

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

Y

A synonym for ST_Y.

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

ST_X

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

SET @pt = 'Point(56.7 53.34)';

SELECT X(GeomFromText(@pt));
+----------------------+
| X(GeomFromText(@pt)) |
+----------------------+
|                 56.7 |
+----------------------+

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

ST_Y

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

SET @pt = 'Point(56.7 53.34)';

SELECT Y(GeomFromText(@pt));
+----------------------+
| Y(GeomFromText(@pt)) |
+----------------------+
|                53.34 |
+----------------------+

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

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.

Geometry Statements

Geometry Constructors

BUFFER

CONVEXHULL

GEOMETRYCOLLECTION

Syntax

Description

Examples

LINESTRING

Syntax

Description

Examples

MULTILINESTRING

Syntax

Description

Example

MULTIPOINT

Syntax

Description

Examples

MULTIPOLYGON

Syntax

Description

Example

POINT

Syntax

Description

Examples

PointOnSurface

POLYGON

Syntax

Description

Examples

ST_BUFFER

Syntax

Description

Examples

ST_CONVEXHULL

Syntax

Description

Examples

ST_INTERSECTION

Syntax

Description

Examples

ST_POINTONSURFACE

Syntax

Description

ST_SYMDIFFERENCE

Syntax

Description

Examples

ST_UNION

Syntax

Description

Examples

ST_AsGeoJSON

Syntax

Description

Examples

See Also

ST_GeomFromGeoJSON

Syntax

Description

Examples

See Also

Geometry Properties

DIMENSION

BOUNDARY

ENVELOPE

GeometryN

GeometryType

IsEmpty

IsSimple

NumGeometries

SRID

IsClosed

IsRing

ST_BOUNDARY

Syntax