Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Great Circle Distance

Posted on 2009-07-10
Medium Priority
656 Views
I need a MYSQL function which receive two geo-coordinates (standard latitude and longitude expressed as DECIMAL(12,9)) and must return the distance in miles between the two points

As far as i have seen these links.

http://www.experts-exchange.com/Database/MySQL/Q_23080406.html?sfQueryTermInfo=1+circl+distanc+great+mysql

http://www.meridianworlddata.com/Distance-Calculation.asp

0
Question by:Kamaraj Subramanian
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 46

Accepted Solution

Kent Olsen earned 1200 total points
ID: 24825940
Hi itkamaraj,

I don't believe that and of the major DMBS engines have a distance based on coordinates function built into them so you'll have to build one yourself.

Below is a PHP function to do that.  It will return kilometers or miles.  You'll just have to convert it to SQL.  :)

Good Luck,
Kent

``````function distance(\$lat1, \$lng1, \$lat2, \$lng2, \$miles = true)
{
\$pi80 = M_PI / 180;
\$lat1 *= \$pi80;
\$lng1 *= \$pi80;
\$lat2 *= \$pi80;
\$lng2 *= \$pi80;
\$r = 6372.797; // mean radius of Earth in km
\$dlat = \$lat2 - \$lat1;
\$dlng = \$lng2 - \$lng1;
\$a = sin(\$dlat / 2) * sin(\$dlat / 2) + cos(\$lat1) * cos(\$lat2) * sin(\$dlng / 2) * sin(\$dlng / 2);
\$c = 2 * atan2(sqrt(\$a), sqrt(1 - \$a));
\$km = \$r * \$c;
return (\$miles ? (\$km * 0.621371192) : \$km);
}
``````
0

LVL 32

Assisted Solution

awking00 earned 800 total points
ID: 24826390
0

LVL 23

Author Closing Comment

ID: 31602203
Thanks
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question