Mapping UK Street Crime

An open-source end-to-end spatial analytics platform to visualise UK street crime data using PostgreSQL/PostGIS on a Raspberry Pi.

Built an end-to-end spatial analytics platform to visualise UK street crime data: ingesting datasets into a PostgreSQL/PostGIS database on a Raspberry Pi via Docker, performing spatial queries, and producing interactive crime maps. Read more in the blog posts:

Screenshots of the application displaying mapped UK street crime data. Visit it here.

Project description

This project implements an open-source end-to-end spatial analytics platform for exploring UK street crime data. It combines geospatial data ingestion, storage, querying, and visualisation using a PostgreSQL/PostGIS database and a web application (built with Streamlit) for interactive exploration.

The workflow begins with data acquisition and preparation. Open UK street crime datasets are downloaded and inspected. Crime records include timestamps and geographic coordinates (latitude and longitude), enabling spatial analysis when stored in a geospatial database.

To support spatial queries, a PostgreSQL database with the PostGIS extension is deployed locally using Docker on a Raspberry Pi. The setup involves:

  • Using a Docker image compatible with the Raspberry Pi’s ARM architecture.
  • Installing PostgreSQL and PostGIS in a container.
  • Configuring the database to accept geospatial data and enable spatial indexing.

Crime data is imported into the PostGIS database with appropriate geographic columns converted into GEOMETRY types. Indexes (e.g. GIST) are created to accelerate spatial queries on large datasets.

Once the data is stored and indexed, a series of spatial queries are constructed to analyse crime patterns, including proximity queries, clustering, and filtering by time or location. These queries leverage PostGIS functions and ensure efficient performance even on modest hardware.

The final stage involves visualisation. Query results are exported and displayed using mapping tools capable of rendering geographic data (e.g. leaflet, Mapbox, or similar). Generated maps illustrate crime distributions across regions and can highlight hotspots or temporal variations in crime intensity.

The app offers three crime maps:

  • Clickable Crime Map:
    • You can click on the map to view street-level crime data near the area with filters by date or type of crime.
  • Postcode Crime Map:
    • You can enter a postcode to view street-level crime data near that postcode with filters by date or type of crime.
  • Area Crime Map
    • You can create an area on the map to view street-level crime data in that area with filters by date or type of crime.

The app also supports natural-language queries via an integrated large language model, enabling users to ask questions about the crime database in plain English (e.g., “”How many burglaries were there within two kilometres of Manchester city centre last month?). The system translates these queries into SQL and executes them against the PostGIS backend.

The two blog posts cover both the infrastructure and analytics components:

This project illustrates how to build a lightweight yet fully functional geospatial data platform on edge hardware, handling end-to-end spatial data workflows from storage to visualisation. It is applicable to urban analytics, public safety dashboards, and any context requiring scalable spatial querying without reliance on cloud services.

The full project is open source and available on GitHub, with a public web app for anyone to explore the data (the public webapp does not use the local PostgreSQL database).