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

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.

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.

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.

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.