在MySql中使用空间函数查找两个坐标之间的距离的正确方法 [英] Correct way of finding distance between two coordinates using spatial function in MySql
问题描述
我正在尝试使用Mysql和PostgresSQL中的空间函数来计算两个位置之间的距离.我已经从Google取得了经度和纬度.详细信息如下
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
位置一-纬度:42.260223;朗:-71.800010
Location one - Lat: 42.260223; Lon: -71.800010
位置2-纬度:42.245647;朗:-71.802521
Location two - Lat: 42.245647; Lon: -71.802521
使用的SQL查询:
SELECT DISTANCE(GEOMFROMTEXT('Point(42.260223 -71.800010)'),GEOMFROMTEXT('Point(42.245647 -71.802521)'))
SELECT DISTANCE(GEOMFROMTEXT('Point(42.260223 -71.800010)'),GEOMFROMTEXT('Point(42.245647 -71.802521)'))
两个数据库都给出相同的结果0.014790703059697.但是,当我在其他系统中计算距离时,结果却有所不同.请参考以下链接
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=搜索 = 1.44英里
http://www.distancecheck.com/zipcode- distance.php?start = 01601& end = 01610 = 1.53英里
所以我想知道我的计算方法/查询是否正确.如果错了,那么查询距离的正确方法是什么.
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.
推荐答案
简单的答案是使用Haversine公式.假定地球不是一个球体,但这不是一个不好的近似.本演示文稿中对此进行了描述,其中包括许多其他细节:
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
这篇关于在MySql中使用空间函数查找两个坐标之间的距离的正确方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!