MySQL for Developers
Sequence
Geographic searches
Sequence

How to search for geographical points in MySQL

Geographical searching is a powerful feature that many applications require, and MySQL has lots of geographic capabilities that can make it easier to search for geographical points. In this video, we will take a closer look at how to search for all the points within a one-mile radius of a given point in the middle.

Basic geographical searching

MySQL has a special data type for a point column, but for this article, we will be using a latitude and longitude column. This method is straightforward and easy to understand but please note that there are more advanced methods for advanced use cases.

Let's start by taking a closer look at these latitude and longitude columns. Suppose we have a table with one million addresses, each of which has a latitude and longitude column. To search for these addresses based on distance, we can use the stDistanceSphere function which calculates the distance between two points on a sphere.

For example

SQL
SELECT stDistanceSphere(
point(lat1, long1),
point(lat2, long2)
)

gives us the distance between two points on the sphere. It is important to note that the calculation is in meters, and we can use this function to make a simple comparison such as whether the distance is less than a specified value.

SQL
SELECT
*
FROM
addresses
WHERE
ST_Distance_Sphere(
POINT(-97.745363, 30.324014),
POINT(longitude, latitude)
) < 1609

In this query, we are searching for all points within one mile of the given point, which is defined by a hardcoded lat-long value.

The query calculates the distance between the hardcoded lat-long value and every row in the table. If the distance is less than 1609 meters (one mile), it is included in the results.

Running this query with a million rows can become slow because the query calculates the distance for every row, even if they are farther away than one mile. Therefore, we need to make an approximate condition that acts as an initial filter and eliminate the false positives later.

Approximate and redundant conditions

Making an approximate condition to quickly filter out rows that are far away from the given point is a popular technique to optimize geographic searching. As mentioned earlier, calculating the distance between every row and the given point can be a time-consuming process. Therefore, we can use a bounding box and filter the rows that fall within that box.

Filtering rows with bounding box

The first step is to calculate the actual bounding box based on the location of the given point. We can use off the shelf functions to do this task. Using the returned latitude range and longitude range will define the box.

SQL
select
*
from
addresses
where
latitude between 30.30954084441 and 30.33848715559 -- Bounding box latitude
and
longitude between -97.76213017291 and -97.72859582708 -- Bounding box longitude
and
ST_Distance_Sphere(
point(-97.745363, 30.324014),
point(longitude, latitude)
) <= 1609;

Next, we add an index to our table to cover either latitude or longitude - which will work equally well. MySQL will use the key for the bounding box filter, and after getting a small subset of rows, it will run the expensive ST_Distance_Sphere function.

SQL
ALTER TABLE addresses ADD INDEX idx_latitude (latitude);

We can confirm that the index works faster by checking the query's execution time, and we should see a significant improvement. Approximate and redundant conditions with indexing can vastly increase the speed of otherwise slow operations.

Wrapping up

MySQL provides powerful geometry and geographic functions that are easy to use and helpful for many applications. Using a simple latitude and longitude column in combination with efficient query optimization techniques such as approximate and redundant conditions, we can do fast and accurate geographical searching.

Please note that for more advanced scenarios, points columns, geometry columns, and other specialized columns are available in MySQL. However, for basic geographical searching, the latitude and longitude columns method is a lightweight and straightforward alternative to these complex features.

About this lesson

Using simple latitude and longitude columns, we'll look at how we can use approximate conditions to quickly perform geographic searches.

07:17
Closed captioned

Meet your instructor, Aaron Francis

Developer Educator at PlanetScale

I’m a dad to one-year-old twins and a software developer with a keen interest in processes and automation.

Feedback or questions? Reach out to our team at education@planetscale.com.

By submitting your email, you agree to the processing of your personal information by PlanetScale as described in the Privacy Policy.