MySQL 8: The right way to store location (latitude, longitude) in a table

Introduction

Understanding how to properly store and manipulate geographical data within a database is an essential skill for a wide range of applications, from social media apps to logistics software. The purpose of this tutorial is to explore the mechanisms provided by MySQL 8 to correctly store, index, and query geographic location coordinates, specifically latitude and longitude.

Getting Started with Geospatial Data

In MySQL, geospatial data types enable the storage of geographic values such as points, lines, and polygons. For the purposes of this tutorial, we will focus on the Point data type, which represents a single location on the Earth’s surface defined by its latitude and longitude.

Basic: Creating a Geospatial Column

To begin, we need to create a table that can store locations. MySQL provides a spatial data type called POINT. Here’s a basic example of how to create a table with a POINT column:

CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    coords POINT NOT NULL,
    SPATIAL INDEX(coords)
);

In the above code snippet, we’ve defined a table named locations that includes an auto-incremented primary key id, a name field for the location name, and a coords field for the geospatial data. We also created a spatial index on the coords column, which will significantly improve the performance of spatial queries.

Inserting Data into the Geospatial Column

With the table structure in place, we can start inserting geographic coordinates. MySQL provides special functions to construct geospatial values like POINT():

INSERT INTO locations (name, coords)
VALUES
    ('Eiffel Tower', ST_PointFromText('POINT(48.8584 2.2945)')),
    ('Statue of Liberty', ST_PointFromText('POINT(40.6892 -74.0445)'));

The ST_PointFromText() function creates a POINT from a text representation, allowing us to insert latitude and longitude values into the coords field. Note that coordinate order is essential: MySQL expects points to be specified in a longitude, latitude format.

Selecting Geospatial Data

When we need to fetch the latitude and longitude values from the database, we can use the ST_X() and ST_Y() functions, which retrieve the X and Y values of our point, corresponding to longitude and latitude, respectively:

SELECT
    name,
    ST_X(coords) AS longitude,
    ST_Y(coords) AS latitude
FROM
    locations;

This query will return the original latitude and longitude of each location in separate columns.

Working with Spatial Functions

MySQL offers an array of functions for working with geospatial data. For instance, several functions enable us to determine the distance between two points. One common method is using the ST_Distance_Sphere() function:

SET @eiffel_tower = ST_GeomFromText('POINT(48.8584 2.2945)');
SET @statue_of_liberty =  ST_GeomFromText('POINT(40.6892 -74.0445)');

SELECT
    ST_Distance_Sphere(@eiffel_tower, @statue_of_liberty) as distance_in_meters;

This function calculates the minimum spherical distance between two points on the Earth’s surface and returns it in meters. It’s a valuable tool for applications that need to calculate ‘as-the-crow-flies’ distances between locations.

Advanced Indexing

MySQL’s spatial indexing is implemented using R-trees, which are specifically designed for indexing multi-dimensional information such as geographical coordinates. A spatial index can dramatically increase the performance of queries that involve spatial data. While we’ve already created a spatial index on our coords column in the initial table creation, let’s take a deeper look at how to harness the power of spatial indexes when working with large datasets and complex queries.

When dealing with an extensive collection of locations, we might want to find all points within a given distance of a central point. For this, we can combine spatial indexes and the ST_CONTAINS() function, which returns 1 or 0 depending on whether a geometry contains another:

SET @central_point = ST_GeomFromText('POINT(48.8584 2.2945)');
SET @radius = 10000; -- 10km in meters

SELECT name, distance_in_meters
FROM (
    SELECT
        name,
        ST_Distance_Sphere(coords, @central_point) AS distance_in_meters
    FROM locations
) AS distances
WHERE distances.distance_in_meters <= @radius;

In the nested subquery, we calculate the distance for every location from the central point and in the outer query we filter by the specified radius. The spatial index on the coords column helps in performing the distance calculations efficiently.

Conclusion

As we have seen, MySQL 8 provides robust support for storing and querying geospatial data. Employing the Point data type and associated spatial functions allows us to precisely and efficiently store and manipulate location data. By leveraging spatial indexing, we can further optimize the performance of our location-aware applications, paving the way for new possibilities in spatial and location-driven innovation.