How do I use latitude and longitude values to find near by records

[#1]

Hi community,

I have attached a sample QVW file that I need some direction on.  In the QVW file, I've loaded some sample retail account information for the San Francisco area.  What I would like to do is be able to search and select one (or more) stores/accounts and then have another chart (straight  table) display all the accounts that are within X distance from the selections.

Does anybody know how to do this?  I have the latitude and longitude information for each account as part of the sample data.

[swuehl]

Please check John's excellent post here:

http://community.qlik.com/message/137084#137084

[#3]

Hi swuehl,

John's post is really great but there is an issue with it that I won't be able to resolve using his solution.  John's example uses 5 points (locations).  My store/account database has over 1M records.  John's solution is precalculating all the possible combinations.  This won't be possible if you have large number of records like I do.  I have attached an example of just a small set of accounts (city of San Mateo in California).

In my example (using John's haversine solution), whenever you select an account, the second chart object will return all the accounts that are within 1 km of the selection.  this is achieve by pre-calculating all the combinations for each account relative to one another.  Not a viable solution if you are dealing with hundreds of thousands of accounts.  Is there a way to calculate on the fly based on the users account selection?  Or load data on the fly so that we don't have to do precalculations?

[swuehl · Accepted solution]

Hi,

as John mentioned, you should be able to do the same in the frontend, calculating the distances on the fly.

I've done this quick&dirty in attached sample. There's probably plenty of room for improvements, f.e. I would precalculate as many stuff as possibe (e.g. the degree to radian calculations) in the script or as global variable (if depending on the vLat1 and vLon1).

Hope this helps,

Stefan

[#5]

Thanks for the reply swuehl. I got another issue related to the result set of the nearby retail stores. 

Is there a way where I can filter the result set of the chart titled "Stores nearby" using the Channel listbox?

Right now the Channel listbox is tied to the "Selected store" chart object.

I want to be able to select, for example "Supermarket" from the channel listbox and have it only return the supermarket accounts that are nearby.  I have attached a sample qvw. 

[swuehl]

Maybe like attached (using an alternate state AS1 to filter the results)?

[#7]

Fantastic!  thanks for your help on this topic!

[Anonymous]

Hi suehl;

I have taken this implementation one step further by adding a Google Map extension which maps the street locations of the nearby stores that are selected however  I did notice that when you make the nearby stores  selection ,  the original store location upon which the distance formula is based is not included . Is there way to concatenate the original  reference location to the resulting nearby stores locations so it can then also be placed as marker on the map for reference.

[Anonymous]

   I managed to solve  by changing the distance formula as follows:

    if($(vCalc) >=0 and $(vCalc) <= 1,$(vCalc))  .This allows the originating location

  which has to be zero to show up  with all other values in the range specified  . Note

  leaving out  an alternate value in the if statement  produces a null by default  which

  prevents it from showing up in the straight table whic is what I needed it to do.

  Thanks for Original Formula et al  saved me a lot of time

  Mike

[#10]

Great job, thanx!

Corrado