CODEWITHSUNDEEP
mysql
Rajendra Khabiya
Rajendra Khabiya

Reputation: 2030

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

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.

Upvotes: 11

Views: 2913

Answers (3)

albert hou
albert hou

Reputation: 66

I am not sure you want a pure sql solution or not. I can only give a solution with other language. I am assume to use id to determine the nearest node.

 List locat= new List(); 
  last = getTheLastRecord();
  locat.add(last);
  count = getTheCountOfRecord();
  for(int i=1;i<count;i++){//i = 1 because last record already read.
   Record r = getRecord(i);
   if(compareDistance(r,last)>5000){
     locat.add(r);
     last = r;
   }
 }

// finally the list of locat will contain every location you want.

Upvotes: 0

Philip Devine
Philip Devine

Reputation: 1169

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 ...");

Upvotes: 6

Markus
Markus

Reputation: 693

So you need to calculate the distance from Lat Lon and then check if the result is greater than 5km. Problem with your sample data is, that the calculated distances are within meters, so you won't get any result from that. I guess you have a few more locations to check in your table.

try

SELECT 
    a.id, a.Latitude, a.Longitude, CONCAT(a.ID,"-",b.ID) as 'FromTo',
    6371 * acos( 
                  cos(radians( b.Latitude ))
                * cos(radians( a.Latitude ))
                * cos(radians( b.Longitude ) - radians( a.Longitude ))
                + sin(radians( b.Latitude )) 
                * sin(radians( a.Latitude ))) as distance 
FROM new_table a INNER JOIN new_table b ON a.id <> b.id
HAVING distance >= 0.001
ORDER BY id, distance;

I set the having clause to greater than a meter HAVING distance >= 0.001. if you want to check for km adjust accordingly!

EDIT

it is not the fastes solution you might have to tweak it a little bit but the procedure would look like

DELIMITER $$
CREATE PROCEDURE `calcDistWithin`(IN dist double)
BEGIN

declare maxTempID int;
declare maxTblID int;
declare breakLoop boolean;
SET breakLoop = FALSE;

DROP TEMPORARY TABLE IF EXISTS tmp;
CREATE TEMPORARY TABLE tmp (ID int, Latitude double, Longitude double, distance double, toID varchar(10));
DROP TEMPORARY TABLE IF EXISTS tmpOUT;
CREATE TEMPORARY TABLE tmpOUT (ID int, Latitude double, Longitude double, distance double, toID varchar(10));

INSERT INTO tmp select ID, Latitude, Longitude, 0, "---"  from new_table limit 1;
INSERT INTO tmpOUT select ID, Latitude, Longitude, 0, "---"  from new_table limit 1;

SELECT ID INTO maxTblID FROM new_table ORDER BY ID DESC LIMIT 1;
SELECT ID into maxTempID FROM tmp ORDER BY ID DESC LIMIT 1;

WHILE breakLoop = FALSE DO

    IF EXISTS (SELECT
                6371 * acos( 
                  cos(radians( b.Latitude ))
                * cos(radians( a.Latitude ))
                * cos(radians( b.Longitude ) - radians( a.Longitude ))
                + sin(radians( b.Latitude )) 
                * sin(radians( a.Latitude ))) as distance
               FROM tmp a INNER JOIN new_table b 
               WHERE a.ID < b.ID AND a.ID = maxTempID
               HAVING distance >= dist
               LIMIT 1) THEN

        INSERT INTO tmpOUT SELECT
            b.ID, b.Latitude, b.Longitude,
            6371 * acos( 
                  cos(radians( b.Latitude ))
                * cos(radians( a.Latitude ))
                * cos(radians( b.Longitude ) - radians( a.Longitude ))
                + sin(radians( b.Latitude )) 
                * sin(radians( a.Latitude ))) as distance,
            a.ID
        FROM tmp a INNER JOIN new_table b 
        WHERE a.ID < b.ID AND a.ID = maxTempID
        HAVING distance >= dist
        ORDER BY a.ID, b.ID, distance
        LIMIT 1;

        INSERT INTO tmp SELECT ID, Latitude, Longitude, distance, toID FROM tmpOUT ORDER BY ID DESC LIMIT 1;
        SELECT ID into maxTempID FROM tmpOUT order by ID DESC LIMIT 1;

    ELSE

        SET breakLoop = TRUE;

    END IF;

END WHILE;

SELECT * FROM tmpOUT;

END$$
DELIMITER ;

to call it just use

CALL calcDistWithin(5.00)

Upvotes: 2

Related Questions