Formule Excel pour calculer la distance entre plusieurs points à l'aide des coordonnées de latitude/longitude
Je suis actuellement à l'élaboration d'une maquette schéma de base de données, avec deux tables: Booking et Waypoint.
- De réservation les magasins de la réservation de taxi de l'information.
- Waypoint magasins le pick-up et drop-off de points pendant le voyage, le long de la latitude /longitude de la position. Chaque séquence est un arrêt dans le voyage.
Comment puis-je calculer la distance entre les différents arrêts à chaque voyage (à l'aide de la lat/lon de données dans Excel?
Est-il un moyen de définir par programme ce dans Excel, c'est à dire afin qu'une formule peut être placé dans le mileage colonne (Booking table), la recherche de la correspondance de séquence (via bookingId) pour ce voyage dans le Waypoint table et retourner un résultat?
Exemple 1:
Un voyage avec 2 arrêts:
1 1 1 MK4 4FL, 2, Levens Hall Drive, Westcroft, Milton Keynes 52.002529 -0.797623
2 1 2 MK2 2RD, 55, Westfield Road, Bletchley, Milton Keynes 51.992571 -0.72753
4.1 km selon Google, entrée en mileage colonne dans Booking table où id = 1
Exemple 2:
Un voyage avec 3 arrêts:
6 3 1 MK7 7DT, 2, Spearmint Close, Walnut Tree, Milton Keynes 52.017486 -0.690113
7 3 2 MK18 1JL, H S B C, Market Hill, Buckingham 52.000674 -0.987062
8 3 1 MK17 0FE, 1, Maids Close, Mursley, Milton Keynes 52.040622 -0.759417
27.7 km selon Google, entrée en mileage colonne dans Booking table où id = 3
- paiement: cpearson.com/Excel/latlong.aspx
- Alors qu'il est facile de calculer les distances sur des lignes droites sur une carte ("loxodromic distances") et pas si difficile de les grands cercles ("distances orthodromiques"), le calcul raisonnablement précise le kilométrage sur la terre nécessite beaucoup plus compliqué solutions minimum de longueur de chemin d'accès de problèmes sur les graphes représentant les routes -- qui à son tour nécessite une bonne route bases de données. Je le recommande pour une utilisation hors-the-shelf de logiciels spécialisés pour cette fonction, à moins que vous ne vous souciez pas vraiment de précision. Si c'est le cas, laissez-moi savoir et je vais trouver les formules... je les ai quelque part...
- Merci de revenir vers moi. La précision n'est pas vraiment un problème 🙂
- Voici un bon lien: stackoverflow.com/questions/365826/.... Une affiche fait mention de "haversine", qui est le nom de la formule pour le calcul des distances sur une sphère. Google "haversine excel', et vous avez quelque chose à obtenir certains résultats.
Vous devez vous connecter pour publier un commentaire.
Jusqu'à tout récemment, des cartes précises qui ont été construits par triangulation, qui dans son essence est l'application de Pythagore le Théorème de. Pour la distance entre n'importe quelle paire de coordonnées prendre la racine carrée de la somme des carrés de la différence de x coordonne et le carré de la différence en y coordonne. X et y des coordonnées doit être dans la même unité (par exemple milles) qui consiste à intégrer les valeurs de latitude et longitude. Cela peut être compliqué parce que le facteur de longitude dépend de la latitude (à pied tout autour de le Pôle Nord est moins loin que la marche autour de l'Équateur), mais dans votre cas, un facteur de 52o Nord devrait servir. À partir de cela, le résultat (qui peut être vérifié ici) sont autour de 20% différente des exemples que vous donnez (dans le second cas, avec couplage Id 6 et 7 et en l'ajoutant au résultat le résultat de l'appariement Id 7 et 8).
Si vous voulez trouver la distance entre deux points, il suffit d'utiliser cette formule et vous obtiendrez le résultat en Km, il suffit de convertir en miles, si nécessaire.
Point A: LAT1, LONG1
Point B: LAT2, LONG2
ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-lat2)) *COS(RADIANS(long1-long2)))*6371
Ce qui concerne
Puisque vous dites que la précision n'est pas importante, et en supposant que les distances sont petites (disons moins de 1000 miles), vous pouvez utiliser le loxodromic distance.
Pour cela, calculer la différence de latitutes (dlat) et de la différence des longitudes (dlon). S'il n'y avait aucune chance (peu probable) que vous êtes de passage au méridien 180°, prendre modulo 360 ° pour s'assurer de la différence des longitudes est entre 180 ° et 180°. Aussi calculer la moyenne latitude (alat).
Puis calculer:
Cette distance en milles nautiques. Appliquer les transformations nécessaires.
EXPLICATION: Ce tire parti du fait que, à un mille marin est, par définition, toujours égal à une minute d'arc de latitude. Le cosinus correspond au fait que les méridiens se rapproche les uns des autres à l'approche des pôles. Le reste est juste l'application du théorème de Pythagore -- qui exige que la partie pertinente de la planète à plat, ce qui est évidemment une bonne approximation pour les petites distances.
Tout dépend quelle est la distance et que la précision dont vous avez besoin. Les calculs basés sur la "Terre localement à plat" le modèle ne sera pas fournir d'excellents résultats sur de longues distances, mais pour la courte distance qui peut être ok. Les modèles en supposant que la Terre est une sphère parfaite (par exemple Haversine formule) ne donnent pas de géodésique de qualité des résultats.
Voir https://en.wikipedia.org/wiki/Geodesics_on_an_ellipsoid pour plus de détails.
Une haute précision (quelques mm) des solutions est connu comme Vincenty les formules de. Pour mon Excel mise en œuvre regardez ici https://github.com/tdjastrzebski/Vincenty-Excel