Database migration and data conversion for improved, consistent and integrated address database for the Government of Gibraltar

Submitting Institution

Northumbria University Newcastle

Unit of Assessment

Computer Science and Informatics

Summary Impact Type


Research Subject Area(s)

Information and Computing Sciences: Artificial Intelligence and Image Processing, Data Format, Information Systems

Download original


Summary of the impact

The UoA research enabled a step increase in the technical and commercial capabilities of Atlantic Geomatics (UK) Ltd (AGUK, Cumbria) and the development of a postal addressing solution for the Government of Gibraltar (GoG). The beneficiaries and benefits included: AGUK who secured a contract safeguarding jobs and opening new international markets. Moreover, the GoG now have a definitive solution for legislation to replace their manual, multiple and inconsistent address lists by a spatially-based official address register (OAR) incorporating geographical information thereby enabling the people of Gibraltar to receive enhanced services (e.g. postal, emergency, utilities) from a centrally managed OAR.

Underpinning research

Staff in the UoA have spent over 15 years conducting research in the field of database management systems. They have worked on the development of an algebraic framework for automatic materialization (persistent storage) and immediate incremental maintenance of materialised views for object-oriented databases, which benefit data warehousing applications (Ali, Fernandes and Paton, 2003).

Dr Ali's research in the field of object-based databases (Ali, Fernandes and Paton, 2003) deals with complex and highly inter-related data (e.g., spatially-based address lists, multi-dimensional data) and has impacted on the complexity and flexibility of the solution for the AGUK's GoG addressing and mapping problems.

Database systems are traditionally used for managing structured data. However, with the advent of the WWW and the widespread use of the internet, the need for efficient manipulation of semi- structured data (e.g. XML) emerged in the late 1990s. The main challenges at the time included efficient and lossless conversion and updating of XML documents to/from database systems. To this end, Dr Ali (Lecturer at Northumbria throughout the period) collaborated with Dr Nick Rossiter (who was Reader in database systems and interoperability at Northumbria at the time, and is now a Visiting Research Fellow) on research that developed a solution to the problem of converting XML data from several linked documents into object-relational format for storage in a database; and converting object-relational data back to XML documents for retrieval purpose (Amornsinlaphachai, Ali and Rossiter, 2005; Amornsinlaphachai, Ali and Rossiter, 2006).

The research work in relatively newer and richer object-based and semi-structured databases required development of methods for lossless migration of relational databases into the newer databases. To this end, they worked on the development of an integrated method for the automatic migration and conversion of relational databases into object-based and XML databases (Maatuk, Ali and Rossiter, 2008; Maatuk, Ali and Rossiter, 2010a; Maatuk, Ali and Rossiter, 2010b). The database migration and conversion method ensures that both source and target databases are equivalent and that no data is lost during the process, which has underpinned an effective addressing solution for Gibraltar, described fully in Section 4.

The design and development of a database for management of addresses and geographic data for GoG involved the development of a standard address structure, lossless conversion of existing addresses (36 different and redundant lists were maintained before this project) into the new address structure, linking new addresses to their geographical points, and an official spatially- based address register (OAR) for commercial as well as domestic addresses of Gibraltar.

References to the research

Outputs marked with a * have been flagged to indicate a 2* quality threshold.

(1*) Ali, M. A., Fernandes, A. A. A. and Paton, N. W. (2003) `MOVIE: An Incremental Maintenance System for Materialized Object Views', Data and Knowledge Engineering (DKE), 47 (2), 131-166. Available at:


(2) Amornsinlaphachai, P., Ali, M. A. and Rossiter, N. (2005) 'Updating XML Using Object- Relational Database', Database: Enterprise, Skills and Innovation, Lecture Notes in Computer Science, Springer Berlin / Heidelberg, pp. 32-35. Available at:


(3*) Amornsinlaphachai, P., Rossiter, N. and Ali, M. A. (2006) `Storing Linked XML Documents in Object-Relational DBMS, Update Language into SQL', Journal of Computing and Technology - CIT, 14 (3), 225-241. Available at: DOI: 10.2498/cit.2006.03.06


(4) Maatuk, A, Ali, M. A. and Rossiter, N. (2008) 'Relational Database Migration: A Perspective', Database and Expert Systems Applications, Lecture Notes in Computer Science, Springer Berlin / Heidelberg, pp. 676-683. Available at:


(5) Maatuk, A, Ali, M. A. and Rossiter, N. (2010b). `Semantic Enrichment: The First Phase of Relational Database Migration', in Tarek Sobh (editor) Innovations and Advances in Computer Sciences and Engineering. Netherlands: Springer, pp. 373-378. Available at:


(6*) Maatuk, A, Ali, M. A. and Rossiter, N. (2010a) `Converting Relational Databases into Object- relational Databases.' Journal of Object Technology (JOT),9 (2), 145-116. Available at:


Details of the impact

Between March 2010 and April 2011, the 15 years of research in object-based and semi-structured databases underpinned a Knowledge Transfer Project with Atlantic Geomatics (UK) Ltd (AGUK, Cumbria), to develop a solution for management of addresses and geographic data (GD) on behalf of the Government of Gibraltar (GoG). Based on this research this project:

i. Devised a standard address format (SAF) for Gibraltar, introduced uniform address structure and postcodes, which has incorporated "Data Specification on Addresses" guidelines of the EU INSPIRE Directive (

ii. Devised a process for converting existing address lists into the proposed SAF.

iii. Devised a strategy for obtaining GD for each address.

iv. Contributed to the development of a pilot version of a spatially-based official address register (OAR).


The beneficiaries include AGUK who secured a contract safeguarding jobs and opening new international markets, GoG who developed a spatially-based official address register (OAR) incorporating geographical information, which upon adoption by GoG will replace manual, multiple and inconsistent address lists and the people of Gibraltar who will receive enhanced services through a centrally-managed OAR.

Impact Achieved and Pipelined

The four key impacts from the application of our research are detailed below.

1. Practice Change: Previously, GoG managed varied amounts of address data, which served only minimal operational functions. Inefficient processes, functional redundancies, poor communication, and inadequate data management had resulted in duplication of effort, inefficient data update and resource usage. While many departments had pursued automation, this was done in a fragmented manner without standards or consistency resulting in multiple data storage, with no integration among them.

The pilot project (January-2011) tackled the above issues and produced a consultancy report (Source vii), which was accepted by GoG (March-2011), and led to a full-scale Geographical Information System (GIS) project with a complete OAR. AGUK entered into a 15-month contract (worth £230k) with GoG in October-2011 (Source v). The project has produced a database of all addresses in Gibraltar which, when adopted by the GoG through legislation, will trigger the process of a major practice change in Gibraltar, replacing 36 different address lists with a single spatial OAR providing GD and postcodes for all addresses in Gibraltar.

2. Expected Enhanced Services: Gibraltar's public services' (post office, telecoms, transportation, utilities, ambulance, fire/rescue, etc) separate address lists and personalised formats created difficulties and barriers to inter-services communication, and for e-businesses to determine addresses for GoG delivery services, emergency and rescues operations. The AGUK project (a natural extension of the KTP project) has delivered the spatial OAR to the GoG for approval and adoption. The benefits of the OAR for GoG and its people include improved (correct and timely) delivery of various services (e.g., mail, government, emergency, health, utilities) (Sources ii, iii and iv).

3. Economy and Commercial: The research outlined in Section 2 impacted on AGUK. AGUK's access to research and knowledge transfer on database systems and data cleansing provided them with new technical skills which have enabled them to take on the development of advanced IT applications to map and record relationship between cities, areas, buildings, locations and street names in response to demand from GoG. This new knowledge provided an opportunity for AGUK to grow beyond their core business. AGUK now aim to tap into new and lucrative markets in collaboration with Northumbria, e.g., pursuing other countries, such as Pakistan and Libya, for delivery of GIS solutions like the one deployed in Gibraltar. Dr Ali has visited Pakistan in December 2012 and April 2013 where he met several people and gave an invited talk on GIS and GoG-like solutions at a national conference (Abasyn University, Peshawar, April 2013). This generated enquiries from a number of attendees interested in pursuing projects similar to that of GoG. Upon implementation, Gibraltar will benefit from uniform and standard addressing and mapping to enable delivery of enhanced services.

  • Evidence/indicators: AGUK's contract with GoG since October 2011 (Source v). The presence in Gibraltar provides AGUK with business opportunities/activities (e.g. surveying underground electricity cable mapping) (Source iv).

4. Transfer of Knowledge: Northumbria University research and academic support during the KTP project provided the necessary hands-on experience to translate research into development of a reusable solution. This enhanced the technical capability of AGUK to proceed independently with the full-scale development project for GoG (Source iv).

  • Evidence/indicators: An AGUK employee gained database design and conversion skills through his collaboration with the Associate while capturing GD in Gibraltar (Source iv).
  • Date of impact: (June-2010-January-2011).

Sources to corroborate the impact

i. Managing Director, Atlantic Geomatics (UK) Ltd — corroborates impacts arising from KTP Project June 2010 - March 2011 as well as wider economic impacts on AGUK.

ii. Managing Director of LPS — Land Property Services, Government of Gibraltar, corroborating the impacts claimed around enhancement of public services and practice change.

iii. Statement from the Land Property Services, Government of Gibraltar, corroborating the impact around enhanced services.

iv. Statement from AGUK, corroborating and echoing the claims made about the impact as detailed in Section 4.

v. AGUK's contract with GoG (copy of front and back of the contract).

vi. The KTP project final reports corroborating the economic impacts associated with the project.

vii. AGUK's consultancy report for the Pilot Addressing Project, which originated from the KTP project, submitted to and endorsed by GoG — confidential (January 2011).