PHP, Mysql-according to a given latitude and longitude point, nearby location query-reasonable use of algorithms, efficiency improved by 2125 times, mysql-2125 times _ PHP Tutorial

PHP, Mysql-based on a given latitude and longitude point, nearby location query-reasonable use of the algorithm, improved efficiency by 2125, mysql-2125 times. PHP, Mysql-based on a given latitude and longitude point, nearby location query-reasonable use of algorithms, efficiency improved by 2125 times, mysql-2125 times the current work is to some of the user's data for PHP, mysql-query nearby locations based on a given latitude and longitude point-improve efficiency by 2125 times, mysql-2125 times

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. extended space in 19.5.6. to test the spatial relationship between RY classes, you cannot use the spatial distance function 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:

Example

123	// $ Lat latitude of a known point$dlng = 2 * asin(sin($distance / (2 * EARTH_RADIUS)) / cos(deg2rad($lat)));$dlng = rad2deg($dlng);// Converts radians.

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

Computing in PHP is:

Example

12	$dlat = $distance/EARTH_RADIUS;// EARTH_RADIUS Earth radius$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)

I have written the above method into a function. The combination is:

Example

123456789101112131415161718192021222324252627

define(EARTH_RADIUS, 6371);// Earth's radius, with an average radius of 6371 km /** * Calculate the four points of a square with a distance between a certain longitude and latitude. * * @ Param lng float longitude * @ Param lat float latitude * @ Param distance float the radius of the circle in which the point is located. the circle is cut inside the square. the default value is 0.5 km. * @ Return array coordinate of the longitude and latitude of the four points of a 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) ); }// 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.

Link: http://digdeeply.org/archives/06152067.html

If you enter a latitude and longitude data and find out which region the location belongs to, how can we establish such a database?

Use Beijing tianchuang software
 

SQL (mysql) allows you to query the longitude and latitude of a restaurant around 500 meters. the database stores the longitude and latitude of all restaurants.

Mysql two-point spacing, you can also make a function yourself

The formula is as follows, in meters:
Longitude and latitude: lng1 lat1
Second point longitude and latitude: lng2 lat2
Round (6378.138*2 * asin (sqrt (pow (sin (
(Lat1 * pi ()/180-lat2 * pi ()/180)/2), 2) + cos (lat1 * pi ()/180) * cos (lat2 * pi () /180 )*
Pow (sin (lng1 * pi ()/180-lng2 * pi ()/180)/2) * 1000)

For example:
SELECT store_id, lng, lat,
ROUND (6378.138*2 * ASIN (SQRT (POW (SIN (22.299439 * PI ()/180-lat * PI ()/180)/2), 2) + COS (22.299439 * PI ()/180) * COS (lat * PI ()/180) * POW (SIN (114.173881 * PI ()/180-lng * PI () /180)/2), 2) * 1000)
AS
Juli
FROM store_info
Order by juli DESC
The LIMIT 316

The current job of pipeline is to perform some user data...