MySQL ZipCode Radius Search

Published on
9,012 Points
1 Endorsement
Last Modified:
Today, we’ll be talking about how to do a Zip Code Radius Search in MySQL; how to set it up, and how to use it.

This has been tested in MySQL 5.1+, so please do not try it in anything lower than this, because it probably will not work.

Now, you may ask… ‘Why do I need a ZipCode Radius Search?’, even though I do not know the answer to your question, I do know why I needed it.  Simply put as a store locator service for a client.  Since there are a few ways to do this, I will only be showing you how I did it (as I found and tested other methods, this method seemed to be the most accurate while being the most efficient in terms of query speed).

First things first, you will need a table to store all 81k+ records from the latest zipcode dump.  We will be using a MyIASM storage system so we can take full advantage of the Spatial Indexing features MySQL has graciously included, but more on that as we go, for now, here is what I did:

Table: ZipCodes

/* Create our table with these basic columns */
CREATE TABLE `zip_codes` (
  `zip` varchar(10) NOT NULL,
  `lat` float NOT NULL DEFAULT '0',
  `lon` float NOT NULL DEFAULT '0',
  `city` varchar(255) NOT NULL DEFAULT '',
  `state` char(2) NOT NULL DEFAULT '',
  `county` varchar(255) NOT NULL DEFAULT '',
  `type` varchar(255) NOT NULL DEFAULT '',
  `Location` point NULL,
  KEY `city` (`city`,`state`),
  KEY `lat` (`lat`),
  KEY `lon` (`lon`),
  KEY `lat_2` (`lat`),
  KEY `lon_2` (`lon`),

Open in new window

We are using a NULL POINT column temporarily, so we can do our ZipCode data load, once this load is completed, run the following statement against the table to update the Location column:
UPDATE zip_codes SET Location = POINT(lat, lon);

Open in new window

This will get our Geographical POINT data set and ready to create our Spatial Index, now you can change this column back to NOT NULL, and create the Spatial Index on it.

Now, I use procedures for every CRUD operation I need, but for this exercise, I’ll just give you the direct query:


SET @lat = 41.92;
	SET @lon = -72.65;
	SET @kmRange = 80.4672; -- = 50 Miles

	SELECT *, (3956 * 2 * ASIN(SQRT(POWER(SIN((@lat - abs(`lat`)) * pi()/180 / 2),2) + COS(@lat * pi()/180 ) * COS(abs(`lat`) *  pi()/180) * POWER(SIN((lon - `lon`) *  pi()/180 / 2), 2)))) as distance
	FROM    `zip_codes`
	WHERE   MBRContains(LineString(Point(@lat + @kmRange / 111.1, @lon + @kmRange / (111.1 / COS(RADIANS(@lat)))), Point(@lat - @kmRange / 111.1, @lon - @kmRange / (111.1 / COS(RADIANS(@lat))))), `Location`)
	Order By distance

Open in new window

You can see that I set the latitude and longitude along with the Kilometer radius.  Change these values to suit your needs.   This query returns me 854 records of towns/cities in a 50 mile radius from Bradley International Airport, in a matter of 142ms (your results may vary)

That's it folks!   Have fun and happy coding
p.s.  You will need to get your own ZipCode - Lat/Lon table... sorry!
1 Comment
LVL 66

Expert Comment

by:Jim Horn
Nice article.  I had a need to do this back in 2001 when designing a client's 'Find your sales rep' functionality in ASP and SQL 7, and remember that it was not possible back then.

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Join & Write a Comment

In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Next Article:

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month