草庐IT

mysql - SQL:根据纬度/经度查找最近的邻居

coder 2023-10-16 原文

我有一张包含咖啡馆纬度/经度的表格。我想做一个 SQL 查询，为我提供离每家咖啡馆最近的咖啡馆。有人可以提供有关如何执行此操作的建议吗？

表格基本上是这样的:

咖啡馆 ID

+-------------------------+----------------------+----------------------+
| cafe_id                 | gps_latitude         | gps_longitude        |
+-------------------------+----------------------+----------------------+
| 011-1003                | 55.86649500000000000 |  8.16856200000000000 |
| 192-143                 | 57.04419159749860000 | 10.36447024359820000 |
| 037-0233                | 55.08773849210000000 |  8.56101036070000000 |
| 121-934                 | 56.89120900000000000 |  9.16818100000000000 |
+-------------------------+----------------------+----------------------+

非常感谢任何帮助!

最佳答案

您可以使用 Spherical Law of Cosines得到用 earth-radii 表示的距离.

有些人更喜欢 Haversine formula因为它给出了更好的精度，但考虑到 MySql 的浮点精度足够高，两者之间的差异可以忽略不计。第一个更容易实现:

select     c1.cafe_id,
           substring_index (
             group_concat( c2.cafe_id order by 
               acos(  sin(radians(c1.gps_latitude)) * sin(radians(c2.gps_latitude)) 
                    + cos(radians(c1.gps_latitude)) * cos(radians(c2.gps_latitude)) 
                      * cos(radians(c2.gps_longitude-c1.gps_longitude)) ) ),
             ',', 1) nearest
from       cafe c1
inner join cafe c2 on c1.cafe_id <> c2.cafe_id
group by   c1.cafe_id

样本数据的输出是:

|  cafe_id |  nearest |
|----------|----------|
| 011-1003 | 037-0233 |
| 037-0233 | 011-1003 |
| 121-934  | 192-143  |
| 192-143  | 121-934  |

这是一个MySql fiddle .

说明

距离计算用在 group_concat 聚合函数的 order by 子句中，这会产生 cafe_id 的逗号分隔列表值按照他们到您分组的咖啡馆的距离排序。 substring_index 函数从该列表中提取第一项。

连接条件很重要，因为如果没有它，您会将咖啡馆本身作为最近的邻居(那么它的距离显然为 0)。

设置距离限制

在评论中，您要求只包含特定半径内的邻居的可能性。

在那种情况下，您可以输出“距离”，转换为公里(英里将是一个不同的因素):

select     c1.cafe_id,
           substring_index (
             group_concat( c2.cafe_id order by 
               acos(  sin(radians(c1.gps_latitude)) * sin(radians(c2.gps_latitude)) 
                    + cos(radians(c1.gps_latitude)) * cos(radians(c2.gps_latitude)) 
                      * cos(radians(c2.gps_longitude-c1.gps_longitude)) ) ),
             ',', 1) nearest,
           min(
               acos(  sin(radians(c1.gps_latitude)) * sin(radians(c2.gps_latitude)) 
                    + cos(radians(c1.gps_latitude)) * cos(radians(c2.gps_latitude)) 
                      * cos(radians(c2.gps_longitude-c1.gps_longitude)) ) )
             * 6371 km
from       cafe c1
inner join cafe c2 on c1.cafe_id <> c2.cafe_id
group by   c1.cafe_id

现在您可以根据距离决定是否要忽略邻居。如果你真的想排除最近邻居太远的咖啡馆，那么在末尾添加一个 having 子句:

having     km < 5

如果您更喜欢英里数，则在 SQL 中使用 3959 作为乘数而不是 6371。

关于mysql - SQL:根据纬度/经度查找最近的邻居，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/39634800/

mysqlSQLgps_latituderadianslatitudelatitude-longitude

有关mysql - SQL:根据纬度/经度查找最近的邻居的更多相关文章

  1. ruby - 如何根据特征实现 FactoryGirl 的条件行为 - 2

    我有一个用户工厂。我希望默认情况下确认用户。但是鉴于unconfirmed特征，我不希望它们被确认。虽然我有一个基于实现细节而不是抽象的工作实现，但我想知道如何正确地做到这一点。factory:userdoafter(:create)do|user,evaluator|#unwantedimplementationdetailshereunlessFactoryGirl.factories[:user].defined_traits.map(&:name).include?(:unconfirmed)user.confirm!endendtrait:unconfirmeddoenden

  2. ruby - 当使用::指定模块时，为什么 Ruby 不在更高范围内查找类？ - 2

    我刚刚被困在这个问题上一段时间了。以这个基地为例:moduleTopclassTestendmoduleFooendend稍后，我可以通过这样做在Foo中定义扩展Test的类:moduleTopmoduleFooclassSomeTest但是，如果我尝试通过使用::指定模块来最小化缩进:moduleTop::FooclassFailure这失败了:NameError:uninitializedconstantTop::Foo::Test这是一个错误，还是仅仅是Ruby解析变量名的方式的逻辑结果？ 最佳答案 Isthisabug,or

  3. ruby - 查找字符串中的内容类型(数字、日期、时间、字符串等) - 2

    我正在尝试解析一个CSV文件并使用SQL命令自动为其创建一个表。CSV中的第一行给出了列标题。但我需要推断每个列的类型。Ruby中是否有任何函数可以找到每个字段中内容的类型。例如，CSV行:"12012","Test","1233.22","12:21:22","10/10/2009"应该产生像这样的类型['integer','string','float','time','date']谢谢! 最佳答案 require'time'defto_something(str)if(num=Integer(str)rescueFloat(s

  4. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  5. Hive SQL 五大经典面试题 - 2

    目录第1题连续问题分析：解法：第2题分组问题分析：解法：第3题间隔连续问题分析：解法：第4题打折日期交叉问题分析：解法：第5题同时在线问题分析：解法：第1题连续问题如下数据为蚂蚁森林中用户领取的减少碳排放量iddtlowcarbon10012021-12-1212310022021-12-124510012021-12-134310012021-12-134510012021-12-132310022021-12-144510012021-12-1423010022021-12-154510012021-12-1523.......找出连续3天及以上减少碳排放量在100以上的用户分析：遇到这类

  6. sql - 查询忽略时间戳日期的时间范围 - 2

    我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

  7. ruby - 如何使用 Selenium Webdriver 根据 div 的内容执行操作？ - 2

    我有一个使用SeleniumWebdriver和Nokogiri的Ruby应用程序。我想选择一个类，然后对于那个类对应的每个div，我想根据div的内容执行一个Action。例如，我正在解析以下页面:https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=puppies这是一个搜索结果页面，我正在寻找描述中包含“Adoption”一词的第一个结果。因此机器人应该寻找带有className:"result"的div，对于每个检查它的.descriptiondiv是否包含单词“adoption

  8. ruby - 如何根据长度将路径数组转换为嵌套数组或散列 - 2

    我需要根据字符串路径的长度将字符串路径数组转换为符号、哈希和数组的数组给定以下数组:array=["info","services","about/company","about/history/part1","about/history/part2"]我想生成以下输出，对不同级别进行分组，根据级别的结构混合使用符号和对象。产生以下输出:[:info,:services,about:[:company,history:[:part1,:part2]]]#altsyntax[:info,:services,{:about=>[:company,{:history=>[:part1,:pa

  9. ruby-on-rails - 在 Rails 中更高效地查找或创建多条记录 - 2

    我有一个应用需要发送用户事件邀请。当用户邀请friend(用户)参加事件时，如果尚不存在将用户连接到该事件的新记录，则会创建该记录。我的模型由用户、事件和events_user组成。classEventdefinvite(user_id,*args)user_id.eachdo|u|e=EventsUser.find_or_create_by_event_id_and_user_id(self.id,u)e.save!endendend用法Event.first.invite([1,2,3])我不认为以上是完成我的任务的最有效方法。我设想了一种方法，例如Model.find_or_cr

  10. ruby-on-rails - 无法安装 mysql2 0.3.14 gem - 2

    我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby​​目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin

随机推荐