Database migration and data conversion for improved, consistent and integrated address database for the Government of Gibraltar
Submitting InstitutionNorthumbria University Newcastle
Unit of AssessmentComputer Science and Informatics
Summary Impact TypeTechnological
Research Subject Area(s)
Information and Computing Sciences: Artificial Intelligence and Image Processing, Data Format, Information Systems
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.
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
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*
(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: http://dx.doi.org/10.1016/S0169-023X(03)00048-X
(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: http://dx.doi.org/10.1007/11511854_13
(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: http://cit.srce.unizg.hr/index.php/CIT/article/view/1607
(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: http://dx.doi.org/10.1007/978-3-540-85654-2_58
(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: http://dx.doi.org/10.1007/978-90-481-3658-2_65
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
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 (http://inspire.jrc.ec.europa.eu/).
ii. Devised a process for converting existing address lists into the
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
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
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).
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