The UK government provides Land Registry Public Data, amongst which is the strangely-named INSPIRE Index Polygons. That webpage says that they are “to help you locate registered freehold land and property in England and Wales”, and provides 348 ZIP files to do this.

~$ unzip -l UK/data/Abertawe_-_Swansea.zip
Archive:  UK/data/Abertawe_-_Swansea.zip
  Length      Date    Time    Name
---------  ---------- -----   ----
    68938  2016-07-03 09:36   INSPIRE Download Licence.pdf
128987164  2016-07-03 09:36   Land_Registry_Cadastral_Parcels.gml
---------                     -------
129056102                     2 files

I’ve just wasted a week of my life getting these files into a 48GB PostGIS db, only to discover that there is zero information in there other than GIS data. In other words, there are lots & lots of geometric polygons (very useful) and zero description on those polygons.

I was hoping to be able to find Areas for Borough Boundaries, and stuff like that. Oh spit.

Here is a quick run-through on how I managed to do all this. I had zero experience beforehand. Christian Ledermann, a Polish chap that adopts the name cleder & came to the June Nottingham Pub meetup to demonstrate his UK School import utility told me: “I have a little script that downloads everything and puts it into a postgis db (and shapefiles as an intermediate step): https://github.com/cleder/uk-landregistry ”. Christian’s script is woefully short on documentation & details, whilst his UK schools utility would act as an efficient MITM attack vector on OSM (hoovering up usernames & passwords), and did not work for me.

The following has been achieved under a fully updated Debian 8.5. I actually used Synaptic for most installations, but that is GUI whilst I can more easily show apt-get instructions here. Look at the OSM Wiki for PostGIS Installation for more help.

1. Install PostGIS, PostgreSQL, GDAL, SubVersion + Fabric

    sudo apt-get install postgresql postgresql-contrib postgis gdal-bin
    sudo apt-get install subversion fabric
   

2. Create the postgis working directory

    mkdir -p uk/data
   

3. Get Christian’s uk-landregistry script, and extract into the working dir

    ~$ svn co https://github.com/cleder/uk-landregistry.git uk_landregistry
    A    uk_landregistry/branches
    A    uk_landregistry/trunk
    A    uk_landregistry/trunk/README.md
    A    uk_landregistry/trunk/fabfile.py
    Checked out revision 2.
    ~$ svn -R ls uk_landregistry
    branches/
    trunk/
    trunk/README.md
    trunk/fabfile.py 
    ~$ svn cat uk_landregistry/trunk/fabfile.py > uk/fabfile.py
    ~$ chmod +x uk/fabfile.py
   

4. Create the PostGIS db + user & place yourself as a superuser into PostgreSQL

    ~$ sudo -u postgres createuser osopen
    ~$ sudo -u postgres createdb https://wiki.openstreetmap.org/wiki/Tag:--encoding=UTF8 https://wiki.openstreetmap.org/wiki/Tag:--owner=osopen osopen_data
    ~$ sudo -u postgres createuser --superuser $USER
   

5. Activate PostGIS in that db

    ~$ psql https://wiki.openstreetmap.org/wiki/Tag:--dbname=osopen_data -c "CREATE EXTENSION postgis;"
    ~$ psql https://wiki.openstreetmap.org/wiki/Tag:--dbname=osopen_data -c "CREATE EXTENSION postgis_topology;"
   

6. Try to prevent authentication errors in the script (change any ‘ident’ or ‘md5’ to ‘trust’):

    ~$ sudo nano /etc/postgresql/9.4/main/pg_hba.conf
    ~$ sudo /etc/init.d/postgresql restart
    ~$ sudo cat /etc/postgresql/9.4/main/pg_hba.conf | fgrep 'host  '
    # host  DATABASE   USER     ADDRESS         METHOD [OPTIONS]
    host    all         all     127.0.0.1/32    trust
    host    all         all     ::1/128         trust
   

7. Now do the import (get_data) + place in PostGIS (import_shp)

    ~$ cd UK
    ~$ fab get_data
    ~$ fab import_shp
   

As you can probably tell, the learning curve for all of the above is intensely high, and Christian’s scripts give no countdown as to their progress, and take forever to complete.

Was it worth it? Well, I learnt a lot. It gave me hands-on experience in using SubVersion, which was the item that tipped me into doing it. I have realised that SVN is just a method of storing files within a directory. All internal dir file manipulation needs to be done via SVN.

It took me a week of frustration just to be able to learn that. I’m now stuck with 48 GB of useless database files. So much for “Open Government”.