MySQL has spatial data types that correspond to OpenGIS classes. The basis for these types is described in Section 11.5.2, “The OpenGIS Geometry Model”.
Some spatial data types hold single geometry values:
GEOMETRY can store geometry values of any
type. The other single-value types (
restrict their values to a particular geometry type.
The other spatial data types hold collections of values:
GEOMETRYCOLLECTION can store a collection of
objects of any type. The other collection types
GEOMETRYCOLLECTION) restrict collection
members to those having a particular geometry type.
Example: To create a table named
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
CREATE TABLE geom (g GEOMETRY NOT NULL);
Columns with a spatial data type can have an
SRID attribute, to explicitly indicate the
spatial reference system (SRS) for values stored in the column.
CREATE TABLE geom ( p POINT NOT NULL SRID 0, g GEOMETRY NOT NULL SRID 4326 );
InnoDB tables permit
values for Cartesian and geographic SRSs.
MyISAM tables permit
values for Cartesian SRSs.
SRID attribute makes a spatial column
SRID-restricted, which has these implications:
The column can contain only values with the given SRID. Attempts to insert values with a different SRID produce an error.
The optimizer can use
SPATIALindexes on the column. See Section 8.3.3, “SPATIAL Index Optimization”.
Spatial columns with no
SRID attribute are
not SRID-restricted and accept values with any SRID. However,
the optimizer cannot use
SPATIAL indexes on
them until the column definition is modified to include an
SRID attribute, which may require that the
column contents first be modified so that all values have the
For other examples showing how to use spatial data types in MySQL, see Section 11.5.6, “Creating Spatial Columns”. For information about spatial reference systems, see Section 11.5.5, “Spatial Reference System Support”.