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.

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

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:

Example

123	//$lat 已知点的纬度$dlng=  2 * asin(sin($distance / (2 * EARTH_RADIUS)) / cos(deg2rad($lat)));$dlng= rad2deg($dlng);//转换弧度

Then there is the latitude range of the query,
In the Haversin formula, δλ= 0 can be

In PHP, the calculation is:

Example

12	$dlat= $distance/EARTH_RADIUS;//EARTH_RADIUS地球半径$dlat= rad2deg($dlat);//转换弧度

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:

Example

123456789101112131415161718192021222324252627

define(EARTH_RADIUS, 6371);//地球半径，平均半径为6371km /** *计算某个经纬度的周围某段距离的正方形的四个点 * *@param lng float 经度 *@param lat float 纬度 *@param distance float 该点所在圆的半径，该圆与此正方形内切，默认值为0.5千米 *@return array 正方形的四个点的经纬度坐标 */ 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)                ); }//使用此函数计算得到结果后，带入sql查询。$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.

Original link: 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.