PHP/SQL 使用一个 SELECT 语句从多个其他 lat/lng 位置获取所有 lat/lng 位置

Question

我有两个表:一个 map 表，其中包含大约 3,000 个纬度/经度对，代表现实世界的“标记”位置；还有一个建筑物表，其中有数百个纬度/经度对，也代表真实世界的建筑物位置。我想做的是获取所有建筑物可见的所有标记，假设建筑物有单独定义的“视野范围”。我有一个完全正常运行的 PHP 脚本，可以成功运行:

<?php

// query the Buildings table to get the building data we need
$sql = "SELECT type, lat, lng FROM Buildings;";

$result = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_assoc($result)) {
    $allBuildings[] = $row;
}

$result -> close();

// count how many buildings there are to save time in the loop
$buildingsNumber = count($allBuildings);

// this is the key query, which runs a SELECT query for every building so that only the markers visible by the buildings are shown
for ($i = 0; $i < $buildingsNumber; $i++) {
    // get the building's lat/lng
    $lat = $allBuildings[$i]["lat"];
    $lng = $allBuildings[$i]["lng"];

    // get the building's vision range in metres
    $distance = $buildings[$allBuildings[$i]["type"]]["vision_range"];

    // this is the core query. it is a radius search using lat/lngs with the centre being the building's location and the radius being its vision_range
    $sql = "SELECT Map.lat, Map.lng,
    (6378137 * acos(cos(radians($lat)) * cos(radians(lat)) * cos(radians(lng) - radians($lng)) + sin(radians($lat)) * sin(radians(lat))))
    AS distance FROM Map
    HAVING distance < $distance;";

    while ($row = mysqli_fetch_assoc($result)) {
        $markersArray[] = $row;
    }
}

?>

问题是，对于如此多的单独查询，结果会在大约 20-30 秒内返回。这是有道理的，因为从 PHP 服务器到 MySQL 服务器的往返时间似乎大约需要 30 毫秒。我需要一个几乎立即返回结果的查询，因此理想情况下我希望避免这些多次往返并发送一个返回一组结果的查询。

我之前曾尝试在不同的查询中使用 mysqli_multi_query，但从未让它成功工作。我也在考虑连接查询，但我不确定这是否有效。有人成功地做过类似的事情吗？或者有人可以建议一种方法来重写我的脚本，只需要运行一个 SELECT 查询吗？

非常感谢，乔治

Answer 1

感谢艾玛的建议。下面是上面的脚本，经过重写，将 SELECT 查询移到 for 循环之外，并对结果使用多重查询:

<?php

// query the Buildings table to get the building data we need
$sql = "SELECT type, lat, lng FROM Buildings;";

$result = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_assoc($result)) {
    $allBuildings[] = $row;
}

$result -> close();

// count how many buildings there are to save time in the loop
$buildingsNumber = count($allBuildings);

// this is the key query, which runs a SELECT query for every building so that only the markers visible by the buildings are shown
for ($i = 0; $i < $buildingsNumber; $i++) {
    // get the building's lat/lng
    $lat = $allBuildings[$i]["lat"];
    $lng = $allBuildings[$i]["lng"];

    // get the building's vision range in metres
    $distance = $buildings[$allBuildings[$i]["type"]]["vision_range"];

    // this is the core query. it is a radius search using lat/lngs with the centre being the building's location and the radius being its vision_range
    $sql .= "SELECT Map.lat, Map.lng,
    (6378137 * acos(cos(radians($lat)) * cos(radians(lat)) * cos(radians(lng) - radians($lng)) + sin(radians($lat)) * sin(radians(lat))))
    AS distance FROM Map
    HAVING distance < $distance;";
}

// note two important things: multi query doesn't work without the MYSQLI_USE_RESULT flag, and we don't close a multi query result otherwise we get server errors
$result = mysqli_multi_query($conn, $sql, MYSQLI_USE_RESULT);
while ($row = mysqli_fetch_assoc($result)) {
    $markersArray[] = $row;
}

?>

请注意我最后关于多查询的评论的重要性，因为它给我带来了很多悲伤。