选择范围+毕达哥拉斯定理的结果

i have a mysql table for uk people that includes:

  • postcode beginning (i.e. BB2)
  • latitude (int)
  • longitude (int)
  • range (int, in miles, 1-20)

http://www.easypeasy.com/guides/article.php?article=64 - theres the article for the sql file i based my table on

now it says i can use Pythagoras theorem to calculate distances based on longitude and latitude.

so lets say i wanted to select all the people who are in range (based on the 1-20 they enter in the range field) of a postcode beginning i search for

for example, lets say i search for "BB2", i want a query that will select all of the people whose postcode beginning is "BB2", AND all the people within range of BB2 (going by the 1-20 mile range in their database field).

can some math whiz help me out?

Google revealed this - the code's in PERL but you can figure out the logic.

Formula and code for calculating distance based on two lat/lon locations

The following is the formula I use in perl to do the calculations. Perl expects all of the angles to be in radians.

return &acos(cos($a1)*cos($b1)*cos($a2)*cos($b2) + cos($a1)*sin($b1)*cos($a2)*sin($b2) + sin($a1)*sin($a2)) * $r;


Where:

$a1 = lat1 in radians
$b1 = lon1 in radians
$a2 = lat2 in radians
$b2 = lon2 in radians
$r = radius of the earth in whatever units you want

The values I use for radius of the earth are:

3963.1 statute miles 3443.9 nautical miles 6378 km

To convert the decimal degrees to radians use the following perl.

# define an accurate value for PI

$pi = atan2(1,1) * 4;

#
# make sure the sign of the angle is correct for the direction
# West an South are negative angles
#

$degrees = $degrees * -1 if $direction =~ /[WwSs]/;
$radians = $degrees*($pi/180);

To convert degree minutes and seconds to decimal degrees use the following perl formula.

$dec_deg = $deg + ($min + $sec/60)/60;

Finally, there is no acos function in perl so here is the function I use. I don't remember where I got the math for this.

# subroutine acos
#
# input: an angle in radians
#
# output: returns the arc cosine of the angle
#
# description: this is needed because perl does not provide an arc cosine function
sub acos {
    my($x) = @_;
    my $ret = atan2(sqrt(1 - $x**2), $x);        return $ret;
}

no it says you can calculate distances base on x,y coordinates not longitude-latitude (using Pythagoras theorem).

to calculate distance between 2 points (x1,y1) & (x2,y2):

d = SquareRoot((x2-x1)^2 + (y2-y1)^2)

using longitude-latitude, you can see here: Calculate distance, bearing and more between two Latitude/Longitude points