在给定距离的表中查找彼此远离的坐标

zqry0prt 于 2021-06-23 发布在 Mysql

关注(0)|答案(2)|浏览(424)

我在mysql表中有类似的数据(约500k）记录

id  latitude    longitude   
------  ----------  ------------
106837  24.7218925  68.2604037  
106838  24.7218947  68.260412   
106839  24.7219007  68.2604083  
106840  24.721902   68.260403   
106841  24.7219149  68.260416   
106842  24.7219169  68.2604118  
106843  24.7219172  68.2604141  
106844  24.7219269  68.2604097  
106845  24.7219299  68.2604039  
106846  24.7219346  68.2603994  
106847  24.7219409  68.2604027  
106848  24.7219434  68.2604129  
106849  24.721956   68.2603941  
106850  24.7219879  68.2603614  
106851  24.7268579  68.2586257  
106852  24.7283047  68.2575022  
106853  24.7283047  68.2575032  
106854  24.7283141  68.2575256  
106855  24.728375   68.2575342  
106856  24.7283862  68.2575007  
106857  24.7284202  68.2575555  
106858  24.7284468  68.257605   
106859  24.7284485  68.2576076  
106860  24.7284639  68.2576095  
106861  24.7284675  68.2576157

我想过滤所有这些坐标是100米远的彼此。
我有500k的坐标，其中一些是在相同的地方，彼此重叠，但我只想区分那些至少在100米距离内彼此相距较远的坐标
架构：

CREATE TABLE `coordinates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `region` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `area` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `territory` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `town` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `latitude` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `longitude` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `completed` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=533273 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

用图片和更多细节更新我的问题。
图像更清晰：

如你所见，我在Mapa，b，c，d，e，f上有6个坐标，我的表中有这些坐标，如下所示。

现在，什么是只检索a，b，c和d坐标的查询？我不想得到e和f，因为e已经离d最近了，f离c很近，或者我们可以说e和f都在其他坐标的半径100米范围内。我希望你能理解我的问题。

mysql latitude-longitude coordinates

来源：https://stackoverflow.com/questions/50580565/find-coordinates-in-a-table-away-from-each-other-at-given-distance

2条答案

按热度按时间

omjgkv6w1#

你可以通过使用 where 子句，其中使用st_distance_sphere（g1，g2[，radius]）函数将差值大于等于100

select *
from tbl
where ST_Distance_Sphere(POINT(Latitude,Longitude), POINT(Latitude,Longitude)) >=100

赞(0）回复(0）举报 2021-06-23

4sup72z82#

算法1。聚类分析
据我所知，你想找到点的“簇”。这是一个非常困难的数学挑战。它超出了简单sql操作的范围。
算法2。详尽的
所以我会简化任务。你从一个500万点的列表开始。您将从列表中删除点，直到没有两点“彼此非常接近”。

Foreach point, A, remaining in the list
    Foreach other point, B, in the list
        If A and B are within 100 meters, delete B from the list.

让我们分析一下这个简单的算法。
假设最终结果是10万分。我们需要询问您需要执行“IsaNearB”测试多少次。
第一点a必须与500k-1 bs进行比较。
最后一点a将不得不与大约10万个bs进行比较。
所以比较的总数在100k^2到500k^2之间。这些数值分别是100亿和2500亿。讨厌。这可能需要几个星期的时间。
算法3：“桶”
做一个200米乘200米的网格(简单sql；0.5m操作）
计算每个点所在的桶。把这个放在一个与点相关的列中。
对于每个铲斗，进行距离检查以消除“非常接近”的点(可能只有几百万次测试。）
现在你已经接近有一个清理点列表。但两个接近的点可能在网格中相邻的桶中。这可以通过重新调整电网，使其向东移动100米，然后向南移动，然后向西移动来解决。也就是说，执行上述3个步骤共4次。
距离
同时，你真的想做算术吗 VARCHAR(191) ? 如果你想使用 ST_Distance_Sphere() . 或者你可以换成 DOUBLE 并使用一个简单的毕达哥拉斯算法(我不知道哪个更快。但我知道这两种方法中的任何一种都足够精确，可以用来决定100米的距离。
请使用innodb。

赞(0）回复(0）举报 2021-06-23

我来回答

在给定距离的表中查找彼此远离的坐标

2条答案

相关问题

热门标签

最新问答