Excel-Formel zur Berechnung der Distanz zwischen mehreren Punkten mit lat/lon-Koordinaten
Ich bin derzeit dabei, eine mock-Datenbank-schema mit zwei Tabellen: Booking
und Waypoint
.
- Buchung speichert die taxi-Buchungs-Informationen.
- Wegpunkt speichert die Abholung und drop-off Punkte während der Reise, zusammen mit dem lat-lon position. Jede Sequenz ist ein Anschlag in der Reise.
Wie würde ich berechnen Sie die Entfernung zwischen den einzelnen Haltestellen in jeder Reise (mit den lat/lon-Daten) in Excel?
Gibt es eine Möglichkeit, programmgesteuert definieren, diese in Excel, d.h. so, dass eine Formel, die platziert werden können in die mileage
Spalte (Booking
Tabelle), Suche die passende Sequenz (über bookingId
) für die Reise in die Waypoint
Tisch und ein Ergebnis zurückgeben?
Beispiel 1:
Eine Reise mit 2 Stopps:
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 Meilen laut Google, Eintrag in mileage
Spalte in Booking
Tisch, wo id = 1
Beispiel 2:
Einer Reise mit 3 Haltestellen:
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 laut Google, Eintrag in mileage
Spalte in Booking
Tisch, wo id = 3
- checkout: cpearson.com/Excel/latlong.aspx
- Es ist zwar einfach zu berechnen Entfernungen über gerade Linien auf einer Karte ("loxodromic Entfernungen"), und nicht, dass schwer über den großen Kreise ("orthodromic Entfernungen"), ist die Berechnung ziemlich genau Laufleistungen über land, erfordert viel mehr komplizierte Lösungen auf ein minimum-Länge Pfad Probleme über Graphen, die die Wege-das wiederum erfordert eine gute route Datenbanken. Ich würde empfehlen die Verwendung von off-the-shelf-spezialisierte software, die für diese Funktion, es sei denn, Sie interessieren sich eigentlich nicht für die Richtigkeit. Wenn das der Fall ist, lassen Sie mich wissen, und ich finde den Formeln... ich habe Sie irgendwo...
- Vielen Dank für immer zu mir zurück. Genauigkeit ist nicht wirklich ein Problem 🙂
- Hier ist ein guter link: stackoverflow.com/questions/365826/.... Ein Plakat macht Erwähnung von "haversine", das ist der name der Formel zur Berechnung von Entfernungen auf einer Kugel. Google 'haversine excel' und Sie irgendetwas zu erhalten einigen Ergebnisse.
Du musst angemeldet sein, um einen Kommentar abzugeben.
Bis vor kurzem, genaue Karten wurden erstellt, durch die triangulation, die im wesentlichen ist die Anwendung von Pythagoras ' s Theorem. Für den Abstand zwischen jedem paar von Koordinaten nehmen Sie die Quadratwurzel der Summe der Quadrate der Differenz in x-Koordinaten und das Quadrat der Differenz in y-Koordinaten. Die x-und y-Koordinaten müssen jedoch die gleichen Einheiten (z.B. Meilen) umfasst factoring die Werte für Breitengrad und Längengrad. Dies kann kompliziert werden, da der Faktor für die Länge hängt von der Breite (zu Fuß rund um den Nordpol ist weniger weit als zu Fuß rund um den Äquator), aber in Ihrem Fall ein Faktor für 52o Norden dienen sollte. Aus diesen Ergebnissen (die möglicherweise überprüft werden hier) um die 20% anders die Beispiele, die Sie geben (im zweiten Fall mit pairing-IDs 6 und 7 und fügte hinzu, dass das Ergebnis die Ergebnis von der Paarung IDs 7 und 8).
Wenn Sie wollen finden Sie die Entfernung zwischen zwei Punkten verwenden Sie einfach diese Formel und Sie erhalten das Ergebnis in Km, nur konvertieren Sie Meilen, wenn nötig.
Punkt A: LAT1, LONG1
Punkt B: LAT2, LONG2
ACOS(COS(RADIANS(90-Lat1)) *COS(BOGENMASS(90-Lat2)) +SIN(BOGENMASS(90-Lat1)) *SIN(BOGENMASS(90-lat2)) *COS(RADIANS(long1-long2)))*6371
Hinsichtlich
Da Sie sagen, die Genauigkeit ist nicht wichtig, und wenn die Entfernungen klein sind (sagen wir weniger als 1000 Meilen), die Sie verwenden können, die loxodromic Entfernung.
Für diese, berechnen Sie die Differenz der latitutes (dlat) und als Differenz der Längengrade (dlon). Wenn es irgendeine chance (sehr unwahrscheinlich), dass Sie überqueren meridian 180°, nehmen modulo 360 ° zu gewährleisten, ist die Differenz von Längengraden zwischen -180º und 180º. Auch berechnen durchschnittlichen Breite (alat).
Dann berechnen:
Diese Distanz in nautischen Meilen. Gelten Umbauten als erforderlich.
ERKLÄRUNG: Dieser nutzt die Tatsache, dass eine nautische Meile ist per definition immer gleich eine minute-Bogen der Breite. Der Kosinus entspricht der Tatsache, dass die Meridiane näher zu einander, wie mit der Annäherung an die Pole. Der rest ist nur Anwendung des Pythagoras-theorem -- was verlangt, dass der relevante Teil der Welt flach sein, was natürlich nur in guter Näherung für kleine Entfernungen.
Alles hängt davon ab, wie weit und was Genauigkeit, die Sie benötigen. Die Berechnungen basieren auf der "Erde lokal flach" Modell nicht liefern tolle Ergebnisse für lange Strecken, aber für die kurze Strecke in Ordnung. Modelle unter der Annahme die Erde ist eine perfekte Kugel ist (z.B. Haversine-Formel) liefern keine geodätische Klasse Ergebnisse.
Sehen https://en.wikipedia.org/wiki/Geodesics_on_an_ellipsoid für mehr details.
Eine hohe Genauigkeit (mehrere mm) Lösungen bekannt ist als Vincenty die Formeln. Für meine Excel-Umsetzung hier Aussehen https://github.com/tdjastrzebski/Vincenty-Excel