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.
psql
- a command line interface for PostgreSQLIn 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.
psql
$ psql -h 0.0.0.0 --username postgres postgres
This opens a terminal based frontend into the postcodes.io database
host
to be 0.0.0.0
postgres
by runningpostgres
database\d
prints all tables in current database\d postcodes
prints the schema for postcodes table-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"
SELECT * FROM postcodes WHERE pc_compact = 'SW1A2AA';
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
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
$ 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