php,mysql-a location based on a given latitude and longitude point--make reasonable use of the algorithm and increase the efficiency by 2,125 times times.

The current job is to analyze some of the user's data, each user has several records, each record has a user's location, is expressed in longitude and latitude.
There is also a given database that stores some known locations and their latitude and longitude, with more than 43W of data in it.
Now you 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, it is believed that the user is in this location.
MySQL itself supports spatial indexing, but in the 5.x version, support for distance () and related () is canceled, refer to this: MySQL 5.1 reference Manual:: 19. The space extension in 19.5.6. A function that tests the spatial relationship between geometry classes cannot use spatial distance functions to directly query points within a certain range of distances. So, the first thing I think about is, for each record, go to traverse, with each point in the database distance calculation, when the distance is less than 500 meters, the match is considered. Doing so can actually get results, but the efficiency is extremely low, because each record has to loop to match 40W data, its consumption time can imagine. After the record, found that each record processing time consumption of up to 1700ms, for the amount of billions of data per day, such a processing speed, so that human ...
I also have the idea of finding a approximate range around the latitude and longitude of each record, say four points of the square, then using the MySQL space calculation, using the MBR to derive a known record of the point within the rectangle, and then to match. Unfortunately, I did not think of a way to calculate the latitude and longitude of four points.
Unexpectedly, the query to a nearby location for the calculation of search, which uses Python to implement the idea.
Therefore, the reference to the original text in the algorithm, using PHP to implement.
The implementation principle is also very similar, first calculate the point around the four points of the rectangle, and then use latitude and longitude to directly match the records in the database.

Refer to some of the spherical calculation formulas on wiki Wikipedia:

• Great-circle distance
• Haversine formula

Assume that the latitude and longitude of the known points are $lng, respectively $lat
The query that implements the Longitude range first,
To make φ1 =φ2 in the Haversin formula, you get:

The calculation in PHP is:

/$lat  The latitude of the known point $dlng =  asin(sin($distancecos( Deg2rad ($lat))); $DLNG rad2deg ($dlng); // Convert radians

Then there is the latitude range of the query,

In the Haversin formula, δλ= 0 can be

In PHP, the calculation is:

$dlat $distance/earth_radius; // Earth_radius Earth Radius $dlat rad2deg ($dlat); // Convert radians

Finally, you can draw 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've written the above method into a function that combines:

Define(Earth_radius, 6371);//radius of the earth with an average radius of 6371km /** * calculates the four points of a square of a distance around a latitude and longitude * * @param LNG float longitude * @param lat float latitude * @param distance float the radius of the circle that the point is in, the circle is tangent to this square, and the default value is 0. 5-kilometer * latitude and longitude coordinates of the four points of an array square @return*/ functionReturnsquarepoint ($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)                ); }//Use this function to calculate the resulting result, and bring in 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 establishing a federated index on LAT and LNG, using this query, the average query consumption per record is 0.8 milliseconds, compared to the previous 1700ms, it is really a great difference. Efficiency really is 2,125 times times the previous ~ ~

Summary: This should not be the best way to be efficient, but the efficiency is actually significantly higher than before. Please keep in mind that there is always a better way.

Originally from: http://digdeeply.org/archives/06152067.html

php,mysql-a location based on a given latitude and longitude point--make reasonable use of the algorithm and increase the efficiency by 2,125 times times.