在MySql中使用空间函数查找两个坐标之间的距离的正确方法
我正在尝试使用 Mysql 和 PostgresSQL 中的空间函数来计算两个位置之间的距离。我从谷歌获取了纬度和经度。详细信息如下
位置一 - 纬度：42.260223；经度：-71.800010
位置二 - 纬度：42.245647； Lon：-71.802521
使用的 SQL 查询：
SELECT DISTANCE(GEOMFROMTEXT('Point(42.260223 -71.800010)'),GEOMFROMTEXT('Point(42.245647 -71.802521)'))
两个数据库给出相同的结果 0.014790703059697。但是当我在其他系统中计算距离时，结果是不同的。请参考以下链接
http://www. zip-codes.com/distance_calculator.asp?zip1=01601&zip2=01610&Submit=Search = 1.44 英里
http://www.distancecheck.com/zipcode-distance.php?start=01601&end=01610 = 1.53 英里
所以我想知道我的计算方法/查询是否正确。如果是错误的，那么查询数据库距离的正确方法是什么。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
简单的答案是使用半正矢公式。这假设地球是一个球体，但事实并非如此，但这并不是一个糟糕的近似。此演示文稿中描述了这一点以及许多其他详细信息：
http: //www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL
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 只是应用勾股定理：c2 = a^2 + b^2。在这种特定情况下，SQRT((42.245647 - 42.260223)^2 + (-71.802521^2 - -71.800010)^2) = 0.014790703。
使用 MySql 距离函数来计算球体上坐标的距离实际上存在两个问题。 (1) MySql计算的是平面上的距离，而不是球体上的距离。 (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.