Rob's OSM Stuff » Post Hoc » Technical Details

Post Hoc Technical Details

Database Tables

Most of the data used by the tool is stored in mySQL database tables. The key tables are as follows:

Table Name	Description
`pb_rm_cmd`	Raw data from the Royal Mail's Central Collections Management Database
`pb_rm_pfd`	Raw data from the Royal Mail's Final Plate Database
`pb_rm`	Final merged database of Royal Mail data
`pb_rm2`	Old merged database of Royal Mail data
`pb_renumbered`	Ordered box number pairs corresponding to boxes that have been renumbered by Royal Mail.
`pb_correct`	Ordered box number pairs corresponding to boxes listed in the Central Collections Management Database that appear to have incorrect numbers.
`pb_removed`	List of boxes that appear on Royal Mail's list, but have are believed to no longer exist on the ground.
`pb_dcs`	Raw data from the Dracos Postbox finder tool
`pb`	Raw data from OpenStreetMap
`codepoint`	A copy of the Codepoint Open dataset

There are two sources of postbox data held by Royal Mail:
- The Central Collections Management Database (CMD) lists every post box, and includes the box number, location coordinates (as OSGB eastings and northings), a location description, box type, and collection times.
- The Final Plate Database (FPD) lists most boxes, and includes the box number, collection times, post code (often missing, or only to sector or district level), box type (sometimes missing), a location description, and details of where a box with later collection can be found.
The most recently available versions of these datasets are from September 2013 and November 2013, as a result of FOIA requests.

Two tables store ordered pairs of boxes corresponding to changes that need to be applied to some of the datasets. Both of these lists are compiled manually by comparing old records and current postcode boundaries.
Where there are discrepancies between the current Central Collections Management Database and the Final Plate Database, the Central Collections Management Database is usually assumed to be authoritative. The main exception is for box numbers where the final part contains more than four digits.
The pb_renumbered table records pairs of postbox numbers that are believed to correspond to boxes that have been renumbered by Royal Mail. Most of these come from instances where the boundaries of Postal Districts have been changed, and so the first half of the box number has been updated. This table is used to update OSM records after import.
The pb_correct table records pairs of postbox numbers that are believed to fix errors in Central Collections Management Database. This table is used to update Royal Mail records after import.

The CSV or XLS files from Royal Mail are first converted into more manageable TSV files. Some minor editing is done at this stage to correct a few obvious typos in box number and postcode formatting.
A Perl script parses the TSV files and imports the data into the two staging tables pb_rm_cmd and pb_rm_fpd.
Another script then applies number corrections from pb_correct to the CMD data in pb_rm_cmd, before loading it into the final table pb_rm.
The boxes in the pb_rm_fpd table are renumbered using the renumbering table pb_renumbered (see below) to update them to their current values.
For the boxes in pb_rm without coordinates, a check is made in the FPD table pb_rm_fpd to see if there is a full postcode for that box. If so, the location of that postcode is added from CodePoint Open in codepoint. Other missing coordinates are inserted from pb_dcs where available.

Post boxes are usually recorded in OpenStreetMap as nodes with the tag amenity=post_box, and (optionally) a number of other tags set. Tagging is free-form, but there are a number of recommended (and widely followed) tagging practices. See the OSM Wiki for details.
Raw data in XML format collected via calls to the XAPI. A bounding box around the UK is split into a number of smaller tiles and the nodes tagged with amenity=post_box are fetched for each one in turn.
The OSM data is refreshed roughly once a week.

A Perl script processes the downloaded data. For each box, the coordinates and a number of key tags are extracted. A count of the number of times any key name occurs is also kept. Some additional bounding box constraints are applied to remove boxes that are located in Continental Europe and most of those in the Republic of Ireland. Any boxes with disused=yes or removed=yes are also removed at this point. The resulting dataset is used to refresh the pb table.
A Perl script automatically changes the reference numbers of any boxes in the OSM table that match those in the renumbering table. The reasoning is that in most cases the collection plate that mappers obtain the number from will still show the old number. Often the Final Plate Database itself still has the old number, so any new plates printed will still be wrong.

Various Perl scripts create the different reports by performing checks on the data and comparisons with the Royal Mail Data