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

I have made a similar application before. I took a look at it today and felt that it was not very reasonable, so I reconsidered 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)
BEGIN
	DECLARE _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 di
			LIMIT 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 is km);

@_data, accept and return

In the return value, di is the distance that has been sorted, from near to far, the unit is m.

If you do not use stored procedures, you can split it into a PHP program:

define("EARTH_RADIUS",6378.137);
/**
 * 获取距离四个坐标
 * @param $lon
 * @param $lat
 * @param int $distance 默认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 ;//最大经度
	$data["minLng"] = $lng - $lngR ;//最小经度
	return $data;
}

First get a square coordinate area, and then splice it into a sql statement through the sql in @_sql above, and then execute it.

Remarks, the result obtained here is a square rather than a circular radius. You can do a secondary filtering at a later stage, such as array_filter(), which will not be described further here.

The above introduces the Mysql stored procedure - using Baidu coordinates to query qualified users within the radius and the distance after sorting, including aspects of the content. I hope it will be helpful to friends who are interested in PHP tutorials.