Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
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
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
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.
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).
Returns a geometry representing the convex hull of the given geometry. This standard function computes the smallest convex polygon enclosing the geometry.
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
NULLMultiLineString_Example:
This page is licensed: GPLv2, originally from fill_help_tables.sql
MultiLineString(ls1,ls2,...)CREATE TABLE mlstr_example (
m MULTILINESTRING
);rUses 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
ST_BUFFER(g1,r)
BUFFER(g1,r)NULLNote 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.
Non-overlapping 'polygon':
Polygon_Example:
This page is licensed: GPLv2, originally from fill_help_tables.sql
Polygon(ls1,ls2,...)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
ST_ConvexHull(g)
ConvexHull(g)SET @g = ST_GEOMFROMTEXT('Point(0 0)');
SELECT ST_ASTEXT(ST_CONVEXHULL(@g));
+------------------------------+
| ST_ASTEXT(ST_CONVEXHULL(@g)) |
+------------------------------+
| POINT(0 0) |
+------------------------------+g2This page is licensed: CC BY-SA / Gnu FDL
ST_UNION(g1,g2)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) |
+---------------------------+Point_Example:
This page is licensed: GPLv2, originally from fill_help_tables.sql
Point(x,y)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)'))));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'));g2This page is licensed: CC BY-SA / Gnu FDL
ST_INTERSECTION(g1,g2)SET @g1 = ST_GEOMFROMTEXT('POINT(2 1)');
SET @g2 = ST_GEOMFROMTEXT('LINESTRING(2 1, 0 2)');
SELECT ASTEXT(ST_INTERSECTION(@g1,@g2));
+----------------------------------+
| ASTEXT(ST_INTERSECTION(@g1,@g2)) |
+----------------------------------+
| POINT(2 1) |
+----------------------------------+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))))));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)) |
+-------------------------------------------------------------------------------------------+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('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))))));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 @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)) |
+----------------------------------------+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)) |
+------------------------------------------------+CREATE TABLE point_example (
p POINT
);INSERT INTO point_example VALUES
(ST_PointFromText('POINT(1 1)')),
(ST_PointFromText('POINT(2 2)')),
(Point(3, 3)),
(Point(4, 4));SELECT ST_AsWKT(p) FROM point_example;+-------------+
| ST_AsWKT(p) |
+-------------+
| POINT(1 1) |
| POINT(2 2) |
| POINT(3 3) |
| POINT(4 4) |
+-------------+CREATE TABLE 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)) |
+---------------------------------------------------------------+Constructs a MultiPolygon value from a list of WKB Polygon arguments. This function creates a geometry collection containing multiple polygons.
Parses a GeoJSON string and returns a geometry. This function converts a standard GeoJSON representation into a MariaDB geometry value.
Constructs a MultiPoint value from a list of WKB Point arguments. It creates a geometry collection consisting of multiple individual points.
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
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.
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)))))));1
Return an error (the default)
2 - 4
The document is accepted, but the coordinates for higher coordinate dimensions are stripped off.
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)))));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]} |
+-------------------------------------------------+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) |
+-----------------------------------+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))) |
+-------------------------------------------------------------------------------------------------------------------------------+SET @j = '{ "type": "Point", "coordinates": [5.3, 15.0]}';
SELECT ST_AsText(ST_GeomFromGeoJSON(@j));
+-----------------------------------+
| ST_AsText(ST_GeomFromGeoJSON(@j)) |
+-----------------------------------+
| POINT(5.3 15) |
+-----------------------------------+CREATE TABLE 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) |
+-----------------------------+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
CREATE TABLE linestring_example (
g LINESTRING
);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)))));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) |
+-------------------------------------------+