Open Postcode Geo

Description

British postcodes with easting, northing, latitude, and longitude.

With additional fields for geospace applications, including postcode area, postcode district, postcode sector, incode, and outcode.

Available as one single table in .csv or a MySQL dump.

The same data is available as an API here.

Licence

Free to use for any purpose - attribution required.

Open Postcode Geo is derived from the ONS Postcode Directory which is licenced under the Open Government Licence and the Ordnance Survey OpenData Licence. Northern Irish postcodes have been removed as these are covered by a more restrictive licence. You may use the additional fields provided by GetTheData without restriction.

For details of the required attribution statements see the ONS Licences page.

Download

Version

The current version of Open Postcode Geo is derived from the February 2017 ONS Postcode Directory release.

Fields

Field Possible Values Comments
postcode [outcode][space][incode] Standard UK postcode.
status live
terminated
Terminated postcodes are no longer in use.
usertype small
large
Large usertypes may not be geographic.
easting int
NULL
northing int
NULL
positional_quality_indicator int See Positional Quality Indicator below
country England
Wales
Scotland
Northern Ireland
Channel Islands
Isle of Man
latitude decimal
longitude decimal
postcode_no_space [outcode][incode] Postcode with space removed.
postcode_fixed_width_seven See comments Outcode and incode separated by zero, one, or two spaces. Incode right aligned. Always seven characters in total.
postcode_fixed_width_eight See comments Outcode and incode separated by one, two or three spaces. Incode right aligned. Always eight characters in total.
postcode_area [A-Z]{1,2} One or two letters.
postcode_district [outcode]
postcode_sector [outcode][space][number]
outcode [outcode]
incode [incode]

Positional Quality Indicator

Shows the status of the assigned grid reference.

  1. Within the building of the matched address closest to the postcode mean.
  2. As for status value 1, except by visual inspection of Landline maps (Scotland only).
  3. Approximate to within 50 metres.
  4. Postcode unit mean (mean of matched addresses with the same postcode, but not snapped to a building).
  5. Imputed by ONS, by reference to surrounding postcode grid references.
  6. Postcode sector mean, (mainly PO Boxes).
  7. Not used.
  8. Postcode terminated prior to Gridlink® initiative, last known ONS postcode grid reference.
  9. No grid reference available.

MySQL

The MySQL dump file consists of a single table called open_postcode_geo.

Four basic indexes are provided to facilitate postcode lookups using any of the four postcode formats:

Install the table from the command line with the below syntax:

mysql -u <user> -p <database> < open_postcode_geo.sql

Example queries and results:

mysql>  select easting, northing from open_postcode_geo where postcode = 'WR3 8AS';
+---------+----------+
| easting | northing |
+---------+----------+
|  384785 |   256819 |
+---------+----------+

mysql> select postcode_district from open_postcode_geo where postcode_fixed_width_eight = 'M1   1AA';
+-------------------+
| postcode_district |
+-------------------+
| M1                |
+-------------------+

mysql> select latitude, longitude from open_postcode_geo where postcode_no_space = 'RG46SU';
+----------+-----------+
| latitude | longitude |
+----------+-----------+
|  51.4709 | -0.918018 |
+----------+-----------+

mysql> select postcode_district from open_postcode_geo where postcode_no_space = 'WC1A1AA';
+-------------------+
| postcode_district |
+-------------------+
| WC1A              |
+-------------------+
        

Find the nearest...

As all mainland British postcodes (but not Northern Irish postcodes) use the same British National Grid, you can simply use Pythagoras' theorem to find the nearest postcodes to a given easting and northing.

For example, if you wanted to find the nearest postcodes to The Tower of London you would first get the easting and northing from the postcode (EC3N 4AB):

mysql> select easting, northing from open_postcode_geo where postcode = 'EC3N 4AB';
+---------+----------+
| easting | northing |
+---------+----------+
|  533668 |   180551 |
+---------+----------+
        

Then use the below query to get the ten nearest postcodes and their distances in metres:

mysql> select postcode, sqrt(pow(abs(533668 - easting),2) + pow(abs(180551 - northing),2)) as distance from open_postcode_geo where easting is not null and northing is not null order by distance limit 10;
+----------+--------------------+
| postcode | distance           |
+----------+--------------------+
| EC3N 4AB |                  0 |
| EC3N 4AE |   68.6804193347711 |
| EC3N 4AQ |   85.7962703152066 |
| EC3N 4AD |  99.98499887483122 |
| E1W 1AY  | 111.16204388189342 |
| E1W 1UN  | 111.16204388189342 |
| E1W 1UU  | 111.16204388189342 |
| E1W 1UY  | 111.16204388189342 |
| E1W 1XA  | 111.16204388189342 |
| E1W 1XB  | 111.16204388189342 |
+----------+--------------------+
10 rows in set (2.49 sec)
        

You will see this query is quite slow, it includes terminated postcodes, and it includes the original postcode (EC3N 4AB). To speed it up add an index:

mysql> alter table open_postcode_geo add index(status, easting, northing);
        

Then modify the query to select only live postcodes, within a 1000m x 1000m bounding box, excluding the original postcode:

mysql> select postcode, sqrt(pow(abs(533668 - easting),2) + pow(abs(180551 - northing),2)) as distance from open_postcode_geo where status = 'live' and easting is not null and northing is not null and easting between 533668 - 500 and 533668 + 500 and northing between 180551 - 500 and 180551 + 500 and postcode != 'EC3N 4AB' order by distance limit 10;
+----------+--------------------+
| postcode | distance           |
+----------+--------------------+
| EC3N 4AE |   68.6804193347711 |
| E1W 1AY  | 111.16204388189342 |
| E1W 1UN  | 111.16204388189342 |
| E1W 1XB  | 111.16204388189342 |
| E1W 1XF  | 111.16204388189342 |
| E1W 1YL  | 111.16204388189342 |
| E1W 1LE  | 125.25174649480941 |
| E1W 1DD  |  135.5949851580065 |
| E1W 1BA  |  161.5951731952412 |
| EC3N 4DR |   196.468827043885 |
+----------+--------------------+
10 rows in set (0.02 sec)
        

We use this exact query on our OX postcode pages to show nearby postcodes. For example, on the page for OX1 3BG you can see the ten nearest postcodes to Oxford's Bodleian Library.