Gaia Data Models

This is the specification document for the gaiaDB Data Model. This data model is still under development and likely to change. Each table is represented with a high-level description and ETL conventions that should be followed. This is continued with a discussion of each field in each table, any conventions related to the field, and constraints that should be followed (like primary key, foreign key, etc). Should you have questions please feel free to visit the forums or the github issue page.

data_source

Access DDL scripts here

Table Description

This table contains records that catalog external (or local) web-hosted entities. All source data in gaiaDB must be referenced in this table.

User Guide

NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA

ETL Conventions

NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA

Gaia Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
data_source_uuid int4 Yes Yes No
org_id varchar(100) Yes No No
org_set_id varchar(100) Yes No No
dataset_name varchar(100) Yes No No
dataset_version varchar(100) Yes No No
geom_type varchar(100) No No No
geom_spec text No No No
boundary_type varchar(100) No No No
has_attributes int4 No No No
download_method varchar(100) Yes No No
download_subtype varchar(100) Yes No No
download_data_standard varchar(100) Yes No No
download_filename varchar(100) Yes No No
download_url varchar(255) Yes No No
download_auth varchar(100) No No No
documentation_url varchar(255) No No No

variable_source

Access DDL scripts here

Table Description

This table contains records that describe the distinct variables in a data source enabling downstream data integrations. All variables from attribute source data must be catalogued in this table.

User Guide

NA NA NA NA NA NA

ETL Conventions

NA NA NA NA NA NA

Gaia Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
variable_source_id serial4 Yes Yes No
geom_dependency_uuid int4 No No Yes data_source
variable_name varchar Yes No No
variable_desc text Yes No No
data_source_uuid int4 Yes No Yes data_source
attr_spec text Yes No No

attr_index

Access DDL scripts here

Table Description

A programmatically derived index table of all the attribute source datasets included in the data_source table.

User Guide

NA NA NA NA NA NA

ETL Conventions

NA NA NA NA NA NA

Gaia Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
attr_index_id numeric Yes Yes No
variable_source_id numeric Yes No Yes variable_source
attr_of_geom_index_id numeric Yes No Yes geom_index
database_schema varchar(255) Yes No No
table_name varchar(255) Yes No No
data_source_id numeric Yes No Yes data_source

geom_index

Access DDL scripts here

Table Description

A programmatically derived index table of all the geometry source datasets included in the data_source table.

User Guide

NA NA NA NA NA NA NA NA NA

ETL Conventions

NA NA NA NA NA NA NA NA NA

Gaia Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
geom_index_id numeric Yes Yes No
data_type_id numeric No No No
data_type_name varchar(255) Yes No No
geom_type_concept_id numeric No No Yes concept
geom_type_source_value varchar(255) No No No
database_schema varchar(255) Yes No No
table_name varchar(255) Yes No No
table_desc varchar(255) Yes No No
data_source_id numeric Yes No Yes data_source

attr_template

Access DDL scripts here

Table Description

This table is a template for the standardized attribute table that get created.

User Guide

NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA

ETL Conventions

NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA

Gaia Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
attr_record_id serial4 Yes Yes No
geom_record_id int4 Yes No Yes geom_template
variable_source_record_id int4 Yes No Yes variable_source
attr_concept_id int4 No No Yes concept
attr_start_date date Yes No No
attr_end_date date Yes No No
value_as_number float8 No No No
value_as_string varchar No No No
value_as_concept_id int4 No No Yes concept
unit_concept_id int4 No No Yes concept
unit_source_value varchar No No No
qualifier_concept_id int4 No No Yes concept
qualifier_source_value varchar No No No
attr_source_concept_id int4 No No Yes concept
attr_source_value varchar Yes No No
value_source_value varchar Yes No No

geom_template

Access DDL scripts here

Table Description

This table is a template for the standardized geometry tables that get created.

User Guide

NA NA NA NA NA NA NA

ETL Conventions

NA NA NA NA NA NA NA

Gaia Field User Guide ETL Conventions Datatype Required Primary Key Foreign Key FK Table FK Domain
geom_record_id serial4 Yes Yes No
geom_name varchar Yes No No
geom_source_coding varchar Yes No No
geom_source_value varchar Yes No No
geom_wgs84 geometry No No No
geom_local_epsg int4 Yes No No
geom_local_value geometry Yes No No