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.
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 ST_CONVEXHULL.
This page is licensed: CC BY-SA / Gnu FDL
Returns a geometry representing the point set union of two geometries. This function merges multiple spatial objects into a single geometry.
Constructs a MultiPolygon value from a list of WKB Polygon arguments. This function creates a geometry collection containing multiple polygons.
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
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 ST_PointOnSurface.
This page is licensed: CC BY-SA / Gnu FDL
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)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 @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)) |
+------------------------------------------------+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)))))));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]} |
+-------------------------------------------------+Returns a geometry representing the point set symmetric difference of two geometries. It outputs the spatial area present in either geometry but not in both.
Constructs a LineString value from a number of Point values. It creates a linear geometry connecting the specified coordinate points.
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 @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) |
+-----------------------------------+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)'))));NULLNULL.Linestring_Example:
This page is licensed: GPLv2, originally from fill_help_tables.sql
LineString(pt1,pt2,...)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) |
+-------------+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 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) |
+-------------------------------------------+Constructs a MultiLineString value from a list of WKB LineString arguments. This function creates a collection of multiple line strings.
MultiLineString(ls1,ls2,...)Constructs a WKB MultiLineString value using WKB LineString arguments. If any argument is not a WKB LineString, the return value isNULL.
MultiLineString_Example:
This page is licensed: GPLv2, originally from
Constructs a MultiPoint value from a list of WKB Point arguments. It creates a geometry collection consisting of multiple individual points.
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.
Returns a geometry representing the convex hull of the given geometry. This standard function computes the smallest convex polygon enclosing the geometry.
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))))));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)))));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_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_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 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) |
+-----------------------------+ST_GeomFromGeoJSON(g[, option])SET @j = '{ "type": "Point", "coordinates": [5.3, 15.0]}';
SELECT ST_AsText(ST_GeomFromGeoJSON(@j));
+-----------------------------------+
| ST_AsText(ST_GeomFromGeoJSON(@j)) |
+-----------------------------------+
| POINT(5.3 15) |
+-----------------------------------+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)) |
+----------------------------------------+Constructs a GeometryCollection value from a list of WKB arguments. This function creates a collection containing multiple geometry objects.
GeometryCollection(g1,g2,...)Constructs a WKB GeometryCollection. If any argument is not a well-formed WKB representation of a geometry, the return value is NULL.
This page is licensed: GPLv2, originally from
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
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'));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))))));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 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)) |
+------------------------------------------------------+