mysql one-to-many with latitude/longitude

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

Answers


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


Need Your Help

TeamCity ssh user

ssh continuous-integration teamcity

I'm trying to run a script from Teamcity Agent on other environment (mac).

Generate IIS application key programmatically

c# asp.net iis command-line

Using the IIS manager GUI, it is easy to use the 'Connections' tree view to navigate to an application, under ASP.Net open 'Machine Key', and then click 'Generate Keys' in order to generate a key for