PHP and MySQL-query nearby locations based on a given latitude and longitude point-use algorithms reasonably

The current job is to analyze some user data. each user has several records, each of which has a user location, expressed by longitude and latitude.
There is also a given database that stores more than pieces of data from known locations and Their longitude and latitude.
Now we need to match the distance between the user's latitude and longitude with the known location. If the distance between them is less than a certain amount of data, for example, 500, we think that the user is at this location.
MySQL supports spatial indexes,. in Version X, distance () and related () are not supported. For more information, see MySQL 5.1 Reference Manual: 19. space Expansion in
19.5.6. A function that tests the spatial relationship between ry classes. The spatial distance function cannot be used to directly query points within a certain range. Therefore, the first thing that comes to my mind is to traverse each record and calculate the distance from each vertex in the database. When the distance is less than 500 meters, we think that the matching is performed. This can indeed produce results, but the efficiency is extremely low, because every record needs to be matched with 40 million pieces of data cyclically, and the time consumed can be imagined. After record, we found that the processing time of each record was 1700 Ms. for the hundreds of millions of data records per day, how could this processing speed be so embarrassing...
I also had the idea that I could find an approximate range around the latitude and longitude of each record point, for example, four points of a square, and then use MySQL space computing, use MBR to obtain the known records in the rectangle and then perform matching. Unfortunately, I did not come up with a method to calculate the longitude and latitude of four points.
Unexpectedly, I found a preliminary research on the location near the computing. I used python to implement this idea.
So I used PHP to implement the algorithm in the original article.
The implementation principle is also very similar. First, calculate the four points of the rectangle around the point, and then use the latitude and longitude to directly match the records in the database.

The red part is the required search range, and the green part is the result range that we can indirectly obtain.

Refer to some spherical computation formulas on Wikipedia:

• Great-circle distance
• Haversine Formula

Assume that the longitude and latitude of known points are $ LNG, $ lat
First, query the longitude range,
In the haversin formula, let's set? 1 =? 2:

PHP is used for computing:

1	// $ Lat latitude of a known point

2	$dlng =  2 * asin(sin($distance/ (2 * EARTH_RADIUS)) / cos(deg2rad($lat)));

3	$dlng = rad2deg($dlng);// Converts radians.

Then query the latitude range,
In the haversin formula, place △λ = 0 to obtain

Computing in PHP is:

1	$dlat = $distance/EARTH_RADIUS;// Earth_radius Earth radius

2	$dlat = rad2deg($dlat);// Converts radians.

Finally, we can get the coordinates of the four points:
Left-top: (LAT + dlat, LNG-dlng)
Right-top: (LAT + dlat, LNG + dlng)
Left-bottom: (lat-dlat, LNG-dlng)
Right-bottom: (lat-dlat, LNG + dlng)

Define (earth_radius, 6371); // Earth radius, the average radius is 6371 km/***, and the four points of the square with a certain distance around a certain latitude and longitude are calculated ** @ Param LNG float longitude * @ Param lat float latitude * @ Param distance float radius of the circle where the point is located, this circle is tangent to this square. The default value is 0.5 km * @ return array coordinate of the longitude and latitude of the four points of the square */function returnsquarepoint ($ LNG, $ Lat, $ distance = 0.5) {$ dlng = 2 * asin (sin ($ distance/(2 * earth_radius)/cos (deg 2rad ($ LAT); $ dlng = rad2deg ($ dlng ); $ dlat = $ distance/earth_radius; $ dlat = rad2deg ($ Dlat); Return array ('left-top' => array ('lat' => $ lat + $ dlat, 'lng '=> $ LNG-$ dlng ), 'Right-top' => array ('lat' => $ lat + $ dlat, 'lng '=> $ LNG + $ dlng ), 'left-bottom' => array ('lat' => $ lat-$ dlat, 'lng '=> $ LNG-$ dlng ), 'Right-bottom' => array ('lat' => $ lat-$ dlat, 'lng '=> $ LNG + $ dlng ));} // use this function to calculate the result and bring it into the SQL query. $ Squares = returnsquarepoint ($ LNG, $ LAT); $ info_ SQL = "select ID, locateinfo, Lat, LNG from 'lbs _ info' where lat <> 0 and lat >{$ squares ['right-bottom '] ['lat']} And Lat <{$ squares ['left -Top '] ['lat']} and LNG> {$ squares ['left-top'] ['lng']} and LNG <{$ squares ['right-bottom '] ['lng']} ";

After creating a joint index on lat and LNG, the query consumes an average of 0.8 milliseconds for each record. Compared with the previous query time of 1700 ms, the query time is quite different. The efficiency is really 2125 times that of the past ~~

Conclusion: this should not be the best way to improve efficiency, but it is indeed more efficient than before. Remember, there is always a better way