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

Apache and Upgrading Ubuntu Server LTS

Now that Ubuntu 14.04 LTS is available it's time to think about upgrading all those 12.04 servers.  For me that meant updating my home file server and development server, not exactly mission critical, so I plowed straight in to see what would get broken.

Largely the update went without fault, but before anything make sure you backup any important files.  To get it started I changed the /etc/update-manager/release-upgrades file from :-

Prompt: LTS

to :-

Prompt: normal

This step might not be necessary by now, but can be used safely if you know you are going from one LTS to another.  Once you get the the final LTS version you can set it back to Prompt: LTS

Next, update the list of available packages and install any updates.

sudo apt-get update

sudo apt-get dist-upgrade 

It's probably wise to reboot the machine at this point and check that all is well before continuing.  Once you're happy to continue start the release update.

sudo do-release-upgrade

Follow the prompts and carefully consider any prompts about replacing configs files, you can always say no, then fix the config by comparing your config with config.dpkg files.  I usually keep a list of all the files that had problems during the upgrade.

Once the upgrade completes you ought to reboot.  Repeat the do-release-upgrade process until you are at the 14.04 LTS version.

 Notable changes

- Apache Web Server, now at version 2.4.

There are some changes to the allowed config files for Apache, for me this manifested itself in a virtual host that no longer was available.  Where previously any file in /etc/apache2/sites-enabled would be parsed, it now only looks for and parses files with .conf extensions.  In addition the options to control access by ip have changes.  Instead of Order Allow, Deny and Allow from xxx.xxx.xxx.xxx/yy you need to use the Require syntax.

- PostgreSQL, from 9.1 to 9.3

As with any update to PostgreSQL you will need to run pg_upgradecluster after dropping the new default cluster with pg_dropcluster --stop 9.3 main; pg_upgradecluster 9.1 main
If you have any databases that use the postgis extension then I suggest making a backup first, dropping the database from 9.1, upgrading the cluster then restoring it to 9.3 after ensuring that postgis is installed and available.

Edit: Further investigation on my postgres migration showed that many tables were missing data.  Double check that upgradecluster completed successfully.  If in any doubt you probably should just do a full pg_backup and pg_restore.