基于Beacon的室内导航路径查询问题（PHP+MySQL环境）

看起来你是想通过mapping表中定义的关联关系，找到从Beacon A到Beacon J的导航路径，之前尝试用Haversine公式没得到理想结果对吧？我来给你一个完整的实现思路和代码示例。

首先，我们需要明确两个核心需求：遍历关联的Beacon找到可行路径，以及计算路径的总距离。MySQL 8.0及以上版本支持递归CTE（公共表表达式），非常适合处理这种图结构的路径遍历问题；而Haversine公式可以用来计算两个Beacon之间的球面距离，我们可以把每一段路径的距离累加得到总长度。

1. MySQL递归查询获取路径及总距离 #

下面的SQL查询会递归遍历mapping表，从Beacon A（id=1）出发，找到所有到达Beacon J（假设其id为10，你需要替换成实际ID）的路径，并计算每条路径的总距离，最后返回最短的那条路径：

WITH RECURSIVE beacon_path AS (
    -- 起始点：Beacon A
    SELECT 
        b.id AS current_id,
        b.beacon AS current_name,
        CAST(b.id AS CHAR(255)) AS path_ids,
        CAST(b.beacon AS CHAR(255)) AS path_names,
        0 AS total_distance,
        0 AS steps
    FROM beacons b
    WHERE b.id = 1 -- Beacon A的ID
    UNION ALL
    -- 递归遍历关联的Beacon
    SELECT 
        m.mapping_beacon_id AS current_id,
        b.beacon AS current_name,
        CONCAT(bp.path_ids, ',', m.mapping_beacon_id) AS path_ids,
        CONCAT(bp.path_names, ' -> ', b.beacon) AS path_names,
        -- 累加当前段的Haversine距离（单位：公里）
        bp.total_distance + (
            6371 * ACOS(
                COS(RADIANS(bp_lat.latitude)) * COS(RADIANS(b.latitude)) * COS(RADIANS(b.longitude) - RADIANS(bp_lat.longitude)) +
                SIN(RADIANS(bp_lat.latitude)) * SIN(RADIANS(b.latitude))
            )
        ) AS total_distance,
        bp.steps + 1 AS steps
    FROM beacon_path bp
    JOIN mapping m ON bp.current_id = m.beacon_id
    JOIN beacons b ON m.mapping_beacon_id = b.id
    JOIN beacons bp_lat ON bp.current_id = bp_lat.id
    -- 避免循环：排除已经在路径中的Beacon
    WHERE NOT FIND_IN_SET(m.mapping_beacon_id, bp.path_ids)
)
-- 筛选到达Beacon J的路径，按总距离升序排序，取最短的一条
SELECT 
    path_names AS navigation_path,
    path_ids AS beacon_ids,
    ROUND(total_distance, 2) AS total_distance_km,
    steps
FROM beacon_path
WHERE current_id = 10 -- 替换为Beacon J的实际ID
ORDER BY total_distance ASC, steps ASC
LIMIT 1;

关键说明： #

• 递归CTE：beacon_path分为两部分，锚点成员定义起始位置，递归成员不断关联mapping表拓展路径。
• Haversine公式：使用地球半径6371公里计算两点间球面距离，若需要英里可以替换为3956。
• 防循环：通过FIND_IN_SET检查下一个Beacon是否已经在当前路径中，避免无限递归。
• 路径排序：优先按总距离排序，再按步数排序，确保得到最短的物理路径。

2. PHP代码实现 #

接下来用PHP连接MySQL，执行上述查询并展示结果。这里使用PDO来确保代码的安全性和兼容性：

<?php
// 数据库配置（替换为你的实际信息）
$host = 'localhost';
$dbname = 'your_database';
$username = 'your_username';
$password = 'your_password';

try {
    // 建立PDO连接
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Beacon J的实际ID（替换为你的值）
    $beaconJId = 10;

    // 准备SQL查询
    $query = "
        WITH RECURSIVE beacon_path AS (
            SELECT 
                b.id AS current_id,
                b.beacon AS current_name,
                CAST(b.id AS CHAR(255)) AS path_ids,
                CAST(b.beacon AS CHAR(255)) AS path_names,
                0 AS total_distance,
                0 AS steps
            FROM beacons b
            WHERE b.id = 1
            UNION ALL
            SELECT 
                m.mapping_beacon_id AS current_id,
                b.beacon AS current_name,
                CONCAT(bp.path_ids, ',', m.mapping_beacon_id) AS path_ids,
                CONCAT(bp.path_names, ' -> ', b.beacon) AS path_names,
                bp.total_distance + (
                    6371 * ACOS(
                        COS(RADIANS(bp_lat.latitude)) * COS(RADIANS(b.latitude)) * COS(RADIANS(b.longitude) - RADIANS(bp_lat.longitude)) +
                        SIN(RADIANS(bp_lat.latitude)) * SIN(RADIANS(b.latitude))
                    )
                ) AS total_distance,
                bp.steps + 1 AS steps
            FROM beacon_path bp
            JOIN mapping m ON bp.current_id = m.beacon_id
            JOIN beacons b ON m.mapping_beacon_id = b.id
            JOIN beacons bp_lat ON bp.current_id = bp_lat.id
            WHERE NOT FIND_IN_SET(m.mapping_beacon_id, bp.path_ids)
        )
        SELECT 
            path_names AS navigation_path,
            path_ids AS beacon_ids,
            ROUND(total_distance, 2) AS total_distance_km,
            steps
        FROM beacon_path
        WHERE current_id = :beacon_j_id
        ORDER BY total_distance ASC, steps ASC
        LIMIT 1;
    ";

    // 执行查询
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(':beacon_j_id', $beaconJId, PDO::PARAM_INT);
    $stmt->execute();

    $result = $stmt->fetch(PDO::FETCH_ASSOC);

    // 展示结果
    if ($result) {
        echo "<h2>从Beacon A到Beacon J的导航路径</h2>";
        echo "<p><strong>路径：</strong>{$result['navigation_path']}</p>";
        echo "<p><strong>Beacon ID序列：</strong>{$result['beacon_ids']}</p>";
        echo "<p><strong>总距离：</strong>{$result['total_distance_km']} 公里</p>";
        echo "<p><strong>路径步数：</strong>{$result['steps']}</p>";
    } else {
        echo "<p>未找到从Beacon A到Beacon J的有效路径，请检查mapping表关联关系。</p>";
    }

} catch(PDOException $e) {
    echo "数据库错误：" . $e->getMessage();
}

// 关闭连接
$pdo = null;
?>

3. 兼容性说明 #

如果你的MySQL版本低于8.0（不支持递归CTE），可以改用PHP实现BFS（广度优先搜索）算法来遍历路径：

1. 从Beacon A出发，维护一个队列存储当前Beacon和已走路径。
2. 每次取出队列中的元素，查询其关联的Beacon。
3. 如果关联Beacon是目标，则返回路径；否则将新路径加入队列（避免重复访问）。
4. 计算总距离时，遍历路径中的每对相邻Beacon，用Haversine公式累加距离。

这种方法的代码量会稍大，但兼容性更好，有需要的话可以再问我要具体实现。

内容的提问来源于stack exchange，提问作者Ashwin