#! /usr/bin/perl

use DBI;

my $dbh = DBI->connect($connectstring, $user, $password);

my $getzip = $dbh->prepare("
SELECT longitude, latitude 
FROM zip_codes 
WHERE zip = ?
");
my $getarea = $dbh->prepare("
SELECT DISTINCT zip, city, longitude, latitude 
FROM zip_codes 
WHERE longitude BETWEEN ? AND ? AND latitude BETWEEN ? AND ?
");

print "Enter a ZIP code : ";
my $zip = <STDIN>; chomp $zip;

$getzip->execute($zip);
($long, $lat) = @{ $getzip->fetchall_arrayref->[0] };

print "Longitude: $long\n";
print "Latitude: $lat\n";

print "Enter a radius (miles): ";
my $miles = <STDIN>; chomp $miles;

my $long_d = $miles / (69.1 * cos($lat / 57.3));
my $long_1 = $long - $long_d;
my $long_2 = $long + $long_d;
my $lat_1  = $lat - ($miles / 69.1);
my $lat_2  = $lat + ($miles / 69.1);

printf "Longitudes: %.4f %.4f\n", $long_1, $long_2;
printf "Latitudes: %.4f %.4f\n\n", $lat_1, $lat_2;

$getarea->execute($long_1, $long_2, $lat_1, $lat_2);
$getarea->bind_columns(\$zip, \$city, \$lo, \$la);
while ($getarea->fetch) {
	my $dlat = 69.1 * ($la - $lat);
	my $dlong = 69.1 * ($lo - $long) * cos($lat / 57.3);
	my $dist = sqrt(($dlat ** 2) + ($dlong ** 2));
	printf "$zip $city (%.3f miles)\n", $dist if ($dist <= $miles);
}

$dbh->disconnect();