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.
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
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
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.
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.