Recordset selection based on the Radius determination

[#1]

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

[swuehl]

Leon,

have a look at

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

[rbecher]

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

[#4]

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

[#5]

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

[rbecher]

Hi Leon,

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

- Ralf

[#7]

Hi Ralf,

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

I guess you have my contact info.

Thanks,

            Leon