Comment puis-je optimiser les requêtes de géorecherche en PHP/MySQL pour les recherches basées sur la distance sur de grands ensembles de données ?

Optimisation de la géo-recherche (distance) en PHP/MySQL

Lors de l'exécution de requêtes basées sur la distance sur de grandes tables contenant des paires latitude-longitude, l'optimisation des performances des requêtes devient cruciale. Considérez les défis suivants rencontrés par une requête MySQL :

• MySQL parcourt toutes les lignes, ce qui entraîne des coûts de calcul élevés.
• Les extensions géospatiales telles que R-Trees peuvent être complexes et nécessiter une expertise mathématique.

Délimiter la zone de recherche

Une solution efficace consiste à définir un cadre de délimitation autour de la zone d'intérêt. La requête peut ensuite sélectionner des lignes dans cette région délimitée, réduisant ainsi considérablement le nombre de calculs de distance. L'article Movable Type fournit un guide détaillé sur la création de cadres de délimitation et leur utilisation dans les requêtes SQL.

Formule de Vincenty pour des résultats plus précis

Si la formule Haversine est insuffisante pour précision, la formule Vincenty peut être déployée. Cet exemple JavaScript démontre son implémentation pour calculer les distances des grands cercles :

//  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);

Conclusion

En tirant parti des cadres de délimitation et en envisageant des méthodes alternatives de calcul de distance, vous pouvez améliorer considérablement les performances de vos requêtes de géo-recherche sur MySQL. Qu'il s'agisse d'une recherche à grande échelle ou d'un composant critique de votre application Web, ces optimisations amélioreront l'expérience utilisateur et garantiront des opérations de base de données efficaces.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!