SQL statement query latitude and longitude range

Specifies a latitude and longitude, given a range value (in kilometers), to isolate the data within this range around the latitude and longitude.
Longitude: 113.914619
Latitude: 22.50128
Range: 2km
Longitude as a data table longitude field
Latitude for the data table latitude field
SQL is tested under MySQL, other databases may need to be modified
The SQL statements are as follows:

Select *  fromLocationwhere sqrt(      (       ((113.914619-Longitude)*PI()*12656*Cos(((22.50128+Latitude/2)*PI()/ the)/ the)       *       ((113.914619-Longitude)*PI()*12656*Cos(((22.50128+Latitude/2)*PI()/ the)/ the)      )      +      (       ((22.50128-Latitude*PI()*12656/ the)       *       ((22.50128-Latitude*PI()*12656/ the)      )  )<2

MySQL Performance tuning – use faster algorithms for distance

Recently encountered a problem, through a constant attempt to finally put a sentence originally occupied nearly 1 seconds of the query optimized to 0.01 seconds, efficiency increased 100 times times.

The problem is that there is a MySQL data table that holds the latitude and longitude information for the user's place of residence, and the table structure can be simplified to: id (int), longitude (long), Latitude () long. One function in a business system is to find the remaining number of users closest to a particular user, and through code analysis, it is possible to determine that the original approach is basically this:

The coordinates of the user who needs to be queried

$lat= -; $lon= -;//executes a query that calculates the distance of the user from all other users, and takes out the last 10 $sql='SELECT * from Users_location ORDER by ACOS (SIN ('. $lat.'* 3.1415)/*sin ((Latitude * 3.1415)/+cos ('. $lat.'* 3.1415) * COS ((latitude * 3.1415)/*cos ('. $lon.'* 3.1415)/Longitude-(* 3.1415)/+) * 6380 ASC Limit';

This SQL execution is very slow, it took nearly 1 seconds to return the results, it should be because the order of the sub-statement with too many mathematical formulas, resulting in the overall speed of operation decreased.

In actual use, it is unlikely that the user will have to calculate the distance from all other users, and then sort the situation, when the number of users reached a level, you can search in a smaller scope, rather than all users to search.

So for this example, I've added 4 where conditions, only for distance calculations for users with longitude and latitude greater than or less than 1 degrees (111 km) of the user, and an index to the longitude and latitude two columns in the data table to optimize the speed at which the where statement executes.

The final SQL statement is as follows

$sql='SELECT * from users_location where latitude >'. $lat.'-1 and Latitude <'. $lat.'+1 and Longitude >'. $lon.'-1 and longitude <'. $lon.'+1 ORDER by ACOS (SIN ('. $lat.'* 3.1415)/*sin ((Latitude * 3.1415)/+cos ('. $lat.'* 3.1415) * COS ((latitude * 3.1415)/*cos ('. $lon.'* 3.1415)/Longitude-(* 3.1415)/+) * 6380 ASC Limit';

Optimized SQL greatly improves the speed of operation and, in some cases, is even 100 times times higher. This approach to narrowing the scope of SQL queries from a business perspective can also be applied elsewhere.

Transferred from: http://cancait.blog.163.com/blog/static/21335744201381401721826/