How to add new Nomenclatures (NUTS) to the database

In progress

Last update  

Introduction

This section describes how it is possible for the system administrator to add new geospatial data for NUTS type nomenclatures.

it is important to underline that with this operation it is possible only to add new nomenclatures (NUTS) without deleting the existing ones. Deleting preexisting nomenclatures also means eliminating the indicators associated with them.

For these operations it is necessary:

  • to have administrator access to the database
  • to use a SIG software for data preparation
  • to use a PostgreSQL client to perform some database operations
  • to have an account to be able to publish the resulting layers through GeoServer.


1) Prepare the GIS file according to the guidelines

In order to be coherent with the already included nomenclatures, the data should come from Eurostat Reference Data (GISCO database) available at: https://ec.europa.eu/eurostat/web/gisco/geodata/reference-data

The data must have a well-defined format, including all the geometries and the attributing values in a single file, which includes the four levels (0 to 3) of administrative resolution of the NUTS.

The following table gives a guideline of attributes. It is important to respect the field naming.

field name

Type

Lenght

comment

stat_levl_

int4

32

Mandatory: 0 = NUTS0 -> 3 = NUTS3

nuts_id

varchar

5

Mandatory: Official nut ID from GISCO

name_ascii

varchar

255

Mandatory: it is the Official GISCO nomenclature name in ASCII

eu28_4

int4

32

Mandatory: 1 = nomenclature belongs to EU 28 +  4 Partner States

eu28_cc_4

int4

32

Mandatory: 1 = nomenclature belongs to EU 28 +  4 Partner States + Candidate Countries

eu28

int4

32

Mandatory: 1 = nomenclature belongs to EU 28

tc

int4

32

Mandatory: 1 = nomenclature belongs to Territorial Cooperation (TCA)

version_

int4

4

Mandatory: year of reference for nomenclatures in four digits  (example: 2016)

gid

int4

32

Mandatory: unique identifier. NB values will be successively automatically re-calculated in order to avoid any duplicates

The projection of the dataset must be ETRS 1989 in Lambert Azimutal projection with center in E52N10, coordinates in meters (EPSG 3035)

You can find an example of a GIS file (in Geopackage format) by clicking on the link: example for NUTS 2016

Take care...

During these operations it is essential that:

  • field names must be the same as in the table above
  • fields type must be the same as in the table above
  • GIS file must be in EPSG 3035

Tip: for any doubts please refers to the template: example for NUTS 2016


2) Import the GIS file in the (geo)database

This section explains how to import the GIS file into the database. The open source software QGIS (https://www.qgis.org) is used, but obviously other solutions are quite possible.

We strongly suggest saving the GIS file in GeoJson or Geopackage format. The shapefile format could possibly cut the name of some locations if they were particularly long ...

2.1 Connect to the Epson database

You can connect to PostgreSQL easily through the QGIS interface, as explained in the following picture:

Connection to the DB (Click to enlarge)

2.2 Import the GIS file into the "gis" schema of the database

Once the connection is established, it is possible to start  importing the GIS file into the database.

Import to the DB first step (Click to enlarge)

Import to the DB second step (Click to enlarge)

Take care...

During these operations it is essential that:

  • the GIS file is inserted in the PostgreSQL scheme called "gis"
  • the resulting PosrGis table must be named "nomenclature2add"

3) Process and check the data through a PostgreSQL function

Once the nomenclatures have been introduced into the database, the administrator simply has to launch a specific function.

in order to do this, you must have a PostgreSQL client such as PgAdmin, Navicat or other.

This function allows to:

  • generate unique identifiers for each new geometry.
  • Establish hierarchical relationships between the various NUT levels, which will be written in a specific table.
  • Establish the spatial extension for each country grouped according to a specific category: eg EU 28, EU 28+ 4 etc. This information is written in a special table.
  • Finally update the main table that contains all the database nomenclatures
  • Generate a temporary table called  test_nomenclature in order to check outputs before to commit to the operational database

Once connected to the database (administrator account), execute a new query (on the to the gis schema) in order  to  launch the function nomenclature_prepare() and successively visualize a test table (test_nomenclature) to check the processed values (hierarchy relations) :

1) Launch the function : gis.nomenclature_prepare()
SELECT * FROM gis.nomenclature_prepare();
2) Open the created table (test_nomenclature) for check
SELECT * FROM gis.test_nomenclature;

Example of  the test_nomenclature table (Click to enlarge)

In addition to the test table previously mentioned, the nomenclature_prepare() function will also generate three new tables (edp_core_trel_spatial_ext_gis_test, edp_core_trel_tunit_hierarchy_test, edp_core_tdta_gis_test), which will subsequently be inserted in the "public" database schema.

4) Commit to public schema

All these operations are performed in a reserved area of the database, it is therefore possible to verify the outcome of the operations before definitively transferring the new nomenclatures to the operating area of the database

The last step, therefore, provides the transfer the new nomenclature table to the "public" database schema.

to do this it is necessary to launch another function called nomenclature_add():


3) Launch the function : gis.nomenclature_add()
SELECT * FROM gis.nomenclature_add();