Loading Codepoint into PostgreSQL / PostGIS

Ordnance Survey GB supply the CodePoint with Polygons dataset as a set of ESRI Shapefiles, sometimes it is useful to load them up into a PostgreSQL table for use with MapServer, GeoServer and/or QuantumGIS.

Requirements

PostgeSQL 9.3 (may work with 9.1, 9.2)
PostGIS (2.1.x)
GDAL (1.10.x)

Create a database and table

createdb ordnancesurvey
psql ordnancesurvey

CREATE TABLE codepoint
(
  ogc_fid integer NOT NULL DEFAULT nextval('fk_ogc_fid_seq'::regclass),
  postcode character varying(8),
  upp character varying(20),
  pc_area character varying(2),
  geom geometry,
  CONSTRAINT codepoint_pkey PRIMARY KEY (ogc_fid)
)
WITH (
  OIDS=FALSE
);
\q

Import the data

To get these into a single PostgreSQL PostGIS table first place all the shape files into a single folder (codepoint_oct2013 in the example).

ogr2ogr -append -f PostgreSQL "PG:dbname=ordnancesurvey" codepoint_oct2013 -lco GEOM_TYPE=geometry -lco GEOMETRY_NAME=geom -nln public.codepoint

Set Spatial Reference to British National Grid

psql ordnancesurvey

SELECT UpdateGeometrySRID('codepoint', 'geom', 27700);

\q

Add Indexes

For performance you should add indexes for the columns you are likely to query, and a spatial index if you are going to do any spatial searches.

psql ordnancesurvey

CREATE INDEX codepoint_postcode_idx ON codepoint USING btree (postcode COLLATE pg_catalog."default");

CREATE INDEX codepoint_geom_idx

  ON codepoint

  USING gist

  (geom);

\q