How do users with fixed coordinates A, B as the center and 1km as the radius sort by distance P?

Currently, the coordinates of user P are: (116.63255, 39.90467), and the current database has 40 million user coordinates which are not fixed. Business Requirements: all users in the center of "P" and "1km" are sorted by distance from near to far, how do I write MYSQL, PHP, or MYSQL stored procedures (if any )?... Now there is user P whose coordinates are :( 116.63255, 39.90467), The current database has 40 million user coordinates is not fixed.

Business Requirements: Take P as the center, and 1km as the radius. All Users sort data from near to far. How do I write the MYSQL or PHP or MYSQL stored procedures (if any )?

The database information of P is:

uidUser UID

longitudeLongitude

latitudeLatitude

The four coordinates centered on P can be converted as follows,

Define ("EARTH_RADIUS", 6378.137 ); /*** get the four coordinates of distance * @ param $ lon * @ param $ lat * @ param int $ distance the default distance is 1KM * @ return array */public function getDistance ($ lon, $ 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"] = $ lon + $ lngR; // maximum longitude $ data ["minLng"] = $ lon-$ lngR; // minimum longitude return $ data ;}

After obtaining the coordinates of the above four vertices, Mysql can write as follows:

"SELECT m.uid,m.username,m.email,m.regip,m.regdate,m.lastloginip,m.lastlogtime,f.weight,f.height,f.sex,f.qq,f.weixin,f.weibo,f.birthyear,f.birthmonth,f.birthday,f.blood,f.capricorn,f.birthprovince,f.birthcity,f.birthtown,f.resideprovince,f.residecity,f.residetown,f.friend,f.feedfriend,f.description,f.friendnum,f.follownum,f.device,f.edition,f.longitude,f.latitude FROM ".$_SGLOBAL["common_server"]->tname("member")." AS m LEFT JOIN ".$_SGLOBAL["common_server"]->tname("memberfield")." AS f ON m.uid=f.uid WHERE (f.longitude < '{$distance["maxLng"]}' AND  f.longitude > '{$distance["minLng"]}') AND (f.latitude < '{$distance["maxLat"]}' AND f.latitude > '{$distance["minLat"]}') AND 1 DESC LIMIT {$offset},20"

Yes. The preceding SQL statements cannot be sorted by distance. How can this problem be solved!

The following describes how to convert the distance between P and P1:

/*** Obtain the distance between two coordinates * @ param $ lat1 * @ param $ lng1 * @ param $ lat2 * @ param $ lng2 * @ param int $ len_type * @ param int $ decimal * @ return float */public function GetDistanceToM ($ lat1, $ lng1, $ lat2, $ lng2, $ len_type = 1, $ decimal = 2) {$ radLat1 = $ lat1 * PI ()/180.0; $ radLat2 = $ lat2 * PI ()/180.0; $ a = $ radLat1-$ radLat2; $ B = ($ lng1 * PI ()/180.0) -($ lng2 * PI ()/180.0); $ s = 2 * asin (sqrt (pow (sin ($ a/2), 2) + cos ($ radLat1) * cos ($ radLat2) * pow (sin ($ B/2), 2); $ s = $ s * EARTH_RADIUS; $ s = round ($ s x 1000); if ($ len_type> 1) {$ s = $ s/1000;} return round ($ s, $ decimal );}

The preceding method converts the distance, but cannot be used in mysql.

Reply content:

Now there is user P whose coordinates are :(116.63255,39.90467), The current database has 40 million user coordinates is not fixed.

Business Requirements: Take P as the center, and 1km as the radius. All Users sort data from near to far. How do I write the MYSQL or PHP or MYSQL stored procedures (if any )?

The database information of P is:

uidUser UID

longitudeLongitude

latitudeLatitude

The four coordinates centered on P can be converted as follows,

Define ("EARTH_RADIUS", 6378.137 ); /*** get the four coordinates of distance * @ param $ lon * @ param $ lat * @ param int $ distance the default distance is 1KM * @ return array */public function getDistance ($ lon, $ 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"] = $ lon + $ lngR; // maximum longitude $ data ["minLng"] = $ lon-$ lngR; // minimum longitude return $ data ;}

After obtaining the coordinates of the above four vertices, Mysql can write as follows:

"SELECT m.uid,m.username,m.email,m.regip,m.regdate,m.lastloginip,m.lastlogtime,f.weight,f.height,f.sex,f.qq,f.weixin,f.weibo,f.birthyear,f.birthmonth,f.birthday,f.blood,f.capricorn,f.birthprovince,f.birthcity,f.birthtown,f.resideprovince,f.residecity,f.residetown,f.friend,f.feedfriend,f.description,f.friendnum,f.follownum,f.device,f.edition,f.longitude,f.latitude FROM ".$_SGLOBAL["common_server"]->tname("member")." AS m LEFT JOIN ".$_SGLOBAL["common_server"]->tname("memberfield")." AS f ON m.uid=f.uid WHERE (f.longitude < '{$distance["maxLng"]}' AND  f.longitude > '{$distance["minLng"]}') AND (f.latitude < '{$distance["maxLat"]}' AND f.latitude > '{$distance["minLat"]}') AND 1 DESC LIMIT {$offset},20"

Yes. The preceding SQL statements cannot be sorted by distance. How can this problem be solved!

The following describes how to convert the distance between P and P1:

/*** Obtain the distance between two coordinates * @ param $ lat1 * @ param $ lng1 * @ param $ lat2 * @ param $ lng2 * @ param int $ len_type * @ param int $ decimal * @ return float */public function GetDistanceToM ($ lat1, $ lng1, $ lat2, $ lng2, $ len_type = 1, $ decimal = 2) {$ radLat1 = $ lat1 * PI ()/180.0; $ radLat2 = $ lat2 * PI ()/180.0; $ a = $ radLat1-$ radLat2; $ B = ($ lng1 * PI ()/180.0) -($ lng2 * PI ()/180.0); $ s = 2 * asin (sqrt (pow (sin ($ a/2), 2) + cos ($ radLat1) * cos ($ radLat2) * pow (sin ($ B/2), 2); $ s = $ s * EARTH_RADIUS; $ s = round ($ s x 1000); if ($ len_type> 1) {$ s = $ s/1000;} return round ($ s, $ decimal );}

The preceding method converts the distance, but cannot be used in mysql.

The landlord can look at this article: http://www.infoq.com/cn/articles/depth-study-of-Symfony2
He listed several solutions to these problems, and finally recommended mongodb, which is also one of the common solutions of LBS.

These two days I have been doing this kind of service, because I have a small amount of data, so I am using the mysql partial index method.

set @x1 = 31.292491624635;set @y1 = 121.50865016331;set @r0 = 10;select *, AsText(pos), X(pos), Y(pos), 6378 * 2 *ASIN(SQRT( POWER(SIN((@orig_lat - abs(X(pos))) * pi()/180 / 2),2) + COS(@orig_lat * pi()/180 ) * COS(abs(X(pos)) *  pi()/180) * POWER(SIN((@orig_lon - Y(pos)) *  pi()/180 / 2), 2))) as distancefrom user_posistion WHERE   MBRContains(LineString(Point(@x1 + @r0 / ( 111.1 / COS(RADIANS(@y1))), @y1 + @r0 / 111.1), Point(@x1 - @r0 / ( 111.1 / COS(RADIANS(@y1))), @y1 - @r0 / 111.1)), pos)order by distance asc

@ X1 and @ y1 are coordinates, @ r0 is the search radius km, and pos is a point field.
First retrieve all vertices in the Radius Range Based on the spatial index, then calculate the distance, and finally sort

Because I have less data and can meet performance requirements

There is a thing called GEOHASH .. The speed is extremely fast. Currently, the company uses this method to meet this requirement.

This is a GIS, and it is best to use database extensions. For example: http://blog.csdn.net/historyasamirror/article/details/6528527. to solve this problem, you must write the two-point distance calculation method into a stored procedure:

select uid from tableName order by distance(l1, n1, tableName.longitude, tableName.latitude) asc limit 10;

Distance is a stored procedure that calculates the distance between two points. This operation is generally not recommended because it affects database performance.

Accurate computing is too complicated, and php + mysql is difficult.

In reality, to solve practical problems, you can simplify the model and sort by distance as follows:

"SELECT * ,longitude*longitude + latitude*latitude as dis FROM table WHERE dis>'$x'  order by dis DESC LIMIT {$offset},20"

However, the efficiency is relatively low.

It is very difficult to get it done in one step. My approach is to use mysql to filter out qualified data within the radius, which is similar to yours. Then, use php to calculate the distance between each vertex and the specified coordinate, and sort the data. Generally, the data size after filtering by mysql is not very large, and the Sorting Problem Using php should be small.

Isn't this the eager version of k nearest neighbor...

K-d tree search for this keyword