topic Re: distance between two coordinates in QlikView

distance between two coordinates

Tue, 19 Jul 2011 11:54:14 GMT

Hi,

Is there a way to calculate distance between two coordinates in a scatter chart (superimposed on google map).

Regards,

Rahul

Re: distance between two coordinates

Fri, 29 Jul 2011 18:00:32 GMT

I just did this the other day. I doubt I set this up the best way but... it's something. This gives you the distance in miles, if you read the macro it should be easy to figure out how to make it in kilometers. Also note that if you have a ton of points, the TempDistance table is goign to be huge. I haven't really figured out hte best way to use this, I usually just do something like select a to_city and then search for distance <=50 or something. First you have to do something like this:

TempDistance:

NOCONCATENATE LOAD DISTINCT

city,

city.latitude,

city.longitude

RESIDENT CityGPS;

JOIN (TempDistance) LOAD DISTINCT

city as 'to_city',

city.latitude as 'to_latitude',

city.longitude as 'to_longitude'

RESIDENT CityGPS;

That join gives you all combinations that you need the distance from. In my case, I wanted the distance from each city, to each city. Then do something like this:

Distance:

LOAD DISTINCT

city,

to_city,

round(haversine(city.latitude,city.longitude,to_latitude,to_longitude),'.1') as 'distance'

RESIDENT TempDistance;

Add the two macros below:

Function haversine(lat1,long1,lat2,long2)

pi=4*Atn(1)

r = 6371 'radius of the earth or something

lat1 = lat1 * pi / 180

lat2 = lat2 * pi / 180

long1 = long1 * pi / 180

long2 = long2 * pi/180

dLat = (lat2 - lat1) '* (pi / 180)

dLon = (long2 - long1)' * (pi / 180)

a = (sin(dLat / 2) * sin(dLat / 2)) + cos(lat1) * cos(lat2)* (sin(dLon / 2) * sin(dLon / 2))

c = 2 * atan2(sqr(a),sqr(1-a))

d = r * c

d = d * .621371192 '.621371192 converts km to miles

haversine = d

end Function

Function atan2(ys,xs)

If xs <> 0 Then

theta = Atn(ys / xs)

If xs < 0 Then

theta = theta + pi

End If

Else

If ys < 0 Then

theta = 3 * pi / 2 '90

Else

theta = pi / 2 '270

End If

atan2 = theta

End Function

Re: distance between two coordinates

Fri, 29 Jul 2011 18:05:33 GMT

My previous post was so long I thought it was best I just add another one. Let's say you want to group cities based on how far they are from certain cities, here's what I did (if you know a better way I'd love to hear it):

Metropolis:

NOCONCATENATE LOAD DISTINCT

city,

if(distance <=50,

if(to_city = 'DALLAS','DFW',

if(to_city = 'HOUSTON','HOUSTON',

if(to_city = 'ODESSA','ODESSA',

if(to_city = 'MCALLEN','SOUTH TEXAS',

if(to_city = 'CORPUS CHRISTI', 'CORPUS',

if(to_city = 'UVALDE', 'SAN ANTONIO',

if(to_city = 'KILLEEN', 'AUSTIN/WACO'))))))),

if(distance <=80 AND to_city = 'SAN ANGELO', 'CENTRAL TEXAS')) as 'metroplex'

RESIDENT Distance;

Re: distance between two coordinates

johnw — Fri, 29 Jul 2011 23:38:45 GMT

The attached example shows three different approaches (arccosine, Haversine, Vincenty) for calculating the distance between two points based on latitude and longitude. See script comments for possible issues with each.

And yes, if you have a lot of points, and want to calculate the values in the script, the resulting table is huge. With a lot of points, you'll likely need to calculate distance in a chart, and make sure the chart doesn't have your full data set of points.

Re: distance between two coordinates

Thu, 22 Nov 2012 01:03:07 GMT

John,

Your solution is very creative. I love it but what would you do if you had millions of locations points as opposed to 5? you can't possibily pre-calculate that many. Is there a solution where you can load data on the fly?

See my post: http://community.qlik.com/message/284004#284004

Thanks for you contribution to the community!

Re: distance between two coordinates

Tue, 03 Jun 2014 17:27:05 GMT

Hi John,

how can I adapt your suggestion/solution to my case?

I have two completely different DBs. The first containing all info (Lat and Long enclosed) for the Pharmacies of Italy. The second containing all info (Lat and Long enclosed) of all Dental Offices of Italy. I need to know all Dental Offices closer to the Pharmacy region by region.

Could you please provide me a little example on what to do?

Thanks a lot in advance

Re: distance between two coordinates

prem_1961 — Thu, 17 Mar 2022 16:47:07 GMT

Hello John,

I don't know whether other got the result what they are looking for, but when I converted your QLIK VIEW app into QLIK Sense app and tried the formula for my dashboard app, it is giving the wrong result for me. I am finding distance between ZIP - 32344 and 32336 which comes to 28.7 Miles as per google map, please see link below:

https://www.google.com/search?q=distance+between+32344+to+32336&ei=ikIzYuPuLq26qtsPjZuE-AM&ved=0ahUKEwjj2-fQqs32AhUtnWoFHY0NAT8Q4dUDCA0&uact=5&oq=distance+between+32344+to+32336&gs_lcp=Cgdnd3Mtd2l6EAM6BwgAEEcQsAM6CggAEEcQsAMQyQM6BQghEKABSgQIQRgASgQIRhgAUPAJWKchYPMsaAJwAXgAgAGRAYgB-QKSAQMxLjKYAQCgAQHIAQjAAQE&sclient=gws-wiz

and same with you formula coming out to be 132.29 miles. Picture attached. Due to nature of my job I can not share the data.

Please let me know if there is any issue.

Thanks

Prem

Re: distance between two coordinates

prem_1961 — Thu, 17 Mar 2022 16:54:39 GMT

Hello John,

Attaching data for your reference.

Thanks

Prem