The stone of his mountain-find a nearby point--geohash scheme discussion

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

In the basic data, the latitude and longitude of the target location are generally preserved, and the latitude and longitude of the users are compared to obtain the proximity.

Goal:
Finds nearby xxx, returns the result from near to far, and the result has a distance from the target point.

To find nearby xxx, propose two scenarios, as follows:

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

The calculation of the two-point distance of the spherical surface is known as the latitude and longitude of two points on the spherical surface.
Points (latitude, longitude), A ($radLat 1, $radLng 1), B ($radLat 2, $radLng 2);

Advantages: Easy to understand and convenient to deploy

Disadvantage: Query the database every time, the performance is worrying

1. Derivation

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

$s =acos (cos ($radLat 1) *cos ($radLat 2) *cos ($radLng 1-$radLng 2) +sin ($radLat 1) *sin ($radLat 2)) * $R;

At present, most of the online use of Google's Open distance computing company, deduced Formula B is:

$s =2*asin (sqrt pow ($radLat 1-$radLat 2)/2, 2) +cos ($radLat 1) *cos ($radLat 2) *pow (sin ($radLng 1-$radLng 2)/2), 2 ) * $R;

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

$R for the Earth's radius

2, by testing two algorithms, the results are the same and all correct, but through the PHP code test, distance between two points, 10W performance comparison, the derivation of the version of the calculation of the long formula B 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:

<?php
  
    //According to longitude and latitude calculation distance of a ($lat 1, $lng 1), B ($lat 2, $LNG 2) public
    static function getdistance ($lat 1, $lng 1, $lat 2, $LNG 2)
    {
        //earth radius
        $R = 6378137;
  
        Convert angle to Fox degree
        $radLat 1 = Deg2rad ($lat 1);
        $radLat 2 = Deg2rad ($lat 2);
        $radLng 1 = Deg2rad ($lng 1);
        $radLng 2 = Deg2rad ($lng 2);
  
        Results
        $s = acos (cos ($radLat 1) *cos ($radLat 2) *cos ($radLng 1-$radLng 2) +sin ($radLat 1) *sin ($radLat 2)) * $R;
  
        Precision
        $s = round ($s * 10000)/10000;
  
        Return  round ($s);
  
>

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

Take all the data out, and then compare it to the PHP loop, and the filter meets the conditional result, which is obviously inefficient; so let's use the MySQL storage function to solve the problem. 4.1, create a MySQL storage function, and the latitude of the field to establish an index.

<?php
  
    //According to longitude and latitude calculation distance of a ($lat 1, $lng 1), B ($lat 2, $LNG 2) public
    static function getdistance ($lat 1, $lng 1, $lat 2, $LNG 2)
    {
        //earth radius
        $R = 6378137;
  
        Convert angle to Fox degree
        $radLat 1 = Deg2rad ($lat 1);
        $radLat 2 = Deg2rad ($lat 2);
        $radLng 1 = Deg2rad ($lng 1);
        $radLng 2 = Deg2rad ($lng 2);
  
        Results
        $s = acos (cos ($radLat 1) *cos ($radLat 2) *cos ($radLng 1-$radLng 2) +sin ($radLat 1) *sin ($radLat 2)) * $R;
  
        Precision
        $s = round ($s * 10000)/10000;
  
        Return  round ($s);
  
>

4.2. Query SQL

With SQL, you can set distances and sorts, search for information that meets the criteria, and have a better sort

1	Select*,latitude,longitude,getdistance (latitude,longitude,30.663262,104.071619) as distance from Mb_shop_ext where 1 havingdistance<1000 ORDER by distance ASC limit0,10

II. Programme B

=================================================================================================

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

Advantages:

1, the use of a field, you can store latitude and longitude, search, only a single index, more efficient
2, the encoded prefix can represent a larger area, find nearby, very convenient. In SQL, like ' wm3yr3% ', you can query all nearby locations.
3, through the coding precision can be fuzzy coordinates, privacy protection and so on.

Disadvantages: Distance and sorting requires two operations (filter results run, actually very fast)

1, Geohash coding algorithm

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

1.1, Latitude Range (-90, 90) flat divided into two intervals (-90, 0), (0,90), if the target latitude in the previous interval, then the code is 0, otherwise encoded as 1.
Because 30.625265 belongs to (0, 90), the encoding is 1.
The (0, 90) is then divided into (0, 45), (45, 90) Two, and 39.92324 is in (0,45), so the encoding is 0,
The (0, 45) is then divided into (0, 22.5), (22.5, 45) Two, and 39.92324 is in (22.5,45), so the encoding is 1,
In turn, the latitude code of the Yongfeng interchange can be 101010111001001000100101101010.

1.2, longitude also uses the same algorithm, to (-180, 180) in order subdivision, ( -180,0), (0,180) to get code 110010011111101001100000000000

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

1.4, with 0-9, b-z (remove A, I, l,o) these 32 letters for BASE32 encoding, get (30.63578,104.031601) encoding for wm3yr31d2524.

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

2. Strategy

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

2, look around, using like ' wm3yr3% ' in SQL, and this result can be cached, in small areas, not because of changes in latitude and longitude, and the database query

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

3, PHP base class

geohash.class.php

?
    PHP class Geohash {private $coding = "0123456789bcdefghjkmnpqrstuvwxyz";
    Private $codingMap =array (); Public Function Geohash () {for ($i =0 $i <32; $i + +) {$this->codingmap[substr ($this-&
        Gt;coding, $i, 1)]=str_pad (Decbin ($i), 5, "0", str_pad_left);
        The Public Function decode ($hash) {$binary = "";
        $hl =strlen ($hash);
        for ($i =0; $i < $hl; $i + +) {$binary. = $this->codingmap[substr ($hash, $i, 1)];
        $BL =strlen ($binary);
        $blat = "";
        $blong = "";
            For ($i =0 $i < $BL $i + +) {if ($i%2) $blat = $blat. substr ($binary, $i, 1);
  
        else $blong = $blong. substr ($binary, $i, 1);
        $lat = $this->bindecode ($blat, -90,90);
        $long = $this->bindecode ($blong,-180,180);
        $LATERR = $this->calcerror (strlen ($blat), -90,90); $LONGERR = $this->calcerror (strlen ($blong),-180,180);
        $latPlaces =max (1,-round (log10 ($LATERR)))-1;
        $longPlaces =max (1,-round (log10 ($LONGERR)))-1;
        $lat =round ($lat, $latPlaces);
        $long =round ($long, $longPlaces);
    Return Array ($lat, $long);
        The public function encode ($lat, $long) {$plat = $this->precision ($lat);
        $latbits = 1;
        $err = 45;
            while ($err > $plat) {$latbits + +;
        $err/=2;
        $plong = $this->precision ($long);
        $longbits = 1;
        $err = 90;
            while ($err > $plong) {$longbits + +;
        $err/=2;
        $bits =max ($latbits, $longbits);
        $longbits = $bits;
        $latbits = $bits;
        $addlong = 1;
            while (($longbits + $latbits)%5!= 0) {$longbits + = $addlong;
            $latbits +=! $addlong;
        $addlong =! $addlong;
        $blat = $this->binencode ($lat, -90,90, $latbits); $blong = $this->binenCode ($long, -180,180, $longbits);
        $binary = "";
        $uselong = 1; while (strlen ($blat) +strlen ($blong)) {if ($uselong) {$binary = $binary. Sub
                STR ($blong, 0, 1);
            $blong =substr ($blong, 1);
                else {$binary = $binary. substr ($blat, 0, 1);
            $blat =substr ($blat, 1);
        } $uselong =! $uselong;
        } $hash = "";
            For ($i =0 $i <strlen ($binary); $i +=5) {$n =bindec (substr ($binary, $i, 5));
        $hash = $hash. $this->coding[$n];
    return $hash;
        Private Function Calcerror ($bits, $min, $max) {$err = ($max-$min)/2;
        while ($bits-) $err/=2;
    return $err;
        Private Function Precision ($number) {$precision = 0;
        $pt =strpos ($number, '. ');
    if ($pt!==false) {$precision =-(strlen ($number)-$pt-1);    Return POW ($precision)/2;
        Private Function Binencode ($number, $min, $max, $bitcount) {if ($bitcount ==0) return "";
        $mid = ($min + $max)/2;
        if ($number > $mid) return "1". $this->binencode ($number, $mid, $max, $bitcount-1);
    else return "0". $this->binencode ($number, $min, $mid, $bitcount-1);
        Private Function Bindecode ($binary, $min, $max) {$mid = ($min + $max)/2;
        if (strlen ($binary) ==0) return $mid;
        $bit =substr ($binary, 0, 1);
        $binary =substr ($binary, 1);
        if ($bit ==1) return $this->bindecode ($binary, $mid, $max);
    else return $this->bindecode ($binary, $min, $mid);
 }}?>

third, testing

<?php require_once (' Mysql.class.php ');
Require_once (' geohash.class.php '); MySQL $conf = array (' Host ' => ' 127.0.0.1 ', ' Port ' => 3306, ' user ' => ' root ', ' password ' =>
' 123456 ', ' database ' => ' mocube ', ' CharSet ' => ' utf8 ', ' persistent ' => false);
$mysql = new Db_mysql ($conf);
$geohash =new Geohash;
Converted to Geohash by latitude//Get nearby information $n _latitude = $_get[' La '];
$n _longitude = $_get[' lo '];
Start $b _time = Microtime (true); Scheme A, direct use of database storage functions, traversal sort//scheme B geohash to find out nearby, then sort//Current Geohash value $n _geohash = $geohash->encode ($n _latitude, $n _longitude
);
Nearby $n = $_get[' n '];
$like _geohash = substr ($n _geohash, 0, $n); $sql = ' SELECT * mb_shop_ext where Geohash like '. $like _geohash. '
%"';
Echo $sql;
$data = $mysql->queryall ($sql); Calculates the actual distance foreach ($data as $key => $val) {$distance = Getdistance ($n _latitude, $n _longitude, $val [' latitude '], $val [' Lo
    Ngitude ']);
    $data [$key] [' distance '] = $distance;
Row sequence $sortdistance [$key] = $distance; }//Distance rowOrdinal array_multisort ($sortdistance, SORT_ASC, $data);
End $e _time = Microtime (true);
echo $e _time-$b _time;
Var_dump ($data);

    According to the latitude and longitude calculation distance of which a ($lat 1, $lng 1), B ($lat 2, $lng 2) function getdistance ($lat 1, $lng 1, $lat 2, $lng 2) {//earth radius $R = 6378137;
    Convert angle to Fox degree $radLat 1 = deg2rad ($lat 1);
    $radLat 2 = Deg2rad ($lat 2);
    $radLng 1 = Deg2rad ($lng 1);
    $radLng 2 = Deg2rad ($lng 2);
    Results $s = acos (cos ($radLat 1) *cos ($radLat 2) *cos ($radLng 1-$radLng 2) +sin ($radLat 1) *sin ($radLat 2)) * $R;
    Precision $s = round ($s * 10000)/10000;
Return round ($s); }?>

Iv. Summary

The highlight of programme B is:
1, search results can be cached, repeated use, will not because the user has a small range of mobile, directly penetrate the database query.
2, first narrow the result range, and then operation, sorting, can improve performance.

254 Records, performance comparison,

In the actual scenario, the solution B database searches for the memory cache, and if the data volume is larger, the result of scenario B will be better.

Programme A:
0.016560077667236
0.032402992248535
0.040318012237549

Programme b
0.0079810619354248
0.0079669952392578
0.0064868927001953

v. Other

Two schemes, according to the application scenario and the load situation reasonable choice, of course, recommended scheme B;
Either way, remember to add an index to the column to facilitate database retrieval.