MySQL stored procedure--through Baidu coordinates, query the radius within the eligible users and the distance after sorting

Before doing a similar application, today turned out to look at, feel that the writing is not very reasonable, and then re-considered after writing a query stored procedures.

Table is not described,

The process is as follows:

--------------------------------Procedure structure for DIS------------------------------DROP Procedure IF EXISTS ' DIS ';D elimiter;; CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' DIS ' (in ' _lat ' varchar (one), in ' _lng ' varchar (one), in ' _ras ' int,inout ' _data ' text) begindeclare _sql text;D eclare earth_radius varchar;D eclare _range varchar (+);D eclare lngr varchar (+);D ECL is Maxlat varchar (;D eclare minlat varchar ();D eclare maxlng varchar (16);D eclare minlng varchar 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 ((') ((') ((') ' ((') ' (' (') ' (', ' _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, ' * + ' 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;;D Elimiter;

Call:

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

Parameter description:

_lat, longitude;

_LNG, Latitude;

_ras, radius (in km);

@_data, accept return

The return value in Di is the distance already sorted, from near, in units of M.

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

Define ("Earth_radius", 6378.137);/** * Get distance four coordinates * @param $lon * @param $lat * @param int $distance default 1KM distance * @return Array */function Getcoor ($LNG, $lat, $distance = 1) {$range = 180/pi () * $distance/earth_radius; $lngR = $range/cos ($lat * PI ( ), $data = Array (), $data ["maxlat"] = $lat + $range; $data ["minlat"] = $lat-$range; $data ["maxlng"] = $lng + $lngR; /maximum Longitude $data["minlng"] = $lng-$lngR;//min. longitude return $data;}

First get a square coordinate region, and then through the above @_sql SQL stitching into an SQL statement, execute can.

Note that here is a square rather than a circular radius of the results, you can do a later time to do a two-time screening can be, such as Array_filter (), here is no more described.

MySQL stored procedure--through Baidu coordinates, query the radius within the eligible users and the distance after sorting