匹配最接近的经度/纬度 [英] Matching closest longitude/latitude

问题描述

使用Maxmind的GeoIP软件，我们可以在大约80％的时间内在25英里内将IP地址的LONG/LAT范围缩小到相对精度.

Using Maxmind's GeoIP software, we can narrow down the LONG/LAT of an IP address to relative accuracy within 25 miles around 80% of the time.

现在，我们不想使用MaxMind提供的任何其他信息，因为在要素名称(即城市)之间存在很多差异以进行查找.我们计划在其他方法无法找到特征的情况下尝试进行这种查找，但是出于性能方面的考虑，在浮点数上的查找比字符串快得多.

Now, we don't want to use any of the other information provided by MaxMind, because there are a lot of discrepancies between feature names, i.e., cities, to perform a look up. We plan on attempting such a look up if other methods fail to locate a feature, but for performance reasons, look ups on floats are much faster than strings.

现在，我对如何找到Maxmind提供给我们数据库的最接近的LAT/LONG一无所知.问题是，我们的datbase功能与Maxmind相比具有更高的精度，因此直接比较可能无效.如果我们尝试在查询过程中将ROUND()应用于列，那显然会很慢.

Now, I'm a little clueless on how we can find the closest matching LAT/LONG given from Maxmind to our database. The problem is, our datbase features has a much higher precision compared to that of Maxmind, therefore a straight comparison might not be effective. If we try applying a ROUND() to the column during query, that will obviously be really slow.

给出以下数据，最快的方法就是这样

Given the following data, would the fastest way simply be something like

长79.93213 LAT 39.13111

LONG 79.93213 LAT 39.13111

SELECT `feature_name` FROM `geo_features`
WHERE long BETWEEN 79.93 AND 79.79.94
AND lat BETWEEN 39.13 AND 39.14

任何人都可以很快提出一种优雅的解决方案吗?我知道MySQL 5中有一些新的空间存储类型，也许任何人都可以提供我似乎不愿为自己蒙蔽的解决方案.

Can anyone thing of an elegant solution that will be blazing fast? I know there are some new spatial storage types in MySQL 5, perhaps anyone can provide a solution beyond the blinders I've seem to put up on myself.

推荐答案

一种优雅的方法(更准确)(但不能很快燃烧)

The elegant (more accurate) way of doing this (but not blazing fast)

// Closest within radius of 25 Miles
// 37, -122 are your current coordinates
// To search by kilometers instead of miles, replace 3959 with 6371
SELECT feature_name, 
 ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) 
  * cos( radians( long ) - radians(-122) ) + sin( radians(37) ) 
  * sin( radians( lat ) ) ) ) AS distance 
FROM geo_features HAVING distance < 25 
ORDER BY distance LIMIT 1;

修改

这是 Haversine公式，用于计算距地理坐标的圆弧距离.以下是不同平台

This is Haversine formula for calculating circular distance from geo-coordinates. Here are some implementation of this formula in different platforms

R = earth’s radius (mean radius = 6,371km)
Δlat = lat2− lat1
Δlong = long2− long1
a = sin²(Δlat/2) + cos(lat1).cos(lat2).sin²(Δlong/2)
c = 2.atan2(√a, √(1−a))
d = R.c
// Note that angles need to be in radians to pass to Trigonometric functions

这篇关于匹配最接近的经度/纬度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！