Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

distance between two coordinates

Hi,

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

Regards,

Rahul

7 Replies
Not applicable
Author

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

End If

atan2 = theta

End Function

Not applicable
Author

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;

johnw
Champion III
Champion III

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.

Not applicable
Author

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!

Not applicable
Author

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

prem_1961
Contributor II
Contributor II

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=0ahUK...

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

prem_1961
Contributor II
Contributor II

Hello John,

Attaching data for your reference.

Thanks

Prem