topic Show distance between two points in QlikView

Show distance between two points

Wed, 11 Dec 2013 15:21:07 GMT

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

Re: Show distance between two points

Anonymous — Wed, 11 Dec 2013 15:34:35 GMT

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

Re: Show distance between two points

Fri, 13 Dec 2013 14:50:21 GMT

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

Re: Show distance between two points

Anonymous — Mon, 16 Dec 2013 10:00:47 GMT

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

Re: Show distance between two points

Wed, 18 Dec 2013 07:57:06 GMT

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

Re: Show distance between two points

Anonymous — Wed, 18 Dec 2013 09:24:10 GMT

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

Re: Show distance between two points

Wed, 18 Dec 2013 09:42:42 GMT

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>

Re: Show distance between two points

Wed, 18 Dec 2013 09:42:55 GMT

Hi Adam,

This works…amazing!

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

Cheers

Chris

Re: Show distance between two points

Anonymous — Wed, 18 Dec 2013 09:52:21 GMT

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

Re: Show distance between two points

Anonymous — Fri, 12 May 2017 16:13:06 GMT

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.

Re: Show distance between two points

robyn369 — Tue, 06 Jun 2023 08:52:23 GMT

Hi All,
BELOW IS FOR QLIK SENSE

Highlevel overview:

IF you have a requirement to plot 2 areas (latitudes and longitudes) and if you want to draw circles around then within a said radius, please see the below.

All you need, is a latitude 1, longitude 1 and latitude 2, longitute 2, with key fields in these 2 tables.

Then you need to join both the tables to get them into one table for calculating the distance.

Further, use the haversine formula (just copy paste) and change the fields as required and inline with you data.
Haversine formula: https://community.qlik.com/t5/QlikView-App-Dev/Haversine-formula-to-find-distance-between-two-lat-long-points/td-p/234630/page/2
use the response with code from kevinpintoka (thanks to him)

Go the GeoAnalytics and use the 'Travel Areas' option.

Script Section:

Cost Value: 10 (in my example 10 km radius)
Cost Field: Blank
Cost Unit: Kilometers (from the above input it will now be 10 KM)
Transportation: Bird
Name: No Change ( leave as it is)
Type: Loaded table
Table Name: specify the table name that contains the distance calculated (from haversine formula) above.
Table Fields: Key field, Latitude1, Longitude1 (i am using latitude 1 since I want to draw circles around this geo point. If you want you can change to latitude 2, longitude 2
Geometry Type: Latitude and Longitude Point
Only load distinct: YES
Location ID Suffix: No change ( leave as it is)

Click Next.
Select the travel areas in the next wizard window.
Insert script and reload.
Note that you may need to reduce the chunk size based on license limitation you have.

If you get an error stating: "dataset is higher than maximum allowed by license", then follow the below link
https://community.qlik.com/t5/Official-Support-Articles/GeoAnalytics-getting-error-QVX-SYNTAX-ERROR-Size-of-dataset-is/ta-p/1715348
(another thanks to this author)
Front End:
Create Geo Map
Add point layer -> add the geopoint_lat1_long1
Add area layer -> add geo_lat2_long2
Add density layer -> add the geopoint_lat1_long1
tip: under this layer -> properties/appearance/radius (here you can specify in meters)/scale (turn default off and switch custom on/change the weight factor to 45) -> this will change the heatmap and rather appear as circles.
Add line layer -> add the distance_km -> under From, To, width/add lat1_long1 AND lat2_long2 like a from an to line. under properties/appearance/change the line width/ change arrow style and arrow curviness. addtionally you can add the label under label section and just add the distance_km. this will show the actual distance on the gep graph.

Hope that helped someone.

Cheers

Kind Regards,

Robinson