-- phpMyAdmin SQL Dump
-- version 2.11.8-rc1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 05, 2009 at 10:06 PM
-- Server version: 5.0.51
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `zips`
--

-- --------------------------------------------------------

--
-- Table structure for table `zips`
--

CREATE TABLE IF NOT EXISTS `zips` (
  `zip` char(5) NOT NULL,
  `lat` float NOT NULL,
  `long` float NOT NULL,
  `city` varchar(64) default NULL,
  `state` varchar(64) default NULL,
  PRIMARY KEY  (`zip`),
  KEY `lat` (`lat`),
  KEY `long` (`long`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`root`@`localhost` PROCEDURE `inside`(origin CHAR(5), distance VARCHAR(8))
BEGIN
	DECLARE oLat FLOAT;
	DECLARE oLong FLOAT;
	DECLARE unit CHAR(2);
	DECLARE d FLOAT;

	SET oLat = ( SELECT `lat` FROM `zips` WHERE `zip` = origin );
	SET oLong = ( SELECT `long` FROM `zips` WHERE `zip` = origin );
	
	SET unit = RIGHT(distance, 2);
	SET d = CONVERT(SUBSTR(distance, 1, LENGTH(distance)-2), UNSIGNED);
	
	SELECT 
		*, 
		CASE
			WHEN unit = 'mi' THEN miles(oLat, oLong, `lat`, `long`)
			WHEN unit = 'km' THEN km(oLat, oLong, `lat`, `long`)
		END
		FROM `zips` 
		WHERE 
			CASE
				WHEN unit = 'mi' THEN miles(oLat, oLong, `lat`, `long`)
				WHEN unit = 'km' THEN km(oLat, oLong, `lat`, `long`)
			END <= d 
		ORDER BY 
			CASE
				WHEN unit = 'mi' THEN miles(oLat, oLong, `lat`, `long`)
				WHEN unit = 'km' THEN km(oLat, oLong, `lat`, `long`)
			END;
END$$

--
-- Functions
--
CREATE DEFINER=`root`@`localhost` FUNCTION `km`(lat1 FLOAT, long1 FLOAT, lat2 FLOAT, long2 FLOAT) RETURNS float
BEGIN
	DECLARE earth FLOAT;
	DECLARE dLat FLOAT;
	DECLARE dLong FLOAT;
	DECLARE a FLOAT;
	DECLARE c FLOAT;
	
	SET earth = 6371.0; /* radius of the Earth in km */
	SET dLat = (lat2-lat1) * PI() / 180; /* latitude distance in radians */
	SET dLong = (long2-long1) * PI() / 180; /* longitude distance in radians */

	SET a = 	SIN(dLat/2) * SIN(dLat/2) + 
				COS(lat1 * PI() / 180) * COS(lat2 * PI() / 180) *
				SIN(dLong/2) * SIN(dLong/2);

	SET c =		2 * ATAN(SQRT(a), SQRT(1-A));

	RETURN earth * c;
END$$

CREATE DEFINER=`root`@`localhost` FUNCTION `miles`(lat1 FLOAT, long1 FLOAT, lat2 FLOAT, long2 FLOAT) RETURNS float
BEGIN
	RETURN km(lat1, long1, lat2, long2) * 0.62;
END$$

DELIMITER ;