Database System
Introduction
The main objectives of the ESPON 2020 Database Portal project could be simplified and summarised in short as:
- Maintain a set of core data of the highest possible quality and regularly updated, useful and usable for the work of the ESPON Programme and projects;
- Collect the most relevant data (and metadata) from ESPON projects;
- Disseminate the above data in a user- and machine-friendly manner to the ESPON community and to the outer World.
The word “data” appears in all the three objectives. Therefore, this project is about data, data quality being a key aspect. Data must be harmonised, quality controlled, as much as possible without gaps and accompanied by metadata. This aspect is one of the pillars in the current proposal.
Description of database system components and methodology
The ESPON 2020 Database system (DBS) (Figure 10) is built around two PostgreSQL/PostGIS databases. PostgreSQL is a very well-known relational database system, used and maintained by a huge worldwide community of users. This free open source database is used at GRID-Geneva for around 10 years for its robustness, performance and tailored geospatial add-on (PostGIS). The relational databases containers are associated with simple file system storage on a Linux server (Debian 'Stretch').
The main components of the system are:
- Main ESPON 2020 database:
- Administration/application content;
- Metadata information;
- Data content (standard data);
- GIS information.
- File system storage in dedicated server folders:
- Non standard data;
- Non data;
- GeoNetwork database, for the provision of metadata according to the INSPIRE normative.
The files stored on server folders will be linked in dedicated fields of the database. The GIS nomenclatures will be stored directly in dedicated PostGIS tables. Some synchronisation scripts will maintain coherence between the main database and the GeoNetwork associated application.
Database model
The data model is implemented in the Django framework, data could be inserted and Web Services developed (for internal connection to the User Interface and interoperability with external applications).
In Figure 1, yellow background includes (1) application tables, which represent the conceptual schema called "admin/app", containing the Django (https://www.djangoproject.com/) standard tables including the users/roles information and (2) tailored tables allowing to track the various actions undertaken by the users. The green background is used for GIS table(s) including geometry. The light blue background includes the data table containing the actual data values. The orange background includes the whole metadata tables. The purple background designates the Geonetwork database which will be synchronised with ESPON database.
Figure 1: database model (click to enlarge)
Tables are prefixed and also coloured in this model. Light green is used for 'tdta_' tables, containing different data inputs (tables modelling the main used concepts, such as: project, dataset, indicator, t-indicators, territorial unit, etc.). Purple tables, 'trel_', are relational tables linking records between several tables. Blue 'tkey_' tables contain defined values stored in the database (used for example for drop down list selection in the user interface). Tables with red lines are the specific tables for the three main object type (set, indicator and resource).
To go further
Interactive Database Schema
(As of 03/2019)