|
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 as latitude (lat float(10,6)) & longitude (lng float(10,6)).
There are two possible options:
1) Loop through all the places in the databases, find the distance from the center point and keep the places that have distance less or equal to the radius.
2) Define an sql function to find the distance between two places, and select the places having distance less or equal to the radius.
Obviously the second option is way better and efficient than the first one. Here is how you do it
DELIMITER $$
DROP FUNCTION IF EXISTS `getDistance`$$ // drop any previously defined function
CREATE FUNCTION `getDistance`(lat1 float(10,6),lng1 float(10,6), c1 VARCHAR(120), c2 VARCHAR(30))
RETURNS VARCHAR(120)
BEGIN
DECLARE lng2, lat2, distance float(10,6);
select CAST(c1 as DECIMAL(10,6)) into lat2;
select CAST(c2 as DECIMAL(10,6)) into lng2;
select ((ACOS(SIN(lat1 * PI() / 180) * SIN(lat2 * PI() / 180) + COS(lat1 * PI() / 180) * COS(lat2 * PI() / 180) * COS((lng1 - lng2) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) into distance;
RETURN distance;
END$$
DELIMITER ;
It defines a MySQL function that you can use from within your code or using SQL query
USAGE: select id, email from custs where getDistance (lat, lng, x, y) <= radius;
where:
lat: is a column in your database representing latitude of a place
lng: is a column in your database representing longitude of a place
x: is the latitude of a central position, say "36.288757"
y: is the longitude of a central position, say "111.804558"
radius: is the radius within which you want to find places. |
