Open Nav

Run Postcodes.io Database Locally

Run a local instance of the latest Postcodes.io database and start dispatching SQL queries in minutes

Updated 18 Aug 2020

You can quickly set up the postcodes.io dataset using our Docker application and database images.

Requirements

  1. Docker
  2. psql - a command line interface for PostgreSQL

Start the Database Container

In your terminal, start the postcodes.io.db stateful docker container

docker run -d -p 5432:5432 idealpostcodes/postcodes.io.db

You may need to wait up to a minute for the data to be ingested and postgresql to be available.

Connect with psql

psql -h 0.0.0.0 --username postgres postgres

This opens a terminal based frontend into the postcodes.io database

  • Configure the host to be 0.0.0.0
  • Login with the default be postgres by running
  • By default, psql will connect to the postgres database

Hints

  • \d prints all tables in current database
  • \d postcodes prints schema for postcodes table
  • Use the -c "SQL STATEMENT" flag to dispatch a command from terminal. E.g. psql -h 0.0.0.0 --username postgres postgres -c "SELECT * FROM postcodes LIMIT 1"

Examples

Get all datapoints for a postcode

SELECT * FROM postcodes WHERE pc_compact = 'SW1A2AA';

Write Query Result as CSV to STDOUT

COPY (
  SELECT 
    postcode, longitude, latitude, parishes.name 
  FROM postcodes 
  RIGHT OUTER JOIN parishes 
  ON postcodes.parish_id=parishes.code 
  WHERE 
    pc_compact~'^CH' 
    OR pc_compact~'^LL'
) to STDOUT DELIMITER ',' CSV

Stream CSV Output to output.csv

psql -h 0.0.0.0 \
  --username postgres \
  postgres \
  -c "COPY (SELECT postcode, longitude, latitude, parishes.name FROM postcodes RIGHT OUTER JOIN parishes ON postcodes.parish_id=parishes.code WHERE pc_compact~'^CH' OR pc_compact~'^LL') to STDOUT DELIMITER ',' CSV" \
  > output.csv