Last active
August 31, 2020 06:06
-
-
Save ker0x/a273a1d4f2deb8450de1 to your computer and use it in GitHub Desktop.
Procedure permettant de récupérer des entités situées dans un rayon de X mètres
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment