mysql - 结果按距坐标最小距离的分辨率分组

Question

我有一个查询来查找距给定坐标最近的纬度/经度:

公共(public)函数 findClosestByLatitudeLongitude($latitude, $longitude, $distanceUnit = 111.045, $radius = 150)

$stmt = $this->db->prepare('SELECT
        f.fcst_latitude,
        f.fcst_longitude,
        f.fcst_resolution,
        :distance_unit * DEGREES(
            ACOS(
              COS(
                RADIANS(:latitude)
              ) * COS(
                RADIANS(f.fcst_latitude)
              ) * COS(
                RADIANS(:longitude) - RADIANS(f.fcst_longitude)
              ) + SIN(
                RADIANS(:latitude)
              ) * SIN(
                RADIANS(f.fcst_latitude)
              )
            )
          ) AS distance
        FROM t_fcst_data_coord AS f
        WHERE 
          f.fcst_latitude BETWEEN :latitude  - (:radius / :distance_unit)
          AND :latitude + (:radius / :distance_unit)
          AND f.fcst_longitude BETWEEN :longitude - (
            :radius / (
              :distance_unit * COS(
                RADIANS(:latitude)
              )
            )
          )
          AND :longitude + (
            :radius / (
              :distance_unit * COS(
                RADIANS(:latitude)
              )
            )
          )
        ORDER BY distance ASC
        LIMIT 100
    ');

结果是一个按距离排序的数组，包含预测的分辨率，如下所示:

(
    [0] => Array
        (
            [fcst_latitude] => 46.295396
            [fcst_longitude] => 6.854558
            [fcst_resolution] => 9.0
            [distance] => 1.2113482186062683
        )

    [1] => Array
        (
            [fcst_latitude] => 46.313622
            [fcst_longitude] => 6.843681
            [fcst_resolution] => 3.0
            [distance] => 1.4198633375521186
        )

    [2] => Array
        (
            [fcst_latitude] => 46.314401
            [fcst_longitude] => 6.884638
            [fcst_resolution] => 3.0
            [distance] => 2.213273758077741
        )

    [3] => Array
        (
            [fcst_latitude] => 46.285180
            [fcst_longitude] => 6.844827
            [fcst_resolution] => 3.0
            [distance] => 2.5347004607874783
        )

    [...] => Array
        (
            [fcst_latitude] => ...
            [fcst_longitude] => ...
            [fcst_resolution] => ...
            [distance] => ...
        )

    [53] => Array
        (
            [fcst_latitude] => 46.199091
            [fcst_longitude] => 6.886765
            [fcst_resolution] => 27.0
            [distance] => 12.064028782357124
        )

    [...] => Array
        (
            [fcst_latitude] => ...
            [fcst_longitude] => ...
            [fcst_resolution] => ...
            [distance] => ...
        )
)

如何获得仅显示唯一分辨率且按分辨率排列的最小距离的结果？预期结果是:

(
    [0] => Array
        (
            [fcst_latitude] => 46.199091
            [fcst_longitude] => 6.886765
            [fcst_resolution] => 27.0
            [distance] => 12.064028782357124
        )

    [1] => Array
        (
            [fcst_latitude] => 46.295396
            [fcst_longitude] => 6.854558
            [fcst_resolution] => 9.0
            [distance] => 1.2113482186062683
        )

    [2] => Array
        (
            [fcst_latitude] => 46.313622
            [fcst_longitude] => 6.843681
            [fcst_resolution] => 3.0
            [distance] => 1.4198633375521186
        )
)

我尝试按 fcst_resolution 进行分组并选择 MIN 距离，但结果是一个纬度和经度错误的数组:

(
    [0] => Array
        (
            [fcst_latitude] => 44.972113
            [fcst_longitude] => 8.737022
            [fcst_resolution] => 9.0
            [distance] => 1.2113482186062683
        )

    [1] => Array
        (
            [fcst_latitude] => 45.231748
            [fcst_longitude] => 5.680505
            [fcst_resolution] => 3.0
            [distance] => 1.4198633375521186
        )

    [2] => Array
        (
            [fcst_latitude] => 45.118703
            [fcst_longitude] => 8.640296
            [fcst_resolution] => 27.0
            [distance] => 12.064028782357124
        )

)

谢谢

Answer 1

有几种方法可以做到这一点。正常的方法是使用子查询来获取每个分辨率的最小距离，然后将其与查询连接起来以获取每个分辨率/距离的完整行。

另一个技巧是按分辨率进行分组，然后对其他每个字段使用 GROUP_CONCAT，按距离排序。然后使用 SUBSTRING_INDEX 从 GROUP_CONCAT 结果中截取每个字段的第一次出现。如果任何内容包含 NULL，或者任何字段返回包含逗号的值，则可能会出现问题。

$stmt = $this->db->prepare('
SELECT
        SUBSTRING_INDEX(GROUP_CONCAT(sub0.fcst_latitude ORDER BY sub0.distance ), ',', 1) AS fcst_latitude,
        SUBSTRING_INDEX(GROUP_CONCAT(sub0.fcst_longitude ORDER BY sub0.distance ), ',', 1) AS fcst_longitude,
        sub0.fcst_resolution,
        SUBSTRING_INDEX(GROUP_CONCAT(sub0.distance ORDER BY sub0.distance ), ',', 1) AS distance,
FROM
(SELECT
        f.fcst_latitude,
        f.fcst_longitude,
        f.fcst_resolution,
        :distance_unit * DEGREES(
            ACOS(
              COS(
                RADIANS(:latitude)
              ) * COS(
                RADIANS(f.fcst_latitude)
              ) * COS(
                RADIANS(:longitude) - RADIANS(f.fcst_longitude)
              ) + SIN(
                RADIANS(:latitude)
              ) * SIN(
                RADIANS(f.fcst_latitude)
              )
            )
          ) AS distance
        FROM t_fcst_data_coord AS f
        WHERE 
          f.fcst_latitude BETWEEN :latitude  - (:radius / :distance_unit)
          AND :latitude + (:radius / :distance_unit)
          AND f.fcst_longitude BETWEEN :longitude - (
            :radius / (
              :distance_unit * COS(
                RADIANS(:latitude)
              )
            )
          )
          AND :longitude + (
            :radius / (
              :distance_unit * COS(
                RADIANS(:latitude)
              )
            )
          )
        ORDER BY distance ASC
        LIMIT 100
) sub0
GROUP BY sub0.fcst_resolution
    ');