MySQL Proximity Search with US Zip Codes

In this article, we'll briefly go over how to run a proximity search with US zip codes in MySQL using some pre-defined location data.

If you want to skip the explanation altogether and dive straight into the code, you can download the SQL script that will create the table and insert all the data needed, as well as the script to query the data at my GitHub repository. It will also help you follow along with this tutorial.

What Are Latitude and Longitude?

Latitude and longitude make up the geographic coordinate system to determine specific locations on Earth. Where latitude determines the north and south position and longitude determines the east and west position.

Think of them as angles inside a circle. 90 degrees up would lead directly north and 90 degrees down would lead directly south, while 0 degrees runs along the Earth's equator.

Distance Calculation on a Sphere

We won't be running this calculation in our query. This will all be handled by MySQL's pre-built functions. I just wanted to give an example of the math involved in calculating distances on a sphere and why we're taking the more simplified approach.

Here is the formula needed to calculate Earth's radius:

R = Earth's radius
Δlat = lat2− lat1; Δlong = long2− long1
a = sin²(Δlat/2) + cos(lat1) * cos(lat2) * sin²(Δlong/2)
c = 2*atan2(√a, √(1−a)); d = R*c

The result is 3956, which we would now use in our MySQL query to run the distance calculation between two coordinates:

3956 * 2 * ASIN ( SQRT (
POWER(SIN((orig.lat - dest.lat)*pi()/180 / 2), 2) +
COS(orig.lat * pi()/180) * COS(dest.lat * pi()/180) *
POWER(SIN((orig.lon - dest.lon) * pi()/180 / 2), 2) ) )
as distance

Of course, you can use this within your query if you're a die-hard trigonometry fan, but this article's goal is to touch more on how to do this quickly and simply.

The MySQL Query

Now, let's see how we can use the latitude and longitude data we've collected (available in the GitHub repository at the link above) to calculate distances between locations by building a query and using MySQL's pre-built functions.

Here's the full query, then we'll break it down step-by-step:

SET @zip_code = '10001';
SET @radius = 25;

SELECT	zip_code,
		(
		ST_Distance_Sphere(
			POINT(
				(SELECT longitude FROM zip_codes WHERE zip_code = @zip_code),
				(SELECT latitude FROM zip_codes WHERE zip_code = @zip_code)
			),
			POINT (longitude, latitude)
		) / 5280) AS distance
FROM	zip_codes
HAVING DISTANCE <= @radius
ORDER BY distance;

Basically, we're taking two coordinates on Earth: The latitude and longitude of our source location, which is 10001, the zip code for Manhattan, New York, and calculating the distances between all zip codes within a 25-mile radius, defined in our @radius variable at the beginning of the query.

The ST_Distance_Sphere() method is known in the MySQL world as a Spatial Convenience Function, providing an easy route to calculating two points on a sphere.

In this method, we'll be passing two points, using the POINT() method. Each point, also an Earth coordinate, requires a longitude value first, then a latitude value, using degrees as your units.

The first point we've defined is the latitude and longitude of our source zip code. The second point is the latitude and longitude of the destination zip codes where we're returning the distance calculations from.

Typically, when running a distance calculation, we would need to convert our coordinates from degrees into radians with radians = π / 180, like we did in our formula above. However, MySQL's pre-built functions do all that work for you, making it easier to run distance calculations which, in turn, makes your code more readable and easier to understand.

Conclusion

This article walked through calculating the distance between two coordinates on a sphere using US zip codes and pre-defined MySQL data.

Please follow the link above to the GitHub repository that has the table structure, data, and full query and start playing around with it to see what you can come up with!