Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.7Mb
PDF (A4) - 35.7Mb
PDF (RPM) - 34.8Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.5Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.5Mb
Man Pages (TGZ) - 203.6Kb
Man Pages (Zip) - 309.0Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  Data Types  /  Extensions for Spatial Data

12.5 Extensions for Spatial Data

The Open Geospatial Consortium (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® 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 Web site 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 data types and functions are available for MyISAM, InnoDB, NDB, and ARCHIVE 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 Section 14.1.14, “CREATE INDEX Syntax”.

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 Section 13.15, “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® 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 Web site 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: http://forums.mysql.com/list.php?23.


User Comments
Sign Up Login You must be logged in to post a comment.