Implementation of subset-aware function layers

[rafdouglas]

UNDP - SDGi – GeoHub

Internal note

Proposal: implementation of

subset-aware function layers

March 16 th , 2023

​Background

Currently we imported ~250 vector files into GeoHub.

Internally, PostgreSQL-side, these files are accessed as views which connect the flat data with geographical admin0 features.

This step was important to assess the data we have and get a first visualization.

However, some shortcomings emerged, namely:

• the original dbf (from the shapefiles) have a number of built-in attributes for the same features, for example series, time series, age group, sex group, urban/rural, along with yearly values.
• Multiple files can refer to the same indicator.
• Creating individual tables for each combination of the above-mentioned attributes would result in 795 views, which would show up as 795 layer in the GoeHub interface
• additionally, it is not possible to push performances in a meaningful way form the point of view of pg_tileserv / PostgreSQL optimizations. It is not possible, for example, to dynamically join data to pre-semplified feature layers, because views can only join to a fixed table.

​Examples

Indicator 10.5.1:

• 7 different files with the same indicator
• Layer name : Indicator 10.5.1 - Financial Soundness Indicators
• Available "time series":

area	series_name	Desc
assets to liabilities	FI_FSI_FSLS	Liquid_assets_to_short_term_liabilities
foreign exchange	FI_FSI_FSSNO	Net_open_position_in_foreign_exchange_to_capital
loans	FI_FSI_FSKNL	Non-performing_loans_net_of_provisions_to_capital
loans	FI_FSI_FSANL	Non-performing_loans_to_total_gross_loans
capital to assets	FI_FSI_FSKA	Regulatory_capital_to_assets
risk-weighted assets	FI_FSI_FSKRTC	Regulatory_Tier_1_capital_to_risk-weighted_assets
return on assets	FI_FSI_FSERA	Return_on_assets

Indicator 4.6.1:

• 1 file

• 6 available "time series":

area	series_name	age_group	sex_group
Literacy	SE_ADT_FUNS__Y16T65___T__SKILL_LTRCY	16-65 years old	Total
Numeracy	SE_ADT_FUNS__Y16T65___T__SKILL_NMRCY	16-65 years old	Total
Literacy	SE_ADT_FUNS__Y16T65__F__SKILL_LTRCY	16-65 years old	Female
Numeracy	SE_ADT_FUNS__Y16T65__F__SKILL_NMRCY	16-65 years old	Female
Literacy	SE_ADT_FUNS__Y16T65__M__SKILL_LTRCY	16-65 years old	Male
Numeracy	SE_ADT_FUNS__Y16T65__M__SKILL_NMRCY	16-65 years old	Male

​Proposed implementation

The proposal would be therefore to create a set of subsettable functions to dynamically join geometry-less data with admin0 features. Advantages:

• The number of layers added to GeoHub would be only ~240.
• Each layer would initially be presented with a default subset.
• Subsets are mutually exclusive (e.g.: either male or female, not both)
• Subsets are processed server-side, and are distinct from the already-implmented filters, which happen client-side.
• Layers could be subset by gender/age/urban area, etc.
• The subset options would extend back to the existing function layers and to the tool piping

The concepts would be:

Dbf(s) →python code→ sdgxx.admin0

One PostgreSQL function per sdg code / indicator / series

Register & expose each PostgreSQL function as a vector layer ({z}/{x}/{y}.pbf)

Subsets for [time series] / sex group / sex age / urban / others (these will become WHEREs in PostgreSQL)

Pull-down menu or time slider to select the year (field format: value_{yyyy})

The (partially) new feature would therefore be the presence of subsets.

Subset options will be exposed by the postgreSQL function via pg_tileserv's endpoints as JSON (similarly to what we have today for the Subnational Dynamic HDI function):

https://pgtileserv.undpgeohub.org/admin.dynamic_subnational_hdi/{z}/{x}/{y}.pbf?params=
{"le_incr":{"value":11},"eys_incr":{"value":22},"mys_incr":{"value":33},"gni_incr":{"value":44}}

will become:

https://pgtileserv.undpgeohub.org/admin.dynamic_subnational_hdi/{z}/{x}/{y}.pbf?params=
{"``values``":
{"le_incr":{"value":11},"eys_incr":{"value":22},"mys_incr":{"value":33},"gni_incr":{"value":44}},
"``subsets``":
{"sex_group":{"value":"Female","options":["Female","Male","Total"]},
"age_group":{"value":"18-24 years","options":["0-12 years","12-18 years","18-24 years","older than 24 years","total"]},
"location":{"value":"urban","options":["urban","rural","total"]}}}

Please note that the current "params" dictionary argument would become params→values, and the subsets would be udner params→subsets.

Optional/future subset could be:

• by country/continent (to make queries faster, not filtered by the client) or
• by user-selected rectangle area.

[rafdouglas]

If a function layer exposes an empty params→values, or has no params→values, then the simulation tab should not be displayed.

Linked with #1558

[iferencik]

Correct