Menu:
using-spatial-indexes:: Using Spatial Indexes
The Open Geospatial Consortium (http://www.opengeospatial.org) (OGC) is an international consortium of more than 250 companies, agencies, and universities participating in the development of publicly available conceptual solutions that can be useful with all kinds of applications that manage spatial data.
The Open Geospatial Consortium publishes the 'OpenGIS(R) Implementation Standard for Geographic information - Simple Feature Access - Part 2: SQL Option', a document that proposes several conceptual ways for extending an SQL RDBMS to support spatial data. This specification is available from the OGC website at http://www.opengeospatial.org/standards/sfs.
Following the OGC specification, MySQL implements spatial extensions as a subset of the SQL with Geometry Types environment. This term refers to an SQL environment that has been extended with a set of geometry types. A geometry-valued SQL column is implemented as a column that has a geometry type. The specification describes a set of SQL geometry types, as well as functions on those types to create and analyze geometry values.
MySQL spatial extensions enable the generation, storage, and analysis of geographic features:
Data types for representing spatial values
Functions for manipulating spatial values
Spatial indexing for improved access times to spatial columns
The spatial data types and functions are available for note 'MyISAM': myisam-storage-engine, note 'InnoDB': innodb-storage-engine, note 'NDB': mysql-cluster, and note 'ARCHIVE': archive-storage-engine. tables. For indexing spatial columns, 'MyISAM' and 'InnoDB' support both 'SPATIAL' and non-'SPATIAL' indexes. The other storage engines support non-'SPATIAL' indexes, as described in *note create-index::.
A geographic feature is anything in the world that has a location. A feature can be:
An entity. For example, a mountain, a pond, a city.
A space. For example, town district, the tropics.
A definable location. For example, a crossroad, as a particular place where two streets intersect.
Some documents use the term geospatial feature to refer to geographic features.
Geometry is another word that denotes a geographic feature. Originally the word geometry meant measurement of the earth. Another meaning comes from cartography, referring to the geometric features that cartographers use to map the world.
The discussion here considers these terms synonymous: geographic feature, geospatial feature, feature, or geometry. The term most commonly used is geometry, defined as a point or an aggregate of points representing anything in the world that has a location.
The following material covers these topics:
The spatial data types implemented in MySQL model
The basis of the spatial extensions in the OpenGIS geometry model
Data formats for representing spatial data
How to use spatial data in MySQL
Use of indexing for spatial data
MySQL differences from the OpenGIS specification
For information about functions that operate on spatial data, see *note spatial-analysis-functions::.
MySQL GIS Conformance and Compatibility
MySQL does not implement the following GIS features:
Additional Metadata Views
OpenGIS specifications propose several additional metadata views. For example, a system view named 'GEOMETRY_COLUMNS' contains a description of geometry columns, one row for each geometry column in the database.
The OpenGIS function 'Length()' on 'LineString' and 'MultiLineString' should be called in MySQL as 'ST_Length()'
The problem is that there is an existing SQL function 'Length()' that calculates the length of string values, and sometimes it is not possible to distinguish whether the function is called in a textual or spatial context.
Additional Resources
The Open Geospatial Consortium publishes the 'OpenGIS(R) Implementation Standard for Geographic information - Simple feature access - Part 2: SQL option', a document that proposes several conceptual ways for extending an SQL RDBMS to support spatial data. The Open Geospatial Consortium (OGC) maintains a website at http://www.opengeospatial.org/. The specification is available there at http://www.opengeospatial.org/standards/sfs. It contains additional information relevant to the material here.
If you have questions or concerns about the use of the spatial extensions to MySQL, you can discuss them in the GIS forum: https://forums.mysql.com/list.php?23.
File: manual.info.tmp, Node: spatial-type-overview, Next: opengis-geometry-model, Prev: spatial-types, Up: spatial-types
MySQL has spatial data types that correspond to OpenGIS classes. The basis for these types is described in *note opengis-geometry-model::.
Some spatial data types hold single geometry values:
'GEOMETRY'
'POINT'
'LINESTRING'
'POLYGON'
'GEOMETRY' can store geometry values of any type. The other single-value types ('POINT', 'LINESTRING', and 'POLYGON') restrict their values to a particular geometry type.
The other spatial data types hold collections of values:
'MULTIPOINT'
'MULTILINESTRING'
'MULTIPOLYGON'
'GEOMETRYCOLLECTION'
'GEOMETRYCOLLECTION' can store a collection of objects of any type. The other collection types ('MULTIPOINT', 'MULTILINESTRING', and 'MULTIPOLYGON') restrict collection members to those having a particular geometry type.
Example: To create a table named 'geom' that has a column named 'g' that can store values of any geometry type, use this statement:
CREATE TABLE geom (g GEOMETRY);'SPATIAL' indexes can be created on 'NOT NULL' spatial columns, so if you plan to index the column, declare it 'NOT NULL':
CREATE TABLE geom (g GEOMETRY NOT NULL);For other examples showing how to use spatial data types in MySQL, see *note creating-spatial-columns::.
File: manual.info.tmp, Node: opengis-geometry-model, Next: gis-data-formats, Prev: spatial-type-overview, Up: spatial-types
Menu:
gis-class-multipolygon:: MultiPolygon Class
The set of geometry types proposed by OGC's SQL with Geometry Types environment is based on the OpenGIS Geometry Model. In this model, each geometric object has the following general properties:
It is associated with a spatial reference system, which describes the coordinate space in which the object is defined.
It belongs to some geometry class.
File: manual.info.tmp, Node: gis-geometry-class-hierarchy, Next: gis-class-geometry, Prev: opengis-geometry-model, Up: opengis-geometry-model
11.4.2.1 The Geometry Class Hierarchy .....................................
The geometry classes define a hierarchy as follows:
'Geometry' (noninstantiable)
* 'Point' (instantiable)
* 'Curve' (noninstantiable)
* 'LineString' (instantiable)
* 'Line'
* 'LinearRing'
* 'Surface' (noninstantiable)
* 'Polygon' (instantiable)
* 'GeometryCollection' (instantiable)
* 'MultiPoint' (instantiable)
* 'MultiCurve' (noninstantiable)
* 'MultiLineString' (instantiable)
* 'MultiSurface' (noninstantiable)
* 'MultiPolygon' (instantiable)It is not possible to create objects in noninstantiable classes. It is possible to create objects in instantiable classes. All classes have properties, and instantiable classes may also have assertions (rules that define valid class instances).
'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':
'Point' represents zero-dimensional objects.
'Curve' represents one-dimensional objects, and has subclass 'LineString', with sub-subclasses 'Line' and 'LinearRing'.
'Surface' is designed for two-dimensional objects and has 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 noninstantiable 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.
File: manual.info.tmp, Node: gis-class-geometry, Next: gis-class-point, Prev: gis-geometry-class-hierarchy, Up: opengis-geometry-model
11.4.2.2 Geometry Class .......................
'Geometry' is the root class of the hierarchy. It is a noninstantiable class but has a number of properties, described in the following list, that are common to all geometry values created from any of the 'Geometry' subclasses. Particular subclasses have their own specific properties, described later.
Geometry Properties
A geometry value has the following properties:
Its type. Each geometry belongs to one of the instantiable classes in the hierarchy.
Its SRID, or spatial reference identifier. This value identifies the geometry's associated spatial reference system that describes the coordinate space in which the geometry object is defined.
In MySQL, the SRID value is an integer associated with the geometry value. The maximum usable SRID value is 2^32−1. If a larger value is given, only the lower 32 bits are used. All computations are done assuming SRID 0, regardless of the actual SRID value. SRID 0 represents an infinite flat Cartesian plane with no units assigned to its axes.
Its coordinates in its spatial reference system, represented as double-precision (8-byte) numbers. All nonempty geometries include at least one pair of (X,Y) coordinates. Empty geometries contain no coordinates.
Coordinates are related to the SRID. For example, in different coordinate systems, the distance between two objects may differ even when objects have the same coordinates, because the distance on the planar coordinate system and the distance on the geodetic system (coordinates on the Earth's surface) are different things.
Its interior, boundary, and exterior.
Every geometry occupies some position in space. The exterior of a geometry is all space not occupied by the geometry. The interior is the space occupied by the geometry. The boundary is the interface between the geometry's interior and exterior.
Its MBR (minimum bounding rectangle), or envelope. This is the bounding geometry, formed by the minimum and maximum (X,Y) coordinates:
((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))Whether the value is simple or nonsimple. Geometry values of types ('LineString', 'MultiPoint', 'MultiLineString') are either simple or nonsimple. Each type determines its own assertions for being simple or nonsimple.
Whether the value is closed or not closed. Geometry values of types ('LineString', 'MultiString') are either closed or not closed. Each type determines its own assertions for being closed or not closed.
Whether the value is empty or nonempty A geometry is empty if it does not have any points. Exterior, interior, and boundary of an empty geometry are not defined (that is, they are represented by a 'NULL' value). An empty geometry is defined to be always simple and has an area of 0.
Its dimension. A geometry can have a dimension of −1, 0, 1, or 2:
* −1 for an empty geometry.
* 0 for a geometry with no length and no area.
* 1 for a geometry with nonzero length and zero area.
* 2 for a geometry with nonzero area.'Point' objects have a dimension of zero. 'LineString' objects have a dimension of 1. 'Polygon' objects have a dimension of 2. The dimensions of 'MultiPoint', 'MultiLineString', and 'MultiPolygon' objects are the same as the dimensions of the elements they consist of.
File: manual.info.tmp, Node: gis-class-point, Next: gis-class-curve, Prev: gis-class-geometry, Up: opengis-geometry-model
11.4.2.3 Point Class ....................
A 'Point' is a geometry that represents a single location in coordinate space.
'Point' Examples
Imagine a large-scale map of the world with many cities. A 'Point' object could represent each city.
On a city map, a 'Point' object could represent a bus stop.
'Point' Properties
X-coordinate value.
Y-coordinate value.
'Point' is defined as a zero-dimensional geometry.
The boundary of a 'Point' is the empty set.
File: manual.info.tmp, Node: gis-class-curve, Next: gis-class-linestring, Prev: gis-class-point, Up: opengis-geometry-model
11.4.2.4 Curve Class ....................
A 'Curve' is a one-dimensional geometry, usually represented by a sequence of points. Particular subclasses of 'Curve' define the type of interpolation between points. 'Curve' is a noninstantiable class.
'Curve' Properties
A 'Curve' has the coordinates of its points.
A 'Curve' is defined as a one-dimensional geometry.
A 'Curve' is simple if it does not pass through the same point twice, with the exception that a curve can still be simple if the start and end points are the same.
A 'Curve' is closed if its start point is equal to its endpoint.
The boundary of a closed 'Curve' is empty.
The boundary of a nonclosed 'Curve' consists of its two endpoints.
A 'Curve' that is simple and closed is a 'LinearRing'.
File: manual.info.tmp, Node: gis-class-linestring, Next: gis-class-surface, Prev: gis-class-curve, Up: opengis-geometry-model
11.4.2.5 LineString Class .........................
A 'LineString' is a 'Curve' with linear interpolation between points.
'LineString' Examples
On a world map, 'LineString' objects could represent rivers.
In a city map, 'LineString' objects could represent streets.
'LineString' Properties
A 'LineString' has coordinates of segments, defined by each consecutive pair of points.
A 'LineString' is a 'Line' if it consists of exactly two points.
A 'LineString' is a 'LinearRing' if it is both closed and simple.
File: manual.info.tmp, Node: gis-class-surface, Next: gis-class-polygon, Prev: gis-class-linestring, Up: opengis-geometry-model
11.4.2.6 Surface Class ......................
A 'Surface' is a two-dimensional geometry. It is a noninstantiable class. Its only instantiable subclass is 'Polygon'.
Simple surfaces in three-dimensional space are isomorphic to planar surfaces.
Polyhedral surfaces are formed by 'stitching' together simple surfaces along their boundaries, polyhedral surfaces in three-dimensional space may not be planar as a whole.
'Surface' Properties
A 'Surface' is defined as a two-dimensional geometry.
The OpenGIS specification defines a simple 'Surface' as a geometry that consists of a single 'patch' that is associated with a single exterior boundary and zero or more interior boundaries.
The boundary of a simple 'Surface' is the set of closed curves corresponding to its exterior and interior boundaries.
File: manual.info.tmp, Node: gis-class-polygon, Next: gis-class-geometrycollection, Prev: gis-class-surface, Up: opengis-geometry-model
11.4.2.7 Polygon Class ......................
A 'Polygon' is a planar 'Surface' representing a multisided geometry. It is defined by a single exterior boundary and zero or more interior boundaries, where each interior boundary defines a hole in the 'Polygon'.
'Polygon' Examples
'Polygon' Assertions
The boundary of a 'Polygon' consists of a set of 'LinearRing' objects (that is, 'LineString' objects that are both simple and closed) that make up its exterior and interior boundaries.
A 'Polygon' has no rings that cross. The rings in the boundary of a 'Polygon' may intersect at a 'Point', but only as a tangent.
A 'Polygon' has no lines, spikes, or punctures.
A 'Polygon' has an interior that is a connected point set.
A 'Polygon' may have holes. The exterior of a 'Polygon' with holes is not connected. Each hole defines a connected component of the exterior.
The preceding assertions make a 'Polygon' a simple geometry.
File: manual.info.tmp, Node: gis-class-geometrycollection, Next: gis-class-multipoint, Prev: gis-class-polygon, Up: opengis-geometry-model
11.4.2.8 GeometryCollection Class .................................
A 'GeometryCollection' is a geometry that is a collection of zero or more geometries of any class.
All the elements in a geometry collection must be in the same spatial reference system (that is, in the same coordinate system). There are no other constraints on the elements of a geometry collection, although the subclasses of 'GeometryCollection' described in the following sections may restrict membership. Restrictions may be based on:
Element type (for example, a 'MultiPoint' may contain only 'Point' elements)
Dimension
Constraints on the degree of spatial overlap between elements
File: manual.info.tmp, Node: gis-class-multipoint, Next: gis-class-multicurve, Prev: gis-class-geometrycollection, Up: opengis-geometry-model
11.4.2.9 MultiPoint Class .........................
A 'MultiPoint' is a geometry collection composed of 'Point' elements. The points are not connected or ordered in any way.
'MultiPoint' Examples
On a world map, a 'MultiPoint' could represent a chain of small islands.
On a city map, a 'MultiPoint' could represent the outlets for a ticket office.
'MultiPoint' Properties
A 'MultiPoint' is a zero-dimensional geometry.
A 'MultiPoint' is simple if no two of its 'Point' values are equal (have identical coordinate values).
The boundary of a 'MultiPoint' is the empty set.
File: manual.info.tmp, Node: gis-class-multicurve, Next: gis-class-multilinestring, Prev: gis-class-multipoint, Up: opengis-geometry-model
11.4.2.10 MultiCurve Class ..........................
A 'MultiCurve' is a geometry collection composed of 'Curve' elements. 'MultiCurve' is a noninstantiable class.
'MultiCurve' Properties
A 'MultiCurve' is a one-dimensional geometry.
A 'MultiCurve' is simple if and only if all of its elements are simple; the only intersections between any two elements occur at points that are on the boundaries of both elements.
A 'MultiCurve' boundary is obtained by applying the 'mod 2 union rule' (also known as the 'odd-even rule'): A point is in the boundary of a 'MultiCurve' if it is in the boundaries of an odd number of 'Curve' elements.
A 'MultiCurve' is closed if all of its elements are closed.
The boundary of a closed 'MultiCurve' is always empty.
File: manual.info.tmp, Node: gis-class-multilinestring, Next: gis-class-multisurface, Prev: gis-class-multicurve, Up: opengis-geometry-model
11.4.2.11 MultiLineString Class ...............................
A 'MultiLineString' is a 'MultiCurve' geometry collection composed of 'LineString' elements.
'MultiLineString' Examples
File: manual.info.tmp, Node: gis-class-multisurface, Next: gis-class-multipolygon, Prev: gis-class-multilinestring, Up: opengis-geometry-model
11.4.2.12 MultiSurface Class ............................
A 'MultiSurface' is a geometry collection composed of surface elements. 'MultiSurface' is a noninstantiable class. Its only instantiable subclass is 'MultiPolygon'.
'MultiSurface' Assertions
Surfaces within a 'MultiSurface' have no interiors that intersect.
Surfaces within a 'MultiSurface' have boundaries that intersect at most at a finite number of points.
File: manual.info.tmp, Node: gis-class-multipolygon, Prev: gis-class-multisurface, Up: opengis-geometry-model
11.4.2.13 MultiPolygon Class ............................
A 'MultiPolygon' is a 'MultiSurface' object composed of 'Polygon' elements.
'MultiPolygon' Examples
'MultiPolygon' Assertions
A 'MultiPolygon' has no two 'Polygon' elements with interiors that intersect.
A 'MultiPolygon' has no two 'Polygon' elements that cross (crossing is also forbidden by the previous assertion), or that touch at an infinite number of points.
A 'MultiPolygon' may not have cut lines, spikes, or punctures. A 'MultiPolygon' is a regular, closed point set.
A 'MultiPolygon' that has more than one 'Polygon' has an interior that is not connected. The number of connected components of the interior of a 'MultiPolygon' is equal to the number of 'Polygon' values in the 'MultiPolygon'.
'MultiPolygon' Properties
A 'MultiPolygon' is a two-dimensional geometry.
A 'MultiPolygon' boundary is a set of closed curves ('LineString' values) corresponding to the boundaries of its 'Polygon' elements.
Each 'Curve' in the boundary of the 'MultiPolygon' is in the boundary of exactly one 'Polygon' element.
Every 'Curve' in the boundary of an 'Polygon' element is in the boundary of the 'MultiPolygon'.
File: manual.info.tmp, Node: gis-data-formats, Next: geometry-well-formedness-validity, Prev: opengis-geometry-model, Up: spatial-types
Two standard spatial data formats are used to represent geometry objects in queries:
Well-Known Text (WKT) format
Well-Known Binary (WKB) format
Internally, MySQL stores geometry values in a format that is not identical to either WKT or WKB format. (Internal format is like WKB but with an initial 4 bytes to indicate the SRID.)
There are functions available to convert between different data formats; see *note gis-format-conversion-functions::.
The following sections describe the spatial data formats MySQL uses:
*note gis-wkt-format::
*note gis-wkb-format::
*note gis-internal-format::
Well-Known Text (WKT) Format
The Well-Known Text (WKT) representation of geometry values is designed for exchanging geometry data in ASCII form. The OpenGIS specification provides a Backus-Naur grammar that specifies the formal production rules for writing WKT values (see *note spatial-types::).
Examples of WKT representations of geometry objects:
A 'Point':
POINT(15 20)The point coordinates are specified with no separating comma. This differs from the syntax for the SQL 'Point()' function, which requires a comma between the coordinates. Take care to use the syntax appropriate to the context of a given spatial operation. For example, the following statements both use 'ST_X()' to extract the X-coordinate from a 'Point' object. The first produces the object directly using the 'Point()' function. The second uses a WKT representation converted to a 'Point' with 'ST_GeomFromText()'.
mysql> SELECT ST_X(Point(15, 20));
+---------------------+
| ST_X(POINT(15, 20)) |
+---------------------+
| 15 |
+---------------------+
mysql> SELECT ST_X(ST_GeomFromText('POINT(15 20)'));
+---------------------------------------+
| ST_X(ST_GeomFromText('POINT(15 20)')) |
+---------------------------------------+
| 15 |
+---------------------------------------+A 'LineString' with four points:
LINESTRING(0 0, 10 10, 20 25, 50 60)The point coordinate pairs are separated by commas.
A 'Polygon' with one exterior ring and one interior ring:
POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))A 'MultiPoint' with three 'Point' values:
MULTIPOINT(0 0, 20 20, 60 60)As of MySQL 5.7.9, spatial functions such as 'ST_MPointFromText()' and 'ST_GeomFromText()' that accept WKT-format representations of 'MultiPoint' values permit individual points within values to be surrounded by parentheses. For example, both of the following function calls are valid, whereas before MySQL 5.7.9 the second one produces an error:
ST_MPointFromText('MULTIPOINT (1 1, 2 2, 3 3)')
ST_MPointFromText('MULTIPOINT ((1 1), (2 2), (3 3))')As of MySQL 5.7.9, output for 'MultiPoint' values includes parentheses around each point. For example:
mysql> SET @mp = 'MULTIPOINT(1 1, 2 2, 3 3)';
mysql> SELECT ST_AsText(ST_GeomFromText(@mp));
+---------------------------------+
| ST_AsText(ST_GeomFromText(@mp)) |
+---------------------------------+
| MULTIPOINT((1 1),(2 2),(3 3)) |
+---------------------------------+Before MySQL 5.7.9, output for the same value does not include parentheses around each point:
mysql> SET @mp = 'MULTIPOINT(1 1, 2 2, 3 3)';
mysql> SELECT ST_AsText(ST_GeomFromText(@mp));
+---------------------------------+
| ST_AsText(ST_GeomFromText(@mp)) |
+---------------------------------+
| MULTIPOINT(1 1,2 2,3 3) |
+---------------------------------+A 'MultiLineString' with two 'LineString' values:
MULTILINESTRING((10 10, 20 20), (15 15, 30 15))A 'MultiPolygon' with two 'Polygon' values:
MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))A 'GeometryCollection' consisting of two 'Point' values and one 'LineString':
GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))Well-Known Binary (WKB) Format
The Well-Known Binary (WKB) representation of geometric values is used for exchanging geometry data as binary streams represented by note 'BLOB': blob. values containing geometric WKB information. This format is defined by the OpenGIS specification (see note spatial-types::). It is also defined in the ISO 'SQL/MM Part 3: Spatial' standard.
WKB uses 1-byte unsigned integers, 4-byte unsigned integers, and 8-byte double-precision numbers (IEEE 754 format). A byte is eight bits.
For example, a WKB value that corresponds to 'POINT(1 -1)' consists of this sequence of 21 bytes, each represented by two hexadecimal digits:
0101000000000000000000F03F000000000000F0BFThe sequence consists of the components shown in the following table.
WKB Components Example
Component Size Value
Byte order
1 byte '01'
WKB type
4 bytes '01000000'
X coordinate
8 bytes '000000000000F03F'
Y coordinate
8 bytes '000000000000F0BF'
Component representation is as follows:
The byte order indicator is either 1 or 0 to signify little-endian or big-endian storage. The little-endian and big-endian byte orders are also known as Network Data Representation (NDR) and External Data Representation (XDR), respectively.
The WKB type is a code that indicates the geometry type. MySQL uses values from 1 through 7 to indicate 'Point', 'LineString', 'Polygon', 'MultiPoint', 'MultiLineString', 'MultiPolygon', and 'GeometryCollection'.
A 'Point' value has X and Y coordinates, each represented as a double-precision value.
WKB values for more complex geometry values have more complex data structures, as detailed in the OpenGIS specification.
Internal Geometry Storage Format
MySQL stores geometry values using 4 bytes to indicate the SRID followed by the WKB representation of the value. For a description of WKB format, see *note gis-wkb-format::.
For the WKB part, these MySQL-specific considerations apply:
The byte-order indicator byte is 1 because MySQL stores geometries as little-endian values.
MySQL supports geometry types of 'Point', 'LineString', 'Polygon', 'MultiPoint', 'MultiLineString', 'MultiPolygon', and 'GeometryCollection'. Other geometry types are not supported.
The 'LENGTH()' function returns the space in bytes required for value storage. Example:
mysql> SET @g = ST_GeomFromText('POINT(1 -1)');
mysql> SELECT LENGTH(@g);
+------------+
| LENGTH(@g) |
+------------+
| 25 |
+------------+
mysql> SELECT HEX(@g);
+----------------------------------------------------+
| HEX(@g) |
+----------------------------------------------------+
| 000000000101000000000000000000F03F000000000000F0BF |
+----------------------------------------------------+The value length is 25 bytes, made up of these components (as can be seen from the hexadecimal value):
4 bytes for integer SRID (0)
1 byte for integer byte order (1 = little-endian)
4 bytes for integer type information (1 = 'Point')
8 bytes for double-precision X coordinate (1)
8 bytes for double-precision Y coordinate (−1)
File: manual.info.tmp, Node: geometry-well-formedness-validity, Next: creating-spatial-columns, Prev: gis-data-formats, Up: spatial-types
For geometry values, MySQL distinguishes between the concepts of syntactically well-formed and geometrically valid.
A geometry is syntactically well-formed if it satisfies conditions such as those in this (nonexhaustive) list:
Linestrings have at least two points
Polygons have at least one ring
Polygon rings are closed (first and last points the same)
Polygon rings have at least 4 points (minimum polygon is a triangle with first and last points the same)
Collections are not empty (except 'GeometryCollection')
A geometry is geometrically valid if it is syntactically well-formed and satisfies conditions such as those in this (nonexhaustive) list:
Polygons are not self-intersecting
Polygon interior rings are inside the exterior ring
Multipolygons do not have overlapping polygons
Spatial functions fail if a geometry is not syntactically well-formed. Spatial import functions that parse WKT or WKB values raise an error for attempts to create a geometry that is not syntactically well-formed. Syntactic well-formedness is also checked for attempts to store geometries into tables.
It is permitted to insert, select, and update geometrically invalid geometries, but they must be syntactically well-formed. Due to the computational expense, MySQL does not check explicitly for geometric validity. Spatial computations may detect some cases of invalid geometries and raise an error, but they may also return an undefined result without detecting the invalidity. Applications that require geometically valid geometries should check them using the 'ST_IsValid()' function.
File: manual.info.tmp, Node: creating-spatial-columns, Next: populating-spatial-columns, Prev: geometry-well-formedness-validity, Up: spatial-types
MySQL provides a standard way of creating spatial columns for geometry types, for example, with note 'CREATE TABLE': create-table. or note 'ALTER TABLE': alter-table. Spatial columns are supported for note 'MyISAM': myisam-storage-engine, note 'InnoDB': innodb-storage-engine, note 'NDB': mysql-cluster, and note 'ARCHIVE': archive-storage-engine. tables. See also the notes about spatial indexes under *note creating-spatial-indexes::.
Use the *note 'CREATE TABLE': create-table. statement to create a table with a spatial column:
CREATE TABLE geom (g GEOMETRY);Use the *note 'ALTER TABLE': alter-table. statement to add or drop a spatial column to or from an existing table:
ALTER TABLE geom ADD pt POINT;
ALTER TABLE geom DROP pt;File: manual.info.tmp, Node: populating-spatial-columns, Next: fetching-spatial-data, Prev: creating-spatial-columns, Up: spatial-types
After you have created spatial columns, you can populate them with spatial data.
Values should be stored in internal geometry format, but you can convert them to that format from either Well-Known Text (WKT) or Well-Known Binary (WKB) format. The following examples demonstrate how to insert geometry values into a table by converting WKT values to internal geometry format:
Perform the conversion directly in the *note 'INSERT': insert. statement:
INSERT INTO geom VALUES (ST_GeomFromText('POINT(1 1)'));
SET @g = 'POINT(1 1)';
INSERT INTO geom VALUES (ST_GeomFromText(@g));Perform the conversion prior to the *note 'INSERT': insert.:
SET @g = ST_GeomFromText('POINT(1 1)');
INSERT INTO geom VALUES (@g);The following examples insert more complex geometries into the table:
SET @g = 'LINESTRING(0 0,1 1,2 2)';
INSERT INTO geom VALUES (ST_GeomFromText(@g));
SET @g = 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';
INSERT INTO geom VALUES (ST_GeomFromText(@g));
SET @g =
'GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))';
INSERT INTO geom VALUES (ST_GeomFromText(@g));The preceding examples use 'ST_GeomFromText()' to create geometry values. You can also use type-specific functions:
SET @g = 'POINT(1 1)';
INSERT INTO geom VALUES (ST_PointFromText(@g));
SET @g = 'LINESTRING(0 0,1 1,2 2)';
INSERT INTO geom VALUES (ST_LineStringFromText(@g));
SET @g = 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';
INSERT INTO geom VALUES (ST_PolygonFromText(@g));
SET @g =
'GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))';
INSERT INTO geom VALUES (ST_GeomCollFromText(@g));A client application program that wants to use WKB representations of geometry values is responsible for sending correctly formed WKB in queries to the server. There are several ways to satisfy this requirement. For example:
Inserting a 'POINT(1 1)' value with hex literal syntax:
INSERT INTO geom VALUES
(ST_GeomFromWKB(X'0101000000000000000000F03F000000000000F03F'));An ODBC application can send a WKB representation, binding it to a placeholder using an argument of *note 'BLOB': blob. type:
INSERT INTO geom VALUES (ST_GeomFromWKB(?))Other programming interfaces may support a similar placeholder mechanism.
In a C program, you can escape a binary value using 'mysql_real_escape_string_quote()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-real-escape-string-quote.html) and include the result in a query string that is sent to the server. See mysql_real_escape_string_quote() (https://dev.mysql.com/doc/c-api/5.7/en/mysql-real-escape-string-quote.html).
File: manual.info.tmp, Node: fetching-spatial-data, Next: optimizing-spatial-analysis, Prev: populating-spatial-columns, Up: spatial-types
Geometry values stored in a table can be fetched in internal format. You can also convert them to WKT or WKB format.
Fetching spatial data in internal format:
Fetching geometry values using internal format can be useful in table-to-table transfers:
CREATE TABLE geom2 (g GEOMETRY) SELECT g FROM geom;Fetching spatial data in WKT format:
The 'ST_AsText()' function converts a geometry from internal format to a WKT string.
SELECT ST_AsText(g) FROM geom;Fetching spatial data in WKB format:
The 'ST_AsBinary()' function converts a geometry from internal format to a *note 'BLOB': blob. containing the WKB value.
SELECT ST_AsBinary(g) FROM geom;File: manual.info.tmp, Node: optimizing-spatial-analysis, Next: creating-spatial-indexes, Prev: fetching-spatial-data, Up: spatial-types
For *note 'MyISAM': myisam-storage-engine. and 'InnoDB' tables, search operations in columns containing spatial data can be optimized using 'SPATIAL' indexes. The most typical operations are:
Point queries that search for all objects that contain a given point
Region queries that search for all objects that overlap a given region
MySQL uses R-Trees with quadratic splitting for 'SPATIAL' indexes on spatial columns. A 'SPATIAL' index is built using the minimum bounding rectangle (MBR) of a geometry. For most geometries, the MBR is a minimum rectangle that surrounds the geometries. For a horizontal or a vertical linestring, the MBR is a rectangle degenerated into the linestring. For a point, the MBR is a rectangle degenerated into the point.
It is also possible to create normal indexes on spatial columns. In a non-'SPATIAL' index, you must declare a prefix for any spatial column except for 'POINT' columns.
'MyISAM' and 'InnoDB' support both 'SPATIAL' and non-'SPATIAL' indexes. Other storage engines support non-'SPATIAL' indexes, as described in *note create-index::.
File: manual.info.tmp, Node: creating-spatial-indexes, Next: using-spatial-indexes, Prev: optimizing-spatial-analysis, Up: spatial-types
For 'InnoDB' and 'MyISAM' tables, MySQL can create spatial indexes using syntax similar to that for creating regular indexes, but using the 'SPATIAL' keyword. Columns in spatial indexes must be declared 'NOT NULL'. The following examples demonstrate how to create spatial indexes:
With *note 'CREATE TABLE': create-table.:
CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g));With *note 'ALTER TABLE': alter-table.:
CREATE TABLE geom (g GEOMETRY NOT NULL);
ALTER TABLE geom ADD SPATIAL INDEX(g);With *note 'CREATE INDEX': create-index.:
CREATE TABLE geom (g GEOMETRY NOT NULL);
CREATE SPATIAL INDEX g ON geom (g);'SPATIAL INDEX' creates an R-tree index. For storage engines that support nonspatial indexing of spatial columns, the engine creates a B-tree index. A B-tree index on spatial values is useful for exact-value lookups, but not for range scans.
For more information on indexing spatial columns, see *note create-index::.
To drop spatial indexes, use note 'ALTER TABLE': alter-table. or note 'DROP INDEX': drop-index.:
With *note 'ALTER TABLE': alter-table.:
ALTER TABLE geom DROP INDEX g;With *note 'DROP INDEX': drop-index.:
DROP INDEX g ON geom;Example: Suppose that a table 'geom' contains more than 32,000 geometries, which are stored in the column 'g' of type 'GEOMETRY'. The table also has an 'AUTO_INCREMENT' column 'fid' for storing object ID values.
mysql> DESCRIBE geom;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| fid | int(11) | | PRI | NULL | auto_increment |
| g | geometry | | | | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM geom;
+----------+
| count(*) |
+----------+
| 32376 |
+----------+
1 row in set (0.00 sec)To add a spatial index on the column 'g', use this statement:
mysql> ALTER TABLE geom ADD SPATIAL INDEX(g);
Query OK, 32376 rows affected (4.05 sec)
Records: 32376 Duplicates: 0 Warnings: 0File: manual.info.tmp, Node: using-spatial-indexes, Prev: creating-spatial-indexes, Up: spatial-types
The optimizer investigates whether available spatial indexes can be involved in the search for queries that use a function such as 'MBRContains()' or 'MBRWithin()' in the 'WHERE' clause. The following query finds all objects that are in the given rectangle:
mysql> SET @poly =
-> 'Polygon((30000 15000,
31000 15000,
31000 16000,
30000 16000,
30000 15000))';
mysql> SELECT fid,ST_AsText(g) FROM geom WHERE
-> MBRContains(ST_GeomFromText(@poly),g);
+-----+---------------------------------------------------------------+
| fid | ST_AsText(g) |
+-----+---------------------------------------------------------------+
| 21 | LINESTRING(30350.4 15828.8,30350.6 15845,30333.8 15845,30 ... |
| 22 | LINESTRING(30350.6 15871.4,30350.6 15887.8,30334 15887.8, ... |
| 23 | LINESTRING(30350.6 15914.2,30350.6 15930.4,30334 15930.4, ... |
| 24 | LINESTRING(30290.2 15823,30290.2 15839.4,30273.4 15839.4, ... |
| 25 | LINESTRING(30291.4 15866.2,30291.6 15882.4,30274.8 15882. ... |
| 26 | LINESTRING(30291.6 15918.2,30291.6 15934.4,30275 15934.4, ... |
| 249 | LINESTRING(30337.8 15938.6,30337.8 15946.8,30320.4 15946. ... |
| 1 | LINESTRING(30250.4 15129.2,30248.8 15138.4,30238.2 15136. ... |
| 2 | LINESTRING(30220.2 15122.8,30217.2 15137.8,30207.6 15136, ... |
| 3 | LINESTRING(30179 15114.4,30176.6 15129.4,30167 15128,3016 ... |
| 4 | LINESTRING(30155.2 15121.4,30140.4 15118.6,30142 15109,30 ... |
| 5 | LINESTRING(30192.4 15085,30177.6 15082.2,30179.2 15072.4, ... |
| 6 | LINESTRING(30244 15087,30229 15086.2,30229.4 15076.4,3024 ... |
| 7 | LINESTRING(30200.6 15059.4,30185.6 15058.6,30186 15048.8, ... |
| 10 | LINESTRING(30179.6 15017.8,30181 15002.8,30190.8 15003.6, ... |
| 11 | LINESTRING(30154.2 15000.4,30168.6 15004.8,30166 15014.2, ... |
| 13 | LINESTRING(30105 15065.8,30108.4 15050.8,30118 15053,3011 ... |
| 154 | LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30 ... |
| 155 | LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30 ... |
| 157 | LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4, ... |
+-----+---------------------------------------------------------------+
20 rows in set (0.00 sec)Use *note 'EXPLAIN': explain. to check the way this query is executed:
mysql> SET @poly =
-> 'Polygon((30000 15000,
31000 15000,
31000 16000,
30000 16000,
30000 15000))';
mysql> EXPLAIN SELECT fid,ST_AsText(g) FROM geom WHERE
-> MBRContains(ST_GeomFromText(@poly),g)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: geom
type: range
possible_keys: g
key: g
key_len: 32
ref: NULL
rows: 50
Extra: Using where
1 row in set (0.00 sec)Check what would happen without a spatial index:
mysql> SET @poly =
-> 'Polygon((30000 15000,
31000 15000,
31000 16000,
30000 16000,
30000 15000))';
mysql> EXPLAIN SELECT fid,ST_AsText(g) FROM g IGNORE INDEX (g) WHERE
-> MBRContains(ST_GeomFromText(@poly),g)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: geom
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 32376
Extra: Using where
1 row in set (0.00 sec)Executing the *note 'SELECT': select. statement without the spatial index yields the same result but causes the execution time to rise from 0.00 seconds to 0.46 seconds:
mysql> SET @poly =
-> 'Polygon((30000 15000,
31000 15000,
31000 16000,
30000 16000,
30000 15000))';
mysql> SELECT fid,ST_AsText(g) FROM geom IGNORE INDEX (g) WHERE
-> MBRContains(ST_GeomFromText(@poly),g);
+-----+---------------------------------------------------------------+
| fid | ST_AsText(g) |
+-----+---------------------------------------------------------------+
| 1 | LINESTRING(30250.4 15129.2,30248.8 15138.4,30238.2 15136. ... |
| 2 | LINESTRING(30220.2 15122.8,30217.2 15137.8,30207.6 15136, ... |
| 3 | LINESTRING(30179 15114.4,30176.6 15129.4,30167 15128,3016 ... |
| 4 | LINESTRING(30155.2 15121.4,30140.4 15118.6,30142 15109,30 ... |
| 5 | LINESTRING(30192.4 15085,30177.6 15082.2,30179.2 15072.4, ... |
| 6 | LINESTRING(30244 15087,30229 15086.2,30229.4 15076.4,3024 ... |
| 7 | LINESTRING(30200.6 15059.4,30185.6 15058.6,30186 15048.8, ... |
| 10 | LINESTRING(30179.6 15017.8,30181 15002.8,30190.8 15003.6, ... |
| 11 | LINESTRING(30154.2 15000.4,30168.6 15004.8,30166 15014.2, ... |
| 13 | LINESTRING(30105 15065.8,30108.4 15050.8,30118 15053,3011 ... |
| 21 | LINESTRING(30350.4 15828.8,30350.6 15845,30333.8 15845,30 ... |
| 22 | LINESTRING(30350.6 15871.4,30350.6 15887.8,30334 15887.8, ... |
| 23 | LINESTRING(30350.6 15914.2,30350.6 15930.4,30334 15930.4, ... |
| 24 | LINESTRING(30290.2 15823,30290.2 15839.4,30273.4 15839.4, ... |
| 25 | LINESTRING(30291.4 15866.2,30291.6 15882.4,30274.8 15882. ... |
| 26 | LINESTRING(30291.6 15918.2,30291.6 15934.4,30275 15934.4, ... |
| 154 | LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30 ... |
| 155 | LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30 ... |
| 157 | LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4, ... |
| 249 | LINESTRING(30337.8 15938.6,30337.8 15946.8,30320.4 15946. ... |
+-----+---------------------------------------------------------------+
20 rows in set (0.46 sec)File: manual.info.tmp, Node: json, Next: data-type-defaults, Prev: spatial-types, Up: data-types