Visit Matillion AI Playground at Snowflake Data Cloud Summit 24

Find out more

Custom Connector for Public Statistics (Police API)

Author of this blog, Phillip Bell, Commercial Senior Sales Engineer at Matillion was asked a little while ago if Matillion could work with the UK Police API:

There are many organizations that have good reasons to look at crime statistics. In this case, a Housing Authority has a responsibility to the locations where they have property.  When you build/rent social housing, you need to make sure your tenants are not an issue for other residents or vice versa and that they are not adversely affected by other people and factors in the immediate area.  They might be interested in crime, air pollution, noise, etc.., as they may be required to take action to their properties or the surrounding location.

Hence, the Police API.  How easily can Matillion get the data from the Police API and gain something useful from it?

Using the UK Police API

Take a look at the following demonstration, throughout which you will find plenty of practical steps for setting up components, manipulating and validating data, creating and scheduling jobs, and finally, analyzing and using the extracted data for further applications.


The video showed a data engineer demonstrating how to interact with the UK Police API to retrieve and process street level crime data using the Matillion Data Productivity Cloud.

Here are the technical highlights:

API Configuration

A demonstration of the API's requirement for parameters like date, latitude, and longitude. How to construct an API endpoint URL dynamically based on these parameters.

Data Loading and Transformation

Data locations with corresponding latitude and longitude were preloaded into a Snowflake table. How to fetch the date from another API, and extract the necessary components (year and month) to match the required format.

Pipelines and Error Handling

Configuring Matillion to handle API interactions and potential errors. The possibility of using error paths to send messages to a webhook site in case of failures.

API Data Extraction and Loading

How to invoke the street-level crimes API using the preformatted date and location data. Note how the API response includes nested data (like crime categories and location details), which are flattened and processed using data transformation components.

Data Aggregation and Analysis

Performing data aggregation, grouping by street name, month, and crime category. This aggregated data is used to count instances of different types of crimes per street and per month. Noting how this can help analyze patterns, such as the prevalence of certain crimes in specific locations during different times.

Application and Insights

How could the resulting data be used to analyze correlations between crime rates and locations such as nightclubs, plus other factors like air pollution, providing another layer of insight?

Leveraging the UK Police API through tools like Matillion opens up a wealth of possibilities for organizations seeking to enhance their understanding of crime patterns and their impact on local communities. Organizations like housing authorities can make more informed decisions about property management and community welfare by efficiently retrieving and processing street-level crime data through the demonstrated techniques. This capability not only improves operational effectiveness but also fosters a proactive approach toward addressing social and environmental challenges, ultimately contributing to safer and more livable neighborhoods. 

Phillip Bell
Phillip Bell

Commercial Senior Sales Engineer