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 19 Feb 2021

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 the 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';

Get all datapoints within a radius

Get all datapoints within a 1000m radius of geolocation -2.4535,53.100918 ordered by the computed distance

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

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