Global Terrestrial Network for Permafrost (GTN-P) - Data Modelisation and Use Cases

News

GTN-P Workshop Report - EUCOP 2023...

On Sunday before the Sixth European Conference on Permafrost officially started, the GTN-P Community was invited to meet for an evening workshop in the...

GTN-P Strategy and Implementation...

With a slight Corona-related delay, the GTN-P Strategy and Implementation Plan 2021-2024 was just released! You can find it here

Measurement Recommendations and...

Check out the new document summarizing how to measure and monitor Permafrost Temperature and Active Layer Thickness

GTN-P at RCOP

Join us at this year's Regional Conference on Permafrost, taking place online from October 24th to 29th (https://uspa.memberclicks.net/rcop-iccre). We are...

GTN-P meeting 2021

The Global Terrestrial Network for Permafrost (GTN-P) will assemble virtually in on November 18th from 1:00 - 3:30 PM GMT. This online meeting is a...

More News

Contact

Anna Irrgang Executive Director gtnp-secretariat@awi.de Contact

Intranet

Data Modelisation and Use Cases

The data modelisation for boreholes (soil temperature) and CALM sites (seasonal active layer thawing depth monitoring) define the structure of the data enabling both data extraction, Data Management System capabilities and data transfer between several systems. The GTN-P system data modelisation has followed the framework of CakePHP and of the ISO TC/211 standard for geospatial data in order to be able to map the relational database toward interoperable data models. An extension of the ISO TC/211 should be defined and submitted to relevant organizations in order to create a standard for permafrost related data.

Data Management System Use Cases for boreholes, CALM site and related variables

Sometimes, the terms "use case" and "goal" are considered to be synonymous. A use case is a software and system engineering term that describes how a user uses a system to accomplish a particular goal containing all system activities that have significance to the users.

Section	Use cases	Description
Data Management System	General use without privileges	Access to the GTN-P website in order to get a broad understanding about the permafrost phenomenom.
Data Management System	GTN-P registration, login and logout	Register to the GTN-P website. A registration with more prvilege is neccessary in order to be able to input data within the database and download the entire dataset.
Data Search	General database search engine	A general search field by name,keyword, countries, and sites allows retrieving all the objects populating the database, i.e. boreholes, grids, and ancillary measurement stations.
Data Search	Retrieve and sort raw data into the index page	Access to the data catalog of the GTN-P website. The user can retrieve the full dataset or specific raw data by field search using the catalog search engine. The raw data download export format will be CSV (Comma Separated Values).
Data Retrieval	Prepare raw data into a downloadable archive	The search result from the catalog page is processed into a downloadable archive. The entire dataset can also be downloaded.
	Prepare geographic information layers into downloadable files	Acess to the PostGIS database in order to prepare geographic layers for the multiformat download.
	Retrieve and sort data into the graphic system	The graphic interface of the GTN-P website. Show some of the important dimensions of the permafrost dynamics into graphics.
Data visualisation	Implement geographic information layers into the cartographic system	Access to the GTN-P database in order to retrieve and present geographical information layers into the cartographic system. The cartographic system enables the user to vizualize, search, query and download data and metadata.


Data input	Data input by the field scientist	Access to the data input form within the GTN-P website. Feed the databae with new boreholes, new grids, and new ancillary measurement stations data and metadata, new temperature profiles, or any new dimensions within the existing objects.


Data integration	Tools for decision making	Access to the GTN-P website as an assisted tool for decision making. The GTN-P website can also be a platform that allows exchanges between policy makers and the scientifc community.

Physical Data Model

The physical data model shows the implemention of the data model in the database.

data modelisation

Objects

Ancillaries

An overview of variables and their attributes of ancillary measurements stored into the database

Name	Comment	Data Type	Length	Mandatory	Primary	Foreign Key
id	Id column and primary key for the ancillary measurement table.	int4	4	X	X
site_id	Foreign key for the appropriate site.	int4	4			X
name		varchar(64)	64
description		text
permafrost_zone		varchar(64)	64

Boreholes

An overview of variables and their attributes of boreholes stored into the database. A borehole designates a column along which temperatures are monitored. Contains ground, surface and air temperatures.

Name	Comment	Minimum Value	Maximum Value	Data Type	Length	Mandatory	Primary	Foreign Key
id	Id column and primary key for temperature column			int4	4	X	X
site_id	Foreign key for the appropriate site			int4	4	X		X
name	Temperature column name			varchar(64)	64
measure_method				varchar(256)	256
deepest_sensor	Depth of the deepest sensor, automatically compute by the senors depth			float8	8
permafrost_thickness	Known or evaluated permafrost thickness at the borehole location			varchar(16)	16
slope		0°	90°	int4	4
aspect				varchar(16)	16
description	Optional descriptions or additionnal comments of the temperature column			free_text
drilling_method				varchar(128)	128
depth	Depth of the borehole	0 m	1500 m	float8	8	X
gtnp	Gtn_P borehole codes			varchar(16)	16
drilling_duration	Duration of the borehole drilling			varchar(64)	64
permafrost_zone				varchar(128)	128
date_drilled	Date of the borehole drilling			varchar(32)	32
code	Temperature column code			varchar(16)	16
prior	First measurement
angle	Borehole angle	0°	180°	float8	8
diameter	Borehole diameter			float8	8
data	Data uploaded to a borehole YES or NO			boolean	32
created				timestamp		X
modified				timestamp		X
user_id	Id of the User who create the borehole			Integer
country_auth	Country responsible for the borehole			Integer array		X
citation_list				Integer array		X
date_start	Timestamp of the first measurement. Compute by the sytem			timestamp
date_end	Timestamp of the last measurement. Compute by the sytem			timestamp
contact_id	Responsible person for the borehole			integer		X
datasets_count	Count of all datasets within a borehole			Integer
md_completeness	Percentage of the metadata filled of a borehole			double precision
timezone				timezone

Active layer monitoring sites

An overview of variables and their attributes of active layer monitoring sites stored into the database. Active layer monitoring sites are scientific protocols measuring active layer's seasonal thawing by grid or transect probing or borehole or thawing tube.

Name	Comment	Minimum Value	Maximum Value	Data Type	Length	Mandatory	Primary	Foreign Key
id	Active layer id column and primary key for active layer thawing measurements.			int4	4	X	X
name	Active layer name.			varchar
site_id	Foreign key for the appropriate site.			int4	4	X		X
description	General description of the active layer measurement protocol.			varchar(1024)	1,024
permafrost_zone				varchar(64)	64
type	Type of active layer measurement : grid, transect, thaw tube. borehole			varchar(32)	32
nodes	Numbers of nodes by grids or transects,			int4	4
grid_ y	Number of nodes from the y axis of the grid.			int4	4
grid_ x	Number of nodes from the x axis of the grid.			int4	4
length	Length of the transect in meter.			int4	4
code	Active layer code.			varchar(16)	16
area	Area cover by the grid.			numeric
created				timestamp		X
modified				timestamp		X
user_id				int4	4	X
country_auth				integer array		X
citation_list				integer array		X
data				integer
contact_id				integer array		X
slope		0°	90°	integer
aspect				varchar	16
depth				double precision
offset				double precision
date_start				timestamp
date_end				timestamp
datasets_count				integer
md_comleteness				double precision
timezone				timezone

Sites

An overview of variables and their attributes of sites stored into the database. A site is part of a country and contains temperature columns, active layers and ancillary measurements. They are represented by a bounding box enclosing the maximum extent of the contained objects.

Name	Comment	Data Type	Length	Mandatory	Primary	Foreign Key
id	Id column and primary key for site table	int4	4	X	X
country_id	Foreign key for the appropriate country	varchar(2)	2			X
name	Site name	varchar(64)	64	X
responsible_country	Responsible country managing the site.	varchar(64)	64	X
observation	Types of observation operate within one site	varchar(16)	16
description	Optional description or additionnal comment on one site.	free_text
created		timestamp		X
modified		timestamp		X
user_id	The id of the user that created the site	Int4		X
prior_measurement	Timestamp of the first measurement within a site	timestamp
last_measurement	Timestamp of the last measurement within a site	timestamp
longitude_max	Maximum longitude estent of a site	double
longitude_min	Minimum longitude extent of a site	double
latitude_max	Maximum latitude extent of a site	double
latitude_min	Minimum latitude extent of a site	double
area	Extent in km2 of the bounding box	double
bh__count	Countof the boreholes that belong to a site	int
al_count	Countof the Active layer Monitoring sites that belong to a site	int
datasets_bh_count	Number of time series of all the boreholes within a site	int
datasets_al_count	Number of time series of all the actice layer monitoring site within a sites	int
datasets_count	Total number of datasets for a site	int

Countries

An overview of variables and their attributes of countries stored into the database. Contains all countries which are part of the GTN-P network.

Name	Comment	Data Type	Length	Mandatory	Primary	Foreign Key
id		int4	4	X	X
code	Primary key and ISO 3166-1 code for countries.	varchar(2)	2	X
name	Countries name.	varchar(30)	30	X
permafrost	Countries having permafrost : t/f	bool		X
created		timestamp without time zone		X
modified		timestamp without time zone		X
Sites_count	Sum of the sites that belong to a country	integer

Attributes

Accessibilities

An overview of variables and their attributes of accessibilities of boreholes/active layer monitoring sites representing the way to physically access the spatial object.

Name	Comment	Data Type	Length	Mandatory	Primary	Foreign Key
id	Id column and primary key of the accessibility table	int4	4	X	X
borehole_id	Foreign key for the appropriate temperature column.	int4	4			X
activelayer_id	Foreign key for the appropriate active layer.	int4	4			X
ancillary_id	Foreign key for the appropriate ancillary measurement.	int4	4			X
type	Type of locomotion in order to access the spatial object.	varchar(256)	256
distance	Distance form road.	float8	8

Observations

An overview of variables and their attributes of ancillary observations that are made at the spatial object location (boreholes/active layer monitoring sites).

Name	Comment	Data Type	Length	Mandatory	Primary	Foreign Key
id	Id column and primary key for the observation table	int4	4	X	X
site_id	Foreign key for the appropriate site	int4	4			X
borehole_id	Foreign key for the appropriate temperature column,	int4	4			X
activelayer_id	Foreign key for the appropriate active layer.	int4	4			X
ancillary_id	Foreign key for the appropriate ancillary measurement,	int4	4			X
vegetation	Description of the vegetation	free_text
hydrology	Decription of the hydrology	free_text
landform	Description of the landform	free_text
lithology	Description of the lithology	free_text
morphology	Description of the morphology	free_text
description	Comments about observations	free_text
vegetation_name	Description of the vegetation from the control vocabulary	varchar (from list)	31	X

Disturbances

An overview of variables and their attributes of disturbances affecting the measurements at the spatial object location (boreholes/active layer monitoring sites).

Name	Comment	Data Type	Length	Mandatory	Primary	Foreign Key
id	Id column and primary key for disturbance table.	int4	4	X	X
temperature_column_id	Foreign key for the appropriate temperature column,	int4	4			X
active_layer_system_id	Foreign key for the appropriate active layer.	int4	4			X
ancillary_measurement_id	Foreign key for the appropriate ancillary measurement,	int4	4			X
type	Type of disturbances affecting the measurements.	varchar(256)	256
distance	Distance from the source of heat.	float8	8
type2	Others disturbances affecting the measurements.	varchar(256)	256
distance2	Distance from the other source of heat.	float8	8

References

An overview of variables and their attributes for publications, articles and bibliography associated to the spatial objects (boreholes/active layer monitoring sites) and the datasets associated.

Name	Comment	Data Type	Length	Mandatory	Primary	Foreign Key
id	Id column and primary key for the reference table	int4	4	X	X
borehole_id	Foreign key for the appropriate temperature column,	int4	4			X
activelayer_id	Foreign key for the appropriate active layer.	int4	4			X
ancillary_id	Foreign key for the appropriate ancillary measurement,	int4	4			X
content	Bibliographic references for the appropriate spatial object	free_text
created		Timestamp		X
modified		Timestamp		X

Pictures

An overview of variables and their attributes for photos or videos associated to spatial objects (boreholes/active layer monitoring sites).

Name	Comment	Data Type	Length	Mandatory	Primary	Foreign Key
id	Id column and primary key of media table	int4		X	X
activelayer_id	Foreign key for the appropriate active layer	int4				X
borehole_id	Foreign key for the appropriate temperature column,	int4				X
ancillary_id	Foreign key for the appropriate ancillary measurement,	int4				X
created		timestamp		X
modified		timestamp		X
filename	url address for the appropriate picture	varchar(256)	256	X
name		varchar	63	X
description		varchar	255

SEOs

Variables

Datatypes

An overview of variables and their attributes for datatypes associated to spatial objects (boreholes/active layer monitoring sites).

Name	Comment	Data Type	Length	Mandatory	Primary
id	Id column and primary key for the data type cv table	int4	4	X	X
name	Terms naming the data types	varchar(256)	256
definition	Definiton of the data type control vocabulary	text

Methods

Name	Comment	Data Type	Length	Mandatory	Primary
id	Id column and primary key for the method table	int4	4	X	X
method_description	Text description of each method	text
name	Variable corresponding to the method	varchar(64)	64
variable_id		int4	4	X

Variables

Name	Comment	Data Type	Length	Mandatory	Primary
id	Id column and primary key for variable name control vocabulary	int4	4	X	X
variable_term	Terms designating variable name control vocabulary	varchar(256)	256
variable_definition	Definition of variable name control vocabulary	text
variable_type	Type of variable to be associated to an unit	varchar(64)	64

Units

Name	Comment	Data Type	Length	Mandatory	Primary
id	Id column and primary key for the unit table	int4	4	X	X
name	Unit name	varchar(255)	255
units_type	Unit type	varchar(255)	255
variable_id	Variable corresponding to this unit	varchar(255)	255

Frequencies

Data

Datasets

Contains the datasets which are a consistent time series of data sharing together the same variables, data policy and quality control check.

Name	Comment	Minimum Value	Maximum Value	Data Type	Length	Mandatory	Primary	Foreign Key
id	data measure id			int4	4	X	X
ancillary_id	Foreign key for the appropriate ancillary measurement,			int4	4			X
borehole_id	Foreign key for the appropriate temperature column			int4	4			X
activelayer_id	Foreign key for the appropriate active layer			int4	4			X
description	optional description for the data measure pack			varchar(256)	256
policy				int4	4			X
quality				int4	4			X
resolution				float8	8
medium				varchar	8	X
variable_id				integer		X		X
method_id				integer		X		X
datatype_id				integer		X		X
frequency_id				integer		X		X
user_id				integer		X
min_value				double precision		X
max_value				double precision		X
avg_value				double precision		X
date_start				timestamp		X
date_end				timestamp		X
data	Number of measurements for the dataset			integer		X
nulls	Number of null values			integer		X
country_auth				integer array		X
collections_count	Count of collections within a datasets			integer
max_depth				float8	8
day_count				varchar(64)	64
created				timestamp
modified				timestamp

Data Collections

Represents the successive submissions of data values made into a dataset

Name	Comment	Minimum Value	Maximum Value	Data Type	Length	Mandatory	Primary	Foreign Key
id				int4	4	X	X
dataset_id	dataset id			int4	4	X		X
description	Description of the data collection			text
date_start	The first date of the data collection			timestamp		X
date_end	The last date of the data collection			timestamp
published	Yes or No			bool		X
created				timestamp		X
modified				timestamp		X
name				varchar	127	X
data	Count of measurement			integer		X
nulls	count of null values			integer		X
perc		0	100	integer		X
user_id				integer		X
min_value				double precision		X
max_value				double precision		X
avg_value				double precision		X

Values

Contains the data value

Name	Comment	Minimum Value	Maximum Value	Data Type	Length	Mandatory	Primary	Foreign Key
id				int4	4	X	X
collection_id				int4	4	X		X
value	the value for the data			float8	8
localdate	the date value for the row			timestamp		X
depth				int4	4
offset_x	The X coordinate of the sensor that measured the value. Applies only to active layers			float8	8
offset_y	The Y coordinate of the sensor that measured the value. Applies only to active layers			float8	8
offset_z	The depth of the sensor that measured the value			float8	8
surface	Satellite parameter			timestamptz
cloud	Satellite parameter			int4	4
quality	Satellite parameter	0	100	integer

Metadata (section in progress)

- Borehole

- Active Layer

Business rules

In progress: Tables, Relationships, Business Rules, Data Type, Data Range, Functions/Triggers/View, Ancillary Spatial Layers

ISSN 2410-2385
Key title: GTN-P database

GTNP Database

Global Terrestrial Network for Permafrost (GTN-P) - Data Modelisation and Use Cases

News

GTN-P Workshop Report - EUCOP 2023...

GTN-P Strategy and Implementation...

Measurement Recommendations and...

GTN-P at RCOP

GTN-P meeting 2021

Contact

Intranet

Data Modelisation and Use Cases

Data Management System Use Cases for boreholes, CALM site and related variables

General use without privileges

GTN-P registration, login and logout

General database search engine

Retrieve and sort raw data into the index page

Prepare raw data into a downloadable archive

Prepare geographic information layers into downloadable files

Retrieve and sort data into the graphic system

Implement geographic information layers into the cartographic system

Data input by the field scientist

Tools for decision making

Physical Data Model

Objects

Ancillaries

Boreholes

Active layer monitoring sites

Sites

Countries

Attributes

Accessibilities

Observations

Disturbances

References

Pictures

Variables

Datatypes

Methods

Variables

Units

Data

Datasets

Data Collections

Values

Business rules