Catalogue description Ministry of Agriculture, Fisheries and Food: Flood and Coastal Defence Division, River and Coastal Engineering Group: Coast Protection Survey of England: Dataset

Hardware: IBM or compatible PCs. Halcrow ran the Informix version of the CPSE database on UNIX work stations, later Access versions of the database ran on IBM or compatible PCs.

Operating system: MS DOS version 5.0. Halcrow ran the Informix version of the CPSE database on UNIX, and the Microsoft Access versions on Windows. MAFF used DOS with the Oracle version and Microsoft Windows subsequently.

Application software: Data originally input into a database established by Halcrow in DataEase version 4.2. Then migrated into an Informix database. This was linked in turn to a Geographical Information System (GIS) using Intergraph software manufactured by Intergraph Corporation. The CPSE data was exported to MAFF in Oracle. MAFF's Oracle database was also linked to an Intergraph GIS. In 1998 Halcrow migrated the database to Microsoft Access 2.0 and Microsoft Access 97. Different versions of Access were used because MAFF and some other recipients of the data (e.g. some maritime local authorities) required Access 2.0, while others wanted it in Access 97.

User interface: The Informix database is believed to have been command-line driven. The Microsoft Access 2.0 version of the database allowed the data to displayed as forms or as tables, and to be input through the on-screen forms or by adding to the columns of data when displayed as tables. The Access 97 version had a considerably more complex interface, involving a hierarchy of"switchboards" and other forms.

Logical structure and schema: There are a number of differences in the structure of the CPSE datasets, which reflect the fact that the datasets were transferred at different times and from different versions of the CPSE database.

Datasets for the original survey, the 1995 update, the 1996 update and the lookup table for principal type of land use were transferred from MAFF in March 1998 in Microsoft Access 2.0, the format in which MAFF held the CPSE database at that time. The Length tables for each year were linked in one-to-many relationships to the Element tables via the def_code field in the Length tables and the defence_code and sub_code fields in the Element tables (def_code and defence_code contain the codes for defence lengths, and sub_code contains sub-codes for defence elements). No relationships had been defined between the Element and Length tables across different years of the Survey. The single lookup table, l_princip_type, explains the values used in the principal_type field in the Length tables. The dataset for the 1996 update did not include the Form_d_96 table, which at that time had not been transferred to MAFF from Halcrow.

In January 2001 the dataset for the 1997 update was deposited by MAFF. This came via a copy of the Access 97 version of the CPSE database. This database included Form_d tables for the 1996 and 1997 updates, and other tables which had not been present in the earlier version: a large number of lookup tables defining values used in the Element, Length and Form_d tables; lookup tables for local authorities, coastal groups and MAFF regions, which related to the data viewing and querying functions; a lookup table (CrossRefCouncilGroup) which connected coastal groups and local authorities; and a Switchboard Items table relating to the operation of the"switchboards" and forms which provided the database's user interface.

The Length and Element tables were linked in one-to-many relationships by def_code in the Length tables and defence_code and sub_code in the Element tables. Similarly, the Element_95 and Element_96 tables were linked in one-to-one relationships to Form_d_96 via defence _code and sub_code. There were no direct links between the data tables and the"codelists" (lookup tables), which were viewed separately.

How data was originally captured and validated: During the original survey in 1993-1994 data was gathered by the three associate consultants using the following methods:

• Through desk reviews of published sources (e.g. Admiralty Tide Tables) and information already held by the associate consultants.
• By obtaining copies of information held by maritime local authorities and other bodies responsible for coast protection defences.
• By visits to maritime local authorities and other bodies.
• Through site visits to inspect coast protection works, take photographs and prepare cross sections of defences.

The annual updates of the CPSE were conducted by Halcrow by sending forms to maritime local authorities and other major bodies responsible for coast protection defences. In 1995 three forms were issued:

• Form A, to gather details of new coast protection structures or changes to existing structures due to capital works or significant maintenance since the original survey.
• Form B, to record alterations to existing man-made defence elements due to changes in the condition of the structure itself or its immediate environment (rather than through new works or maintenance).
• Form C, to record previously unclassified and unprotected frontages identified as requiring new protection works within 10 years.

In the 1996 annual update these forms were joined by a Form D, to gather information on defence elements whose condition had previously been classified as"Class 4", and to allow respondents to identify elements which had been newly classified as Class 4. A different set of forms was sent out in the 1997 update, reflecting the decision to focus on Class 4 elements. These included:

• Form D.1, to record alterations to existing Class 4 defence.
• Form D.2, to record changes in the maintenance policies towards existing Class 4 defences.
• Form D.3, to record defence elements which had entered the Class 4 category since the 1996 update, and any associated maintenance policies.
• Form D.4, to record changes due to capital works or significant maintenance to defence elements which had been previously classified as Class 4.

Although the 1997 update only required details of Class 4 defences, some local authorities provided voluntary updates of their element and length data, and this information was incorporated into the 1997 data tables. The dataset for the 1997 update also includes data from some returns from the 1996 update which had been received after the"cut-off" point for that sweep.

In the 1995 and 1996 updates completed survey forms were checked by Halcrow on receipt using validation checklists and by plotting any significant changes onto maps. Survey respondents were then contacted for further information where there were uncertainties or ambiguities in the data.

The CPSE datasets are closed, in the sense that new tables were created for each year of the survey. Data in the tables for one year of the survey was not overwritten.

Constraints on the reliability of the data: The CPSE datasets reflect the availability of information at the time that the data was gathered. The annual updates of data were also dependent on forms being completed and returned by the bodies to which they were sent. The response rate to the questions relating to Class 4 defences in the 1996 survey is thought to have been low (30-40%).

Validation performed after transfer: Details of the content and transformation validation checks performed by NDAD on each CPSE dataset are recorded in the catalogues of individual datasets.

The aim of the original Coast Protection Survey of England (CPSE) was designed to gather data on the location, extent, nature, adequacy and state of repair of coast protection defences along the English coastline, with the primary purpose of identifying requirements for defence works within the next 3-5 years. The survey also aimed to examine unprotected lengths of coast identified as 'significantly eroding', i.e. those with 'substantial [land] assets which may reasonably require protection works within the next 10 years'. It was intended that data from the CPSE would help Ministry of Agriculture, Fisheries and Food (MAFF) assess priorities for future expenditure on coast protection, and would inform discussions between MAFF's Regional Engineers and coast protection authorities about future programmes of capital works.

Sir William Halcrow and Partners Ltd, an engineering consultancy, was appointed as project manager/lead consultant to co-ordinate the original survey. The gathering of data was delegated to three other engineering consultancies who were appointed by MAFF as 'associate consultants'. The areas covered and the associate consultants involved were:

• 1. York Area/NW (Scottish border to the River Dee): Mouchel Consulting Ltd.
• 2. York/Lincoln Areas NE (Scottish border to the Wash): Posford Duvivier International Consulting Engineers.
• 3. Cambridge Area (King's Lynn to Southend-on-Sea): Mott MacDonald.
• 4. Tunbridge Wells Area (Thames estuary to the Isle of Wight): Posford Duvivier International Consulting Engineers.
• 5. Taunton Area (Dorset, Devon, Cornwall, Somerset and Bristol to the Severn): Mouchel Consulting Ltd.

The original survey covered 1,018 km of coast line out of a total coastal area of 3763 km. 860 km of predominately man-made coast protection defences were surveyed, together with 158 km of naturally defended coast. 134.8 km of natural coast were identified as significantly eroding. Data was gathered on both 'defence lengths' and 'defence elements'.

Most of the data gathering and field work by associate consultants was done in the second half of 1993. Associate consultants were also required to produce at least one colour photograph of each defence element, at least one representative cross-section of each defence, and outline maps showing the location and extent of defence lengths and defence elements. The data, cross-sections, photographs and outline maps were submitted to Halcrow and were used to produce a report for each region of the survey. These reports set out the objectives in carrying out the survey, the methodology used by the associate consultant and summary findings for each survey area. They also included appendices of photographs and cross sections of defences, printouts of data for defence elements and defence lengths, lists of contacts consulted during the survey, and outline maps. An amalgamated database of survey data was produced by Halcrow from the data submitted by the associate consultants.

After the original survey the CPSE database continued to be maintained by Halcrow under contract to MAFF. Printouts were held by the Regional Engineers in MAFF's regional offices and were used by them to assess applications for grants for coast protection schemes. Copies of the database were supplied to other bodies. Digital maps derived from data in the CPSE database were maintained by Halcrow on a Geographical Information System (GIS).

Three annual updates of the CPSE were conducted by Halcrow through surveys of maritime local authorities and other major bodies responsible for coast protection defences. For each update, Halcrow produced new tables by copying data from the most recent survey and then updating and amending it as necessary. Data from the earlier updates and from the original survey was preserved and was not overwritten. Additional tables were created for the data relating to 'Class 4' defence elements which was gathered in the 1996 and 1997 annual updates.

It should be noted that in the area of sea defences (i.e. defences against flooding from the sea rather than coastal erosion), the counterpart of the CPSE database was the Sea Defence Survey database maintained by the Environment Agency, which included details of the condition of sea defences along the Welsh and English coastlines. This survey was first conducted in 1991 by the Environment Agency's predecessor, the National Rivers Authority, and was updated annually. A separate survey of coastal defences in Wales was conducted by the Welsh Office (see related material).