Select Records which have distance of 5km or greater between them

Question

I am working on a location app and i need to get all locations from my Mysql Location table which have a 5km distance between them.

For Ex location table have below entries:

  id     Latitude       Longitude
  1     22.7499180     75.8950577
  2     22.7498474     75.8950653
  3     22.7498035     75.8950424
  4     22.7497787     75.8950729
  5     22.7498245     75.8950806
  6     22.7497902     75.8950272
  7     22.7497864     75.8950424
  8     22.7497768     75.8950500
  9     22.7497864     75.8950577
  10    22.7497921     75.8950653
  11    22.7497597     75.8950653
  12    22.7498283     75.8950653
  13    22.7497978     75.8950577

So from above table how i need to fetch results something like this

  id     Latitude       Longitude   Distance (>=5Km)
  1     22.7499180     75.8950577     --
  4     22.7497787     75.8950729    6km (From lat long of id 1)
  8     22.7497768     75.8950500    8km (From lat long of id 4)
  11    22.7497597     75.8950653    6km (From lat long of id 8)
  13    22.7497978     75.8950577    10km (From lat long of id 11)

I searched a lot to get such results but i got query only to get result on basis of some fixed lat/long or a fixed radius. Please help with Mysql query if possible.

Edit (from OP's comment)

I need is to calculate distance from last selected value... For Ex. Start from Record 1. distance of 1 is compared with record 2 it is < 5km, compared with record 3 also < 5km ,when compared with 4 its distance is > 5km so we keep it in list THAN NEXT RECORD WILL BE COMPARED WITH RECORD 4. so distance of 4 will be compared with 5 and if record 5 have distance > 5km from 4 next comparison is done with record 5 as reference.

Answer 1

No stored procedure, just pure unbridled sql glory:

SET @prevLong=-1.0000;
SET @prevLat=-1.0000;
SET @currDist=1.0000;
select id, diff from (
select id,
@prevLat prev_lat,
@currDist:= 6371 * 2 * (atan2(sqrt(sin(radians(@prevLat - lat)/2)
                       * sin(radians(@prevLat - lat)/2)
                       + cos(radians(lat))
                       * cos(radians(@prevLat))
                       * sin(radians(@prevLong - longi)/2)
                       * sin(radians(@prevLong - longi)/2))
                  ,sqrt(1-(sin(radians(@prevLat - lat)/2)
                           * sin(radians(@prevLat - lat)/2)
                           + cos(radians(lat))
                           * cos(radians(@prevLat))
                           * sin(radians(@prevLong
                                         - longi)/2)
                           * sin(radians(@prevLong - longi)/2))))) diff,
@prevLong prevLong,
case when @currdist > 5 then @prevLat:=lat  else null end  curr_lat,
case when @currDist > 5 then @prevLong:= longi  else null end  curr_long

  from latLong
order by id asc
) a where diff > 5

SQLFiddle to prove that magic is real: http://sqlfiddle.com/#!9/7e4fe/19

Edit In Codeigniter you can use variables like the following:

$this->db->query("SET @prevLong=-1.0000");
$this->db->query("SET @prevLat=-1.0000");
$this->db->query("SET @prevDist=-1.0000");

Then issue your query as normal

$query= $this->db->query("SELECT ...");