时间戳

首页 > 解决方案 > MySQL 空间连接最近点

mysql - MySQL 空间连接最近点

问题描述

我环顾四周，发现很多人试图按到设定点的距离来订购一张点表，但我很好奇如何在两点之间的最小距离上有效地连接两张表。就我而言，请考虑表格nodes和centroids.

CREATE TABLE nodes (
    node_id VARCHAR(255),
    pt POINT
);
CREATE TABLE centroids (
    centroid_id MEDIUMINT UNSIGNED,
    temperature FLOAT,
    pt POINT
);

我有大约 300k 个节点和 15k 个质心，我想获得离每个节点最近的质心，这样我就可以为每个节点分配一个温度。到目前为止，我已经pt在两个表上创建了空间索引并尝试运行以下查询：

SELECT
    nodes.node_id,
    MIN(ST_DISTANCE(nodes.pt, centroids.pt))
FROM nodes
INNER JOIN centroids
ON ST_DISTANCE(nodes.pt, centroids.pt) <= 4810
GROUP BY
    nodes.node_id
LIMIT 10;

显然，这个查询不会解决我的问题；它不检索温度，假设最近的质心在 4810 以内，并且只评估 10 个节点。但是，即使进行了这些简化，此查询的优化也很差，并且在我键入时仍在运行。当我让 MySQL 提供有关查询的详细信息时，它说没有使用索引，并且没有任何空间索引被列为可能的键。

我如何构建一个查询，该查询实际上可以使用空间索引有效地返回我想要加入的数据？

标签： mysqlsqlspatial

解决方案

有很多方法可以解决这个每组最少 n 个问题。

一种方法使用自左连接反模式（这允许联系）：

select 
    n.node_id,
    c.centroid_id,
    st_distance(n.pt, c.pt) dist,
    c.temperature
from nodes n
cross join centroids c
left join centroids c1 
    on c1.centroid_id <> c.centroid_id
    and st_distance(n.pt, c1.pt) < st_distance(n.pt, c.pt)
where c1.centroid_id is null

相同的逻辑可以用not exists条件来表示。

另一种选择是使用相关子查询进行过滤（这不允许联系）：

select 
    n.node_id,
    n.node_id,
    c.centroid_id,
    st_distance(n.pt, c.pt) dist,
    c.temperature
from nodes n
inner join centroids c
    on c.centroid_id = (
        select c1.centroid_id
        from centroids c1
        order by st_distance(n.pt, c1.pt) 
        limit 1
    )

最后：如果你想要的只是temperature最近的质心，那么一个简单的子查询应该是一个不错的选择：

select 
    n.node_id,
    (
        select c1.temperature
        from centroids c1
        order by st_distance(n.pt, c1.pt) 
        limit 1
    ) temperature 
from nodes n

推荐阅读