OS Meridian Roads Data

I have been doing a lot of work processing OS OpenData recently and whilst loading Meridian 2 onto a PostGIS system, I have encountered a couple of issues that I thought were worthy of a post.

 

The source data files I am working with are the latest MapInfo TAB files (Version: 07/2013) downloaded from the OpenData website.  My working system is a Kubuntu 13.04 laptop and the Server a virtual machine running PostgreSQL 9.1.  The actual data transfer is performed by the Geospatial Data Abstraction Library (GDAL).  I have written a short BASH script that calls the GDAL ogr2ogr utility, iterating over the extracted files from the downloaded archive:

#!/bin/bash 
function UploadFiles
{
 # Upload TAB files to PostGIS with OGR.
 for File in "$1"/*.TAB 
   do
     if test -f "$File"
     then
       echo "$File"'...'
       export PGCLIENTENCODING=LATIN1
       ogr2ogr -f "PostgreSQL" PG:"host=XXX.XXX.XXX.XXX 
       user=postgres dbname=OpenData password=XXXXX port=5432"
       "$File" -a_srs "EPSG:27700" -skipfailures -progress
     fi
   done
 # recurse directories
 for SubDir in "$1"/*
   do
     if test -d "$SubDir"
     then
       UploadFiles "$SubDir"
     fi
   done
}
UploadFiles "/media/Archive/GIS Data/OS OpenData/Meridian"
However, I did encounter a few snags.  Firstly, the A road section ‘O13RDLWRD2HTV’ caused the following warning:
Warning 1: Geometry to be inserted is of type Multi Line String, whereas the layer geometry type is Line String.

Converting the geometry for this record into well known text format revealed:MULTILINESTRING((430708.0001 431916.9998,430793.00005 431966.00005),(430791.00015 431966.00005,430837.9999 431994.0001))The Meridian road network is intended to consist of continuous lines, so this two part record is, in effect, a broken line.  The work around is to remove the inner line ends and convert the remaining end points into a single line string:LINESTRING(430708.0001 431916.9998, 430837.9999 431994.0001)To help visualise this I have prepared a graphic using Quantum GIS. The original broken line is rendered in red and my suggested solution in green.

MeridianBug

The fixed record can be added back onto the server with the following SQL command:

INSERT INTO "a_road" 
("wkb_geometry" , "feature_code", "osodr", "route_number", 
 "road_name", "length_metres", "trunk_indicator") 
VALUES 
(ST_GeomFromEWKT('SRID=27700;LINESTRING(430708.0001 431916.9998, 
 430837.9999 431994.0001)'), 3001, 'O13RDLWRD2HTV', 'A61', '', 
 152, '');

There is a second similar problem in the minor roads layer for road section ‘O16A01KU1ABTW’.  The solution for this is exactly the same, stitching the two segments back into a single line.  I have reported these two issues to Ordnance Survey and  I have received feedback from the Product Manager that both errors have now been fixed. The changes will be issued in the next release of Meridian 2 Open Data.

One last thing to mention, on my first attempt at the data upload, I did find some accented characters had not been correctly loaded.  The PGCLIENTENCODING define in the BASH script hints PostgreSQL to perform the correct character re-encoding during the import.