Importing the National Statistics Postcode Directory into PostGIS
This isn’t a very difficult task, but creating the table is somewhat laborious, so perhaps you’ll find the following SQL helpful.
Step 1
The first step is to create an appropriate table, and pull everything in as character data.
create table nspd_2008_11 ( postcode_7 char(7), postcode_8 char(8), postcode_egif char(8), intro_date_string char(6), termination_date_string char(6), county char(2), la char(2), ward char(2), large_user char(1), grid_easting char(6), grid_northing char(7), grid_ref_quality char(1), health_authority char(3), pan_sha char(3), country char(3), non_geo char(1), in_paf char(1), go_region char(1), ssr char(1), parl_const char(3), eer char(2), tecr char(3), ttwa char(3), pct char(5), nuts char(10), ed_1991_ogss char(8), ed_1991 char(6), ed_quality char(1), address_count char(4), delivery_point_count char(4), multiple_occupancy_count char(4), small_business_count char(4), previous_sha char(3), lea char(3), ha char(3), ward_1991 char(6), ward_1991_ogss char(6), ward_1998 char(6), stat_ward_2005 char(6), oa char(10), oa_indicator char(1), cas_ward char(6), national_park char(2), lsoa char(9), scottish_dzone char(9), msoa char(9), urban_rural_ew char(1), urban_rural_scot char(1), urban_rural_ni char(1), scottish_izone char(9), soa_ni char(8), oa_class char(3), old_pct char(5) ); copy nspd_2008_11 from '/path/to/nspd/file.csv' csv; |
Step 2
Since we’re going to the trouble of bringing it into PostGIS, likelihood is we’re going to want to use the spatial data. So let’s create a new table to hold the point geometries.
Notes:
- If necessary, we can join this table with our original table, on a postcode column, in future queries. We’ll index the relevant postcode columns in each table (as primary keys) to speed this up.
- We include OIDS, even though they are deprecated, as an easy way to permit access to the data for visualisation using qGIS.
- We exclude postcodes with the quality code ‘9’, because this code means there is no grid reference available.
- We set the SRID of the point data to 27700, which is the appropriate value for the OSGB36 projection here.
create table nspd_2008_11_geometry with (oids) as ( select postcode_7, grid_ref_quality, st_setsrid(st_makepoint( cast (grid_easting as integer), cast (grid_northing as integer) ), 27700) as point from nspd_2008_11 where grid_ref_quality != '9' ); alter table nspd_2008_11 add constraint postcode_7_key primary key (postcode_7); alter table nspd_2008_11_geometry add constraint postcode_7_geometry_key primary key (postcode_7); vacuum analyze; |
Now we can connect to the geometry table from qGIS to see the rough shape of Britain in postcode points. In a future post, I may look at creating polygons to represent postcode sectors using the data we’ve just imported.
-
farouk