Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Distance on Googlemaps

I have read over all the great examples on creating a googlemaps and I have been successful. I have 6000 acct with latitude and longitude values. I have found some examples of how to calculate the distance between two acct.  The question I have is are there some examples of how I can select any account, plug in a distance and get all the accounts within that distance.  Please advise.

David

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: Distance on Googlemaps

A sort of.  Here how it looks in my case:

milefactor*acos(sin(latitude/rc)*sin(lat1/rc) + cos(latitude/rc)*cos(lat1/rc)*cos(longitude/rc - long1/rc))

where variables are

milefactor           3963                                 radius of the planet in miles (so the distance is in miles)

rc                       57.2958                             grads in radian

lat1                  =avg(SelectLatitude)          SelectLatitude is of the center point

long1               =avg(SelectLongitude)       SelectLongitude is of the center point

For center point selection, I use a data island that has three fields - full address of the center point (it could be a copy of account name in your case, use different field name or use approach with alternate sets), SelectLatitude, and SelectLongitude.

And, latitude and longitude are the fields, coordinate of Accounts in this case

13 Replies
christophebrault
Valued Contributor

Re: Distance on Googlemaps

Hi,

Are you talking about straight line distance or traffic distance ?

Not applicable

Re: Distance on Googlemaps

Straight Line.  I have the distance formula figured out already but what I dont know is how to select an Acct Number down, have it go to find the Lat and Long, enter the distance of choice, and then return all accts with that distance.

christophebrault
Valued Contributor

Re: Distance on Googlemaps

Ok, I've an example to do something like this.

I try to share it with you asap

mov
Esteemed Contributor III

Re: Distance on Googlemaps

David,

I don't want to take points from Christopher Brault, so keep the "correct answer" for him.  I just want to say that you're almost there already, since you have the formula.  If it is a table, use calculated dimension like this:
if(<your distance formula here> <= vDistance, AccountID)
And, check "suppress when value is null".
As for the expressions, it could be anything you want - Account name, the same distance formula, etc.

By changing the variable vDistance (use input box) you'll get different results.

Regards,
Michael

christophebrault
Valued Contributor

Re: Distance on Googlemaps

I'll try to explain what i do in my app :

I have a field called "CR" with longitude and latitude.

I've defined an alternate State call CR1. I use a listBox with this alternate State to select only ONE CR

then i show :

In one straight table, all CR with the distance in km with this expression :

if(CR=only({CR1}CR),0,

  if(isnull(atan2(sqrt(sqr(cos(latitude*pi()/180)*sin(longitude*pi()/180-Lon1))

          +sqr(cos(Lat1)*sin(latitude*pi()/180)-sin(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1)))

      ,sin(Lat1)*sin(latitude*pi()/180)+cos(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1))*6371), '999',

  num(atan2(sqrt(sqr(cos(latitude*pi()/180)*sin(longitude*pi()/180-Lon1))

          +sqr(cos(Lat1)*sin(latitude*pi()/180)-sin(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1)))

     ,sin(Lat1)*sin(latitude*pi()/180)+cos(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1))*6371

      ,'0')))

From this expression you can create a listbox with an expression like this one :

=class(aggr(if(CR=only({CR1}CR),0,

  if(isnull(atan2(sqrt(sqr(cos(latitude*pi()/180)*sin(longitude*pi()/180-Lon1))

          +sqr(cos(Lat1)*sin(latitude*pi()/180)-sin(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1)))

      ,sin(Lat1)*sin(latitude*pi()/180)+cos(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1))*6371), '999',

  num(atan2(sqrt(sqr(cos(latitude*pi()/180)*sin(longitude*pi()/180-Lon1))

          +sqr(cos(Lat1)*sin(latitude*pi()/180)-sin(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1)))

     ,sin(Lat1)*sin(latitude*pi()/180)+cos(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1))*6371

      ,'0')))

      ,CR)

   ,10,'km')

You can use it to filter in a map for example.

In google map, i change the color of each point with this expression :

=if(CR=only({CR1}CR),vGris,

aggr( if(

  if(CR=only({CR1}CR),vGris,

  if(isnull(atan2(sqrt(sqr(cos(latitude*pi()/180)*sin(longitude*pi()/180-Lon1))

          +sqr(cos(Lat1)*sin(latitude*pi()/180)-sin(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1)))

      ,sin(Lat1)*sin(latitude*pi()/180)+cos(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1))*6371), '999',

  num(atan2(sqrt(sqr(cos(latitude*pi()/180)*sin(longitude*pi()/180-Lon1))

          +sqr(cos(Lat1)*sin(latitude*pi()/180)-sin(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1)))

     ,sin(Lat1)*sin(latitude*pi()/180)+cos(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1))*6371

      ,'0')))<=10,Color(1),

      if(

  if(CR=only({CR1}CR),vGris,

  if(isnull(atan2(sqrt(sqr(cos(latitude*pi()/180)*sin(longitude*pi()/180-Lon1))

          +sqr(cos(Lat1)*sin(latitude*pi()/180)-sin(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1)))

      ,sin(Lat1)*sin(latitude*pi()/180)+cos(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1))*6371), '999',

  num(atan2(sqrt(sqr(cos(latitude*pi()/180)*sin(longitude*pi()/180-Lon1))

          +sqr(cos(Lat1)*sin(latitude*pi()/180)-sin(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1)))

     ,sin(Lat1)*sin(latitude*pi()/180)+cos(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1))*6371

      ,'0')))<=20,color(2),

      if(

  if(CR=only({CR1}CR),vGris,

  if(isnull(atan2(sqrt(sqr(cos(latitude*pi()/180)*sin(longitude*pi()/180-Lon1))

          +sqr(cos(Lat1)*sin(latitude*pi()/180)-sin(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1)))

      ,sin(Lat1)*sin(latitude*pi()/180)+cos(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1))*6371), '999',

  num(atan2(sqrt(sqr(cos(latitude*pi()/180)*sin(longitude*pi()/180-Lon1))

          +sqr(cos(Lat1)*sin(latitude*pi()/180)-sin(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1)))

     ,sin(Lat1)*sin(latitude*pi()/180)+cos(Lat1)*cos(latitude*pi()/180)*cos(longitude*pi()/180-Lon1))*6371

      ,'0')))<=50,color(3),color(4))))

      

    ,CR)

    )

I hope this help. I can't share a qvw because i didn't achieve it with fake data...

Not applicable

Re: Distance on Googlemaps

HI Michael,

When you say distance formula are you saying the:

ACOS(SIN([Latitude]*Pi()/180)..........

David

mov
Esteemed Contributor III

Re: Distance on Googlemaps

A sort of.  Here how it looks in my case:

milefactor*acos(sin(latitude/rc)*sin(lat1/rc) + cos(latitude/rc)*cos(lat1/rc)*cos(longitude/rc - long1/rc))

where variables are

milefactor           3963                                 radius of the planet in miles (so the distance is in miles)

rc                       57.2958                             grads in radian

lat1                  =avg(SelectLatitude)          SelectLatitude is of the center point

long1               =avg(SelectLongitude)       SelectLongitude is of the center point

For center point selection, I use a data island that has three fields - full address of the center point (it could be a copy of account name in your case, use different field name or use approach with alternate sets), SelectLatitude, and SelectLongitude.

And, latitude and longitude are the fields, coordinate of Accounts in this case

Not applicable

Re: Distance on Googlemaps

Hi Michael,

Is there any way you can post a small sample qvw file of your data for me to review.  I have only been trying to figure these maps out for a few weeks and would love to see another example.  Even if it is only 10 data points.

David

Not applicable

Re: Distance on Googlemaps

Hi Mike,

Thanks for the information. Is there any way you can share a small example of what you do.

David

Community Browser