DELIMITER $$

DROP FUNCTION IF EXISTS `postcode_dist` $$
CREATE FUNCTION `postcode_dist`(from_postcode INT, to_postcode INT) RETURNS decimal(10,2)
BEGIN
DECLARE dist FLOAT;
DECLARE from_lat DECIMAL (12,6);
DECLARE from_lon DECIMAL (12,6);
DECLARE to_lat DECIMAL (12,6);
DECLARE to_lon DECIMAL (12,6);

SELECT cast(lat as DECIMAL(12,6)) as from_lat INTO from_lat FROM postcode_db where postcode = from_postcode limit 1;
SELECT cast(lon as DECIMAL(12,6)) as from_lon INTO from_lon FROM postcode_db where postcode = from_postcode limit 1;
SELECT cast(lat as DECIMAL(12,6)) as to_lat INTO to_lat FROM postcode_db where postcode = to_postcode limit 1;
SELECT cast(lon as DECIMAL(12,6)) as to_lon INTO to_lon FROM postcode_db where postcode = to_postcode limit 1;

set dist = from_lon - to_lon;
set dist = sin(from_lat * pi()/180) * sin(to_lat * pi()/180) + (cos(from_lat * pi()/180) * cos(to_lat * pi()/180) * cos(dist * pi()/180));
set dist = acos(dist);
set dist = (dist / (pi()/180)) * 111.325;


RETURN dist;
END $$
DELIMITER ;