Locating the fault location near the point Geohash discussion

With the popularity of mobile terminals, many applications are based on the LBS function, the vicinity of xxx (restaurants, banks, sister paper, etc.).

In the basic data, the latitude and longitude of the target location is generally saved, and the latitude and longitude of the user provided is used to compare it to get it nearby.

Goal:
Find nearby xxx, returning results from near to far, and the result has a distance from the target point.

For the search of nearby xxx, proposed two scenarios, as follows:

I. Programme A:
=================================================================================================

The abstract is the calculation of the distance of the spherical two points, that is, the latitude and longitude of two points on the spherical surface;
Point (latitude, longitude), A ($radLat 1, $radLng 1), B ($radLat 2, $radLng 2);

Advantages: Easy to understand, simple and convenient to deploy

Cons: Database is queried every time, performance is worrying

1. Derivation

Through the cosine theorem and the Radian calculation method, the final deduced formula A is:

$s = acos(cos($radLat1)*cos($radLat2)*cos($radLng1-$radLng2)+sin($radLat1)*sin($radLat2))*$R;

At present, most of the online use of Google public Distance computing company, the derivation Formula B is:

$s = 2*asin(sqrt(pow(sin(($radLat1-$radLat2)/2),2)+cos($radLat1)*cos($radLat2)*pow(sin(($radLng1-$radLng2)/2),2)))*$R;

which
$radLat 1, $radLng 1, $radLat 2, $radLng 2 radians

$R is the radius of the Earth

2, by testing the two algorithms, the results are the same and all correct, but through the PHP code test, two points between the distance, 10W performance comparison, self-deduction version calculation of the long formula B is better, as follows:

Formula A
0.56368780136108float (431)
0.57460689544678float (431)
0.59051203727722float (431)

Formula B
0.47404885292053float (431)
0.47808718681335float (431)
0.47946381568909float (431)

3, so the formula deduced by the mathematical method:

4, in the actual application, need to traverse from the database to check out the matching conditions, and sorting operations,

By taking all the data out and then comparing it with the PHP loop, the filter matches the conditional result and obviously the performance is low; so we use the next MySQL storage function to solve this problem.

4.1. Create a MySQL storage function and index the Latitude field

4.2. Query SQL

With SQL, you can set distance and sort, search for eligible information, and have a better sort

1	SELECT *,latitude,longitude,GETDISTANCE(latitude,longitude,30.663262,104.071619) AS distance FROM mb_shop_ext where 1 HAVING distance<1000 ORDER BY distance ASC LIMIT 0,10

Ii. Programme b
=================================================================================================

Geohash algorithm; Geohash is an address code that encodes two-dimensional latitude and longitude into one-dimensional strings.
For example, the Chengdu Yongfeng Interchange code is wm3yr31d2524

Advantages:

1, the use of a field, you can store latitude and longitude; search, only one index, high efficiency
2, the encoding prefix can represent a larger area, find nearby, very convenient. In SQL, like ' wm3yr3% ', you can query all nearby locations.
3, through the coding accuracy can be blurred coordinates, privacy protection and so on.

Cons: Distance and sort require two operations (filter results run, actually quite fast)

1, Geohash coding algorithm

Latitude and longitude of Chengdu Yongfeng Interchange (30.63578,104.031601)

1.1, the Latitude range (-90, 90) is divided into two intervals (-90, 0), (0, 90), if the target latitude is in the previous interval, the code is 0, otherwise the encoding is 1.
Since 30.625265 belongs to (0, 90), take the code to 1.
Then (0, 90) is divided into (0, 45), (45, 90) two intervals, and 39.92324 is (0, 45), so the code is 0,
Then (0, 45) is divided into (0, 22.5), (22.5, 45) two intervals, and 39.92324 is (22.5, 45), so the code is 1,
The latitude code of Yongfeng Interchange is 101010111001001000100101101010.

1.2, the longitude also uses the same algorithm, pairs (-180, 180) subdivide, ( -180,0), (0,180) obtains the code 110010011111101001100000000000

1.3, the combination of latitude and longitude code, from high to low, first take a longitude, then take a latitude; results 111001001100011111101011100011000010110000010001010001000100

1.4, with 0-9, b-z (remove A, I, L, O) These 32 letters are BASE32 encoded, get (30.63578,104.031601) encoded as wm3yr31d2524.

11100 10011 00011 11110 10111 00011 00001 01100 00010 00101 00010 00100 => wm3yr31d2524十进制 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15base32 0 1 2 3 4 5 6 7 8 9 b c d e f g十进制 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31base32 h j k m n p q r s t u vw x y z

2. Strategy

1, in the latitude and longitude storage, the database new Add a field Geohash, record this point Geohash value

2. Find nearby, use like ' wm3yr3% ' in SQL, and this result can be cached; in small areas, the database query will not be re-queried because of the latitude and longitude changes.

3, find out the limited results, such as the need for distance or sorting, can use distance formula and two-dimensional data sorting; This is also a small amount of data, will be very fast.

Iv. Summary

The highlights of Plan B are:
1, the search results can be cached, re-use, not because the user has a small range of mobile, directly through the database query.
2, reduce the result range, and then calculate, sort, can improve performance.

254 Records, performance comparison,

In a real-world scenario, the scenario B database searches for a memory cache, and if the amount of data is larger, scenario B results better.

Scenario A:
0.016560077667236
0.032402992248535
0.040318012237549

Scenario B
0.0079810619354248
0.0079669952392578
0.0064868927001953

V. Other

The two schemes, according to the application scenario and the load situation reasonable choice, of course recommended program B;
Either way, remember to index the columns to facilitate database retrieval.

Locating the fault location near the point Geohash discussion