On the 26th July it was announced that the United Kingdom would be hosting 2023’s Eurovision Song Contest. No fewer than 13 cities are expected to bid for the honor of hosting the show, and a fierce competition is predicted as cities and venues aim to prove their suitability for the event. And - as is often the case at CARTO - we thought “we could make a map for that.”
So welcome to our run-down of the top 10 most suitable event venues in the UK for the Eurovision Song Contest! This was decided by the most ruthless judge of all: Spatial Data Science.
We’ve created a multi-criteria site suitability assessment of music venues across the UK. Following the announcement of the results (with - we promise - far less preamble than in the actual contest) we’ll show you how you can repeat this analysis for any site suitability assessment - even for those less glamorous occasions!
So, what makes for a successful Eurovision Song Contest venue? Like with so many events, a lot of it comes down to transport and logistics. If you can’t get the performers, audience members, staff and containers of glitter to the venue, then you don’t have a show.
For our analysis, we explored four key criteria for evaluating how suitable a venue is:
This list could easily be extended or altered depending on the focus of your site suitability assessment. Check out the Use Cases section of our blog for inspiration on suitability analysis and Site Selection for activities ranging from electric vehicles to targeted radio broadcasts.
We scored each venue against each of these indicators and combined these into a final overall score, which leads us to our top 10 venues…
Twelve points go to Principality Stadium! Capable of hosting enormous crowds of 74,500, it’s the third largest venue in the country (behind Wembley and Old Trafford). It’s got fantastic transport links, with Cardiff Central Station just over the road and Cardiff Airport only 8.9 miles (14 kilometers) away. It also boasts a city center location, allowing it to benefit from 49 hotels with a 15-minute walk.
Explore the top 10 on the map below! Play around with the different layers, dynamic widgets and bespoke popups made possible through our data visualization & analytics suite, CARTO Builder!
Open this in a full window here (recommended for mobile users).
How long do you think it took all this analysis to run? Hold on to your swivel chair/beanbag. Three seconds. That’s IT!
Thanks to the power of Spatial SQL and CARTO being cloud-native, we were able to perform all of this analysis in just three seconds, and in just ONE SQL query! Not only is SQL a quick and easy way of performing site suitability analysis, it’s incredibly iterative; you can add, take away and change variables and datasets and have your new results in just three seconds.
Ready to learn how? Make sure you sign up for CARTO’s 14-day free trial so you can analyze along with us!
The SQL code for this analysis is quite long because it wraps up a lot of different functions into just one query. However, it’s actually quite simple code and repeats a lot of the same functionality for the different variables we’re looking at - so don’t be put off! There are 5 key steps to this code:
Step 1: Load the requisite datasets. Here we’re using venues, hotels, stations and airports. Give each of them an alias; this makes things SO much easier than having to type (ok, let’s be honest - copy & paste) the full qualified name out each time.
Step 2: Count hotels within 1200m. Here we use a LEFT JOIN to join the hotels to the venues, using the spatial predicate (i.e. relationship) ST_DISTANCE() to specify this join should only occur where hotels are within 1200m of the venue. The COUNT() function is used to count where this join occurs, creating a field “hotels_count”. As this is an aggregate function, all other fields also must be aggregated, either using GROUP BY or ST_UNION_AGG() for geometries.
Step 3: Calculate distances to airports and stations. The MIN(ST_DISTANCE()) function is used to calculate the distance from the venue to the closest transport facilities, giving us the new fields “station_distance” and “airports_distance.”
Step 4: Min-max normalization. We have our four suitability indicators - hooray! To be able to combine these into one overall suitability score, we first need to normalize them so they are all comparable. We’ll be using a min-max method here, which gives the optimum venue a score of 1 (e.g. the venue with the greatest capacity) and the least suitable venue (e.g. the smallest) a score of 0. All other venues have decimal values between 0 and 1 relative to the range of the data. The basic syntax for this is “(Data value - range minimum)/data range.”
Step 5: And finally! That’s the hard part done! Now we just need to add those four individual scores together to give us an overall venue suitability score out of 4. We’ll also give each venue a rank so we can focus on just the top 10 for our visualization.
And here’s what that looks like!
/*Step 1: Load the requisite datasets*/
WITH stations AS (SELECT * FROM cartodb-gcp-marketing-team.eurovision2023.osm_stations),
airports AS (SELECT * FROM `carto-data.ac_lqe3zwgu.sub_natural_earth_geography_glo_airports_410` where gps_code LIKE 'EG%'),
venues AS (SELECT * FROM cartodb-gcp-marketing-team.eurovision2023.Music_venues where capacity > 10000),
hotels AS (SELECT * FROM cartodb-gcp-marketing-team.eurovision2023.safegraph_accommodation),
/*Step 2: Count hotels within 1200m*/
hotelcount AS(SELECT ST_UNION_AGG(venues.geom) AS geom, venues.name, venues.city, venues.capacity, venues.imagelink, venues.imagecredit, COUNT(hotels.geom) AS hotels_count FROM venues
LEFT JOIN hotels ON ST_DISTANCE(venues.geom, hotels.geom) < 1200
GROUP BY venues.name, venues.capacity, venues.city, venues.imagelink, venues.imagecredit),
/*Step 3: Calculate distances to airports and stations*/
distances AS(SELECT ST_UNION_AGG(venues.geom) AS geom, venues.name, venues.city, venues.capacity, hotelcount.hotels_count, hotelcount.imagelink, hotelcount.imagecredit,
ROUND(MIN(ST_DISTANCE(venues.geom, stations.geom)),0) AS station_distance, ROUND((MIN(ST_DISTANCE(venues.geom,airports.geom))/1000),2) AS airports_distance
FROM venues, stations, airports
LEFT JOIN hotelcount ON venues.name = hotelcount.name
GROUP BY venues.name, venues.capacity, venues.city, hotelcount.hotels_count, hotelcount.hotels_count, hotelcount.imagelink, hotelcount.imagecredit),
/*Step 4: Min-max normalization*/
minmax AS(SELECT *,
ROUND((distances.capacity-(SELECT MIN(distances.capacity) FROM distances))
/(SELECT MAX(distances.capacity)-MIN(distances.capacity) FROM distances),3) AS capacity_norm,
1-ROUND((distances.station_distance-(SELECT MIN(distances.station_distance) FROM distances))
/(SELECT MAX(distances.station_distance)-MIN(distances.station_distance) FROM distances),3) AS station_dist_norm,
1-ROUND((distances.airports_distance -(SELECT MIN(distances.airports_distance ) FROM distances))
/(SELECT MAX(distances.airports_distance )-MIN(distances.airports_distance ) from distances),3) AS airports_dist_norm,
ROUND((distances.hotels_count-(SELECT MIN(distances.hotels_count) FROM distances))
/*Step 5: And finally!*/
SELECT *,
ROUND(capacity_norm+station_dist_norm+airports_dist_norm+hotels_norm,3)AS overall_score,
RANK() OVER(ORDER BY capacity_norm+station_dist_norm+airports_dist_norm+hotels_norm desc) as RANK
FROM minmax ORDER BY overall_score DESC LIMIT 10
Now it’s time to visualize all your hard analytical efforts!
At CARTO, we always advise you to craft a visualization like you’re telling a story. Think about the journey your user will go on; what is the main question they want answering? Start with answering that. In this case, that’s “which is the most suitable venue for this event?” We help them quickly answer that by cutting out the data noise and just showing the 10 most suitable sites, colored and sized by their rank. This is aided by the use of a categorical widget which dynamically compares the scores of in-view venues.
The follow-up question will then probably be “and why is that venue so suitable?” That question can be answered by allowing users to toggle on/off the data which formed this analysis (stations, airports and hotels).
Additionally, considering the content of the popups for each layer is a great way to help users get the information they require. You can choose from a series of popup design options and select the variables you’d like to display, or do something custom with HTML. For example, our venues dataset includes a field called “imagelink” which can be used to display an image from the web using <img…>
(see below).
<div class="CDB-Popup CDB-Popup--highlightFirst">
<dl>
<div><dd>{{name}}</dd></div>
<div>
<dd><img width="200" src="{{imagelink}}"></dd>
<dd><small>{{imagecredit}}</small></dd>
</div>
<div><dt>Overall score</dt><dd>{{overall_score}}</dd></div>
<div><dt>Rank</dt><dd>{{rank}}</dd></div>
<div> Capacity: {{capacity}}</div>
<div>Hotels within 1200m: {{hotels_count}}</div>
<div>Closest station: {{station_distance}}m</div>
<div>Closest airport: {{airports_distance}}km</div>
</dl>
</div>
Find out more about creating custom interactions here.
We hope you enjoyed learning about how Spatial Data Science can be used to quickly and efficiently perform Site Suitability analysis! Not all assessments will be as glamorous as this one, but the concepts and methods are transferable. Interested in learning more about Spatial Data Science and logistics? Check out our guide to CARTO for logistics and the supply chain here.
What to try out CARTO for yourself?
Sign up for our free 14-day trialThe Great Resignation, The Big Quit or The Big Reshuffle? The employment landscape has been given many names since early 2021, but they all have one thing in common. They a...
Use CasesThe Circular Economy is a cornerstone of sustainability. It’s defined as a model of production and consumption where the lifecycle of materials and products is extended. Th...
Use CasesOne of the defining elements of the COVID epidemic has been how quickly the situation seems to change. One moment things feel positive with the number of infections slowing...
Use CasesPlease fill out the below form and we'll be in touch real soon.