Geek Answers Handbook

The correct way to find the distance between two coordinates using a spatial function in MySql

I am trying to calculate the distance between two points using spatial functions in both Mysql and PostgresSQL. I took latitude and longitude from Google. Details below

Location 1 - Lat: 42.260223; Lon: -71.800010

The location of the two - Lat: 42.245647; Lon: -71.802521

Used SQL query:

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

Both databases give the same result 0.014790703059697. But when I calculate the distance on other systems, the results are different. Please refer to the links below.

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 if the method / method request is correct. And if that is not true, then what is the correct way to query db for distance.

+8
mysql gis spatial
source share
2 answers

The simple answer is to use the Haversin formula. This suggests that the Earth is a sphere that it is not, but this is not a bad approximation. This, with many other details, is described in this presentation:

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

+8
source share

In the above case, MySql simply applies the Pythagorean theorem: c2 = a ^ 2 + b ^ 2. In this particular 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 function for distance with coordinates on a sphere. (1) MySql is the dropping distance on the plane, not the sphere. (2) The result is returned in degrees, not miles. To get the true, spherical distance in miles, km, feet, etc., you need to convert your lat and long degrees into units that you want to measure by determining the radius of the line from the center of the earth for latitude (latitude) you measure.

To get the true measure is quite difficult, many people and companies have made a career out of it.

+5
source share

More articles:


All Articles