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) :
SELECT * FROM gis.nomenclature_prepare();
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.