Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

5 Replies
Highlighted
Not applicable

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

Highlighted
Not applicable

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;

Highlighted
MVP
MVP

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.

Highlighted
Not applicable

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!

Highlighted
Not applicable

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