How to find the nearest locations from a collection of coordinates (latitude and longitude) stored as Points (Spatial Data Types) in MySQL

2 Years ago, I wrote a tutorial about how to find the nearest locations from a collection of coordinates stored in a MySQL database. The tutorial works quite well when you store the latitude and longitude in decimal fields of the table. However, it seems that is quite convenient to store this kind of data in the Point data type of MySQL (it has spatial data types that correspond to OpenGIS classes). The geometry arguments should consist of points that specify (longitude, latitude) coordinate values:

• Longitude and latitude are the first and second coordinates of the point, respectively.
• Both coordinates are in degrees.
• Longitude values must be in the range (-180, 180]. Positive values are east of the prime meridian.
• Latitude values must be in the range [-90, 90]. Positive values are north of the equator.

I will explain you in this tutorial how to easily create a basic table that stores the latitude and longitude of a location using the Point spatial data type of MySQL and query the distance between a custom Point.

1. Create locations table

To follow this tutorial, I will explain you using a simple table that contains 3 columns, the primary key (ID), the name that the location will have and lastly the point column that will store the Point:

CREATE TABLE `locations` (
    `id` bigint NOT NULL,
    `name` varchar(255) DEFAULT NULL,
    `point` point NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Create Indexes for table `locations`
-- including the Spatial index on point
--
ALTER TABLE `locations`
    ADD PRIMARY KEY (`id`),
    ADD SPATIAL KEY `point` (`point`);

This table will contain the following records that can be used to test, of course you can change them and use your own, but these will do the trick initially:

INSERT INTO 
    `locations` (`id`, `name`, `point`) 
    VALUES
        (null, 'Bogotá', ST_GeomFromText('POINT(-74.07867091 4.66455174)')),

        (null, 'Medellín', ST_GeomFromText('POINT(-75.57050110 6.24478548)')),

        (null, 'Barrancabermeja', ST_GeomFromText('POINT(-73.84928550 7.06125013)')),

        (null, 'Cúcuta', ST_GeomFromText('POINT(-72.49432589 7.88475514)')),

        (null, 'Cali', ST_GeomFromText('POINT(-76.51532198 3.48835279)')),

        (null, 'Villavicencio', ST_GeomFromText('POINT(-73.63690401 4.13510880)')),

        (null, 'San Gil', ST_GeomFromText('POINT(-73.13373892 6.55526689)'))
;

Every record will contain a City from Colombia (South America) and the POINT representation of its longitude and latitude respectively. After inserting the records, your table will look like this:

2. Query locations and order them by distance

Now for example, if you need to query the locations that are the closest to a given point, in this case, let's say from Bucaramanga (Longitude -73.109857 and latitude 7.107069):

I'd like to obtain a list like the following one, ordering the results from the closest location to the farthest:

• San Gil
• Barrancabermeja
• Cúcuta
• Medellin
• Bogotá
• Villavicencio
• Cali

The query that will help me to obtain the list will look like this:

SELECT
    id,
    name,
    ST_X(point) AS "latitude",
    ST_Y(point) AS "longitude",
    (
        ST_Length(
            LineString(
                point, 
                ST_GeomFromText('POINT(-73.109857 7.107069)')
            )
        )
    ) AS distance
FROM locations ORDER BY distance ASC;

This will provide the following records as result:

3. Query locations and obtain distance in meters

Additionally, if you want to calculate as well the distance in meters between the search point and every point of the records, you can use the ST_distance_sphere function that returns the linear distance in meters between two longitude and latitude points. It uses a spherical earth and radius of 6370986 meters:

SELECT
    id,
    name,
    ST_X(point) AS "latitude",
    ST_Y(point) AS "longitude",
    (
        ST_Length(
            LineString(
                point, 
                ST_GeomFromText('POINT(-73.109857 7.107069)')
            )
        )
    ) AS distance,
    (
        ST_distance_sphere(point, ST_GeomFromText('POINT(-73.109857 7.107069)')) 
    ) AS distanceInMeters
FROM locations ORDER BY distance ASC;

This query will generate the following result:

With this new field, you may filter the results where the distance in meters is less than 150.000 (150 Kilometers or 93 Miles):

SELECT * FROM 
	(
        SELECT
        id,
        name,
        ST_X(point) AS "latitude",
        ST_Y(point) AS "longitude",
        (
            ST_Length(
                LineString(
                    point, 
                    ST_GeomFromText('POINT(-73.109857 7.107069)')
                )
            )
        ) AS distance,
        (
            ST_distance_sphere(point, ST_GeomFromText('POINT(-73.109857 7.107069)')) 
        ) AS distanceInMeters
        FROM locations
        ORDER BY distance ASC
    ) 
as result
-- Where the distance in meters between the comparison point is less than 150 KM or 93 Miles (150000 Meters) --
WHERE result.distanceInMeters < 150000;

The result of this query will be the following one:

And that's it! All you need to do with these queries is to replace the Point that I used to measure the distances with your own point and your own records.

Happy coding ❤️!