Show distance between two points

[#1]

Hi,

Does anyone know and/or anyone tried to show distance between 2 longitude and latitude points on a map in an application?

This is possible, using the haversine formula and plenty of pages and calculators out there on the internet to do it. However, can this formula be written in the script somehow so when you clicked 2 points/results on the map something would highlight distance betwen them?

Cheers

Chris

[Anonymous]

Hi Chris,

I have calculated distances between LAT/LON pairs for a flight application I built. In this case I made the calculations in the load script as pushing this kind of thing into the front end is likely to cause poor performance. I had the following fields for each flight, which had a FlightID as my unique dimension:

[Origin Latitude]

[Origin Longitude]

[Destination Longitude]

[Destination Latitude]

I could then calculate the distance in KM with the following, which I think is based on the Haversine Formula

ACOS(SIN([Origin Latitude]*Pi()/180)*SIN([Destination Latitude]*Pi()/180)+COS([Origin Latitude]*Pi()/180)*COS([Destination Latitude]*Pi()/180)*COS(([Destination Longitude]*Pi()/180)-([Origin Longitude]*Pi()/180)))*6371 as [Distance (km)],

Now the challenge for you would be to work out how you enable the ability to click on two points on a map and then get the distance out of that. I suppose that depends on how your data model is put together but hopefull the above will give you some ideas to get started.

Regards,

Adam

[#3]

Hi Adam,

Cheers for this. I have testing this with a few results - see attached application. Can you advise where I add in formula you refer to?:

(ACOS(SIN([Origin Latitude]*Pi()/180)*SIN([Destination Latitude]*Pi()/180)+COS([Origin Latitude]*Pi()/180)*COS([Destination Latitude]*Pi()/180)*COS(([Destination Longitude]*Pi()/180)-([Origin Longitude]*Pi()/180)))*6371 as [Distance (km)],)

Even if I can't get it on a map if a field documented the distance it would be great.

Cheers

Chris

[Anonymous]

Hi Chris,

This function is used in the load script to make a field containing the distance. The data model is a little complex but the simple version related to just this function is as follows.

I start with two tables. One contains the flight ID and the Origin and Destination ports. The other contains the ports and their Lat and Lon.

Next I do two joins, first to link the Lat and Lon for the origin port and name these as Origin Latitude and Origin Longitude and then I repeat this to create the Destination Latitude and Destination Longitude. This gives me:

FlightID

Origin Port

Destination Port

Origin Latitude

Origin Longitude

Destination Latitude

Destination Longitude

Once I have those fields in a table I can then do the Haversine formula above to add in the distance in KM

[#5]

Hi Adam,

Thanks for getting back to me. Apologies if I sound really silly here but please see attached. I've tried to follow instructions but getting stuck on how to do the 2 joins? Also when this works where do you insert the formual..on the script or on the table box in the app or on a chart?

On the attached you will see our data and what that relates to in terms of your headings above.

Appreciate any further guidance...

Chris

[Anonymous]

I think you have everything you need in the second sheet of your spreadsheet. The only bit missing is some kind of ID number for each row, which I have created using a RecNo() function and called RowID.

LOAD RecNo() as RowID,

     Address,

     Destination,

     Latitude,

     Longitude,

     Dest Latitude,

     Dest Longitude,

     ACOS(SIN([Latitude]*Pi()/180)*SIN([Dest Latitude]*Pi()/180)+COS([Latitude]*Pi()/180)*COS([Dest Latitude]*Pi()/180)*COS(([Dest Longitude]*Pi()/180)-([Longitude]*Pi()/180)))*6371 as [Distance (km)]

FROM

(ooxml, embedded labels, header is 1 lines, table is Sheet2);

This will create a new field with the distance in it for each row of your second sheet.

You can test the distance formula out in your current data model by placing it as an expression in a table as written above (without the 'as [Distance (km)]' part)

Address	Destination	Distance (KM)
5 Addison Road	Area Housing Office, 7 Wesley Street	7.6
5 Chatley Road	Area Housing Office, 19/25 Hulton District Centre	6.2
8 Ackworth Road	Area Housing Office, 7 Wesley Street	0.6
17 Farnham Drive	Area Housing Office, 7 Wesley Street	9.0
Area Housing Office, 7 Wesley Street	Area Housing Office, 19/25 Hulton District Centre	5.7

[#7]

Hi Adam,

This works…amazing!

Last question is do you know the amends needed to show distance in miles?

Cheers

Chris Fray (BA (Hons), PgDip, MA, MCIH)

Business Intelligence Manager

Business Excellence Team

City West Housing Trust

0161 605 7236

chris.fray@citywest.org.uk<mailto:chris.fray@citywest.org.uk>

[#8 · Accepted solution]

Hi Adam,

This works…amazing!

Last question is do you know the amends needed to show distance in
miles?

Cheers

Chris

[Anonymous]

I think the 6371 is the radius if the Earth in KM. You should just be able to change that to 3959 and get your results in miles.

Glad this is sorted out, spherical geometry is nobody's friend. Please mark the answer as correct so others can find it and use it as well.

Cheers,

Adam

[Anonymous]

It's been a while since I've done this kind of development but I suspect there are two approaches. One would be to make a table in the load script of Origins and Destinations and the distance between them. This could either be a complete list of distances between pairs, a flag to say if it is above or below the limit (flag_is_within_5km = 1) or you could limit the load to only those under 5km. The other approach is to do this on the fly in the application but that might have quite an overhead as every selection means you would be doing a lot of calculations.