mysql one-to-many with latitude/longitude

Question

I also searched around but found nothing that really helped. Here's the situation:

Let's say I have a simple relationship for cities and weather predictions. We can relate with the latitude and longitude, so we could have the following:

CITY TABLE:

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

WEATHER PREDICTION TABLE:

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

I have this query to get the closest record to Barcelona in (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

But, What's the most efficient way to query for return all cities with the most near weather prediction point for a date, like this:

PREDICTION FOR (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

score 0 · Answer 1 · edited May 23 '17 at 11:50

The best way is you pre-calculate the values on db for each lat, long because that is the most cost operation.

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

Same for weather

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

Other is you pre-calculate the radius in db 1000 meters to each direction, won't be a radius circle but a square.

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

Final query need:

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

Then using variables you can assign a row_id to get the smallest distance of each city.

ROW_NUMBER() in MySQL

mysql one-to-many with latitude/longitude

1 Answers1