Eyesis :: Projects :: SQL :: Procedure to Calculate Distance

> Projects > SQL > Procedure to Calculate Distance

SQL > Procedure to Calculate Distance

Sometimes it may be necessary to have to distance calculation on the database side. It's always best to do as much processing on the database side as you can. This SQL procedure can be utilized to determine the distance between two different coordinates.

The CalcDist function accepts 4 parameters. One set of coordinates, a challenging set of coordinates and a unit of distance the result should be returned in (default is miles). Coordinates must be stored and passed in order for this to work.


DELIMITER |

CREATE FUNCTION CalcDist

(

    iLat1 DECIMAL(9,6),

    iLng1 DECIMAL(9,6),

    iLat2 DECIMAL(9,6),

    iLng2 DECIMAL(9,6),

    iUnits CHAR(2)

) RETURNS DECIMAL(6,1)

DETERMINISTIC

BEGIN

    DECLARE wDist DECIMAL(6,1);



    SET wDist = DEGREES(ACOS(SIN(RADIANS(iLat1)) 

                * SIN(RADIANS(iLat2)) + COS(RADIANS(iLat1)) 

                * COS(RADIANS(iLat2)) * COS(RADIANS(iLng1 - iLng2)))) 

                * 60 * 1.1515;



    IF iUnits = 'km' THEN

        RETURN wDist * 1.609344;

    END IF;



    RETURN wDist;

END|

Example Usage

To get members within 40 km of the zip code 90210 you could use something like this:

SELECT *, CalcDist('34.103131', '-118.416253', `Lat`, `Lng`, 'km') AS `Dist`
FROM `members`
HAVING `Dist` < 40
ORDER BY `Dist`;

Keep in mind that you'll need to store the latitude and longitude of your members. Using their zip/postal code, the coordinates can be determined using my EyeGeoData class.