mysql 一对多与纬度/经度

Question

我也四处搜寻，但没有发现任何真正有帮助的东西。情况如下:

假设我对城市和天气预报有一个简单的关系。我们可以与纬度和经度联系起来，因此我们可以得到以下结果:

城市表:

cityId  |  name       |  lat         |  lng
=====================================================
1       |  Barcelona  | -33.46773911 | 151.38214111
2       |  London     | 46.57906604  | 11.24854176
3       |  Paris      | 20.38509560  | -99.95350647
4       |  Madrid     | 44.38623047  | 6.64792013

天气预报表:

weatherId  | date        | prediction |  lat         |  lng
=====================================================================
1          |  2015-01-01 | SUN        | -33.36773911 | 151.28214111
2          |  2015-01-02 | CLOUD      | -33.36773911 | 151.28214111
3          |  2015-01-01 | RAIN       | 44.37623047  | 6.64792013

我有这个查询来获取 (2015-01-01) 与巴塞罗那最接近的记录:

SELECT prediction, lat, lng, (6371 * acos(cos(radians(-33.46773911)) * cos(radians(lat)) * cos(radians(lng) - radians(151.38214111)) + sin(radians(-33.46773911)) * sin(radians(lat)))) as radius
FROM weather
WHERE
  (lat between -33.06773911 and -33.56773911) AND
  (lng between 151.08214111 and 151.58214111) AND
  date = '2015-01-01'
HAVING
  radius IS NOT NULL AND radius <= 2000
ORDER BY
  radius ASC
LIMIT 1

但是，查询返回某个日期天气预报点最近的所有城市的最有效方法是什么，如下所示:

预测(2015-01-01):

cityId  |  name       |  lat         |  lng          | prediction
==================================================================
1       |  Barcelona  | -33.46773911 | 151.38214111  | SUN
2       |  London     | 46.57906604  | 11.24854176   | RAIN
3       |  Paris      | 20.38509560  | -99.95350647  | RAIN
4       |  Madrid     | 44.38623047  | 6.64792013    | RAIN

Answer 1

最好的方法是预先计算每个经纬度的 db 值，因为这是成本最高的操作。

Id  |  name |  lat  |  lng  | acos(cos(radians(lat)) c1 | radians(lng) c2 | sin(radians(lng)) c3
=====================================================
1   |  Bar  | -33.4 | 151.3
2   |  Lon  | 46.5  | 11.2
3   |  Par  | 20.3  | -99.9
4   |  Mad  | 44.3  | 6.6

天气也一样

Id  | date  | pred |  lat  |  lng  | cos(radians(lat)) w1 | radians(lng) w2 | sin(radians(lat) w3
=====================================================================
1   |  2015 | SUN  | -33.3 | 151.2
2   |  2015 | CLOUD| -33.3 | 151.2
3   |  2015 | RAIN | 44.3  | 6.6

另一个是你预先计算每个方向 1000 米分贝的半径，不会是一个半径圆，而是一个正方形。

Id  |  name |  lat  |  lng  | lat_east_1000 | lat_west_1000 | lng_north_1000 | lng_south_1000
    =====================================================
    1   |  Bar  | -33.4 | 151.3
    2   |  Lon  | 46.5  | 11.2
    3   |  Par  | 20.3  | -99.9
    4   |  Mad  | 44.3  | 6.6

最终查询需求:

SELECT *, distance(using c1,c2,c3,w1,w2,w3 precalculated values) as distance
FROM city c
JOIN weather w
  ON w.lat between c.lat_west_1000 and c.lat_east_1000
 AND w.lng between c.lng_north_1000 and c.lnd_south_1000

然后使用变量，您可以分配 row_id 来获取每个城市的最小距离。

ROW_NUMBER() in MySQL