Afstand berekenen

[velde046]

Hoi, ik heb een probleem waar ik niet uit kom.
Ik probeer met een enkele formule de afstand tussen twee decimale coordinaten te berekenen. Nou heb ik wel iets gevonden op http://www.cpearson.com/excel/LatLong.aspx en de formule gebruikt, dit naar de Nederlandse functie vertaald. Maar de uitkomsten verschillen totaal van de waarden die ik al had....
In kolom Werkelijke afstand staat de afstand die ik wist, in de laatste kolom de waarde volgens de formule.

Ik snap niet waar het mis gaat, heb ik iets fout gedaan? Of zit er een fout in de formule?
Wie kan helpen?

 

[AlexCEL]

Er staat op door jouw gevonden pagina een linkje naar een prachtige Excelsheet die je kan gebruiken (zie onder).

Ik vermoed dat het fout gaat omdat je X,Y-coördinaten gebruikt en geen graden. Maar ik ben geen cartograaf..

 

[velde046]

In de tekst staat dat indien je voor beide halfronden gebruik moet maken van decimale coordinaten. En dat is volgens mij wat ik toch heb.... ik wete niet hoe ik de coordinaten die ik heb kan controleren helaas... die komen van een lijstje dat ik heb.
En dus heb ik vervolgens de formule gebruikt die staat bij het gedeelte over decimale berekening:

Great Circle Distances With Decimal Degrees Coordinates

To calculate the Great Circle Distance between Location 1 and Location 2, where the coordinates are in decimal degree format, use the following formula:

=RadiusEarth*((2*ASIN(SQRT((SIN((RADIANS(D71)-RADIANS(D72))/2)^2)+
COS(RADIANS(D71))*COS(RADIANS(D72))*
(SIN((RADIANS(E71)-RADIANS(E72))/2)^2)))))

Here, cells D71 and E71 contain the latitude and longitude of Location 1 and cells D72 and E72 contain the latitude and longitude of Location 2. For readability, the formula is split over several lines. In Excel, of course, the formula is in a single line in a single cell.

The same formula, using defined names for the coordinates is shown below. Lat1__ and Long1__ are the coordinates of the start location and Lat2__ and Long2__ are the coordinates of the end location.

=RadiusEarth*((2*ASIN(SQRT((SIN((RADIANS(Lat1__)-RADIANS(Lat2__))/2)^2)+
COS(RADIANS(Lat1__))*COS(RADIANS(Lat2__))* (SIN((RADIANS(Long1__)-RADIANS(Long2__))/2)^2)))))

Zoals gezegd weet ik niet hoe ik de lijst met coordinaten die ik heb moet controleren, maar het zou aardig betrouwbaar moeten zijn, komt uit gegevens van duivenorganisatie...
Ik zat te denken dat ik X en Y misschien moest omwisselen aangezien tweede getal breedtegraad is (daarom is bij die ene ook een min coordinaat, want links van Greenwich) en eerste getal lengte graad? Poeh dit is een lastige hoor.

 

[alphamax]

Misschien de komma eens goed zetten in X en Y, de waardes zijn nu een factor 10000 teveel.
De formule heb je juist vertaald.

 

[velde046]

Dat had ik me wel gerealiseerd, maar dat is niet het probleem de getallen wijken ook af, de plaats van de komma is niet zo belangrijk in dat geval. Wel krijg ik nu een ander vermoeden, namelijk dat de oorspronkelijke tabel geen rekening houdt met de ronding van de aarde, maar van vlak uitgaat. Wellicht dat daarom het verschil groter is naarmate de afstand korter is... moet ik nu nog een formule vinden die het uitrekent als een plat vlak om te vergelijken....

Hopelijk is dat makkelijker dan afstand op een bol berekenen...
Als iemand het weet hoor ik het ook graag

 

[lam201]

Maar de uitkomsten verschillen totaal van de waarden die ik al had....
In kolom Werkelijke afstand staat de afstand die ik wist, in de laatste kolom de waarde volgens de formule.

misschien klopt jouw eerste afstand niet?
Als ik de eerste coordinaten (Baccum) invoer in Google Maps kom ik op een afstand van 109,68 KM
Als ik de formule uit jouw Excel gebruik kom ik uit op 109,68 KM
(wel corrigeren met die factor 10.000)

Met de berekening (formule) lijkt dus niks mis

 

[alphamax]

Wel krijg ik nu een ander vermoeden, namelijk dat de oorspronkelijke tabel geen rekening houdt met de ronding van de aarde, maar van vlak uitgaat.

Vlak? Een duif is geen mol.

Probeer eerst maar eens te achterhalen hoe betrouwbaar de bron van jouw data is.
Waar komt deze data vandaan?
Welke coordiantenstelsel heb je? ik zou latitude en longistude verwachten, X en Y lijkt me een andersoortig coordinatenstelsel.

 

[alphamax]

De coordinaten uit berich#1 liggen in resp. duitsland, belgie, zuid-frankrijk
Mij lijkt dat het Rijksdriehoeksstelsel daar niet meer van toepassing is.
Zie https://nl.wikipedia.org/wiki/Rijksdriehoekscoördinaten en https://nl.wikipedia.org/wiki/Rijksdriehoekscoördinaten#/media/Bestand:RDbounds.png

 

[AlexCEL]

*Opmerking reeds verwijderd*

Bovenstaande kwam ik ook achter (te laat)...

 

[emields]

in belgië gebruiken we dit.

https://www.kbdb.be/nuttige-programmas/

 

[velde046]

in belgië gebruiken we dit.

https://www.kbdb.be/nuttige-programmas/

Daar komen inderdaad de gezochte waarden uit, alleen wordt het berekend via allemaal tussenstappen/cellen. Ik hoopte dat ik het in één formule kon doen.... eens kijken of ik het kan combineren...

 

[emields]

je kan proberen maar dat zal dan wel een formule zijn van een paar regels lang.

 

[cow18]

@emields,
gebruik pearson's benadering in #1, dat is toch (bijna) een UDF

 

[velde046]

Het spijt me, ik was op het verkeerde been gezet. Het blijkt dat de coordinaten die ik daar heb dus geen decimale coördinaten zijn, maar toch een graden/minuten/seconden. De cijfers voor de komma zijn dus de graden, minuten en seconden,.
Ter illustratie, de coördinaten 531945,4 en 65503,5 zijn dus feitelijk 53° 19' 45,4'' en 6° 55' 03,5''.
Mijn probleem is nu dat ik wel een formule heb om het op basis van dit systeem te berekenen, maar blijkbaar zijn de waarden nu niet geschikt om juist mee te rekenen. Ik moet denk ik de waarde in de cel omzetten naar een tijdwaarde... dus dat 531945,4 eigenlijk 53:19:45,4 wordt.... dan kan ik deze eventueel converteren naar decimaal of de andere formule proberen.
Maar ik krijg het dus nog niet zo dat ik het in het goede format krijg....iemand een idee hoe ik dat kan fiksen?

 

[emields]

bekijk dit maar eens

 

[velde046]

bekijk dit maar eens

Bedankt voor de tip, maar deze had ik inderdaad gezien. Mijn doel is om dit te doen zonder allemaal hulpkolommen. Ik heb twee formules die dat zouden moeten kunnen volgens de beschrijving :
Voor StraalAarde reken ik met 6378,137. De eerste is voor coordinaten in graden, minuten, seconden en is :
=StraalAarde*((2*ASIN(WORTEL((SIN((RADIALEN(Lat1_*24)-
RADIALEN(Lat2_*24))/2)^2)+COS(RADIALEN(Lat1_*24))*COS(RADIALEN(Lat2_*24))*
(SIN((RADIALEN(Long1_*24)-RADIALEN(Long2_*24))/2)^2)))))
De tweede is voor berekening met decimale coordinaten:
=StraalAarde*((2*ASIN(WORTEL((SIN((RADIALEN(Lat1__)-RADIALEN(Lat2__))/2)^2)+
COS(RADIALEN(Lat1__))*COS(RADIALEN(Lat2__))* (SIN((RADIALEN(Long1__)-RADIALEN(Long2__))/2)^2)))))

Mijn coördinaten zijn in principe in graden, minuten, seconden.
Thuisbasis
Lat_1 Long_1
+531945,4 +65503,5 (Lat_1 is dus 53° 19' 45,4", Long_1 is dus eigenlijk 6° 55' 03,5")


Plaats Lat_2 Long_2
Baccum +523040,9 +72514,4 (afstand zou moeten uitkomen op 97,106)
Chimay +500208,8 +42239,7 (afstand zou moeten uitkomen op 406,306)
Dax +434242,0 -10241,0 (afstand zou moeten uitkomen op 1218,815)

Wat moet ik doen met de coördinaatcellen om ze bruikbaar te maken voor één van de opgegeven formules en dus zonder extra hulpkolommen...(hooguit twee extra kolommen voor bewerkte coördinaten, zodat ze bruikbaar zijn).
Het vervelende is dat het lastig te controleren is. De waarden die ik voor afstand hierboven geef zijn berekend door een postduivenorganisatie. Ik heb ook een online tool geprobeerd en ook het bestand wat je als voorbeeld meegaf en die geven mij drie verschillende antwoorden.... Dus dat helpt ook niet echt (marge van max. 700 meter op Dax aan afstand, de tweede lag er tussenin. Mijn vermoeden is dat er wellicht ook telkens met een iets andere StraalAarde gerekend wordt, maar dat kan ik niet controleren, en ben ik ook niet zeker in hoeverre een klein verschil daarin gaat doorwerken in de uitkomst.
Niet verwacht dat dit zo'n uitdaging zou zijn...

 

[emields]

straal aarde is dan ook op iedere anders aangezien de aarde geen perfecte bol is.

 

[cow18]

https://www.igismap.com/haversine-formula-calculate-geographic-distance-earth/
zie bijlage
B1:C5 naar radialen vertalen in B14:C18
Dan haversine-formule met afstand in km in D1418 met aardstraal = 6.371 km (zoals daar aangenomen)
In E14:E18 de afwijking in meters tov de berekening van Emields
In F14:F18 de afwijking in m per km afstand --> zo te zien is de afwijking tov Emield's afstand ergens 3 m per km (dus relatief klein), verschil tussen ons beiden voor die 4 afstanden is 4,36 km op een totaal van 2.045 km.
Grapje, zou je bv. de aardstraal een beetje (0.2%) laten toenemen tot 6384 km (in cel B21) dan zijn we gelijk.
Dus, moet je, zoals Emields in #17 ook aangaf, zelf een beetje opzoekingswerk doen om, per punt (?), een iets betere inschatting van de aardstraal te bekomen of natte vinger iets kiezen tussen 6.371 en 6.384 km of die foutmarge aanvaarden.

nog wat knipsels

 

[cow18]

nog even vergeleken met jouw reactie #16 en een foutje gevonden in de omzetting naar radialen bij negatieve waarden.
Nu is het verschil van Dax -> thuis en jouw berekende waarde nog 480 m, wat binnen je foutmarge valt en eigenlijk op de keeper beschouwd peanuts is. (0.4 promille)

 

[velde046]

Ben eruit.... heb toch maar de oplossing met tussenstappen gekozen en nu werkt het goed. Heb nog wel minimale verschillend met andere tools, maar ik weet niet of die ook dezelfde straal van de aarde gebruiken. De perfecte oplossing zat er niet bij, maar de gouden tips en hulp van iedereen hebben me enorm geholpen om het toch voor elkaar te krijgen.

Bedankt.