Solved

Great Circle Distance

Posted on 2009-07-10
3
640 Views
Last Modified: 2012-05-07
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

Please help me for this
0
Comment
Question by:Kamaraj Subramanian
3 Comments
 
LVL 45

Accepted Solution

by:
Kdo earned 300 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);

}

Open in new window

0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 200 total points
ID: 24826390
0
 
LVL 23

Author Closing Comment

by:Kamaraj Subramanian
ID: 31602203
Thanks
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now