This isn’t a very difficult task, but creating the table is somewhat laborious, so perhaps you’ll find the following SQL helpful.
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;
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.
- 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.