Geo-Search (Distance) in PHP/MySQL (Performance)

by

Calculate a bounding box to select a subset of the rows in the WHERE clause of your SQL query, so that you’re only executing the expensive distance calculation on that subset of rows rather than against the entire 200k records in your table. The method is described in this article on Movable Type (with PHP code examples). Then you can include the Haversine calculation in your query against that subset to calculate the actual distances, and factor in the HAVING clause at that point.

It’s the bounding box that helps your performance, because it means you’re only doing the expensive distance calculation on a small subset of your data. This is effectively the same method that Patrick has suggested, but the Movable Type link has extensive explanations of the method, as well as PHP code that you can use to build the bounding box and your SQL query.

EDIT

If you don’t think haversine is accurate enough, then there’s also the Vincenty formula.

//  Vincenty formula to calculate great circle distance between 2 locations expressed as Lat/Long in KM

function VincentyDistance($lat1,$lat2,$lon1,$lon2){
    $a = 6378137 - 21 * sin($lat1);
    $b = 6356752.3142;
    $f = 1/298.257223563;

    $p1_lat = $lat1/57.29577951;
    $p2_lat = $lat2/57.29577951;
    $p1_lon = $lon1/57.29577951;
    $p2_lon = $lon2/57.29577951;

    $L = $p2_lon - $p1_lon;

    $U1 = atan((1-$f) * tan($p1_lat));
    $U2 = atan((1-$f) * tan($p2_lat));

    $sinU1 = sin($U1);
    $cosU1 = cos($U1);
    $sinU2 = sin($U2);
    $cosU2 = cos($U2);

    $lambda = $L;
    $lambdaP = 2*M_PI;
    $iterLimit = 20;

    while(abs($lambda-$lambdaP) > 1e-12 && $iterLimit>0) {
        $sinLambda = sin($lambda);
        $cosLambda = cos($lambda);
        $sinSigma = sqrt(($cosU2*$sinLambda) * ($cosU2*$sinLambda) + ($cosU1*$sinU2-$sinU1*$cosU2*$cosLambda) * ($cosU1*$sinU2-$sinU1*$cosU2*$cosLambda));

        //if ($sinSigma==0){return 0;}  // co-incident points
        $cosSigma = $sinU1*$sinU2 + $cosU1*$cosU2*$cosLambda;
        $sigma = atan2($sinSigma, $cosSigma);
        $alpha = asin($cosU1 * $cosU2 * $sinLambda / $sinSigma);
        $cosSqAlpha = cos($alpha) * cos($alpha);
        $cos2SigmaM = $cosSigma - 2*$sinU1*$sinU2/$cosSqAlpha;
        $C = $f/16*$cosSqAlpha*(4+$f*(4-3*$cosSqAlpha));
        $lambdaP = $lambda;
        $lambda = $L + (1-$C) * $f * sin($alpha) * ($sigma + $C*$sinSigma*($cos2SigmaM+$C*$cosSigma*(-1+2*$cos2SigmaM*$cos2SigmaM)));
    }

    $uSq = $cosSqAlpha*($a*$a-$b*$b)/($b*$b);
    $A = 1 + $uSq/16384*(4096+$uSq*(-768+$uSq*(320-175*$uSq)));
    $B = $uSq/1024 * (256+$uSq*(-128+$uSq*(74-47*$uSq)));

    $deltaSigma = $B*$sinSigma*($cos2SigmaM+$B/4*($cosSigma*(-1+2*$cos2SigmaM*$cos2SigmaM)- $B/6*$cos2SigmaM*(-3+4*$sinSigma*$sinSigma)*(-3+4*$cos2SigmaM*$cos2SigmaM)));

    $s = $b*$A*($sigma-$deltaSigma);
    return $s/1000;
}


echo VincentyDistance($lat1,$lat2,$lon1,$lon2);

More Related Contents:

  1. Doing calculations in MySQL vs PHP
  2. Calculating distance between zip codes in PHP
  3. MySQL Performance – “IN” Clause vs. Equals (=) for a Single Value [duplicate]
  4. MySQL Stored Procedure vs. complex query
  5. MySQL Query IN() Clause Slow on Indexed Column
  6. Prevent creating duplicate entries in PHP
  7. Query database then email posts from where user is subbed to
  8. Undefined index $_POST
  9. PHP Merging Row with same value (more than one column)
  10. Getting raw SQL query string from PDO prepared statements
  11. How to get count of rows in MySQL table using PHP?
  12. “Invalid parameter number: parameter was not defined” Inserting data
  13. MySQL Prepared statements with a variable size variable list
  14. Fatal error: Call to undefined function mysql_connect()
  15. Having issue with matching rows in the database using PDO
  16. How can I properly use a PDO object for a parameterized SELECT query
  17. How do I return integer and numeric columns from MySQL as integers and numerics in PHP?
  18. Why is password_verify returning false?
  19. Doctrine Query Language get Max/Latest Row Per Group
  20. Set timezone in PHP and MySQL
  21. MySQL diacritic insensitive search (spanish accents)
  22. Doctrine – How to print out the real sql, not just the prepared statement?
  23. Getting a PHP PDO connection from a mysql_connect()?
  24. How does array_diff work?
  25. How can I do a migration in laravel 5.5?
  26. Best way to store IP in database?
  27. Which is fastest in PHP- MySQL or MySQLi?
  28. PHP image resize on the fly vs storing resized images
  29. How can I list data in sections by an ID, with a while loop in PHP? [closed]
  30. find in set in laravel ? example

Leave a Comment