Procedure permettant de récupérer des entités situées dans un rayon de X mètres

procedure_distance.sql

CREATE DEFINER=`root`@`localhost` 
PROCEDURE `getMediaNear`(IN `latitude` DOUBLE, IN `longitude` DOUBLE, IN `distance` FLOAT)
    NO SQL
BEGIN
	DECLARE `lon1` FLOAT;
	DECLARE `lon2` FLOAT;
	DECLARE `lat1` FLOAT;
	DECLARE `lat2` FLOAT;
        
    SET lon1 = longitude-distance/abs(cos(radians(latitude))*111);
	SET lon2 = longitude+distance/abs(cos(radians(latitude))*111);
	SET lat1 = latitude-(distance/111);
	SET lat2 = latitude+(distance/111);
        
    SELECT Media.id, 6371*2*ASIN(SQRT(POWER(SIN((latitude-Media.lat)*pi()/180/2), 2)+COS(latitude*pi()/180)*COS(Media.lat*pi()/180)*POWER(SIN((longitude-Media.lng)*pi()/180/2), 2))) AS Media__distance
    FROM medias AS Media, sites AS Site
    WHERE Media.site_id = Site.id
    AND Site.close = 1 
    AND Site.valid = 1 
    AND Site.public = 1
    AND Media.valid = 1
    AND Media.lng BETWEEN lon1 
    AND lon2 
    AND Media.lat BETWEEN lat1 
    AND lat2
    HAVING Media__distance <= distance 
    ORDER BY Media__distance;
END