Solved

Great Circle Distance

Posted on 2009-07-10
3
638 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
Comment Utility
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 31

Assisted Solution

by:awking00
awking00 earned 200 total points
Comment Utility
0
 
LVL 23

Author Closing Comment

by:Kamaraj Subramanian
Comment Utility
Thanks
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL syntax error in VBA 11 31
PL/SQL LOOP CURSOR 3 39
2 Access tables, count verbiage used 6 19
PL/SQL - Leading zeros 7 39
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.​
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

772 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

16 Experts available now in Live!

Get 1:1 Help Now