PHP based on the latitude and longitude of the address to query the surrounding city examples

The current work is to analyze some of the user's data, each user has a number of records, each record has a user position, is expressed in longitude and latitude.
There is also a given database, which stores a number of known locations and their latitude and longitude, with more than 43W of data.
Now need to take the user's latitude and longitude and known locations for distance matching, if the distance between them is less than a certain amount of data, such as 500 meters, the user is considered in this location.
MySQL itself supports spatial indexing, but in the 5.x version, the support for distance () and related () is eliminated, and the space distance function is not used to directly query the point within a certain range of distance. So, the first thing I think about is, for each record, go to traverse, with each point in the database to calculate distance, when the distance is less than 500 meters, think match. It does get the results, but the efficiency is extremely low, because each record is going to loop around the 40W data, and the time can be imagined. After recording, found that each record processing time consumption reached 1700ms, for the amount of billions of data per day, such a processing speed, so that the human why ah ...
I also had the idea of finding a ballpark area around the latitude and longitude of each record, such as the four points of the square, and then using the MySQL space calculation, using the MBR to draw the known records in the rectangle, and then to match. Unfortunately, I didn't think of a way to calculate the latitude and longitude of four points.

Unexpectedly, the query came up with a search in the vicinity of the computation, using Python to implement the idea.
So I refer to the algorithm in the original text, using PHP to implement.
The implementation principle is very similar, first calculate the rectangle around the point of four points, and then use latitude and longitude to directly match the records in the database.

The red part for the required search range, the green part we can indirectly get the result range

Refer to some of the spherical formula on the Wiki encyclopedia:

Great-circle distance
Haversine formula

It is assumed that the latitude and longitude of the known points are $lng, $lat
To realize the longitude range of the query first,
In the Haversin formula to make φ1 =φ2, you can get:

The calculation in PHP is:

Example

The code is as follows	Copy Code
$lat the latitude of known points $DLNG = 2 * ASIN (SIN ($distance/(2 * earth_radius))/cos (Deg2rad ($lat))); $DLNG = Rad2deg ($DLNG);//Convert radians

And then the latitude range of queries,

In the Haversin formula, δλ= 0 can be

The calculation in PHP is:

Example

The code is as follows	Copy Code
$dlat = $distance/earth_radius;//earth_radius Earth radius $dlat = Rad2deg ($dlat);//Convert radians

Finally, we can get the coordinates of 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)

I have written the above method as a function, and the combination is:

Example

The code is as follows

Copy Code

Define (Earth_radius, 6371);//Earth radius, average radius 6371km /** * Four points of a square that calculates a distance around a certain latitude and longitude degree * * @param LNG float longitude * @param lat float latitude * @param distance Float the radius of the circle, which is tangent to this square, and the default value is 0.5 km. * @return the latitude and longitude coordinates of the four points of an array square */ function Returnsquarepoint ($LNG, $lat, $distance = 0.5) { $DLNG = 2 * ASIN (SIN ($distance/(2 * earth_radius))/cos (Deg2rad ($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) ); } After you use this function to calculate the results, take 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 ']} ';

When a federated index is established on the LAT and LNG, the query consumes an average of 0.8 milliseconds per record, which is vastly different than the previous 1700ms. Efficiency is really 2,125 times times that before ~ ~

Conclusion: This should not be the best way to be efficient, but it does have a significant increase in efficiency than before. Keep in mind that there is always a better way.