Calculating distance between multiple adresses in excel with OSM help

General talk
1

Hi,

I have multiple adresses that I want to know the distance between in a excel sheet and I know its possible to setup api with OSM to calculate these. Could someone please help me through the process of how I do it?

Thanks in advance! :slight_smile:

Sincerely
Amar

2

the OSM wiki at https://wiki.openstreetmap.org/wiki/Routing

can be a starting point for your research.

3

If you know the coordinates of the addresses you can calculate the birdseye distance between them: DISTANCE = ACOS(SIN(A3*$C$2)SIN(A4$C$2)+COS(A3*$C$2)COS(A4$C$2)COS(B4$C$2-B3*$C$2))*6371
The coordinates have to be in decimal format. In a spreadsheet the latitudes are in column A, the longitudes in column B. Cell C2 contains the constant value 0.0174532925 (Pi/180). Result is in kilometers. ref.: https://www.movable-type.co.uk/scripts/latlong.html

But I doubt this is what you are looking for? The distance ‘on the ground’ depends on the presence of roads and of the type of ‘vehicle’ you are calculating the distance for. A pedestrian surely will take a different route compared to automobiles, bicycles, etc., etc…

4

Looks like Discourse has mangled the formula. Here it is in full:
ACOS(SIN(A3*$C$2)*SIN(A4* $C$2)+COS(A3*$C$2)*COS(A4* $C$2)*COS(B4* $C$2-B3*$C$2))*6371

I assume row 3 has the coordinates for address A, and row 4 for address B? So
ACOS(SIN(lat1*constant)*SIN(lat2*constant)+COS(lat1*constant)*COS(lat2*constant)*COS(lon2*constant-lon1*constant))*6371

?

5

Having tested this between London and Edinburgh, I get within 1km of the calculation result from GPS Visualiser - so I’m going to assume the second formula is accurate.

(The 1km difference may be because I couldn’t locate the exact address for the node for each city in OSM: I took the address by right-clicking nearby at openstreetmap.org)

6

Hi eteb3, looks you’ve used the right formula. How the formula I posted got corrupted, I cannot trace. The one you used corresponds 1:1 to the one in my archive.

7

Thanks.

Discourse (this platform) converts straight quotes to curly quotes, and interprets * * as indicating italics for everything in between (so 4 * 5 * 6 without spaces becomes 456).

Bracketting the formula with ` at the beginning and end, or using the </> button, will prevent this.

1 Like
9

Thanks for the explanation.

1 Like