# 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
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:

city,

city.latitude,

city.longitude

RESIDENT CityGPS;

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:

city,

to_city,

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

RESIDENT TempDistance;

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:

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

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,