<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

MySQL ZipCode Radius Search

Published on
9,137 Points
3,037 Views
1 Endorsement
Last Modified:
Approved
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
Author:kevp75
1 Comment
LVL 67

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

Featured Post

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month