/* 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;
UPDATE zip_codes SET Location = POINT(lat, lon);
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
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.
Comments (1)
Commented: