imranul hoque's blog

Sometimes we need to find out list of places that are within a certain radius from a center place where coordinates of the places are saved in the database. Now we have 2 solutions for this – either loop through all the places find the distance from the center point and keep the places that have distance less or equal to the radius, or make an sql function to find the distance of two places, and select the places having distance less or equal to the radius. Obviously the second option is better than the first one. So I have written a Mysql function that does the work for you.
In my case, the coordinates were saved in the database as a string of the form "10.1357002, 49.9225563, 0". This is the standard format of coordinates that is used by many (for example Google map). The first element is the longitude, second one is latitude and we can ignore the third (always 0). So here is the Mysql function that returns the distance between 2 coordinates in Miles.

DELIMITER $$

DROP FUNCTION IF EXISTS `GetDistance`$$

CREATE FUNCTION `GetDistance`(coordinate1 VARCHAR(120), coordinate2 VARCHAR(120))
	RETURNS VARCHAR(120)
BEGIN
	DECLARE pos_comma1, pos_comma2 INT;
	DECLARE lon1, lon2, lat1, lat2, distance DECIMAL(12,8);

	select locate(',', coordinate1) into pos_comma1;
	select locate(',', coordinate1, pos_comma1+1) into pos_comma2;
	select CAST(substring(coordinate1, 1, pos_comma1-1) as DECIMAL(12,8)) into lon1;
	select CAST(substring(coordinate1, pos_comma1+1, pos_comma2-pos_comma1-1) as DECIMAL(12,8)) into lat1;

	select locate(',', coordinate2) into pos_comma1;
	select locate(',', coordinate2, pos_comma1+1) into pos_comma2;
	select CAST(substring(coordinate2, 1, pos_comma1-1) as DECIMAL(12,8)) into lon2;
	select CAST(substring(coordinate2, pos_comma1+1, pos_comma2-pos_comma1-1) as DECIMAL(12,8)) into lat2;

        select ((ACOS(SIN(lat1 * PI() / 180) * SIN(lat2 * PI() / 180) + COS(lat1 * PI() / 180) * COS(lat2 * PI() / 180) * COS((lon1 - lon2) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) into distance;
	RETURN distance;

END$$

DELIMITER ;

Example Usage:

Lets say you have to find out all the postcodes that are in 40 miles radius from a place having coordinate "10.1357002, 49.9225563, 0". You have a table POSTCODES having fields id, postcode, coordinates. So your sql should look like this:

  select id, postcode from POSTCODES where GetDistance("10.1357002, 49.9225563, 0", coordinates) <= 40;

Isn’t it simple?