GB Postcodes

The server importing instructions allow optionally download gb_postcode_data.sql.gz. This document explains how the file got created.

GB vs UK

GB (Great Britain) is more correct as the Ordnance Survey dataset doesn't contain postcodes from Northern Ireland.

Importing separately after the initial import

If you forgot to download the file, or have a new version, you can import it separately:

  1. Import the downloaded gb_postcode_data.sql.gz file.

  2. Run the SQL query SELECT count(getorcreate_postcode_id(postcode)) FROM gb_postcode;. This will update the search index.

  3. Run utils/setup.php --calculate-postcodes from the build directory. This will copy data form the gb_postcode table to the location_postcodes table.

Converting Code-Point Open data

  1. Download from Code-Point® Open. It requires an email address where a download link will be send to.

  2. unzip

    Unpacked you'll see a directory of CSV files.

    $ more codepo_gb/Data/CSV/n.csv
    "N1 0AA",10,530626,183961,"E92000001","E19000003","E18000007","","E09000019","E05000368"
    "N1 0AB",10,530559,183978,"E92000001","E19000003","E18000007","","E09000019","E05000368"

    The coordinates are "Northings" and "Eastings" in OSGB 1936 projection. They can be projected to WGS84 like this

    SELECT ST_AsText(ST_Transform(ST_SetSRID('POINT(530626 183961)'::geometry,27700), 4326));
    POINT(-0.117872733220225 51.5394424719303)

    -0.117872733220225 51.5394424719303 on OSM map

  3. Create database, import CSV files, add geometry column, dump into file

    createdb $DBNAME
    echo 'CREATE EXTENSION postgis' | psql $DBNAME
    cat data/gb_postcode_table.sql | psql $DBNAME      
    cat codepo_gb/Data/CSV/*.csv | ./data-sources/gb-postcodes/convert_codepoint.php | psql $DBNAME
    cat codepo_gb/Doc/licence.txt | iconv -f iso-8859-1 -t utf-8 | dos2unix | sed 's/^/-- /g' > gb_postcode_data.sql
    pg_dump -a -t gb_postcode $DBNAME | grep -v '^--' >> gb_postcode_data.sql
    gzip -9 -f gb_postcode_data.sql
    ls -lah gb_postcode_data.*
    # dropdb $DBNAME