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.

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.

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

 

 

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

 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      

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

Strategy and Implementation Plan