SQL :从给定的纬度和经度寻找不同半径内的最近点
SQL :从给定的纬度和经度寻找不同半径内的最近点
提问于 2018-09-11 08:02:08
到目前为止，我已经能够在同一半径内找到最近的点(这个上下文中的仓库)。(常见的问题已经有了很多答案)。
这是实际的代码(它不使用多重半径)
$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模式：
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-09-11 09:59:18
因此，这里的解决方案(从上一次查询中更新)。
如果您有一种更有效/更易读的方法来计算距离，请不要介意给另一个解析器。
SELECT DISTINCT
warehouse_latitude.post_id,
warehouse_longitude.meta_value as longitude,
warehouse_latitude.meta_value as latitude,
warehouse_radius.meta_value as radius,
((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
LEFT JOIN {$this->wpdb->postmeta} as warehouse_radius ON warehouse_latitude.post_id = warehouse_radius.post_id
WHERE warehouse_latitude.meta_key = 'warehouse_latitude' AND warehouse_longitude.meta_key = 'warehouse_longitude' AND warehouse_radius.meta_key = 'warehouse_radius'
HAVING distance < (radius * 0.62)
ORDER BY distance ASC
LIMIT 1我在radius值上添加了一个连接：
LEFT JOIN {$this->wpdb->postmeta} as warehouse_radius ON warehouse_latitude.post_id = warehouse_radius.post_id 在WHERE声明中的另一个条件是：
AND warehouse_radius.meta_key = 'warehouse_radius'因此：
我可以将已有的声明更新为：
HAVING distance < (radius * 0.62) -- Km to MilesStack Overflow用户
发布于 2018-09-11 16:33:19
没有答案。太久不能发表评论..。
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'因此，您最好首先编写这些内部连接。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52271343
复制相关文章
相似问题
腾讯云开发者
