匹配最接近的经度/纬度

发布于 2024-10-17 02:09:37 字数 613 浏览 2 评论 0原文

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

现在，我们不想使用 MaxMind 提供的任何其他信息，因为特征名称（即城市）之间存在很多差异来执行查找。如果其他方法无法找到某个功能，我们计划尝试进行此类查找，但出于性能原因，浮点数查找比字符串查找快得多。

现在，我对如何找到 Maxmind 给我们的数据库的最接近匹配的 LAT/LONG 有点一无所知。问题是，我们的数据库特征比 Maxmind 的精度高得多，因此直接比较可能无效。如果我们尝试在查询期间对列应用 ROUND()，那显然会非常慢。

鉴于以下数据，最快的方法是否只是类似于

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 中有一些新的空间存储类型，也许任何人都可以提供一个解决方案，超越我似乎给自己设置的障碍。

原文

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.

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.

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

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

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

嘿看小鸭子会跑 2024-10-24 02:09:37

执行此操作的优雅（更准确）方式（但速度不快）

// 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 公式用于计算距地理坐标的圆距离。以下是此公式在不同平台中的一些实现

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

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;

Edit

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

回复收藏 0 原文

~没有更多了~