到目前为止,我已经能够在同一半径内找到最近的点(这里是仓库)。(已经有很多答案的常见问题)。
这是实际的代码(它不使用多个半径)

$radius = 5; // miles

$q = "
    SELECT DISTINCT
      warehouse_latitude.post_id,
      warehouse_longitude.meta_value as longitude,
      warehouse_latitude.meta_value as latitude,
      ((ACOS(SIN($latitude * PI() / 180) * SIN(warehouse_latitude.meta_value * PI() / 180) + COS($latitude * PI() / 180) * COS(warehouse_latitude.meta_value * PI() / 180) * COS(($longitude - warehouse_longitude.meta_value) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance
    FROM {$this->wpdb->postmeta} as warehouse_latitude
      LEFT JOIN {$this->wpdb->postmeta} as warehouse_longitude ON warehouse_latitude.post_id = warehouse_longitude.post_id
    WHERE warehouse_latitude.meta_key = 'warehouse_latitude' AND warehouse_longitude.meta_key = 'warehouse_longitude'
    HAVING distance < $radius
    ORDER BY distance ASC
    LIMIT 1
";

相反:
我想选择最近的点,但半径是为每个点(仓库)定义的。
A点(9714):半径5英里
B点(9715):半径2英里
C点(9716):半径10英里
所以如果B点和C点在范围内,B点是最近的,它应该选择B点。
wp_postmeta表:
meta_id | post_id | meta_key             | meta_value
------------------------------------------------------
324802  | 9714    | warehouse_latitude   | 47.1978754
324809  | 9715    | warehouse_latitude   | 47.2064462
324814  | 9716    | warehouse_latitude   | 47.214434
324803  | 9714    | warehouse_longitude  | -1.54441
324810  | 9715    | warehouse_longitude  | -1.5461347
324815  | 9716    | warehouse_longitude  | -1.565993
324806  | 9714    | warehouse_radius     | 5
324811  | 9715    | warehouse_radius     | 2
324816  | 9716    | warehouse_radius     | 10

架构:
mysql - SQL:从给定的纬度和经度中查找不同半径内的最近点-LMLPHP

最佳答案

没有答案。太长,无法评论。。。

1  LEFT JOIN warehouse_longitude ON ... -- This is an INNER JOIN because of LINE 3
2  LEFT JOIN warehouse_radius ON ...    -- This is also an INNER JOIN, because of LINE 4
3 WHERE warehouse_longitude.meta_key = 'warehouse_longitude'
4   AND warehouse_radius.meta_key = 'warehouse_radius'

所以你不妨把这些写为内部连接。

关于mysql - SQL:从给定的纬度和经度中查找不同半径内的最近点,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/52271343/

10-13 00:52