## Controlling space in football – data modelling with our analytics database, and visual analysis in Tableau

Football isn’t just about controlling the ball – in fact a player only has contact with the ball for an average of two minutes per game – it’s also about controlling space.

Attackers try to create space for themselves and their attacking manoeuvres while defenders work to narrow that space as much as possible to prevent goal-scoring opportunities.

We can easily look at video footage to analyse certain events, player movements and positioning but from a visual perspective, all the additional cues we get from the real-life images can be distracting when we’re trying to focus on specific aspects of the game.

To reduce the complexity of real-life images we can create visualisations from data – and while this removes some details it allows us to pay attention to things like the amount of space being controlled by any one player.

An effective visualisation of such data comes in the form of a Voronoi chart, a diagram which presents the space around each player as a polygon with the borders representing the mid-point between one player and all other players around him or her, at each point in time.

Here’s an example of a Voronoi chart. It can be animated to show changes over time:

The data required for this involves tracking data, which is typically recorded at a rate from 10 to 30 data points per player, per second.

In this blog post we’ll show you how to model the data in preparation for building the diagram. We’ll then go on to demonstrate how you can create the Voronoi chart in Tableau.

## Part 1: Data modeling

The best approach for analysing and visualising data, particularly (complex) spatial data, is to get the data modeling right – this reduces the effort required during analysis and visualisation significantly. Why? Because rather than building complex and advanced formulae to create a diagram, the data is already in the right shape to visualise quickly and effectively.

### Polygon requirements of Tableau

But before starting to transform the data, you have to understand, which data format Tableau requires to visualize a polygon. Each vertex (corner) of the polygon has to be represented in a single row. An ordering number defines the path between the vertices, so that Tableau knows how to draw the lines. To identify, which vertex belongs to which polygon, Tableau also needs a group identifier. For this showcase, this will be the individual player, as we want to create a space polygon for each player.

### Original data

The original data contains the position information for each player and the ball per match frame. The data source we used provides a frequence of 25 frames per second. So a complete football match includes about 140k to 150k frames and over 3 million data points.

To rebuild our example on your own, we provide an anonymised data sample with 1000 frames of position data at our GitHub repository.

### Calculate Voronoi polygons

Calculating the Voronoi polygons for each frame can easily be done with Python. The SciPy library offers, among a huge amount of other scientific and mathematical packages, the library Voronoi to generate such polygons. We added a small Python script to our GitHub, which provides an example for one single frame.

The plot functionality of Python is used to graphically show the intermediate results of the script.

[code language="Python"] # plot points & pitch x, y = pitch.T plt.plot(*points.T, 'b.') #Show plot plt.show() [/code]

These are the different coordinates for each player in the first frame of the match. And that’s also the only input you need for the Voronoi-function to calculate the different Voronoi regions. The Voronoi_plot_2d-function is used to plot the resulting Voronoi diagram.

[code language="Python"] #calculate simple voronoi to plot vor = Voronoi(points) #plot voronoi voronoi_plot_2d(vor) plt.show() [/code]

The graphic shows the biggest challenge when adapting Voronoi diagrams for football. The inner Voronoi regions already look like complete polygons with the information for each point of the polygon – and this is exactly what’s needed for the Tableau visualization. But the outer regions are infinite. These should be limited by the pitch size. Fortunately, the great Stack Overflow community provides a solution for that problem, which we just re-used.

[code language="Python"] # create boundary polygon based on pitch size boundary_polygon = Polygon(pitch) #create diameter to handle infinite voronoi regions diameter = np.linalg.norm(pitch.ptp(axis=0)) … calculate voronoi polygons & loop for p in voronoi_polygons(Voronoi(points), diameter): # build intersection with pitch boundary polygon x, y = zip(*p.intersection(boundary_polygon).exterior.coords) … [/code]

Based on the pitch size, a boundary polygon is defined which represents the limit for our outer Voronoi regions. The re-used voronoi_polygons-function returns all Voronoi polygons. The diameter is used to create polygons for the infinite Voronoi regions with a size large enough to build the intersection with our pitch. For the final result, we got multiple Voronoi polygons, which are perfect for our pitch size.

### Voronoi UDF – bringing the calculation logic to your data

The Python script allows us to calculate the Voronoi polygons for one single frame. We could now load the whole position data, process it in Python and write it back to the database. But it’s way more efficient to bring the calculation logic to your data instead. This is where you can use the User Defined Function (UDF) framework of our analytics database. It gives you the possibility to execute any programming language inside the database with the help of a scripting language docker container.

If you’re not yet using an Exasol analytics database, but want to test this functionality, we advise you to just download the Exasol community edition. It’s free and gives you a chance to see it in action with your own data.Please complete the following steps to use the Voronoi UDF in the Exasol Community Edition:

- Add the Python language container with all packages you need to the database
- Import the sample data into the database
- Create the Voronoi UDF

For a test you could also calculate the polygons for the first frame of the sample data:

[code language="SQL"] select voronoi_showcase.voronoi_polygons(match_id, team_id, person_id, frame_id, frame_x, frame_y, 105, 68) from voronoi_showcase.MATCH_POSITION where frame_id = 10000 and team_id <> 'BALL' group by match_id, frame_id order by match_id, frame_id, person_id; [/code]

The resulting coordinates for the single polygon vertices should be the same as in the Python script.

### Data transformation

The final data transformation step combines the position data and the voronoi polygon coordinates. As the visualisation showed, both types of coordinates are independent facts. Therefore, you shouldn’t join the data. Otherwise coordinates get multiplied. Just use a UNION to combine both coordinate types.

At GitHub you’ll find the corresponding script to create the MATCH_VORONOI table. This table will be the data source for your Tableau report.

### Frame rate reduction

During our tests we recognized that 25 frames per second are too much for a smooth visualization in Tableau. Everything looked like it was filmed in super slow-mo. So we reduced the frames per second by filtering frames.

The following filters can be applied in our analytics database, or in Tableau, to help the animation look smoother :

Frames per second | Exasol filter | Tableau filter |
---|---|---|

12 | FRAME_ID mod 2 = 0 | FRAME_ID % 2 = 0 |

9 | FRAME_ID mod 3 = 0 | FRAME_ID % 3 = 0 |

6 | FRAME_ID mod 4 = 0 | FRAME_ID % 4 = 0 |

Hint: we used 9 frames per second in our viz 😉

### Let’s take a look at the architecture

Now let’s take a step back from the data processing details and take a look at the overall solution. The data source we used for our showcase, which we’re unfortunately not allowed to share, provides XML files for the tracking data of single football matches. To parse and publish the data into the database we again use a Python UDF. The persistent position data is the source for our Voronoi Python UDF and both get combined and published in the reporting layer. The reporting layer is the access point for the Tableau users. The usage of the User Defined Function framework of our analytics database not only enables you to build a slim architecture, it helps you better prepare the data for even easier visualizations – by extending the data processing with non-standard-SQL functionality.

## Part 2: Building the Voronoi diagram

Now that we have all the data in the right format and in one place, building the Voronoi chart is actually very simple.

We’ll split the process into three parts: setting up the background image, building the Voronoi chart, animating the chart.

### Setting up the background image

We’re looking at football data and that means we need a football pitch as our background image to make the visualisation useful – and to make the spatial aspect meaningful.

You will need the outline of a football pitch as a jpeg or png file. Then you’re ready to start.

- In Tableau in a new sheet go to
**Map → Background Images**→**<data source name>**

2. Select **Add Image**… and choose the image file from a folder or URL

3. Edit the details as follows to construct the playing field with player locations from your data:

**a)** Your **X-Field** comes from the field** Voronoi X**’, select that from the drop-down menu.

**b)** The** X-Field** ranges from -55.5 on the left to 55.5 on the right to give you the right size for the pitch in alignment with the data.

**c)** These are again the pitch size values of the tracking data source, which were already used during the Voronoi calculation.

**d)** Your** Y-Field** is ‘**Vornoi Y**’.

**e)** The** Y-Field** ranges from -34 on the left to 34 on the right.

**f)** Adjust the level of washout to suit your visualisation.

**g)** The end result should look like this:

4. Click OK. You won’t see the playing field until you add data.

### Create the Visualization

- Add data:

**a)** Drag **Voronoi X** to columns

**b)** Drag **Voronoi Y** to rows

**c)** Go to Analysis and uncheck ‘**Aggregate Measures’**

**d)** Your result will look something like this

2. Drag **Team ID** to color, so you can distinguish the different teams.

3. Now bring in your players

**a)** Bring your field** Frame X** to columns and

**b) Frame Y** to rows

**c)** For both columns and rows create a dual-axis

**d)** Change the **mark type** to circle

**e)** Your viz will look like this:

4. Build the actual Voronoi chart:

**a)** Add **Frame ID** to the pages shelf

**b)** Change the** Voronoi marks type** to **Polygons**

**c)** Add **Person ID** to the **Details card** for the polygons

**d)** And the field ‘Voronoi Order’ to** Path**

**e)** Again with the **Polygon**, change the opacity to about 40% (depends a bit on your color choices). Add dark borders to your polygons

**f)** You should now have a viz that looks like this:

5. Format your viz

**a)** Hide the **headers** for both axes

**b)** Remove** zero lines, row and column dividers** for the whole sheet

**c)** Add a dark border around the **circle marks** (your players and the ball)

**d)** Format your **tooltips**

**e)** Add a title and a description

### Animate the visualisation

- Set the
**speed**to**level 3**(fastest) - Unselect
**‘Show History’**(if selected) - Hit
**Play**

These steps show that building the actual visualisation is straight-forward once the data is in place and in the right format.

We’d love to hear from you and see what visualisations you create following this tutorial. So feel free to tag us in your Tweets using @ExasolAG and #sportsanalytics.

### Controlling space in football – data modelling with our analytics database, and visual analysis in Tableau

Learn how to model your data in Exasol and create a Voronoi diagram in Tableau to analyse how space is controlled by different players during a game.

To download your free user guide, visit our landing page: