topic Re: Recordset selection based on the Radius determination in QlikView

Recordset selection based on the Radius determination

Thu, 14 Apr 2016 23:00:02 GMT

Greeting All,

I am looking for solution for the following requirements (Oracle Database).

The data includes Origination (let’s say Los Angeles) and Destination (let’s say New York) points within US as a fields. The requirement is to query the dataset based on these 2 points and not only the cities specified, but all records within all cities which would come up into the area within certain radius selected by user (let’s say 50, 100 and 200 mi) from the main cities. As an example, user is selecting LA and NY as a filter criteria and specifying the radius 50mi. The output of the query should return all records satisfying the main conditions plus all records for all cities surrounding LA and NY within 50 mi radius (let’s say Beverly Hills, Pasadena, etc. for origination and Manhattan, Brooklyn, etc. for destination).

It is worth noting that providing this functionality is critical for the whole QlikView project.

Would be great to have any advice on possible solution.

Thanks,

Leon

Re: Recordset selection based on the Radius determination

swuehl — Fri, 15 Apr 2016 16:32:55 GMT

Leon,

have a look at

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

Re: Recordset selection based on the Radius determination

rbecher — Fri, 15 Apr 2016 16:37:34 GMT

Hi Leon,

if you have the geo position (lat/lon) you can calculate the Haversine formula:

https://gist.github.com/ralfbecher/a4c22c35ee2f3ab43d6f

// calculation of distance of two geo references (lat/lon):
= 12742 * atan2(sqrt(sqr(sin(((lat2-lat1)*PI()/180)/2)) 
+ (cos(lat1*PI()/180) * cos(lat2*PI()/180)) 
* pow(sin(((lon2-lon1)*PI()/180)/2),2)),
sqrt(1 - (sqr(sin(((lat2-lat1)*PI()/180)/2)) 
+ (cos(lat1*PI()/180) * cos(lat2*PI()/180)) 
* pow(sin(((lon2-lon1)*PI()/180)/2),2))))

Haversine formula - Wikipedia, the free encyclopedia

- Ralf

Re: Recordset selection based on the Radius determination

Fri, 15 Apr 2016 17:01:08 GMT

Thank you guys for your prompt respond – I was under impression that it is a dead end.

I’ll go through your recommendations and let you know.

Thanks again,

Leon

Re: Recordset selection based on the Radius determination

Fri, 13 May 2016 16:44:08 GMT

Hi Ralf,

Just letting you know that even with your recommendations, my experienced developer is still working on this function (almost for a month!). I think would be really beneficial for QlikView if Product Development team would get involved into that since this functionality is a showstopper for our QV project and could be extremely important for other customers.

Thanks,

Leon

BTW

It was nice meeting you at Qonnection. Would be great if this message would be passed to Aran

Re: Recordset selection based on the Radius determination

rbecher — Fri, 13 May 2016 17:55:33 GMT

Hi Leon,

what is the problem with this calculation? Do you need any help?

- Ralf

Re: Recordset selection based on the Radius determination

Fri, 13 May 2016 18:23:05 GMT

Hi Ralf,

Yes, I do. Would be great if we can discuss this off-line.

I guess you have my contact info.

Thanks,

Leon