Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
By Owen Coyle (@TheOC) and Roan Pilsworth
We brought joy to the worst place in England using Alteryx. Here is how…
Modern life revolves around data, yet the metrics we use to assess an area are anything but clear. Health, wealth, population, education, and even “happiness” are all qualities politicians and policymakers attempt to quantify – each one an effort to stamp our homes with some sort of crude label. But in England, some would argue, there are key pillars of our society that get overlooked. One such cornerstone is so central to our culture that its absence can make an otherwise idyllic location feel utterly desolate. We say no more! In this blog, @TheOC and I have detailed the steps we took to help us not just identify, but also bring joy, to the true worst place in England.
Source: Generated with ChatGPT
Inspiration
We’d like to take you on a story, a hike, almost, with this blog. Picture this:
It’s a Friday evening, the week’s winding down, and @TheOC and I are nursing a frosty beer. As the hops began to ignite sparks of inspiration, we started musing – there has to be a “worst place” in England. And then we thought, surely, with the help of Alteryx, we can bring joy to this sombre site!
The Scope
- Worst Place in England: We would define it as the furthest away point from a pub.
- England: We used country boundaries data from the UK Government – they seem like they know what they’re doing, most of the time.
- Pubs: We consider ourselves pretty clued up on pubs in our hometowns, but to make it fair, we grabbed some data on open pubs from Kaggle.
The Process: Step by Step
To take on this noble quest, we realized, we must first pinpoint this pub-less point. One method we thought of involves drawing a circle around every pub in the country. If we could then adjust the size of these circles by changing their radius, there must be a value at which a single small area of England is left untouched. We just needed a way to find that precise value.
Source: GIPHY
With this in mind, we turned to a bisection algorithm. It’s like a mathematical take on trial and error, iteratively approaching an exact, unknown value. Here’s how it works:
- Create trade areas around all pubs on the map.
- Cut these trade areas out of England’s boundary.
- Decide the next step:
- If there is a single cut-out section with an area < 1 square mile, we’ve found our spot - exit the loop!
- If there are no cut-outs, the trade area radius is too large - decrease it.
- If there are multiple cut-outs or the area is > 1 square mile, the radius is too small—increase it.
Hopefully, that all sounds straightforward enough. But you’re probably wondering: How do we pick the starting radius? And how much do we adjust it each time? Great questions, and we’ve got answers!
Starting Radius: Covering All of England
To make this dynamic, we set the initial radius to the perimeter of England itself - about 2,600 miles according to our map. This was maybe overkill, but we wanted to make sure we weren't going to miss anything. If you want to reduce the runtime, pick a smaller value for this upper bound! Just make sure it’s still larger than the true answer.
Adjusting the Radius: Halving Our Way to Precision
Our rule is simple:
- On the first iteration, your adjustment value is half the starting radius (calculated above).
- For all other iterations, half the adjustment value from the previous iteration.
For example:
- Start with a radius of 10 miles.
- Iteration 1: Adjust by 5 miles (radius = 5).
- Iteration 2: Adjust by 2.5 miles (radius = 2.5 or 7.5, depending on direction).
- Iteration 3: Adjust by 1.25 miles, and so on.
Whilst not identical, the bisection algorithm is similar to a binary search. Please see the gif below to help illustrate the power of these methods, compared to a basic sequential search:
Source: mathwarehouse.com
This approach converges logarithmically (think inverse exponential), getting us very close to the answer in just a few steps. In our case, starting at ~2,600 miles, we nailed a spot within ~ 0.1 miles of the true “pub-less” point after just 15 iterations. That’s the power of a bisection algorithm.
Precision
We ran the algorithm as explained above, but we decided that the output area was not quite small enough. We could have adjusted the minimum output area and left the macro running for longer, but instead, we just used the spatial info tool to find the centre of our output object.
(Coordinates = 55.4794926494675, -2.19591365338952)
Real-World Applications (Beyond Pubs)
Source: Tenor
Whilst I hope you all agree that our efforts are truly for the most honorable cause, this workflow has potential for other scenarios:
- Retail Expansion: Already got some shops across the country? Find the spot furthest from your current locations to plan your next shop.
- Infrastructure Planning: Planning a new broadcasting tower? Pinpoint the area with the least coverage to maximize impact.
The beauty of our macro is its flexibility—just plug in a spatial object (like a country or region) and a list of at least two points (like shops or tower locations). Each use case might need a tweak or two, but the core logic holds. Please feel free to download the macro, play with your own data, and let us know your creative applications below!
Fixing the “Worst” Place: Our Three-Step Plan
For those of you who have read this far, you are likely wondering, how did we actually bring joy to this pub-less wasteland?
Whilst we cannot claim to have some sort of magical elixir of joy to sprinkle on this spot, we generally find that all you need is friendship, some good laughs (and maybe even a beer). We’ll let you guys judge our success, but here’s our solution:
- Locate the Spot: Using Alteryx, we pinpointed coordinates.
- Make the Hike: We woke up bright and early, fed the coordinates from Alteryx into What3Words, and journeyed to this exact location:
- Raise a Glass: Finally, to earn my Community name (coined by @mceleavey two years ago), we Gripped one Enormous can of Pilsner, and Consumed. (or a fruity Orange Crush for @TheOC)
Lessons
First and foremost, our mission was a success. Alteryx had helped us to bring joy to what we labelled the “Worst Place in England”. However, after speaking to the friendly locals, being taken aback by the natural beauty, and having more than one close call with the wildlife, I can say with certainty that our memories of this place are strong. If it's not too soppy, we would like to conclude that the “Worst Place in England”, given the right circumstances, is also one of the best.
*Disclaimer – I do not actually believe that a location's value should be solely assessed on its proximity to a pub. @TheOC is yet to confirm his thoughts.
