MySQL ZipCode Radius Search

Published:
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`),
                      ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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:

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
~Kevin
p.s.  You will need to get your own ZipCode - Lat/Lon table... sorry!
1
3,680 Views

Comments (1)

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

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

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.