Run Postcodes.io Database Locally

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