Wednesday, January 2, 2013

How to find distances relative to points of data in the same database

If you have a scenario where you need to calculate the distance in miles, between various point given as longitude and latitude, and these points are in the same database, then simply use the Haver-sine formula to calculate the distances:
 select 
    `origin`.`id` AS `o_id`,
    `destination`.`id` AS `d_id`,
    cast((3959 * 
        acos((((cos(radians(`origin`.`latitude`))
        * cos(radians(`destination`.`latitude`)))
        * cos((radians(`destination`.`longitude`)
        - radians(`origin`.`longitude`))))
        + (sin(radians(`origin`.`latitude`))
        * sin(radians(`destination`.`latitude`))))))
   as decimal (4 , 1 )) AS `miles_apart`
from `point_of_origin` `origin` join `destinations` `destination`;

No comments:

Post a Comment