Scottish Government

APPENDIX D: ASSET DATABASE

Asset Database Administration

The database was developed to allow data to be input through GIS attribute tables either MapInfo Tab files or ARCGISDBF tables. In addition where there are significant amounts of data to be input this can be through Access forms such as the one shown below. An example of an input form (this one is for the FPS table) for the database is shown below in Figure D- 1.

Figure D- 1: Example Database Entry Form

Database Tables and Source Information

The information within the Asset Database comes from a number of sources: the Scottish Executive files, local authority Questionnaires, on site condition assessment, hydrological assessment and hydraulic modelling. Information that is stored within the Asset Database that has come from sources is detailed below.

Information related to each Flood Prevention Scheme is stored within the ' FPS' table in the Asset Database. The majority of the information in the Flood Prevention Scheme table came from the hard copy paper file for the scheme provided by the Scottish Executive. Table D- 1 below identifies information that was sourced from a dataset other than the hard copy paper file.

Table D- 1: Source Information in the FPS table

Defence Not Part of the FPS

During the condition assessment the conditions of the defences identified on the FPS drawings were recorded. In addition other defences that effectively form an integral part of a flood prevention scheme were also assessed and recorded. Each element was therefore assigned a code to indicate whether it was constructed under the formal FPS or some other time or under different powers. The field allows the database administrator to record and identify those defences in the database that form part of the Flood Prevention Scheme but which are not part of the records submitted at the time of scheme promotion.

Proposed/ Unbuilt Defences

During the site visits, it was apparent that some defences identified on the original FPS drawings and schedule had not been constructed or had been removed since construction. This identified that there are often differences between confirmed drawings, 'as-built' (or record drawings) and what was constructed or is still on site. Wherever possible 'as-built' or record drawings were used to enter data into the database however these drawings were not available for each scheme. To record defences that are present on the 'as-built' drawings and hence in the database, but which were not present on the day of the condition assessment, the database was set up with a field in the defence tables called 'Constructed'. The Constructed field allows the database administrator to record and identify those defences in the database that were present on the day of the condition assessment.

In addition to allowing the user to record that a defence identified in the Flood Prevention Scheme was not constructed, the 'Constructed' field allows the database administrator to input and digitise defences or Flood Prevention Schemes that are/were proposed but which have not been constructed yet. For example when the Flood Prevention Scheme is confirmed for a scheme, then the details of the scheme can be entered into the database and viewed through the web GIS application. This may be useful in terms of SPP7 which allows an alternative response for areas within areas of flood risk where a scheme is proposed.

Flood outlines and Areas benefiting from defences

Following the completion of a hydraulic model for a scheme, the modelled defended and undefended flood outlines at the 100, 200 and 1000 year return periods were entered into the database. The flood outlines can be viewed alongside the defences within the scheme. In addition the area benefiting and the standard of protection afforded by a scheme are identified as a separate layer.

If a historic flood outline was available for the scheme, this information was digitised and entered into the database, allowing the user to compare the historic and the modelled flood outlines for the scheme.

Web GIS Application

One of the aims of this project was to make the database available to users and this has been achieved through developing a web GIS application. The GIS functionality within the web GIS application has been designed to provide users with simple tools that allow them to view the geographical data and associated data within the database.

The website for the web GIS application is currently located here at the address shown below and available with a username and password. Passwords can be made available through the Climate Change and Air team at the Scottish Executive.

http://www.scottishflooddefences.gov.uk/Site/ SE Splash.asp

In addition to viewing the geographical data, users are able to download the original Flood Prevention Scheme drawings and the condition assessment report.

The Web GIS application allows the user to view photographs, information on the scheme and information on the defences within the scheme.

Figure D- 2 to Figure D- 10 below show screen shots of the Web GIS Application.

Figure D- 2: Web GIS Application Login Page

The entry page with log in requirement.

Figure D- 3: Web GIS Application Mapping Page

Overview page showing all of Scotland and distribution of schemes.

Figure D- 4: Web GIS Application following 'Zoom In' Action

Schemes with 200k map background

Figure D- 5: Web GIS Application showing Galashiels

Schemes over a 50k background, showing flood outlines.

Figure D- 6: Galashiels Plumtree/Wilderhaugh

Figure D- 7: Galashiels Plumtree/Wilderhaugh and 100 year undefended outline

Figure D- 8: Galashiels Plumtree/Wilderhaugh Flood Prevention Scheme Information Page

Figure D- 9: Galashiels Plumtree/Wilderhaugh Scheme Photograph

Figure D- 10: Galashiels Plumtree/Wilderhaugh Flood Defence Asset 'Wall' Page

Tool tips

Tool tips have been added to provide assistance and a description of the headings in the data forms in the application.

Datasets

In addition to the datasets from the Asset database the following datasets are available to view in the Web GIS Application,

• Scotland Relief Map
• 200,000 Map
• 50,000 OS Map
• 10,000 OS Map
• Landline data

Data and SEPA Website

Two datasets produced during this project, in addition to being made available through the Web GIS Application, will also be available along with the Indicative River and Coastal Flood Maps on the SEPA website. The data that will be available through the SEPA website are the 200 year modelled defended outlines and the location of the Flood Prevention Schemes.

Web GIS Application Datasets

It is recommended that the following datasets could also be included in the web GIS application:-.

• NEXTMapDTM data
• SEPA flood maps
• OS MasterMap data

Including the MasterMap dataset within the Web GIS application would provide users with the most current large scale mapping product available from the Ordnance Survey today. In addition to being current, the inclusion of the MasterMap dataset within the Web GIS application could have a number of benefits for the economic assessment of the Flood Prevention Schemes in the future. For example MasterMap data can be used to determine the number of commercial and/or residential properties within a specified area and this could be used to determine the number of defended and undefended commercial and residential properties in the future.

Another important factor for the inclusion of OS MasterMap over OS Landline is the withdrawal of OS Landline by Ordnance Survey in the future. Although this will not happen until 31 March 2008 at the earliest it would be beneficial to include this data.

Asset Database Forms

It is recommended that data entry forms are developed to enable the easy input of new data into the database. For example, forms should be developed for the SQL Server database to facilitate the viewing and update of data in the database, for example the FPS table which holds data on Flood Prevention Schemes. An example of this can be seen earlier in Figure D- 1.