Correct way of finding distance between two coordinates using spatial function in MySql

Question

I am trying to calculate distance between two locations using spatial functions in both Mysql and PostgresSQL. I have taken the latitude and longitude from Google. The details are below

Location one - Lat: 42.260223; Lon: -71.800010

Location two - Lat: 42.245647; Lon: -71.802521

SQL Query used:

SELECT DISTANCE(GEOMFROMTEXT('Point(42.260223 -71.800010)'),GEOMFROMTEXT('Point(42.245647 -71.802521)'))

The both databases are giving the same result 0.014790703059697. But when I calculate distance in other systems the results are different. Please refer the below links

http://www.zip-codes.com/distance_calculator.asp?zip1=01601&zip2=01610&Submit=Search = 1.44 miles

http://www.distancecheck.com/zipcode-distance.php?start=01601&end=01610 = 1.53 miles

So I want to know whether my calculation method/query is right or not. And if it is wrong, then what is the right way of querying the db for the distance.

Halflight

possible duplicate of this – Bihar

Answer 1

The simple answer is to use the Haversine formula. This assumes the earth is a sphere, which it isn't, but it's not a bad approximation. This, with lots of other details are described in this presentation:

http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

Upthrust

This presentation was probably the most concise and straight-to-the-meat solution I've come across. It's configured to return distances in miles but just swap out the value for the earth's radius with the kilometre equivalent and you're off to the races. – Kiyohara

yes, this is the best reckoner I found. Just want to confirm whether the distance returned by the formula is in km or miles? – Jewess

If you're using 3956 for the radius, your numbers will come out in miles. – Upthrust

Answer 2

In the case above, MySql is simply applying the pythagorean theorem: c2 = a^2 + b^2. In this specific case SQRT((42.245647 - 42.260223)^2 + (-71.802521^2 - -71.800010)^2) = 0.014790703.

There are actually two problems with using the MySql distance functon for distance with coordinates on a sphere. (1) MySql is caclulating distance on a plane, not a sphere. (2) The result is coming back in a form of degrees, not miles. To get a true, spherical distance in miles, km, ft etc, you need to convert your lat and long degrees into the units you want to measure by determining the radius from a line through the center of the earth for the latitude(s) you are measuring.

To get a true measure is quite complicated, many individuals and companies have made careers out of this.

Larger