What’s the Ideal Data Type to Use When Storing Latitude / Longitude in a MySQL Database?

1. Overview

Storing latitude and longitude data in relational databases poses some challenges due to the spatial nature of the data, as traditional numeric data types don’t lend themselves well to their related calculations.

In this article, we’ll explore some options for storing geographical coordinates (latitude/longitude) in MySQL. First, we’ll look at DECIMAL for its simplicity. Second, we’ll look at POINT, MySQL’s first-class datatype for coordinates introduced in 5.7.

Note that we won’t recommend using FLOAT or DOUBLE as they don’t carry the precision for accurate latitude/longitude calculations.

2. DECIMAL

If we primarily need to store and retrieve the values and perform simple calculations, storing latitude and longitude in separate DECIMAL columns is often quite straightforward and highly recommended. It provides exact precision because it stores numbers as exact decimal values, avoiding the floating-point inaccuracies that can occur with FLOAT or DOUBLE.

This is crucial for geographical coordinates, where even tiny inaccuracies can lead to noticeable errors in distance calculations or mapping. We have control over precision and scale. Accordingly, we can specify the total number of digits (precision) and the number of digits after the decimal point (scale) to our needs.

2.1. Choosing Precision and Scale

Latitude can range from -90 to +90. Therefore, we generally need two digits before the decimal point. A common recommendation is DECIMAL(10, 8) or DECIMAL(9, 7).

Longitude can range from -180 to +180. Hence, we generally need three digits before the decimal point. A common recommendation is DECIMAL(11, 8) or DECIMAL(10, 7).

As a reminder, in DECIMAL(x, y), x is our precision, and y is our scale; x-y gives us the number of digits before the decimal point. In both cases, we can adjust the scale based on the required accuracy:

• 5 decimal places yield 1-meter accuracy
• 6 decimal places yield 10 centimeter accuracy
• 7-8 decimal places yield sub-centimeter accuracy (for highly precise surveying)

2.2. DECIMAL Example

An example of using the DECIMAL type includes our general recommendations:

CREATE TABLE Coordinates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location VARCHAR(255),
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL
);

INSERT INTO Coordinates (location, latitude, longitude)
VALUES ('Eiffel Tower', 48.8584, 2.2945);

SELECT * FROM Coordinates;
+----+--------------+-------------+------------+
| id | location     | latitude    | longitude  |
+----+--------------+-------------+------------+
|  1 | Eiffel Tower | 48.85840000 | 2.29450000 |
+----+--------------+-------------+------------+

2.3. When to Choose DECIMAL?

We should use DECIMAL when:

• We primarily need to store and retrieve the raw latitude and longitude values
• We handle our geospatial calculations in our application code, such as calculating the distance between two coordinates
• We need a strict, exact decimal representation without any potential floating-point inaccuracies
• We’re using an older version of MySQL (pre-8.0), where spatial functions were less robust or efficient for geographic (spherical) calculations

3. POINT

POINT is ideal if we plan to perform more complex geospatial operations directly within MySQL. These calculations can be finding points within a certain radius, checking if a point is within a polygon, or calculating distances using built-in spatial functions.

3.1. Especially Designed for Spatial Data

POINT in MySQL 8.0+ is specially designed for spatial data in several ways:

• MySQL’s spatial queries — like bounding box searches and nearest neighbor searches — are designed around POINTs
• Built-in functions like ST_Distance_Sphere(), ST_Contains(), and ST_Intersects() all use POINT
• We can create SPATIAL indexes on POINT columns, which significantly speed up spatial queries
• In MySQL 8.0+,  we can use Spatial Reference Identifiers (SRIDs). For latitude/longitude, we should generally use SRID 4326 (WGS84), which is the standard used by GPS and most mapping systems
• A large number of spatial functions in 8.0+ compute results on an ellipsoid (if the data is on an ellipsoid geometry), whereas in pre-8.0, everything is computed on a flat Cartesian plane

3.2. POINT Example

An example of using the POINT type – note that ST_PointFromText expects coordinates in (longitude latitude) order, not (latitude longitude):

CREATE TABLE Locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    coordinates POINT NOT NULL SRID 4326,
    SPATIAL INDEX(coordinates)
);

INSERT INTO Locations (name, coordinates)
VALUES ('Eiffel Tower', ST_PointFromText('POINT(2.2945 48.8584)', 4326));
INSERT INTO Locations (name, coordinates)
VALUES ('Arc de Triomphe', ST_PointFromText('POINT(2.2950 48.8738)', 4326));

We can query latitude/longitude data just like any other type of data. For example, let’s query the distance in meters between the Eiffel Tower and the Arc de Triomphe:

SELECT
    ST_Distance_Sphere(t1.coordinates, t2.coordinates) AS Distance_in_Meters 
FROM
    Locations AS t1,
    Locations AS t2 
WHERE
    t1.name = 'Eiffel Tower' AND t2.name = 'Arc de Triomphe';

We should get a result set that includes the distance in meters:

+--------------------+
| Distance_in_Meters |
+--------------------+
| 1711.9340400063334 |
+--------------------+

3.3. When to Choose POINT?

We should use POINT when:

• We’re using MySQL 8.0 or later and want to leverage its enhanced spatial capabilities
• We need to perform complex geospatial queries directly within the database
• We want to utilize spatial indexes for performance optimization on geographical searches
• We prefer to keep location data consolidated in a single column

4. Conclusion

In this article, we learned about the most suitable data types for storing latitude and longitude in a MySQL database. The two most commonly used data types are DECIMAL and POINT.  There’s no single data type that’s ideal for all types of use cases. Therefore, our choice is guided by needs for precision, storage efficiency, and the types of spatial queries we plan to run.