topic Re: How do I use latitude and longitude values to find near by records in QlikView

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

Wed, 21 Nov 2012 16:29:45 GMT

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.

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

swuehl — Wed, 21 Nov 2012 16:43:42 GMT

Please check John's excellent post here:

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

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

Thu, 22 Nov 2012 01:01:39 GMT

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?

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

swuehl — Thu, 22 Nov 2012 10:29:44 GMT

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

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

Mon, 26 Nov 2012 22:20:03 GMT

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.

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

swuehl — Mon, 26 Nov 2012 22:41:04 GMT

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

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

Tue, 27 Nov 2012 00:57:37 GMT

Fantastic! thanks for your help on this topic!

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

Anonymous — Sun, 13 Jan 2013 15:17:42 GMT

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.

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

Anonymous — Mon, 14 Jan 2013 00:50:36 GMT

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

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

Sun, 20 Jan 2013 21:18:18 GMT

Great job, thanx!

Corrado

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

Mon, 30 Sep 2013 04:26:12 GMT

Hi mpejskar,

I've been trying to do exactly this for my build.

At the moment I have a Google Maps extension on my screen which pin points any company that is selected onto the map.

I also have a table which shows the surrounding companies of the selected company.

What I am trying to achieve is to also have the surrounding companies show up on the map.

Would you be able to provide any insight into how you went in achieving this?

Is it a matter of altering the Google maps expressions?

Any help on this matter will be greatly appreciated.

Tony.

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

Wed, 12 Feb 2014 05:43:43 GMT

Hi Mike, May I know how did you capture the latitude and logitude information for each account here?

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

Tue, 25 Feb 2014 13:03:50 GMT

Hey Tony,

I think I'm trying to do just what you do. Would you mind uploading your qvw document or send it to me? I'd be thankful.

Thanks,

Netanel

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

Thu, 21 Apr 2016 08:19:52 GMT

Hi Swuehl,

I am trying to implement your logic in script level, but in my case I have lack of data if I do outer join it's going to hang stage and even computer takes 100% memory and system is going to hang.

In my scenario I have to give radius dynamically for pickup city & Delivery city in the two separate input boxs.

kindly suggest me some alternative solution.

Thanks,

Ashok.

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

swuehl — Thu, 21 Apr 2016 08:44:46 GMT

You would need to give some more details about your setting to understand the difference between yours and the one discussed here.

And why do you use two input boxes (two radii) for the two cities? Are you trying to find other cities that are located in the intersecting area?

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

Thu, 21 Apr 2016 08:57:14 GMT

I have latitude and longitude for both pick up city and delivery city, and the scenario is I need to calculate radius for both pickup and delivery & both radius should change dynamically.. so how shall I proceed wid this.

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

swuehl — Thu, 21 Apr 2016 09:03:03 GMT

Sorry, I don't understand how you want to calculcate the radius.

I would suggest that you create a new thread, and post as much information as possible:

Your current data model, some sample lines of data, your current chart and your expected result for the sample data.

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

Thu, 21 Apr 2016 10:37:05 GMT

Hi Swuehl,

As u provided formula for km is below one

atan2(sqrt(sqr(sin((only({1}Latitude)*PI()/180-vLat1)/2))+cos(vLat1)*cos(only({1}Latitude)*PI()/180)*sqr(sin((only({1}Longitude)*PI()/180-vLon1)/2)))

,sqrt(1-(sqr(sin((only({1}Latitude)*PI()/180-vLat1)/2))

+cos(vLat1)*cos(only({1}Latitude)*PI()/180)*sqr(sin((only({1}Longitude)*PI()/180-vLon1)/2)))))*2*6371

My requirement is user will enter miles instead of kilometers,so what should i do change in above formula.

Thanks,

Ashok.

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

Sun, 27 Nov 2016 15:16:12 GMT

Hi Stefan,

I have data a below, i want to calculate the distance using latitude and longitude, please help me out.

I tried the way you did but by distance in expression is showing zero

Please help me out wite below link

Calculating distance using Latitude and Longitude

employeid	name	state	District	Latitude	Longitude
1	john	asd	newyork	22.37	114.12
2	ricky	adsd	carolina	22.36	114.13
3	patrik	affdf	WDC	22.32	114.21
4	micheal	agffsf	London	22.32	114.16
5	harry	addsdsdfs	Hong Kong	22.28	114.18