mysql - Order results by proximity (with coordinates & radius) -

Given a database of 4 circles, where each circle has radius and a geographic center:

  
 
 ID | Radius | Latitude | Longitude --- + -------- + ---------- + ---------- 1 | 3 | 40.71 | 100.23 2 | 10 | 50.13 | 100.23 3 12 | 39.92 || 100.23 4 4. 80.99 | 100.23   
 Note: To keep things simple, the longitude for each circle is identical.  
 Assuming that we are on circle 2, I correspond to  latitude  /  longitude  coordinate and  radius  of each circle Find each board nearby. For example, according to the latitude / longitude coordinates, we have this command:  
 
 Circle 1 (due to proximity:  9.42 <50.13 - 40.71 )  Circle 3 (Proximity reason:  10.21 <- 50.13 - 39.92 )  
 Circle 4 (Reason for proximity:  30.86 <- 80.99 - 50.13 )   
 But according to coordinates of latitude / longitude and the radius of each circle, we should:  
 
 circle 3 ( Because of proximity:  1.79 <-12 - 10.21 )  
 Circle 1 (Proximity RAN:  6.42 <- li> )  
 Circle 4 (due to proximity:  26.86 <- 30.86 - 4 )   
 Is there an easy way to do this in SQL?  
  
 
 
  cube  and  physicality  extensions Can be provided in the discrete so that at least the estimated answer can be generated. In particular, they believe that the Earth is a simple field, which makes mathematics very easy.  
 With those extensions, you can make the distance between Circle 2 and others like this:  
 circle from circle, circle x, where x.id = 2 and circle. Id & lt; & Gt; From the circle, select circle.id, earth_distance (ll_to_earth (circle.latitude, circle.longitude), ll_to_earth (x.latitude, x.longitude)); X.id order by 2;   
 The circle should be correct for the radius, from the above distance to  x.radius  and  circle.radius  should be subtracted, however you need it By default, the value in  earth_distance  meters will be calculated.  
 Now, do a little more than scanning the entire list of queries to the circle and calculate the distance for each, then sort and limit them, which is more challenging, some approaches are: < / P> 
 
 Using the ability of the cube to index with the essence, so that you can make a pointer to search within a certain box around the center of any of the chakras, and hence the list of circles Down to Consider can.  
 The distance between each circle and everything else is edited at any time of a circle, using the triggers to maintain this calculation in a separate table.   
 The second option basically starts:  
  To create a table circle, an AID to AIID, BIID, B_ID, Earth_distance (ll_to_earth (a. Latitude, a.longitude), ll_to_earth (b.latitude, B.longitude)) circle, circle B where a.id  circle_distance  called by the trigger on   
 then  circle . This means that you can do this:  
  Select earth_distance from b_id where a_id = $ circle_id by earth_design limit $ n   
 to use this query Will be able to quickly scan  (a_id, earth_distance)  index