有效地从数据库中选择最接近的(距离)记录 [英] Effectively selecting the closest (distance) record from a database

问题描述

我有一个拥有4万个场所的数据库，并且现在正在不断增长.

I've got a database with a 40k venues and growing right now.

假设我是红点

我希望能够尽快检索最近的记录.

I want to be able to retrieve the closest record as quickly as possible.

但是下一个项目的距离也可以是任意值.并且也可能有0-n个匹配项.但是，当我只寻找1个时，是否需要加载所有40000个结果?

However the distance too the next item could be anything. And there also might be 0-n matches. But do I need to load all 40000 results when I'm just looking for 1?

如何按距离对记录进行排序?应该在MYSQL还是PHP中完成? 这种计算几乎发生在每个用户，每个页面的每个请求上，因此解决方案需要快速.

How can I sort the records by distance? Should it be done in MYSQL, or PHP? This calculation happens at almost every request, per user, per page, so the solution needs to be quick.

编辑:感谢您提供快速且有希望的答案，我需要查看这些资源，并在几天之内接受/评论答案.

Edit Thanks for the quick and promising answers, I'll need to review these resources, and will accept/comment on answers within a few days.

推荐答案

此Scribd演示文稿(理论+数学公式+ Mysql)中涵盖了此问题: MySQL的地理距离

this problem is covered in this Scribd presentation (theory + math formulas + Mysql): Geo Distance with MySQL

我希望它能满足您的所有需求

I hope it covers everything you need

这篇关于有效地从数据库中选择最接近的(距离)记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！