java - 查找表 2 中与表 1 中的每个位置最接近的位置

Question

我当前使用的 mySQL 数据库有两个表:一个名为 source，另一个名为 siteinfo。

siteinfo表如图所示:

+-----------+----------+----------+
| longitude | latitude | sitecode |
+-----------+----------+----------+
|    1.3009 | 1.900989 |        2 |
+-----------+----------+----------+
|    1.7034 | 1.20034  |        3 |
+-----------+----------+----------+

同样，源表如下所示:

+-----------+----------+----------+
| longitude | latitude | sitecode |
+-----------+----------+----------+
|    1.3009 | 1.900989 |   NULL   |
+-----------+----------+----------+

我的基本目标是:

对于 source 表中的每一行，我想要获取名为经度和纬度的列，并将它们与 siteinfo 表中相应的经度和纬度列进行比较。

源表中的那些行的经度和纬度行与 siteinfo 表中的行最相似，然后使用相应的 更新 sitecode 列siteinfo 表中 sitecode 列的 sitecode。

例如，源表中的 long 和 lat 值与 siteinfo 表第一行中的值最接近，因此 sitecode 更新为2.

为了解决这个问题，我知道我必须创建两个结果集并使用 while(next()) 迭代每个结果集的每一行。问题是，如何比较才能找到最相似的条目？

我想用蛮力来实现，使用 For 循环来比较源结果集的第一行条目与 Siteinfo 结果集的每一行，然后递增到第二行。

但是，我了解结果集通过 next() 函数迭代每一行，这与 for 循环不同，因为我想将一个结果集的第一行与另一个结果集的每一行进行比较。

二、如何找出两个结果表的某列条目的差异？

Answer 1

不，您不必创建两个结果集。您只需创建一个 MySQL 函数来计算 Great Circle Distance两点之间...

CREATE FUNCTION great_circle_km (lon1 DOUBLE, lat1 DOUBLE, lon2 DOUBLE, lat2 DOUBLE)
RETURNS DOUBLE
RETURN 6371 * acos(cos(radians(lat1)) * cos(radians(lat2)) * cos(radians(lon2) - radians(lon1)) + sin(radians(lat1)) * sin(radians(lat2)))

...然后像这样使用 UPDATE 查询

UPDATE `source` SET `source`.`sitecode` = (
    SELECT `sitecode` FROM `siteinfo`
    ORDER BY great_circle_km(`source`.`longitude`, `source`.`latitude`, `siteinfo`.`longitude`, `siteinfo`.`latitude`)
    LIMIT 1
    )

请注意，上述查询将执行相当于 CROSS JOIN(笛卡尔积)的操作，因此对于较大的源表，处理的有效行数将显着增加。例如，使用两个 500 行表将有效处理 250,000 行，使用两个 5,000 行表将有效处理 25,000,000 行。

如果表之间可能存在大量精确匹配，那么首先更新它们可能会更快......

UPDATE 
    `source` 
    INNER JOIN 
    `siteinfo` 
        ON `source`.`longitude` = `siteinfo`.`longitude` 
            AND `source`.`latitude` = `siteinfo`.`latitude`
SET `source`.`sitecode` = `siteinfo`.`sitecode`

...然后更新剩余行

UPDATE `source` SET `source`.`sitecode` = (
    SELECT `sitecode` FROM `siteinfo`
    ORDER BY great_circle_km(`source`.`longitude`, `source`.`latitude`, `siteinfo`.`longitude`, `siteinfo`.`latitude`)
    LIMIT 1
    )
WHERE `source`.`sitecode` IS NULL