Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
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

Hi Adam,

This works…amazing!

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

Cheers

Chris

9 Replies
Employee
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,

Adam

Not applicable

Re: Show distance between two points

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

Employee
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

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

Employee
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.

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 RoadArea Housing Office, 7 Wesley Street7.6
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

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>

Not applicable

Re: Show distance between two points

Hi Adam,

This works…amazing!

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

Cheers

Chris

Highlighted
Employee
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,

Adam

Employee
Employee

Re: Show distance between two points

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.