document.write('
');
document.write('DELIMITER $
');
document.write('
');
document.write('DROP FUNCTION IF EXISTS zip_deg2rad$
');
document.write('DROP FUNCTION IF EXISTS zip_gcd$
');
document.write('DROP FUNCTION IF EXISTS zip_distance$
');
document.write('DROP PROCEDURE IF EXISTS zip_radius$
');
document.write('
');
document.write('CREATE FUNCTION zip_deg2rad(DEGREES DOUBLE) RETURNS DOUBLE
');
document.write('BEGIN
');
document.write(' RETURN DEGREES / (180 / PI()+0.000000000000000);
');
document.write('END$
');
document.write('
');
document.write('CREATE FUNCTION zip_gcd(type ENUM(\'M\', \'N\', \'K\'), src_lat DOUBLE, src_long DOUBLE, dst_lat DOUBLE, dst_long DOUBLE) RETURNS DOUBLE
');
document.write('BEGIN
');
document.write(' DECLARE temp DOUBLE;
');
document.write('
');
document.write(' DECLARE STATUTE_MILES DECIMAL(5,1);
');
document.write(' DECLARE NAUTICAL_MILES DECIMAL(9,5);
');
document.write(' DECLARE KILOMETERS DECIMAL(5,1);
');
document.write('
');
document.write(' SET STATUTE_MILES = 3963.0;
');
document.write(' SET NAUTICAL_MILES = 3437.74677;
');
document.write(' SET KILOMETERS = 6378.7;
');
document.write('
');
document.write(' SET src_lat = zip_deg2rad(src_lat);
');
document.write(' SET src_long = zip_deg2rad(src_long);
');
document.write(' SET dst_lat = zip_deg2rad(dst_lat);
');
document.write(' SET dst_long = zip_deg2rad(dst_long);
');
document.write('
');
document.write(' SET temp = ACOS(SIN(src_lat) * SIN(dst_lat) + COS(src_lat) * COS(dst_lat) * COS(dst_long - src_long));
');
document.write('
');
document.write(' IF type = "M" THEN
');
document.write(' SET temp = STATUTE_MILES * temp;
');
document.write(' END IF;
');
document.write('
');
document.write(' IF type = "N" THEN
');
document.write(' SET temp = NAUTICAL_MILES * temp;
');
document.write(' END IF;
');
document.write('
');
document.write(' IF type = "K" THEN
');
document.write(' SET temp = KILOMETERS * temp;
');
document.write(' END IF;
');
document.write('
');
document.write(' RETURN temp;
');
document.write('END$
');
document.write('
');
document.write('CREATE FUNCTION zip_distance(type ENUM(\'M\', \'N\', \'K\'), zip_start VARCHAR(5), zip_finish VARCHAR(5)) RETURNS DOUBLE
');
document.write('BEGIN
');
document.write(' DECLARE distance DOUBLE;
');
document.write('
');
document.write(' DECLARE start_lat DOUBLE;
');
document.write(' DECLARE start_long DOUBLE;
');
document.write(' DECLARE finish_lat DOUBLE;
');
document.write(' DECLARE finish_long DOUBLE;
');
document.write('
');
document.write(' SELECT latitude, longitude INTO start_lat, start_long FROM zipcodes WHERE zip = zip_start;
');
document.write(' SELECT latitude, longitude INTO finish_lat, finish_long FROM zipcodes WHERE zip = zip_finish;
');
document.write('
');
document.write(' SELECT zip_gcd(type, start_lat, start_long, finish_lat, finish_long) INTO distance;
');
document.write('
');
document.write(' RETURN distance;
');
document.write('END$
');
document.write('
');
document.write('CREATE PROCEDURE zip_radius(IN type ENUM(\'M\', \'N\', \'K\'), IN zip_start VARCHAR(5), IN radius INT, prec INT)
');
document.write('BEGIN
');
document.write(' DECLARE src_lat DOUBLE;
');
document.write(' DECLARE src_long DOUBLE;
');
document.write('
');
document.write(' SELECT latitude, longitude INTO src_lat, src_long FROM zipcodes WHERE zip = zip_start;
');
document.write('
');
document.write(' SELECT zip, city, state, county, ROUND(zip_gcd(type, src_lat, src_long, latitude, longitude), prec) AS `distance`
');
document.write(' FROM zipcodes
');
document.write(' WHERE zip != zip_start
');
document.write(' AND (POW((69.1 * (longitude - src_long) * COS(src_lat / 57.3)), 2) + POW((69.1 * (latitude - src_lat)), 2)) <= (radius * radius)
');
document.write(' ORDER BY `distance` ASC;
');
document.write('END$
');
document.write('
');
document.write('DELIMITER ;
');
document.write('
');
document.write('
<?php');
document.write('
$mysqli = mysqli_connect
(\'localhost\',
\'dbuser\',
\'dbpass\',
\'zipcodes\');
');
document.write('
');
document.write('
if (mysqli_connect_errno
()) {');
document.write('
printf("Connection failed: %s\n", mysqli_connect_error
());
');
document.write('
exit;
');
document.write('
}');
document.write('
');
document.write('
$sql =
"CALL zip_radius(\'M\', 28273, 5, 0)";
');
document.write('
');
document.write('
if (!
$mysqli->
real_query($sql)) {');
document.write('
printf("Invalid query: %s\nWhole query: %s\n",
$mysqli->
error,
$sql);
');
document.write('
exit;
');
document.write('
}');
document.write('
');
document.write('
if ($result =
$mysqli->
store_result()) {');
document.write('
while ($record =
$result->
fetch_assoc()) {');
document.write('
$records[] =
$record;
');
document.write('
}');
document.write('
');
document.write('
$result->
close();
');
document.write('
}');
document.write('
');
document.write('
$mysqli->
close();
');
document.write('
');
document.write('
print_r($records);
');
document.write('
?>');
document.write('
');
document.write('Array
');
document.write('(
');
document.write(' [0] => Array
');
document.write(' (
');
document.write(' [zip] => 28217
');
document.write(' [city] => CHARLOTTE
');
document.write(' [state] => NC
');
document.write(' [county] => MECKLENBURG
');
document.write(' [distance] => 2
');
document.write(' )
');
document.write('
');
document.write(' [1] => Array
');
document.write(' (
');
document.write(' [zip] => 28134
');
document.write(' [city] => PINEVILLE
');
document.write(' [state] => NC
');
document.write(' [county] => MECKLENBURG
');
document.write(' [distance] => 4
');
document.write(' )
');
document.write('
');
document.write(' [2] => Array
');
document.write(' (
');
document.write(' [zip] => 29716
');
document.write(' [city] => FORT MILL
');
document.write(' [state] => SC
');
document.write(' [county] => YORK
');
document.write(' [distance] => 5
');
document.write(' )
');
document.write('
');
document.write(' [3] => Array
');
document.write(' (
');
document.write(' [zip] => 28210
');
document.write(' [city] => CHARLOTTE
');
document.write(' [state] => NC
');
document.write(' [county] => MECKLENBURG
');
document.write(' [distance] => 5
');
document.write(' )
');
document.write(')
');
document.write('
');
document.write('');
document.write('It is very important to note that every single time you use zip_radius and zip_distance to remember to ALWAYS quote the zip codes. Since some of these zip codes start with a zero, not quoting them will produce NULL results from MySQL. This is because MySQL will treat them as numbers, and as such, numbers do not start with zeros. [smile] ');
document.write('
');