# New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

## Show distance between two points

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

1 Solution

Accepted Solutions
Not applicable

## Re: Show distance between two points

This works…amazing!

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

Cheers

Chris

9 Replies
Employee

## Re: Show distance between two points

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,

Not applicable

## Re: Show distance between two points

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

Employee

## Re: Show distance between two points

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

Not applicable

## Re: Show distance between two points

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

Employee

## Re: Show distance between two points

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.

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)

5 Chatley RoadArea Housing Office, 19/25 Hulton District Centre6.2
8 Ackworth RoadArea Housing Office, 7 Wesley Street0.6
17 Farnham DriveArea Housing Office, 7 Wesley Street9.0
Area Housing Office, 7 Wesley StreetArea Housing Office, 19/25 Hulton District Centre5.7
Not applicable

## Re: Show distance between two points

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)

City West Housing Trust

0161 605 7236

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

Not applicable

## Re: Show distance between two points

This works…amazing!

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

Cheers

Chris

Employee

## Re: Show distance between two points

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,