MySQL PROCEDURE or PHP class?

[unwrittenphp]

I have a database with coordinates to listed addresses.... I'm getting distance between these addresses and also grabbing addresses in a set radius, would it better to use MySQL procedures for all this or go with a custom PHP class?

[Dan Grossman]

It would be more efficient to do this in MySQL. You can get the addresses in a set radius directly with a single query; SQL is fully capable of the math required.

[unwrittenphp]

Okay, so my next question would be, can I stay with using mysql functions or do I have to go to mysqli functions for call these procedures? I keep getting errors in both mysql and mysqli functions but it's working fine from the command line...

DELIMITER $$
 
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetNearbyZipCodes`(
	zipbase VARCHAR(10),
	r NUMERIC(15)
)
BEGIN
	DECLARE lat1 DECIMAL(9, 6);
	DECLARE lon1 DECIMAL(9, 6);
	SELECT latitude, longitude INTO lat1, lon1 FROM zipcodes WHERE zipcode = zipbase;
	SELECT B.zipcode, B.city_name, GetDistance(lat1, lon1, B.latitude, B.longitude) AS distance
		FROM zipcodes AS B
		WHERE
			GetDistance(lat1, lon1, B.latitude, B.longitude) <= r
		AND
			GetDistance(lat1, lon1, B.latitude, B.longitude) > 0
		ORDER BY distance;
END $$
 
CREATE DEFINER=`root`@`localhost` FUNCTION `GetDistance`(
	lat1 NUMERIC(9, 6),
	lon1 NUMERIC(9, 6),
	lat2 NUMERIC(9, 6),
	lon2 NUMERIC(9, 6)
) RETURNS DECIMAL(10, 5)
BEGIN
	DECLARE x DECIMAL(20, 10);
	SET x = SIN(RADIANS(lat1)) * SIN(RADIANS(lat2)) + COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * COS(ABS(RADIANS(lon2) - RADIANS(lon1)));
	SET x = ATAN((SQRT(1 - POWER(x, 2))) / x);
	RETURN (1.852 * 60 * DEGREES(x) / 1.609344);
END $$
 
DELIMITER ;

This is what I'm using for zipcodes so far, working my way to exact addresses

mysql> CALL GetNearbyZipCodes('80601', 10);
+---------+---------------+----------+
| zipcode | city_name     | distance |
+---------+---------------+----------+
| 80640   | Henderson     |  6.03482 |
| 80022   | Commerce City |  6.72023 |
| 80233   | Denver        |  7.27297 |
| 80614   | Eastlake      |  8.19226 |
| 80241   | Denver        |  8.78002 |
| 80621   | Fort Lupton   |  8.81560 |
| 80249   | Denver        |  8.98407 |
| 80024   | Dupont        |  9.42712 |
+---------+---------------+----------+
8 rows in set (0.03 sec)

Just to add, I'm new to using MySQL to do this stuff, so any advice on this would be greatly appreciated.