Run Postcodes.io Database Locally

You can run the postcodes.io dataset locally with our pre-seeded database image and query it with plain SQL. This guide starts the database container, connects with psql and works through four example queries.

The image ships a pg_dump of the latest ONS Postcode Directory (ONSPD), OS Open Names and Scottish Postcode Directory data, loaded into the public schema as four tables: postcodes, outcodes, places and scottish_postcodes. It is rebuilt each time the upstream data refreshes. Full details, including the API container and its configuration, are in the self-hosting documentation.

Requirements

1. Docker
2. psql, the PostgreSQL command line client

Start the Database Container

The image is built on postgis/postgis and needs the standard Postgres credentials. It exits on start without a password.

docker run -d -p 5432:5432 \
  -e POSTGRES_USER=postcodesio \
  -e POSTGRES_DB=postcodesiodb \
  -e POSTGRES_PASSWORD=password \
  idealpostcodes/postcodes.io.db

On first start the container enables PostGIS and restores the dump. Allow 1 to 3 minutes depending on hardware. Later starts skip the restore.

If you also want the HTTP API, clone the postcodes.io repository and run docker-compose up -d. That brings up the database and API containers together, with the API on port 8000.

Connect with psql

$ psql -h localhost --username postcodesio postcodesiodb

Enter password when prompted, or set PGPASSWORD=password in your environment to skip the prompt.

Hints

• \dt lists the tables in the current database
• \d postcodes prints the schema for the postcodes table
• Use the -c "SQL STATEMENT" flag to run a single statement from the terminal, e.g. psql -h localhost --username postcodesio postcodesiodb -c "SELECT * FROM postcodes LIMIT 1"
• Postcodes are stored with their space (SW1A 2AA). Compare on replace(postcode, ' ', '') to match user input in any format
• Terminated postcodes are included. Filter on date_of_termination IS NULL for live postcodes only

Examples

Get all datapoints for a postcode

SELECT * FROM postcodes WHERE replace(postcode, ' ', '') = 'SW1A2AA';

Get all datapoints within a radius

Get all postcodes within 1000m of longitude -2.4535, latitude 53.100918, ordered by the computed distance in metres. The location column is a PostGIS geography.

SELECT
  postcodes.*,
  ST_Distance(
    location,
    ST_GeographyFromText('POINT(-2.4535 53.100918)')
  ) AS distance
FROM
  postcodes
WHERE
  ST_DWithin(
    location,
    ST_GeographyFromText('POINT(-2.4535 53.100918)'),
    1000
  )
ORDER BY
  distance ASC

Write Query Result as CSV to STDOUT

Parish is a column on postcodes, so no join is needed.

COPY (
  SELECT
    postcode, longitude, latitude, parish
  FROM postcodes
  WHERE
    postcode ~ '^CH'
    OR postcode ~ '^LL'
) TO STDOUT DELIMITER ',' CSV

Stream CSV Output to output.csv

$ psql -h localhost \
  --username postcodesio \
  postcodesiodb \
  -c "COPY (SELECT postcode, longitude, latitude, parish FROM postcodes WHERE postcode ~ '^CH' OR postcode ~ '^LL') TO STDOUT DELIMITER ',' CSV" \
  > output.csv