Explore geometric data types and functions in MariaDB Server. This section details how to store, query, and manipulate spatial data, enabling geospatial applications within your database.
GIS stands for Geographic Information System.
Further reading:
OGC Simple Feature Access - the Open Geospatial Consortium's OpenGIS Simple Features Specifications For SQL.
Geo/Spatial Search with MySQL - a presentation by Alexander Rubin, from the MySQL Conference in 2006.
There are no differences between GIS in stable versions of MariaDB and GIS in MySQL. There are, however, some extensions and enhancements being worked on.
This page is licensed: CC BY-SA / Gnu FDL
Geometry is the base class. It is an abstract class. The instantiable subclasses of Geometry are restricted to zero-, one-, and two-dimensional geometric objects that exist in two-dimensional coordinate space. All instantiable geometry classes are defined so that valid instances of a geometry class are topologically closed (that is, all defined geometries include their boundary).
The base Geometry class has subclasses for Point, Curve, Surface, and GeometryCollection:
represents zero-dimensional objects.
On , and tables, MariaDB can create spatial indexes (an R-tree index) using syntax similar to that for creating regular indexes, but extended with the SPATIAL keyword. Columns in spatial indexes must be declared NOT NULL.
Spatial indexes can be created when the table is created, or added after the fact:
SPATIAL INDEX creates an R-tree
Curve represents one-dimensional objects, and has a subclass LineString, with sub-subclasses Line and LinearRing.
Surface is designed for two-dimensional objects and has a subclass Polygon.
GeometryCollection has specialized zero-, one-, and two-dimensional collection classes named MultiPoint, MultiLineString, and MultiPolygon for modeling geometries corresponding to collections of Points, LineStrings, and Polygons, respectively. MultiCurve and MultiSurface are introduced as abstract superclasses that generalize the collection interfaces to handle Curves and Surfaces.
Geometry, Curve, Surface, MultiCurve, and MultiSurface are defined as non-instantiable classes. They define a common set of methods for their subclasses and are included for extensibility.
Point, LineString, Polygon, GeometryCollection, MultiPoint,MultiLineString, and MultiPolygon are instantiable classes.
This page is licensed: GPLv2, originally from fill_help_tables.sql
B-treeB-treeFor more information on indexing spatial columns, see CREATE INDEX.
To drop spatial indexes, use ALTER TABLE or DROP INDEX:
If innodb_checksum_algorithm is set to full_crc32 or strict_full_crc32, and if the table does not use ROW_FORMAT=COMPRESSED, InnoDB spatial indexes are encrypted if the table is encrypted.
See MDEV-12026 for more information.
This page is licensed: GPLv2, originally from fill_help_tables.sql
GIS stands for .
MySQL operates on spatial data based on the OpenGIS standards, particularly the (Simple feature access, SQL option).
Initial support was based on version 05-134 of the standard. MariaDB implements a subset of the 'SQL with Geometry Types' environment proposed by the OGC. And the SQL environment was extended with a set of geometry types.
MariaDB supports spatial extensions to operate on spatial features. These features are available for , , , NDB, and tables.
For spatial columns, Aria and MyISAM supports both and non-SPATIAL indexes. Other storage engines support non-SPATIAL indexes.
We aim at meeting the OpenGIS requirements. One thing missed in previous MariaDB versions is that the functions which check spatial relations didn't consider the actual shape of an object, instead they operate only on their bounding rectangles. These legacy functions have been left as they are and new, properly-working functions are named with an 'ST_' prefix, in accordance with the latest OpenGIS requirements. Also, operations over geometry features were added.
CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g));ALTER TABLE geom ADD SPATIAL INDEX(g);CREATE SPATIAL INDEX sp_index ON geom (g);ALTER TABLE geom DROP INDEX g;DROP INDEX sp_index ON geom;Spatial operators produce new geometries.
union of A and B
intersection of A and B
symdifference, notintersecting parts of A and B
returns the shape of the area that lies in 'radius' distance from the shape A.
Predicates return a boolean result of the relationship.
if A and B have an intersection
if A and B cross
if A and B are equal
if A lies within B
if B lies within A
if A and B have no intersection
This page is licensed: CC BY-SA / Gnu FDL
if A touches B
GEOMETRY
Note: For clarity, only one type is listed per table in the examples below, but a table row can contain multiple types.
This page is licensed: GPLv2, originally from fill_help_tables.sql
CREATE TABLE object (shapeA POLYGON, shapeB LINESTRING);INSERT INTO geometry_example VALUES
(Point(0, 0)),
(ST_PolygonFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))')),
(ST_LineFromText('LINESTRING(0 0, 1 1, 2 2)')),
(ST_MultiLineStringFromText(
'MULTILINESTRING((0 1, 0 2, 0 3),
(1 0, 2 0, 3 0))')),
(ST_MultiPointFromText('MULTIPOINT(0 0, 1 1, 0 1, 1 0)')),
(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)))'));SELECT ST_AsWKT(g) FROM geometry_example;
+-----------------------------------------------------------------------------------------------+
| ST_AsWKT(g) |
+-----------------------------------------------------------------------------------------------+
| POINT(0 0) |
| POLYGON((0 0,0 1,1 1,1 0,0 0)) |
| LINESTRING(0 0,1 1,2 2) |
| MULTILINESTRING((0 1,0 2,0 3),(1 0,2 0,3 0)) |
| MULTIPOINT(0 0,1 1,0 1,1 0) |
| 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))) |
+-----------------------------------------------------------------------------------------------+CREATE TABLE gis_point (g POINT);
SHOW FIELDS FROM gis_point;
INSERT INTO gis_point VALUES
(PointFromText('POINT(10 10)')),
(PointFromText('POINT(20 10)')),
(PointFromText('POINT(20 20)')),
(PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
<</sql>>
=== [[linestring|LINESTRING]]
<<code lang=sql inline=false wrap=true>>
CREATE TABLE gis_line (g LINESTRING);
SHOW FIELDS FROM gis_line;
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_polygon (g POLYGON);
SHOW FIELDS FROM gis_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 gis_multi_point (g MULTIPOINT);
SHOW FIELDS FROM gis_multi_point;
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)))));CREATE TABLE gis_multi_line (g MULTILINESTRING);
SHOW FIELDS FROM gis_multi_line;
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))))));CREATE TABLE gis_multi_polygon (g MULTIPOLYGON);
SHOW FIELDS FROM gis_multi_polygon;
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)))))));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 gis_geometry (g GEOMETRY);
SHOW FIELDS FROM gis_geometry;
INSERT INTO gis_geometry SELECT * FROM gis_point;
INSERT INTO gis_geometry SELECT * FROM gis_line;
INSERT INTO gis_geometry SELECT * FROM gis_polygon;
INSERT INTO gis_geometry SELECT * FROM gis_multi_point;
INSERT INTO gis_geometry SELECT * FROM gis_multi_line;
INSERT INTO gis_geometry SELECT * FROM gis_multi_polygon;
INSERT INTO gis_geometry SELECT * FROM gis_geometrycollection;