?
Solved

Great Circle Distance

Posted on 2009-07-10
3
Medium Priority
?
653 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
[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
  • Learn & ask questions
3 Comments
 
LVL 46

Accepted Solution

by:
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);
}

Open in new window

0
 
LVL 32

Assisted Solution

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

Author Closing Comment

by:Kamaraj Subramanian
ID: 31602203
Thanks
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

777 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