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	description	example
name	_ between words	sampling_time_end
primary key column	table name + _id	dataset_id
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
unique constraint	un_ + table name + column name/postfix	un_feature_identifier, un_dataset_identity (multiple columns)
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
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

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

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

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

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