52°North Sensor Observation Service 5.x Database model


This page describes the 52°North Sensor Observation Service 5.x database model. The database model is divided into two major profiles, the Simple and the Transactional profile.

For better readability the table and column names contain uppercase and lowercase. Dependening on the DBMS the names have only uppercase (Oracle) or lowercase (PostgreSQL, MySQL). Due to the limitation of 30 characters for names in Oracle, some names are abbreviated. Documentation on how you can adapt the Hibernate mappings to map the SOS on your own database model is available, too.

Mapping type PostgreSQL type Oracle type MySQL type MS SQL Server
long bigint number(19,0) bigint(20) -
string varchar(255) varchar2(255 char) varchar(255) -
timestamp timestamp without time zone timestamp datetime -
blob oid blob longblob -
integer int4 number(10,0) int(11) -
big_decimal numeric(19,2) number(19,2) decimal(19,2) -
double double precision double precision double -
short smallint smallint smallint -
org.hibernate.type.StringClobType text clob longtext -
org.hibernate.spatial.GeometryType geometry SDO_GEOMETRY geometry -

General remarks

Conventions

convention descriptionSorted ascending example
name _ between words sampling_time_end
not-null columns columns marked as not null are required and should be mapped as formula or the property observation.vertical_from, observation.vertical_to
foreign key column fk_ + ( additional information +) referenced table name + _id fk_unit_id, fk_first_observation_id
index idx_ + column name idx_sampling_time_end
sampling_time properties if only one timestamp column is available, you can map the properties to one column with insert/update=false for one property
primary key column table name + _id dataset_id
unique constraint un_ + table name + column name/postfix un_feature_identifier, un_dataset_identity (multiple columns)

id fields

  • internally used identifiers
  • used for creating associations between tables

identifier fields

  • identifiers which are queryable in the SOS operations
  • listed in the Capabilities

Simpe database model

The Simpe database model contains the minimal required database structure to run the 52°North SOS 5.x, Sensor Web Server Helgoland.

ER-Diagram

Simple database model

Download link: db_model_110_simple.svg

Tables

Table Sequence
category category_seq
category_i18n category_i18n_seq
datase dataset_seq
dataset_i18n dataset_i18n_seq
dataset_reference  
feature feature_seq
feature_hierarchy -
feature_i18n feature_i18n_seq
observation observation_seq
observation_i18n observation_i18n_seq
offering offering_seq
offering_i18n offering_i18n_seq
phenomenon phenomenon_seq
phenomenon_i18n phenomenon_i18n_seq
platform platform_seq
platform_i18n platform_i18n_seq
procedure procedure_seq
procedure_i18n procedure_i18n_seq
unit unit_seq
unit_i18n unit_i18n_seq
value_profile value_profile_seq
value_profile_i18n value_profile_i18n_seq

Transactional database model

The Transactional database model contains the required database structure to run the 52°North SOS 5.x, Sensor Web Server Helgoland and the Sensor Web Server STA.

ER-Diagram

Transactional database model

Download link: db_model_110_transactional.svg

Additional Tables

Table Sequence
codespace codespace_seq
composite_phenomenon -
dataset_parameter -
datastream datastream_seq
datastream_dataset -
datastream_i18n datastream_i18n_seq
feature_parameter  
format format _seq
historical_location historical_location_seq
location location_seq
location_historical_location -
location_i18n location_i18n_seq
observation_parameter -
offering_feature_type -
offering_hierarchy -
offering_observation_type -
offering_related_feature -
parameter parameter_seq
platform_parameter -
procedure_hierarchy -
procedure_history -
related_dataset -
related_feature related_feature_seq
related_observation  
result_template result_template_seq
service service_seq
thing_location -
value_blob -

eReporting database model

The eReporting database model contains the required database structure to run the 52°North SOS 5.x to provide AQD e-Reporting data.

ER-Diagram

e-Rporting database model

Download link: db_model_110_ereporting.svg

Additional Tables

Table Sequence
assessment_type -
ereporting_quality ereporting_quality_seq
network network_seq
sampling_point sampling_point_seq
station station_seq

Extended model to support Samplings/MeasuringPrograms

The Extended model to support Samplings/MeasuringPrograms adds the required database structure to provide samplings and measuring programs via Sensor Web Server Helgoland.

ER-Diagram

Download link simple: db_model_110_simple_sampling.svg

Download link transactional: db_model_110_transactional_sampling.svg

Download link ee-Reporting: db_model_110_ereporting_sampling.svg

Additional Tables

Table Sequence
measuring_program measuring_program_seq
measuring_program_dataset -
measuring_program_i18n measuring_program_i18n_seq
sampling sampling_seq
sampling_dataset -
sampling_i18n sampling_i18n_seq

Extended feature concept

The Extended feature concept adds the required database structure to provide WaterML MontitoringPoint data.

Additional tables:

Table Sequence
Topic revision: r3 - 02 Apr 2020, EikeJuerrens
Legal Notice | Privacy Statement


This site is powered by FoswikiCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Wiki? Send feedback