Western Mass Hosting's mysql Articles

MySQL ZipCode Radius Search

Today, boys and girls 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: zip_codes

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

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);`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

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