Evolusys is a small but fast-growing company. Two years ago, we were 20 and today almost 30. Although we anticipated this growth, it is now time to move and find more spaces for our offices.
In this context, I was wondering what the optimal location for our new offices should be. Our consultants are living around Lake Geneva, some of them in France. Every day, they come to their Workplace which can be a customer site and / or our office. As a first approach, we looked at optimizing the distance and time to commute for each of our colleagues.
I therefore used one of my favourite tools for this: Alteryx, the leader in the self-service data analytics segment.
Alteryx is the ideal tool to discover data, assess its quality, blend it and then put it to work. Today, we put the focus on the spatial functions that helped me to solve this case.
1. I started with the Excel file containing the home addresses of my colleague that I geocoded. Alteryx can use many geocoding extensions and you will find some in the community galleries.
I used the European geocoder, as it is appropriate and straightforward – It will take the Excel File “Employee.xlsx” as an input, and then you let it "geocode" by choosing the fields containing the address, zip code and country. The result is a map you see directly in Alteryx with points showing the address of your colleague.
2. Next, we are using the map tool that let’s you draw a polygon on the map ; this is the area we are targeting for the location of an office, the whole Leman arc.
The map tool is generating a polygon, a geometry shape with coordinates in latitude and longitude.
3. Then I would like to generate within this polygon all the possible points for a potential location for our office.
4.Then, for each grid, I would like a point instead of a grid. I’m doing this with another tool that returns the centroid of each grid.
The workflow is very simple: map tool, grid, centroid
5. Having this, I will cross-join the employee points and the grid’s centroid that will return all the combinations – The idea is to calculate the distances - assuming that all the employees are going to each single point of the grid. The cross join operation will return about 40K combinations.
The entire data flow will look like this – We perform a cross-join (like a multiplication) on the two sets and we calculate the distance.
Calculating the distance could be a challenge because distance "as the crow flies" would not work.
6.What is a bit magic with Alteryx is that this tool integrates a Tom-Tom database with drive times – and you can choose between peak hours, night or non-peak-hours.
7. So, the results will be a file with the distance and time to commute at peak hours between each employee’s address and each centroid of the grids.
The final step is to aggregate by centroid and take the top 5; where the distance and time are the minimum and the winner is…
Morges ! At first place comes this lovely lakeside little town. It is important to notice that if we were to choose any other location, it would imply additional transport costs and travel time. By looking at the table, one can see that if the chosen location is Geneva, you add 40% in time and distance.
The model could be further improved by integrating the % of time, people go actually need to go to the office and much more... but as a first shot to a challenging problem... it is not bad :-)
Experience the ultimate data blending with Evolusys !