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...
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.
Explore miscellaneous GIS functions in MariaDB Server. This section details various SQL functions that support geographic information system operations and spatial data analysis.
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.
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.
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
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
Synonym for ST_SRID. Returns the Spatial Reference Identifier (SRID) integer associated with the geometry.
A synonym for ST_SRID.
This page is licensed: CC BY-SA / Gnu FDL
Constructs a GeometryCollection value from a list of WKB arguments. This function creates a collection containing multiple geometry objects.
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
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
Synonym for ST_ISRING. Checks if a LineString is a ring, meaning it is both closed and simple.
A synonym for .
This page is licensed: CC BY-SA / Gnu FDL
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
Synonym for ST_ENDPOINT. Returns the last point of a LineString geometry.
A synonym for .
This page is licensed: CC BY-SA / Gnu FDL
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.
Synonym for ST_X. Returns the X-coordinate value of a Point geometry as a double-precision number.
A synonym for .
This page is licensed: CC BY-SA / Gnu FDL
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.
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.
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.
This page is licensed: GPLv2, originally from fill_help_tables.sql
GeometryCollection(g1,g2,...)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'));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 |
+------------------------------------+g2iThis page is licensed: CC BY-SA / Gnu FDL
ST_Relate(g1, g2, i)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) |
+------------------------------------+g2This page is licensed: CC BY-SA / Gnu FDL
ST_SYMDIFFERENCE(g1,g2)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) |
+-----------------------------------+A POINT is always simple.
This page is licensed: CC BY-SA / Gnu FDL
ST_IsSimple(g)
IsSimple(g)SET @g = 'Point(1 2)';
SELECT ST_ISSIMPLE(GEOMFROMTEXT(@g));
+-------------------------------+
| ST_ISSIMPLE(GEOMFROMTEXT(@g)) |
+-------------------------------+
| 1 |
+-------------------------------+ST_NumGeometries() and NumGeometries() are synonyms.
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 |
+----------------------------------+The polygon is defined by the corner points of the bounding box:
ST_ENVELOPE() and ENVELOPE() are synonyms.
This page is licensed: GPLv2, originally from fill_help_tables.sql
ST_ENVELOPE(g)
ENVELOPE(g)POLYGON((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))Constructs a LineString value from a number of Point values. It creates a linear geometry connecting the specified coordinate points.
LineString(pt1,pt2,...)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.
Linestring_Example:
This page is licensed: GPLv2, originally from
Constructs a MultiPolygon value from a list of WKB Polygon arguments. This function creates a geometry collection containing multiple polygons.
Returns a geometry representing all points within a given distance of the geometry. This standard-compliant function creates a buffer zone around the object.
ST_BUFFER(g1,r)
BUFFER(g1,r)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.
Determining whether a point is within a buffer zone:
This page is licensed: CC BY-SA / Gnu FDL
Constructs a Point value using its coordinates. This basic constructor creates a point geometry from given X and Y (and optionally Z/M) values.
Point(x,y)Constructs a WKB Point using the given coordinates.
Point_Example:
This page is licensed: GPLv2, originally from
Returns the inherent dimension of the geometry value. Returns 0 for points, 1 for linear geometries, and 2 for surface geometries.
Returns a geometry representing the point set union of two geometries. This function merges multiple spatial objects into a single geometry.
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
Synonym for ST_ISCLOSED. Checks if a LineString's start and end points are the same (closed).
A synonym for ST_IsClosed.
This page is licensed: CC BY-SA / Gnu FDL
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).
Polygon(ls1,ls2,...)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.
Non-overlapping 'polygon':
Polygon_Example:
This page is licensed: GPLv2, originally from
Checks if two geometries overlap. Returns 1 if they share space but neither completely contains the other, and they have the same dimension.
ST_OVERLAPS(g1,g2)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
Returns the N-th geometry in a GeometryCollection. Geometries are numbered beginning with 1.
Returns a geometry representing the difference between two geometries. The result contains points from the first geometry that are not in the second.
Checks if two geometries touch. Returns 1 if they intersect only at their boundaries and not their interiors.
ST_TOUCHES(g1,g2)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.
This page is licensed: CC BY-SA / Gnu FDL
Synonym for ST_NUMPOINTS. Returns the number of points in a LineString geometry.
A synonym for ST_NumPoints.
This page is licensed: CC BY-SA / Gnu FDL
Check if two MBRs are disjoint. Returns 1 if the Minimum Bounding Rectangles of the two geometries do not intersect or touch at all.
MBRDisjoint(g1,g2)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.
This page is licensed: GPLv2, originally from
Decode a Geohash to retrieve the latitude. This function returns the latitude coordinate (Y-axis) from a given Geohash string.
ST_LatFromGeoHash(geohash)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.
This page is licensed: CC BY-SA / Gnu FDL
Decode a Geohash to retrieve the longitude. This function returns the longitude coordinate (X-axis) from a given Geohash string.
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.
This page is licensed: CC BY-SA / Gnu FDL
Returns the Spatial Reference Identifier (SRID) for the geometry. This integer represents the coordinate system used.
ST_SRID(g)
SRID(g)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.
This page is licensed: GPLv2, originally from
Returns a geometry representing the convex hull of the given geometry. This standard function computes the smallest convex polygon enclosing the geometry.
ST_ConvexHull(g)
ConvexHull(g)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.
The ConvexHull of a single point is simply the single point:
This page is licensed: CC BY-SA / Gnu FDL
Returns the name of the geometry type (e.g., 'POINT', 'LINESTRING', 'POLYGON') for the given geometry instance.
ST_GeometryType(g)
GeometryType(g)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.
This page is licensed: GPLv2, originally from
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 ST_BUFFER.
This page is licensed: CC BY-SA / Gnu FDL
Check if a geometry is valid. This function returns 1 if the geometry complies with OGC specifications (e.g., no self-intersections), 0 otherwise.
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 , requires valid GIS data, or ERROR 3037 (22023): Invalid GIS data provided to function st_isvalid is returned.
A requires both x and y co-ordinates:
This page is licensed: CC BY-SA / Gnu FDL
Check if two MBRs are identical. Returns 1 if the Minimum Bounding Rectangles of both geometries share the exact same coordinates.
MBREqual(g1,g2)
MBREquals(g1,g2)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.
This page is licensed: GPLv2, originally from
Synonym for ST_CONTAINS. Checks if the first geometry completely contains the second geometry, with no points of the second geometry outside the first.
Contains(g1,g2)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
Checks if two geometries intersect. Returns 1 if the geometries share any portion of space (interior or boundary), 0 otherwise.
ST_INTERSECTS(g1,g2)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 .
This page is licensed: CC BY-SA / Gnu FDL
Checks if a geometry is empty. Returns 1 if the geometry contains no points, and 0 otherwise.
ST_IsEmpty(g)
IsEmpty(g)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
Calculates the spherical distance between two points or geometries. Uses a spherical earth model to determine the minimum distance in meters.
ST_DISTANCE_SPHERE(g1,g2,[r])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.
This page is licensed: CC BY-SA / Gnu FDL
Returns the N-th Point in a LineString. This function retrieves a specific point from the sequence based on its 1-based index.
ST_PointN(ls,N)
PointN(ls,N)Returns the N-th Point in the LineString value ls. Points are numbered beginning with 1.
ST_PointN() and PointN() are synonyms.
This page is licensed: GPLv2, originally from
Returns the start Point of a LineString. This function retrieves the initial coordinate in the linear geometry sequence.
ST_StartPoint(ls)
StartPoint(ls)Returns the Point that is the start point of the LineString value ls.
ST_StartPoint() and StartPoint() are synonyms.
This page is licensed: GPLv2, originally from
Returns the end Point of a LineString. This function retrieves the final coordinate in the linear geometry sequence.
ST_EndPoint(ls)
EndPoint(ls)Returns the Point that is the endpoint of the LineString value ls.
ST_EndPoint() and EndPoint() are synonyms.
This page is licensed: GPLv2, originally from
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
Returns the count of Points in a LineString. This function calculates the total number of vertices defining the line.
ST_NumPoints(ls)
NumPoints(ls)Returns the number of Point objects in the LineString value ls.
ST_NumPoints() and NumPoints() are synonyms.
This page is licensed: GPLv2, originally from
Checks if the LineString is a ring. Returns 1 if the LineString is both closed (start equals end) and simple (no self-intersection).
ST_IsRing(g)
IsRing(g)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
Returns the Y-coordinate of a Point geometry. This function extracts the vertical coordinate value as a double-precision number.
Synonym for ST_EQUALS. Checks if two geometries are spatially equal, meaning they contain exactly the same point set.
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
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
Synonym for ST_GEOMETRYTYPE. Returns the name of the geometry type (e.g., 'POINT') of the geometry instance.
A synonym for .
This page is licensed: CC BY-SA / Gnu FDL
Synonym for ST_Y. Returns the Y-coordinate value of a Point geometry as a double-precision number.
A synonym for .
This page is licensed: CC BY-SA / Gnu FDL
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
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
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)) |
+---------------------------------------------------------------+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)) |
+----------------------------------------------------------+ST_LongFromGeoHash(geohash)ST_IsValid(g)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.
NULLMultiLineString_Example:
This page is licensed: GPLv2, originally from fill_help_tables.sql
MultiLineString(ls1,ls2,...)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))))));NULLMultiPoint_Example:
This page is licensed: GPLv2, originally from fill_help_tables.sql
MultiPoint(pt1,pt2,...)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)))));This page is licensed: CC BY-SA / Gnu FDL
ST_LENGTH(ls)SET @ls = 'LineString(1 1,2 2,3 3)';
SELECT ST_LENGTH(ST_GeomFromText(@ls));
+---------------------------------+
| ST_LENGTH(ST_GeomFromText(@ls)) |
+---------------------------------+
| 2.82842712474619 |
+---------------------------------+g2This page is licensed: GPLv2, originally from fill_help_tables.sql
MBROverlaps(g1,g2)1
Return an error (the default)
2 - 4
The document is accepted, but the coordinates for higher coordinate dimensions are stripped off.
This page is licensed: CC BY-SA / Gnu FDL
ST_GeomFromGeoJSON(g[, option])0g1g2OVERLAPS() 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)0g1g2INTERSECTS() 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)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.
This page is licensed: CC BY-SA / Gnu FDL
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.
A POINT requires both x and y co-ordinates:
This page is licensed: CC BY-SA / Gnu FDL
g2This page is licensed: GPLv2, originally from fill_help_tables.sql
MBRTouches(g1,g2)g1g2WITHIN() is based on the original MySQL implementation, and uses object bounding rectangles, while ST_WITHIN() uses object shapes.
This page is licensed: GPLv2, originally from fill_help_tables.sql
Within(g1,g2)ST_IsClosed() and IsClosed() are synonyms.This page is licensed: CC BY-SA / Gnu FDL
ST_IsClosed(g)
IsClosed(g)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 |
+--------------------------------+ST_LENGTH() is the OpenGIS equivalent.
This page is licensed: GPLv2, originally from fill_help_tables.sql
GLength(ls)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.
This page is licensed: CC BY-SA / Gnu FDL
ST_X() and X() are synonyms.
This page is licensed: GPLv2, originally from fill_help_tables.sql
ST_X(p)
X(p)SET @pt = 'Point(56.7 53.34)';
SELECT X(GeomFromText(@pt));
+----------------------+
| X(GeomFromText(@pt)) |
+----------------------+
| 56.7 |
+----------------------+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.
This page is licensed: CC BY-SA / Gnu FDL
MBRCoveredBy(g1, g2)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)))));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)))))));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 |
+---------------------------------+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)'))));CREATE TABLE point_example (
p POINT
);-1
empty geometry
0
geometry with no length or area
1
geometry with no area but nonzero length
2
geometry with nonzero area
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 @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))))));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) |
+----------------------------------------+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(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 |
+---------------------+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 |
+----------------------+SELECT SRID(GeomFromText('LineString(1 1,2 2)',101));
+-----------------------------------------------+
| SRID(GeomFromText('LineString(1 1,2 2)',101)) |
+-----------------------------------------------+
| 101 |
+-----------------------------------------------+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)) |
+------------------------------+SELECT GeometryType(GeomFromText('POINT(1 1)'));
+------------------------------------------+
| GeometryType(GeomFromText('POINT(1 1)')) |
+------------------------------------------+
| POINT |
+------------------------------------------+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 |
+-------------------+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 @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 |
+---------------------------------------------+SET @ls = 'LineString(1 1,2 2,3 3)';
SELECT AsText(PointN(GeomFromText(@ls),2));
+-------------------------------------+
| AsText(PointN(GeomFromText(@ls),2)) |
+-------------------------------------+
| POINT(2 2) |
+-------------------------------------+SET @ls = 'LineString(1 1,2 2,3 3)';
SELECT AsText(StartPoint(GeomFromText(@ls)));
+---------------------------------------+
| AsText(StartPoint(GeomFromText(@ls))) |
+---------------------------------------+
| POINT(1 1) |
+---------------------------------------+SET @ls = 'LineString(1 1,2 2,3 3)';
SELECT AsText(EndPoint(GeomFromText(@ls)));
+-------------------------------------+
| AsText(EndPoint(GeomFromText(@ls))) |
+-------------------------------------+
| POINT(3 3) |
+-------------------------------------+SET @ls = 'LineString(1 1,2 2,3 3)';
SELECT NumPoints(GeomFromText(@ls));
+------------------------------+
| NumPoints(GeomFromText(@ls)) |
+------------------------------+
| 3 |
+------------------------------+SET @pt = 'Point(56.7 53.34)';
SELECT Y(GeomFromText(@pt));
+----------------------+
| Y(GeomFromText(@pt)) |
+----------------------+
| 53.34 |
+----------------------+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.
MBRContains(g1,g2)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().
This page is licensed: GPLv2, originally from
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.
ST_WITHIN(g1,g2)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.
This page is licensed: CC BY-SA / Gnu FDL
Checks if geometry A contains geometry B. Returns 1 if B is completely inside A, 0 otherwise.
ST_CONTAINS(g1,g2)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 .
This page is licensed: CC BY-SA / Gnu FDL
Check if two MBRs intersect. Returns 1 if the Minimum Bounding Rectangles of the geometries share any portion of space, including boundaries.
MBRIntersects(g1,g2)Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 intersect.
This page is licensed: GPLv2, originally from
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) |
+-----------------------------+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 @j = '{ "type": "Point", "coordinates": [5.3, 15.0]}';
SELECT ST_AsText(ST_GeomFromGeoJSON(@j));
+-----------------------------------+
| ST_AsText(ST_GeomFromGeoJSON(@j)) |
+-----------------------------------+
| POINT(5.3 15) |
+-----------------------------------+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 |
+----------------------------------------------+---------------------+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 |
+-----------------------------------------------------+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 |
+---------------------+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 |
+-----------------+SET @ls = 'LineString(1 1,2 2,3 3)';
SELECT GLength(GeomFromText(@ls));
+----------------------------+
| GLength(GeomFromText(@ls)) |
+----------------------------+
| 2.82842712474619 |
+----------------------------+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 @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 |
+------------------------+------------------------+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) |
+-------------------------------------------+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))) |
+-------------------------------------------------------------------------------------------------------------------------------+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) |
+-------------+SELECT Dimension(GeomFromText('LineString(1 1,2 2)'));
+------------------------------------------------+
| Dimension(GeomFromText('LineString(1 1,2 2)')) |
+------------------------------------------------+
| 1 |
+------------------------------------------------+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 @g1 = ST_GEOMFROMTEXT('POINT(0 2)');
SET @g1 = ST_GEOMFROMTEXT('POINT(2 0)');
SELECT ST_EQUALS(@g1,@g2);
+--------------------+
| ST_EQUALS(@g1,@g2) |
+--------------------+
| 0 |
+--------------------+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 |
+---------------------------------+---------------------------------------+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)) |
+----------------------------------------+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.SET @g2 = ST_GEOMFROMTEXT('LINESTRING(2 0, 0 2)');
SELECT ST_INTERSECTS(@g1,@g2);
+------------------------+
| ST_INTERSECTS(@g1,@g2) |
+------------------------+
| 0 |
+------------------------+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.
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 |
+--------------------+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 |
+----------------------+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 |
+------------------------+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.
Multiple Point geometries aggregated into a MultiPoint geometry:
This page is licensed: CC BY-SA / Gnu FDL
This page is licensed: GPLv2, originally from fill_help_tables.sql
MBRWithin(g1,g2)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 |
+----------------------+----------------------+ST_Collect(g)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('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 |
+--------------------+--------------------+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.
Crosses(g1,g2)Returns 1 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 uses object shapes.
This page is licensed: GPLv2, originally from
Generates a GeoJSON object from a given geometry. This function converts internal geometry data into the standard JSON-based format for web mapping.
ST_AsGeoJSON(g[, max_decimals[, options]])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.
This page is licensed: CC BY-SA / Gnu FDL
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]} |
+-------------------------------------------------+0g1g2TOUCHES() 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
Touches(g1,g2)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.
This page is licensed: CC BY-SA / Gnu FDL
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) |
+---------------------------------------------------------+Returns a geometry representing the point set intersection of two geometries. It outputs the spatial region shared by both input objects.
Returns the combinatorial boundary of a geometry. For a Polygon, this is the line string defining the boundary.
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_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) |
+--------------------------------------------------------------------------+0g1g2DISJOINT() 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
Disjoint(g1,g2)g1g2ST_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().
This page is licensed: CC BY-SA / Gnu FDL
ST_DISJOINT(g1,g2)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 |
+----------------------+g1g2NULLg1g2 is a 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.
This page is licensed: CC BY-SA / Gnu FDL
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 |
+---------------------+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.