Distance Calculation

I was tasked with an interesting request recently at work. It was to find how many hotels of an external brand were near one of our brands’ hotels. For those not familiar with the hotel industry, we leverage a data provider called STR. So no crazy web scrapers needed. We have all the hotel data required for the task.

The key fields required here are a unique identifier for each hotel and their respective latitude and longitude. My resulting data would need to have a row of every external brand hotel for each internal brand hotel. First, I created two tables in the database. One for all internal and the other for the external brand. Querying both tables together without specifying a join relation results in a row of every external brand hotel for each internal hotel. The only thing I needed to add was a calculation to determine the distance between two lat/lon coordinates.

We’re a Vertica shop. So I checked the reference guide and found the DISTANCEV function. It is as simple as passing in all four coordinates and it returns the distance in kilometers. I just multiplied that by 0.621371 to convert to miles. The task was to find number of external hotels within a 2 mile and 7 mile radius per internal hotel. There are a number of ways to get this but I decided to use Tableau. You never know when requirements will change and a viz will then be asked for. I pass this as a custom SQL query in Tableau’s data window.

screenhunter_43-sep-20-21-32

The output needed to be a list of our internal hotels with two columns. One counting the number of external hotels within 2 miles and another within 7 miles. Using Tableau’s Level of Detail function, I created the following.

if {fixed [choice_id], [external_str_nbr] : sum([distance])} <=2 then 1 else 0 end

For every unique pair of internal and external hotel ids, I check the distance between the two and if it is less than or equal to 2, I sum 1. This is repeated with less than or equal to 7 calculated field.

My end result is as follows:

screenhunter_43-sep-21-09-16

The way I have this setup, under 2 miles are also included in under 7. This was a fun task. I’ve never done anything related to distance measurement. Luckily, there are tools that make it so simple.

 

Share Button

Leave a Reply