Mysql stored procedure-query the qualified users within the radius and the sorted distance through Baidu coordinates

: This article mainly introduces the Mysql stored procedure-using Baidu coordinates to query qualified users within the radius and the distance after sorting. if you are interested in the PHP Tutorial, refer to it. I have done a similar application before. I looked it up today and thought it was not very reasonable. so I re-thought it and wrote a query stored procedure.

The table is not described,

The process is as follows:

-- ------------------------------ Procedure structure for DIS-- ----------------------------DROP PROCEDURE IF EXISTS `DIS`;DELIMITER ;;CREATE DEFINER=`root`@`localhost` PROCEDURE `DIS`(IN `_lat` varchar(11),IN `_lng` varchar(11),IN `_ras` int,INOUT `_data` text)BEGINDECLARE _sql text ;DECLARE EARTH_RADIUS VARCHAR(10) ;DECLARE _range VARCHAR(16);DECLARE lngR VARCHAR(16);DECLARE maxLat VARCHAR(16);DECLARE minLat VARCHAR(16);DECLARE maxLng VARCHAR(16);DECLARE minLng VARCHAR(16);SET EARTH_RADIUS = 6378.137;SET _range = 180 / pi() * _ras / EARTH_RADIUS;SET lngR = _range / cos(_lat * pi() / 180);SET maxLat = _lat + _range;SET minLat = _lat - _range;SET maxLng = _lng + lngR ;SET minLng = _lng - lngR ;SET @_sql = CONCAT('SELECT *, ceil(2 * asin(sqrt(pow(sin((((',_lat,' * PI() / 180.0) - (',_lat,' * PI() / 180.0))) / 2),2) + cos(',_lat,' * PI() / 180.0) * cos(lat * PI() / 180.0) * pow(sin(((',_lng,' * PI() / 180.0) - (lng * PI() / 180.0)) / 2),2))) * ',EARTH_RADIUS,' * 1000) AS di FROM dis WHERE lat BETWEEN ',minLat,' AND ',maxLat,' AND lng BETWEEN ',minLng,' AND ',maxLng,' ORDER BY diLIMIT 0,10');PREPARE stmt FROM @_sql;EXECUTE stmt;END;;DELIMITER ;

Call:

CALL DIS(_lat,_lng,_ras,@_data);

Parameter description:

_ Lat, longitude;

_ Lng, latitude;

_ Ras, radius (unit: km );

@ _ Data: accept the response

In the return value, di is sorted by distance, from near to far, and the unit is m.

If you do not use stored procedures, you can split them into PHP programs:

Define ("EARTH_RADIUS", 6378.137 ); /*** obtain the four coordinates from the distance * @ param $ lon * @ param $ lat * @ param int $ distance: the default distance is 1KM * @ return array */function getCoor ($ lng, $ lat, $ distance = 1) {$ range = 180/pi () * $ distance/EARTH_RADIUS; $ lngR = $ range/cos ($ lat * pi () /180); $ data = array (); $ data ["maxLat"] = $ lat + $ range; $ data ["minLat"] = $ lat-$ range; $ data ["maxLng"] = $ lng + $ lngR; // maximum longitude $ data ["minLng"] = $ lng-$ lngR; // minimum longitude return $ data ;}

First, obtain a coordinate area of the positive shape, and then splice the SQL statement in @ _ SQL above into an SQL statement and execute it.

Note: What we get here is the result of a positive shape rather than a circular radius. we can perform a secondary sieve in the later stage, for example, array_filter (). here we will not describe it much more.

The above describes the Mysql stored procedure-using Baidu coordinates to query the qualified users within the radius and the sorted distance, including the following content, if you are interested in PHP tutorials.