Pivoting data information

PIVOTED TABLE/VIEW + OGC Geoserver publication


Due to ESPON Database 2020 data model choice to have the most simple data structure as possible, data is stored in one table ‘tdta_dataitem’. One row is corresponding to one data value. This table has the following structure:

  • decimal_value for any integer or
  • float values txt_value for any textual values
  • year_start for the start date
  • year_end for the ending date
  • obj_id is the reference to the indicator id
  • gid_id is the reference to the territorial unit (including the geometry)

Notice that each gid_id is caracterised by a nomenclature id, a level and a version.

In order to obtain a nice and ready to use data as well as in CSV/XLS that through OGC services, the data needs to be pivoted in order to obtain indicator years in columns.

To achieve that one script for testing purpose and batch launch is test_crosstab_panda.py

The selection of indicator for which CSV and OGC geoserver has to be create or recreate is done by declaring an array of indic_id in code through find_indicator() function. This is launched manually:

cd /var/www/edp/.../services/dataimport/
../../../django-edp/manage.py shell < test_crosstab_panda.py

It calls CrosstabPD class of connectDB.py script as well as in the usual process launched once data upload is done!

Process description

Here is a presentation of the process to generate pivoted tables/view allowing creation of CSV/XLS and Geoserver layer publication.

Various functions are called by CrosstabPD class of connectDB.py script to acheive the following steps:

  • Drop existing postgresql view and geoserver layers
  • Create a dedicated table by indicator in PostgreSQL (pivoted schema), dedicated view by indicator levels and publish layers in geoserver.

At the end of this process, geoserver propose WMS/WFS services with one layer by indicator level. For example for indicator 321: espon_layers:view_ind_321_0,espon_layers:view_ind_321_1,espon_layers:view_ind_321_2,espon_layers:view_ind_321_3. WMS for the last one is: https://database.espon.eu/geoserver/espon_layers/wms?service=WMS&version=1.1.0&request=GetMap&layers=espon_layers%3Aview_ind_321_0&bbox=2641758.46955152%2C1428695.35991733%2C7313157.28662197%2C5456938.51107795&width=768&height=662&srs=EPSG%3A3035&format=application/openlayers

CSV/XLS can be also created after this operation. This is done through package.py creating package including the various files.

A detailed information is available in Github at: https://git.unepgrid.ch/espon-egtc/edp/src/branch/master/django-edp/edp_damf/services/dataimport/README.md