MySQL GIS: Spatial Data Management Guide

A Geographic Information System (GIS) is a system designed to capture, store, manipulate, analyze, manage, and present spatial or geographic data. MySQL's GIS functionality adheres to the Open Geospatial Consortium (OGC) OpenGIS Geometry Model international standard. MySQL's support for GIS began in version 5.6 and has been continuously enhanced with each subsequent release, with significant leaps forward in MySQL 8.0.

• ​MySQL 5.6:​​ Introduced basic spatial support, including fundamental spatial data types (GEOMETRY, POINT, LINESTRING, POLYGON, etc.), a subset of functions, and R-Tree indexing (for accelerating spatial queries). However, the functionality was still quite basic at this stage.
• ​MySQL 5.7:​​ Brought important enhancements to GIS, adding a large number of new GIS functions (over 50), greatly improving feature completeness and enabling handling of more complex spatial operations and GeoJSON format.
• ​MySQL 8.0:​​ Further enhanced and optimized GIS performance, introducing new GIS functions like ST_IsValid, ST_IsSimple, and others.

Let's explore the GIS features supported by MySQL.

GIS Data Types

MySQL currently supports eight standard-compliant spatial data types. These cover fundamental usage scenarios and are sufficient for many common spatial data storage and query needs, such as geographic location search, area containment judgment, and distance calculation.

‍

You can choose the appropriate type based on your requirements. The syntax for defining them in the command line is similar to other data types.

CREATE TABLE gis_places (
    gis_id INT AUTO_INCREMENT PRIMARY KEY,
    gis_name VARCHAR(255) NOT NULL,
    gis_location POINT NOT NULL,
    SPATIAL INDEX(location_index) (gis_location)
);

‍

Note: SRID 4326:​​ SRID (Spatial Reference System Identifier) is a numeric identifier defining the coordinate system for geospatial data. SRID 4326 corresponds to the WGS 84 coordinate system, which is widely used by the Global Positioning System (GPS).

GIS Functions

MySQL provides a rich set of GIS functions for manipulating and querying spatial data. These functions follow the Open Geospatial Consortium (OGC) standards. According to the official documentation, MySQL 8.0+ versions support 93 geographic functions.

​Basic Constructor Functions:​​

• ST_GeomFromText()converts WKT (Well-Known Text) format text into a geometry object.
• ST_PointFromText()is specifically used to create point objects.
• ST_Dimension()returns the object's dimension (-1 for empty, 0 for point, 1 for line, 2 for polygon).
• ST_GeometryType()returns the name of the geometry type.

​Object-Specific Functions:​​

• ​Point objects:​​ ST_X()/ ST_Y()retrieve coordinate values.
• ​Line objects:​​ ST_Length()calculates length, ST_StartPoint()/ ST_EndPoint()get endpoints.
• ​Polygon objects:​​ ST_Area()calculates area, ST_ExteriorRing()returns the exterior ring.

​Spatial Analysis Functions:​​ For topological relationship judgment, spatial operations, etc.

​Data Format Conversion:​​ Text/binary conversion, coordinate system processing, etc.

These functions enable MySQL to handle most common GIS application scenarios, such as geolocation search, spatial relationship analysis, and geofencing, providing robust geospatial data processing capabilities.

‍

Indexing

GIS uses R-Tree indexing for retrieval to improve performance. An R-Tree is an efficient index data structure specifically designed for multi-dimensional spatial data (like geographic coordinates, geometric shapes). It is essentially an extension of the B-Tree into multi-dimensional space. It approximates spatial objects using their Minimum Bounding Rectangle (MBR) and organizes them into a balanced tree, enabling fast spatial range queries and adjacency queries.

‍

Summary

GIS is a very powerful and widely used set of functionalities. Its integration with a relational database like MySQL is a logical progression driven by demand. If you plan to use MySQL's GIS features, it is recommended to directly choose MySQL 8.0 or a higher version.