52°North Sensor Observation Service 4.x Database model

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

DEPRECATED! The Spatial Filtering Profile profile is an extented mapping to support the SOS 2.0 Spatial Filtering profile. Now the sampling geometry, used for the Spatial Filtering Profile, is stored in the observation table.

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. SensorObservationServiceHibernateMapping

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	-
org.hibernate.type.TrueFalseType	char(1)	char(1 char)	char(1)	-
org.hibernate.type.StringClobType	text	clob	longtext	-
org.hibernate.spatial.GeometryType	geometry	SDO_GEOMETRY	geometry	-

General remarks

id fields

• internally used identifiers
• used for creating associations between tables
• when inserting new entries into a table the id of the next value shall be generated through the appropriate database function: e.g. in case of PostgreSQL nextval(‚sequence_name‘) Please refer to the following table showing the relevant sequence_names

Table	Sequence	Profile
codespace	codespaceId_seq	Core
featureOfInterest	featureOfInterestId_seq	Core
featureOfInterestType	featureOfInterestTypeId_seq	Core
observableProperty	observablePropertyId_seq	Core
observationConstellation	observationConstellationId_seq	Core
observation	observationId_seq	Core
observationTpe	observationtypeid_seq	Core
offering	offeringId_seq	Core
parameter	parameterId_seq	Core
procedureDescriptionFormat	procDescFormatId_seq	Core
procedure	procedureId_seq	Core
relatedObservation	relatedobservationid_seq	Core
series	seriesid_seq	Core
unit	unitId_seq	Core
relatedFeature	relatedFeatureId_seq	Transactional
relatedFeatureRole	relatedFeatureRoleId_seq	Transactional
resultTemplate	resultTemplateId_seq	Transactional
validProcedureTime	validProcedureTimeId_seq	Transactional
seriesMetadata	metadataid_seq	Series Metadata
seriesRelation	seriesrelationid_seq	Series Metadata
address	addressid_seq	Extended Feature Concept
contact	contactid_seq	Extended Feature Concept
coordinatesystemaxis	coordsysaxisid_seq	Extended Feature Concept
domainofvalidity	domofvalidityid_seq	Extended Feature Concept
exextent	exextentid_seq	Extended Feature Concept
onlineresource	onlineresourceid_seq	Extended Feature Concept
responsibleparty	responsiblepartyid_seq	Extended Feature Concept
role	roleid_seq	Extended Feature Concept
telephone	telephoneid_seq	Extended Feature Concept
verticalcrs	verticalcrsid_seq	Extended Feature Concept
verticalcs	verticalcsid_seq	Extended Feature Concept
verticaldatum	verticaldatumid_seq	Extended Feature Concept
verticalexextent	verticalexextentid_seq	Extended Feature Concept

identifier fields

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

Core database model

The Core database model contains the minimal required database structure to run the 52°North SOS 4.x.

Series database model

This database model contains the core and transactional tables:

Further diagrams:

• Core series database model contains only the minimum required tables
  • SOS 4.4.3 core series database model as svg.
• Full series database model contains the core, transactional, i18n, series metadata and extended feature tables:
  • SOS 4.4.3 full series database model as svg.

Older database models:

• SOS 4.4.x development 2016-022 series concept database model as svg
• SOS 4.3.x series concept database model as svg
• SOS 4.3.x series concept database model as png

Database model was created wiht DbVisualizer

codespace and codespacename columns

The tables for the main SOS objects Procedure, ObservableProperty, FeatureOfInterest, Offering, Observation, and Series have the same base structure which includes the codespace and the codespacename columns.

The codespace is the codespace for the identifier (gml:identifier) and the codespacename is the codespace for thehttps://wiki.52north.org/bin/edit/SensorWeb/SensorObservationServiceIVDatabaseModel#save name (gml:name). Both GML elements have a codeSpace which may be different. The codespacename can be left blank because the codespace for the name is not required.

codespace table

Mapping file: Codespace.hbm.xml

Description:

Name	Type	Constraint	Description
codespaceid	long	PK	id of the codespace
codespace	string	Not Null, unique	codespace value, e.g. http://www.opengis.net/def/nil/OGC/0/unknown

compositephenomenon table (Optional)

Mapping file: ObservableProperty.hbm.xml

Description: Phenomenon hierarchy, e.g phenomenon is "weather" and the sub phenoma are "temperature", "wind_speed", "wind_direction"

Name	Type	Constraint	Description
parentObservablePropertyId	long	PK, FK, Not Null	the parent observable property, e.g. weather, pointing to the entry in the observableProperty table
childObservablePropertyId	long	PK, FK, Not Null	the child observable property, e.g. temperature, wind_speed, pointing to the entry in the observableProperty table

featureOfInterest table

Mapping file: FeatureOfInterest.hbm.xml

Description: the geometries of the observations

Name	Type	Constraint	Description
featureOfInterestId	long	PK	id of the featureOfInterest
hibernatediscriminator	string	Not Null	Valid values are null/empty, Specimen and WmlMonitoringPoint
featureofinteresttypeid	long	FK, Not Null	the type of the featureOfInterest, pointing to the entry in the featureOfInterestType table
identifier	string	unique	the identifier of the feature of interest, queryabel parameter (this corresponds to the names of the measurement stations, a short name or a number)
codespace	long	FK	the codespace for the identifier, pointing to the entry in the codespace table
name	String	-	this corresponds to the names of the measurement stations
codespaceName	long	FK	the codespace for the name, pointing to the entry in the codespace table
description	string	-	the textual description of this feature
geom	Geometry	-	the geometry of the station (composed of the “latitude” and “longitude”)
descriptionXml	String	-	XML description of the feature, used when transactional is supported
url	String	unique	Reference URL to the feature of interest if it is stored in another service, e.g. WFS

featureOfInterestType table

Mapping file: FeatureOfInterestType.hbm.xml

Description: the type of the geometries of the observations

Name	Type	Constraint	Description
featureOfInterestTypeId	long	PK	id of the featureOfInterestType
featureOfInterestType	string	Not Null, unique	featureOfInterestType value, e.g. http://www.opengis.net/def/samplingFeatureType/OGC-OM/2.0/SF_SamplingPoint

featureRelation table (Optional)

Mapping file: FeatureOfInterest.hbm.xml

Description: the relation between features, e.g. parent and children

Name	Type	Constraint	Description
parentfeatureid	long	PK, FK	id of the parent feature
childfeatureid	long	PK, FK	id of the child feature

observableProperty table

Mapping file: ObservableProperty.hbm.xml

Description: parameters that are observed.

Name	Type	Constraint	Description
observablePropertyId	long	PK	id of the observableProperty
identifier	string	unique	the identifier of the observable property (phenomenon), queryabel parameter (e.g. the variable name “water_temp”)
codespace	long	FK	the codespace for the identifier, pointing to the entry in the codespace table
name	String	-	the name of the observable property
codespaceName	long	FK	the codespace for the name, pointing to the entry in the codespace table
description	string	-	the long_name attribute of the observed property (phenomenon) (e.g. “water temperature”)
disabled	character	Not Null	Indicator if this observableProperty should not be provided by the SOS
hiddenchild	character	Not Null	Indicator if this observableProperty is a hidden child observableProperty, used for OM_ComplexObservation)

observationConstellation table

Mapping file: ObservationConstellation.hbm.xml

Description: Look-up table to check if the observation type of the InsertObservation request is valid for the constellation procedure, observableProperty, and offering.

Name	Type	Constraint	Description
observationConstellationId	long	PK, Not Null	id of the observationConstellation
observablePropertyId	long	FK, Not Null	pointing to the parameter whose value is being observed, pointing to the entry in the observableProperty table
procedureId	long	FK, Not Null	pointing to the procedure that has generated the observed value, pointing to the entry in the procedure table
observationTypeId	long	FK	pointing to the observation type that has assigned to the the observed value, pointing to the entry in the observationType table
offeringId	long	FK, Not Null	id of the offering, pointing to the entry in the offering table
deleted	org.hibernate.type.TrueFalseType	Not Null, default: 'F'	used for transactional DeleteSensor operation, indicates if the observation constellation (related procedure) is deleted or not
hiddenChild	org.hibernate.type.TrueFalseType	Not Null, default: 'F'	indicates if the related procedure of the observation constellation is a child procedure, necessary for SOS 2.0 Capabilities

Complex Constraint: unique combination of observablePropertyId, procedureId, and offeringId

observationType table

Mapping file: ObservationType.hbm.xml

Description: Observation type

Name	Type	Constraint	Description
observationTypeId	long	PK, Not Null	id of the observationType
observationType	string	Not Null, unique	observationType value, e.g. http://www.opengis.net/def/observationType/OGC-OM/2.0/OM_Measurement

sensorSystem table (Optional)

Mapping file: Procedure.hbm.xml

Description: Procedure hierarchy

Name	Type	Constraint	Description
parentSensorId	long	PK, FK, Not Null	the parent procedure/sensor, e.g. a sensor system, pointing to the entry in the procedure table
childSensorId	long	PK, FK, Not Null	the child procedure/sensor, e.g. component of the system, pointing to the entry in the procedure table

series table

Mapping file: Series.hbm.xml, SeriesInfo.hbm.xml

Description: Describes a series, a combination of featureOfInterest, observableProperty and procedure

Name	Type	Constraint	Description
seriesId	long	PK, Not Null	id of the series
featureOfInterestId	long	FK, Not Null	pointing to the series location, pointing to the entry in the featureOfInterest table
observablePropertyId	long	FK, Not Null	pointing to the parameter whose value is being observed in this series, pointing to the entry in the observableProperty table
procedureId	long	FK, Not Null	pointing to the procedure that has generated the observed values of this series, pointing to the entry in the procedure table
offeringId	long	FK, Not Null	pointing to the offering that the observed values are related to, pointing to the entry in the offering table
deleted	org.hibernate.type.TrueFalseType	Not Null, default: 'F'	used for transactional DeleteSensor operation, indicates if the observation constellation (related procedure) is deleted or not
published	org.hibernate.type.TrueFalseType	Not Null, default: 'T'	indicates if the series is published or not, currently used in GetObservation and GetDataAvailability. If flag is false, series is not included in the response (%N&since 4.1.4)
firstTimeStamp	timestamp	-	update via transactional profile, used by the REST-API
lastTimeStamp	timestamp	-	update via transactional profile, used by the REST-API
firstNumericValue	double	-	update via transactional profile, used by the REST-API
lastNumericValue	double	-	update via transactional profile, used by the REST-API
unit	long	FK	update via transactional profile, used by the REST-API
seriestype	string	-	update via transactional profile, used by the REST-API

Complex Constraint: unique combination of featureOfInterestId, observablePropertyId, and procedureId

(series) observation table

Mapping file: SeriesObservation.hbm.xml, SeriesObservationInfo.hbm.xml, SeriesObservationTime.hbm.xml

Description: Observation table with a relation to the series table instead of relation to the featureOfInterest, observableProperty and procedure tables.

Name	Type	Constraint	Description
observationId	long	PK, Not Null	id of the observation
seriesId	long	FK, Not Null	pointing to the series, pointing to the entry in the series table
phenomenonTimeStart	timestamp	Not Null	timestamp for which the measured value applied
phenomenonTimeEnd	timestamp	Not Null	can be identical to phenomenonstarttime (unless the measured value belongs to a time period; in this case phenomenonTimeStart and phenomenonendtime can be different)
resultTime	timestamp	Not Null	the timestamp when the result was published, can be identical to phenomenonTimeStart
identifier	string	unique	identifier of the observation, queryable in GetObservationById
codespace	long	FK	the codespace for the identifier, pointing to the entry in the codespace table
name	String	-	the name of the observation
codespaceName	long	FK	the codespace for the name, pointing to the entry in the codespace table
description	string	-	the textual description of this observation
deleted	org.hibernate.type.TrueFalseType	Not Null, default: 'F'	used for transactional DeleteObservation operation, indicates if the observation is deleted or not
validTimeStart	timestamp	-	starting time for the period this observation is valid, e.g. forecasting
validTimeEnd	timestamp	-	end time for the period this observation is valid , e.g. forecasting
unitId	long	FK	pointing to the unit of measurement of the observation, pointing to the entry in the unit table
samplingGeometry	org.hibernate.spatial.GeometryType	Not Null	the exact measurement location if the feature of interest describes an area, e.g. a lake

Complex Constraint: unique combination of seriesId, phenomenonTimeStart, phenomenonTimeEnd, resultTime

observationHasOffering

Mapping file: Observation.hbm.xml, ObservationInfo.hbm.xml

Description: Relation table between an observation and one or more offerings

Name	Type	Constraint	Description
observationId	long	PK, FK, Not Null	id of the observation, pointing to the entry in the observation table
offeringId	long	PK, FK, Not Null	id of the offering, pointing to the entry in the offering table

offering table

Mapping file: Offering.hbm.xml

Description: needed for structuring the data in a SOS server

Name	Type	Constraint	Description
offeringId	long	PK	id of the offering
hibernatediscriminator	character	Not Null	only needed for internal purposes
identifier	string	unique	the identifier of the offering, queryabel parameter (e.g. “water_temperature_sensor_timeseries”)
codespace	long	FK	the codespace for the identifier, pointing to the entry in the codespace table
name	String	-	the name of this offering
codespaceName	long	FK	the codespace for the name, pointing to the entry in the codespace table
description	string	-	the textual description of this offering
disabled	character	Not Null	Indicator if this offering should not be provided by the SOS

offeringAllowedObservationType table (Optional)

Mapping file: Offering.hbm.xml

Description: Look-up table for the observation types which are valid for the offering. The valid observation types were inserted during the InsertSensor request.

Name	Type	Constraint	Description
offeringId	long	PK, FK, Not Null	id of the offering, pointing to the entry in the offering table
observationTypeId	long	PK, FK, Not Null	pointing to the observation type that has assigned to the the offering, pointing to the entry in the observationType table

offeringRelation table (Optional)

Mapping file: Offering.hbm.xml

Description: the relation between offerings, e.g. parent and children

Name	Type	Constraint	Description
parentofferingid	long	PK, FK	id of the parent offering
childofferingid	long	PK, FK	id of the child offering

procedure table

Mapping file: Procedure.hbm.xml

Description: the processes thorough which the observed values were generated

Name	Type	Constraint	Description
procedureId	long	PK	id of the procedure
hibernatediscriminator	character	Not Null	only needed for internal purposes
procedureDescriptionFormatId	long	FK, Not Null	the description format for this procedure, pointing to the entry in the procedureDescriptionFormat table
identifier	string	unique	the identifier of the procedure, queryabel parameter e.g. “water_temperature_sensor”)
codespace	long	FK	the codespace for the identifier, pointing to the entry in the codespace table
name	String	-	the name of the procedure
codespaceName	long	FK	the codespace for the name, pointing to the entry in the codespace table
description	string	-	the textual description of this procedure
deleted	org.hibernate.type.TrueFalseType	Not Null, default: 'F'	used for transactional DeleteSensor operation, indicates if the procedure is deleted or not
disabled	org.hibernate.type.TrueFalseType	Not Null, default: 'F'	Indicator if this procedure should not be provided by the SOS.
descriptionFile	org.hibernate.type.StringClobType	-	field for full (XML) encoded procedure description or link to a procedure description file
referenceflag	org.hibernate.type.TrueFalseType	default: 'F'	used by the REST-API
typeof	bigint	-	Relation/foreign key to procedure table. Optional, contains procedureId if this procedure is typeOf another procedure
istype	org.hibernate.type.TrueFalseType	default: 'F'	Flag to indicate that this procedure is a type description, has no observations.
isaggregation	org.hibernate.type.TrueFalseType	default: 'F'	Flag to indicate that this procedure is an aggregation (e.g. System, PhysicalSystem).
mobile	org.hibernate.type.TrueFalseType	default: 'F'	Flag to indicate that this procedure is stationary (false) or mobile (true). Optional
insitu	org.hibernate.type.TrueFalseType	default: 'F'	Flag to indicate that this procedure is insitu (true) or remote (false). Optional

procedureDescriptionFormat table

Mapping file: ProcedureDescriptionFormat.hbm.xml

Description: the format in which procedures shall be described

Name	Type	Constraint	Description
procedureDescriptionFormatId	long	PK, Not Null	id of the procedureDescriptionFormat
procedureDescriptionFormat	string	Not Null, (future: unique)	procedureDescriptionFormat value, e.g. http://www.opengis.net/sensorML/1.0.1

relatedObservation table (Optional)

Mapping file: Offering.hbm.xml

Description: the relation between observations, e.g. parent and children

Name	Type	Constraint	Description
relatedobservationid	long	PK	id of the relatedObservation entry
observationid	long	FK	id of the observation
relatedobservation	long	FK	id of the related observation
role	string	-	Role of the relation
relatedurl	string	-	An external link to the related observation

unit table

Mapping file: Unit.hbm.xml

Description: units of measurement

Name	Type	Constraint	Description
unitId	long	PK	id of the unit
unit	string	Not Null, unique	unit value representing a unit of measurement (e.g. “units” attribute of the variable “water_temp” -> “C”)

...value tables

Mapping file: Observation.hbm.xml

Description: the measured value

Name	Type	Constraint	Description
observationId	long	PK, FK, Not Null	id of the observation, pointing to the entry in the observation table
value	see following table	-	that value that has been measured (e.g. the measured water temperature value)

concrete value tables with value type

table	value type	O&M observation type
blobValue	blob	-
booleanValue	org.hibernate.type.TrueFalseType	OM_TruthObservation
categoryValue	string	OM_CategoryObservation
countValue	integer	OM_CountObservation
geometryValue	org.hibernate.spatial.GeometryType	Om_GeometryObservation
numericValue	double	OM_Measurement
sweDataArrayValue	org.hibernate.type.StringClobType	OM_SWEDataArrayObservation
textValue	org.hibernate.type.StringClobType	OM_TextObservation

special value tables

complex value ( since 4.3.0)

The representation of the OM_ComplexObservation uses two tables as described in the following

complexvalue

Mapping file: Observation.hbm.xml

Description: the value table, required for Hibernate

Name	Type	Constraint	Description
observationId	long	PK, FK, Not Null	id of the observation, pointing to the entry in the observation table

compositeobservation

Mapping file: Observation.hbm.xml

Description: the relation between the observation and the child observations

Name	Type	Constraint	Description
observationId	long	PK, FK, Not Null	id of the observation, pointing to the entry in the observation table
childobservationid	long	PK, FK, Not Null	id of the child observation, pointing to the entry in the observation table

profile value ( since 4.4.0)

The representation of the profile observations, e.g. GWML 2.0 GeologyLog observation, uses two tables as described in the following

profilevalue

Mapping file: Observation.hbm.xml

Description: the profile value

Name	Type	Constraint	Description
observationId	long	PK, FK, Not Null	id of the observation, pointing to the entry in the observation table
fromlevel	double	-	the from level value
tolevel	double	-	the to level value
levelunitid	long	FK	pointing to the unit of measurement of the level, pointing to the entry in the unit table

profileobservation

Mapping file: Observation.hbm.xml

Description: the measured value

Name	Type	Constraint	Description
observationId	long	PK, FK, Not Null	id of the observation, pointing to the entry in the observation table
childobservationid	long	PK, FK, Not Null	id of the child observation, pointing to the entry in the observation table

...parameter tables (Optional) ( since 4.4.0)

Table	Mapping	Object
parameters	Observation.hbm.xml	observation
featureParameters	FeatureOfInterest.hbm.xml	featureOfinterest
seriesparameter	SeriesParameter.hbm.xml	series (only if Series Metadata is enabled)

Description: the relation table between the object and the parameters

Name	Type	Constraint	Description
parameterid	long	PK, FK, Not Null	id of the parameter
observation/featureofinterestid/seriesid	long	FK, Not Null	id of the observation/featureofinterest/series, pointing to the entry in the related table
name	string	Not Null	that value that has been measured (e.g. the measured water temperature value)

...parameterValue tables

Description: the parameter value tables

Name	Type	Constraint	Description
parameterid	long	PK, FK, Not Null	id of the parameter, pointing to the entry in the parameter table
value	see following table	-	that value of the parameter
unitid	long	FK	pointing to the unit of measurement of the level, pointing to the entry in the unit table

concrete parameter value tables with value type

Description: the parameter value tables

object	postfix
observation	...parametervalue
featureOfInterest	...featparamvalue
series table	...seriesparamvalue

table	value type
boolean...	org.hibernate.type.TrueFalseType
category...	string
count...	integer
numeric...	double
text...	org.hibernate.type.StringClobType
xml...	org.hibernate.type.StringClobType

Transactional database model

The Transactional database model extends the Core database model to support the OGC SOS 2.0 Transactional and Result Handling profile.

Add Transactional model schema

offeringAllowedFeatureType table

Mapping file: TOffering.hbm.xml

Description: Look-up table for the feature types which are valid for the offering. The valid feature types were inserted during the InsertSensor request.

Name	Type	Constraint	Description
offeringId	long	PK, FK, Not Null	id of the offering, pointing to the entry in the offering table
featureOfInterestTypeId	long	PK, FK, Not Null	pointing to the observation type that has assigned to the the offering, pointing to the entry in the featureOfInterestType table

offeringHasRelatedFeature table

Mapping file: TOffering.hbm.xml

Description: Relation table between an offering and one or more related features. The related features were inserted during ther InsertSensor request.

Name	Type	Constraint	Description
offeringId	long	PK, FK, Not Null	id of the offering, pointing to the entry in the offering table
relatedFeatureId	long	PK, FK, Not Null	pointing to the related feature that has assigned to the the offering, pointing to the entry in the relatedFeature table

relatedFeature table

Mapping file: RelatedFeature.hbm.xml

Description: Related feature table

Name	Type	Constraint	Description
relatedFeatureId	long	PK, Not Null	id of related feature
featureOfInterestId	long	FK, Not Null	pointing to the feature that describes this related feature, pointing to the entry in the featureOfInterest table

relatedFeatureHasRole table

Mapping file: RelatedFeature.hbm.xml

Description: Realtion table between a related feature and one or more roles.

Name	Type	Constraint	Description
relatedFeatureId	long	PK, Not Null	id of related feature
relatedFeatureRoleId	long	PK, Not Null	role of the related feature, pointing to the entry in the relatedFeatureRole table

relatedFeatureRole table

Mapping file: RelatedFeatureRole.hbm.xml

Description: Related feature roles table

Name	Type	Constraint	Description
relatedFeatureRoleId	long	PK, Not Null	id of related feature role
relatedFeatureRole	string	Not Null, unique	related feature role

resultTemplate table

Mapping file: ResultTemplate.hbm.xml

Description: Holds the result template information which are necessary for the result handling operations.

Name	Type	Constraint	Description
resultTemplateId	long	PK, Not Null	id of the result template
offeringId	long	Not Null	pointing to the offering the values should be add, pointing to the entry in the offering table
observablePropertyId	long	Not Null	pointing to the parameter whose value is being observed, pointing to the entry in the observableProperty table
procedureId	long	Not Null	pointing to the procedure that has generated the observed value, pointing to the entry in the procedure table
featureOfInterestId	long	Not Null	pointing to the measurement location, pointing to the entry in the featureOfInterest table
identifier	string	Not Null	identifier of the result template, necessary for InsertResult operation
resultStructure	org.hibernate.type.StringClobType	Not Null	Description how the result values are structured
resultEncoding	org.hibernate.type.StringClobType	Not Null	Description how the result values are encoded

validProcedureTime table

Mapping file: ValidProcedureTime.hbm.xml

Description: Stores the procedure descriptions which were inserted during an InsertSensor request or updated during a UpdateSensor request.

Name	Type	Constraint	Description
validProcedureTimeId	long	PK, Not Null	id of the validProcedureTime
procedureId	long	Not Null	pointing to the procedure this description relates to, pointing to the entry in the procedure table
procedureDescriptionFormatId	long	FK, Not Null	the description format for this procedure, pointing to the entry in the procedureDescriptionFormat table
startTime	timestamp	Not Null	start time, since the description is valid
endTime	timestamp	-	ent time, until the description is valid, for the currently valid description, this field is empty
descriptionXml	org.hibernate.type.StringClobType	Not null	field for full (XML) encoded procedure description

Multilingualism database model ( since 4.2.0)

Series metadata database model ( since 4.4.0) (Optional)

Mapping file: metadata/series/SeriesMetadata.hbm.xml

Description: Holds additional series metadata for different domains.

Name	Type	Constraint	Description
metadataid	long	PK, Not Null	Table primary key, used for relations
seriesid	long	FK, Not Null	Foreign Key (FK) to the related series. Contains "series".seriesId
identifier	string	Not Null	The identifier of the metadata value.
value	string	Not Null	The metadata value.
domain	string	Not Null	The metadata value domain, should be a XML namespace

Extended feature concept database model ( since 4.4.0)

The extended feature concept supports the storage of futher informatin of specialiezed featureofinterest.

The mapping are containe in the subfolder /feature

SF Specimen

specimen table (Optional)

Mapping file: specimen/specimen.hbm.xml

Description: Holds the additional information of a SQ specimen feature.

Name	Type	Constraint	Description
featureofinterestid	long	PK, Not Null	Foreign Key (FK) to the related featureOfInterest from the featureOfInterest table. Contains "featureOfInterest".featureOfInterestId
materialclass	string	-	Material class
samplingtimestart	timestamp	-	Time stamp when the specimen was started
samplingtimeend	timestamp	-	Time stamp when the specimen was stopped
samplingmethod	string	-	Sampling method as referenced link
size	string	double	Size of the specimen
sizeunitid	long	-	Foreign Key (FK) to the related unit of measure. Contains "unit".unitid. Optional
currentlocation	string	-	Current location of the specimen
specimentype	string	-	Specimen type

WaterML 2.0 Monitoring Point

Here the tables are described which are used for the addtionial information of a WaterML MonitoringPoint.

So that the WaterML MonitoringPoint can be recognized by Hibernate, the hibernatediscriminator column value of the featureofinterest table should be set to WmlMonitoringPoint.

address table

Mapping file: gmd/Address.hbm.xml

Description: You should define nilreason or href|role|arcrole|title|show|actuate|remoteschema for reference or the other fields for the full object

Name	Type	Constraint	Description
pkid	long	PK, Not Null	Table primary key, used for relations
nilreason	string	-	NilReason which would be set in XML if other fields are not defined. Values: inapplicable, missing, template, unknown and withheld
href	string	-	The xlink:href value
role	string	-	The xlink:role value
arcrole	string	-	The xlink:arcrole value
title	string	-	The xlink:title value
show	string	-	The xlink:show value
actuate	string	-	The xlink:actuate value
remoteschema	string	-	The xlink:remoteschema value
id	string	-	The id of this object
uuid	string	-	The uuid of this object
city	string	-	The city value of the address
administrativearea	string	-	The administrativearea value of the address
postalcode	string	-	The postalcode value of the address
country	string	-	The country value of the address

addressdeliverypoint table

Mapping file: gmd/Address.hbm.xml

Description: The delivery points of an address.

Name	Type	Constraint	Description
addressid	long	PK, FK, Not Null	Foreign Key (FK) to the related address from the address table. Contains "address.pkid"
deliverypoint	string	PK, Not Null	Delivery points of the related address

addressemail table

Mapping file: gmd/Address.hbm.xml

Description: The electronic mail address of an address

Name	Type	Constraint	Description
addressid	long	PK, FK, Not Null	Foreign Key (FK) to the related address from the address table. Contains "address.pkid"
electronicmailaddress	string	PK, Not Null	Electronic mail address of the related address

contact table

Mapping file: gmd/Contact.hbm.xml

Description: You should define nilreason or href|role|arcrole|title|show|actuate|remoteschema for reference or the other fields for the full object

Name	Type	Constraint	Description
pkid	long	PK, Not Null	Table primary key, used for relations
nilreason	string	-	NilReason which would be set in XML if other fields are not defined. Values: inapplicable, missing, template, unknown and withheld
href	string	-	The xlink:href value
role	string	-	The xlink:role value
arcrole	string	-	The xlink:arcrole value
title	string	-	The xlink:title value
show	string	-	The xlink:show value
actuate	string	-	The xlink:actuate value
remoteschema	string	-	The xlink:remoteschema value
id	string	-	The id of this object
uuid	string	-	The uuid of this object
phoneid	long	FK	the phone for the contact, pointing to the entry in the telephone table
addressid	long	FK	the address for the contact, pointing to the entry in the address table
onlineresourceid	long	FK	the online resource for the contact, pointing to the entry in the onlineresource table
hoursofservice	string	-	The hours of service
contactinstructions	sttring	-	The contact instructions

coordinatesystemaxis table

Mapping file: gml/CoordinateSystemAxis.hbm.xml

Description: You should define nilreason or href|role|arcrole|title|show|actuate|remoteschema for reference or the other fields for the full object

Name	Type	Constraint	Description
pkid	long	PK, Not Null	Table primary key, used for relations
nilreason	string	-	NilReason which would be set in XML if other fields are not defined. Values: inapplicable, missing, template, unknown and withheld
href	string	-	The xlink:href value
role	string	-	The xlink:role value
arcrole	string	-	The xlink:arcrole value
title	string	-	The xlink:title value
show	string	-	The xlink:show value
actuate	string	-	The xlink:actuate value
remoteschema	string	-	The xlink:remoteschema value
identifier	string	unique	the identifier of the object
codespace	long	FK	the codespace for the identifier, pointing to the entry in the codespace table
name	String	-	the name of the object
codespaceName	long	FK	the codespace for the name, pointing to the entry in the codespace table
description	string	-	the textual description of this object
remarks	string	-	Remarks of the object
axisabbrev	string	-	Required if XML object should be encoded
codespaceaxisabbrev	long		the codespace for the axisabbrev , pointing to the entry in the codespace table
axisdirection	string	-	Required if XML object should be encoded
codespaceaxisdirection	long	-	the codespace for the axisdirection , pointing to the entry in the codespace table
minimumvalue	double	-	The minimum double value of the object
maximumvalue	double	-	The maximum double value of the object
rangemeaning	string	-	The range meaning of the object
codespacerangemeaning	-		the codespace for the rangemeaning , pointing to the entry in the codespace table
unitid	long	FK	pointing to the unit of measurement of the object, pointing to the entry in the unit table

domainofvalidity table

Mapping file: gml/DomainOfValidity.hbm.xml

Description: You should define nilreason or href|role|arcrole|title|show|actuate|remoteschema for reference or the other fields for the full object

Name	Type	Constraint	Description
pkid	long	PK, Not Null	Table primary key, used for relations
nilreason	string	-	NilReason which would be set in XML if other fields are not defined. Values: inapplicable, missing, template, unknown and withheld
href	string	-	The xlink:href value
role	string	-	The xlink:role value
arcrole	string	-	The xlink:arcrole value
title	string	-	The xlink:title value
show	string	-	The xlink:show value
actuate	string	-	The xlink:actuate value
remoteschema	string	-	The xlink:remoteschema value
identifier	string	unique	the identifier of the object
codespace	long	FK	the codespace for the identifier, pointing to the entry in the codespace table
name	String	-	the name of the object
codespaceName	long	FK	the codespace for the name, pointing to the entry in the codespace table
description	string	-	the textual description of this object
exextentid	long	FK	The ExExtent of the domainOfValidity object, pointing to the entry in the exextent table

exextent table

Mapping file: gmd/ExExtent.hbm.xml

Description:

Name	Type	Constraint	Description
pkid	long	PK, Not Null	Table primary key, used for relations
id	string	-	The id of this object
uuid	string	-	The uuid of this object
description	string	-	The textual description of this object

exextentverticalext table

Mapping file: gmd/ExExtent.hbm.xml

Description: Relation table for the ExVerticalExtents that belongs to the ExExtent.

Name	Type	Constraint	Description
exextentid	long	PK, FK, Not Null	The ExExtent object, pointing to the entry in the exextent table
exverticalextentid	long	PK, FK, Not Null	The ExVerticalExtents of the ExExtent object, pointing to the entry in the verticalexextent table

onlineresource table

Mapping file: gmd/Onlineresource.hbm.xml

Description: The online resources used by the responsible party. You should define nilreason or href|role|arcrole|title|show|actuate|remoteschema for reference or the other fields for the full object

Name	Type	Constraint	Description
pkid	long	PK, Not Null	Table primary key, used for relations
nilreason	string	-	NilReason which would be set in XML if other fields are not defined. Values: inapplicable, missing, template, unknown and withheld
href	string	-	The xlink:href value
role	string	-	The xlink:role value
arcrole	string	-	The xlink:arcrole value
title	string	-	The xlink:title value
show	string	-	The xlink:show value
actuate	string	-	The xlink:actuate value
remoteschema	string	-	The xlink:remoteschema value
id	string	-	The id of this object
uuid	string	-	The uuid of this object
linkage	string	-	Required if XML object should be encoded, the linkage of the online resource
protocol	string	-	The protocol of the online resource
applicationprofile	string		The applicationprofile of the online resource
name	string	-	The name of the online resource
description	string	-	The description of the online resource
function	string	-	The function of the online resource

phonefacsimile table

Mapping file: gmd/Telephone.hbm.xml

Description: Contains the facsimile numbers belonging to the telephone

Name	Type	Constraint	Description
phoneid	long	PK, FK, Not Null	The phone object, pointing to the entry in the telephone table
facsimile	string	PK, Not Null	The facsimile number for the telephone object

phonevoice table

Mapping file: gmd/Telephone.hbm.xml

Description: Contains the voice numbers belonging to the telephone

Name	Type	Constraint	Description
phoneid	long	PK, FK, Not Null	The phone object, pointing to the entry in the telephone table
voice	string	PK, Not Null	The voice number for the telephone object

responsibleparty table

Mapping file: gmd/ResponsibleParty.hbm.xml

Description: The ResponsibleParty representation. You should define nilreason or href|role|arcrole|title|show|actuate|remoteschema for reference or the other fields for the full object

Name	Type	Constraint	Description
pkid	long	PK, Not Null	Table primary key, used for relations
nilreason	string	-	NilReason which would be set in XML if other fields are not defined. Values: inapplicable, missing, template, unknown and withheld
href	string	-	The xlink:href value
role	string	-	The xlink:role value
arcrole	string	-	The xlink:arcrole value
title	string	-	The xlink:title value
show	string	-	The xlink:show value
actuate	string	-	The xlink:actuate value
remoteschema	string	-	The xlink:remoteschema value
id	string	-	The id of this object
uuid	string	-	The uuid of this object
individualname	string	-	The individual name
organizationname	string	-	The organization name
positionname	string	-	The position name
contactinfo	long	FK	The related contact object, pointing to the entry in the pkid table
cirole	long	FK	The related role object, pointing to the entry in the pkid table

role table

Mapping file: gmd/Role.hbm.xml

Description: The roles used by responsible party (see http://www.isotc211.org/2005/resources/Codelist/gmxCodelists.xml#CI_RoleCode)

Name	Type	Constraint	Description
pkid	long	PK, Not Null	Table primary key, used for relations
codelist	string	Not Null	http://www.isotc211.org/2005/resources/Codelist/gmxCodelists.xml#CI_RoleCode
codelistvalue	string	Not Null	http://www.isotc211.org/2005/resources/Codelist/gmxCodelists.xml#CI_RoleCode values

telephone table

Mapping file: gml/Telephone.hbm.xml

Description: The telephone object used in contact. You should define nilreason or href|role|arcrole|title|show|actuate|remoteschema for reference or the other fields for the full object

Name	Type	Constraint	Description
pkid	long	PK, Not Null	Table primary key, used for relations
nilreason	string	-	NilReason which would be set in XML if other fields are not defined. Values: inapplicable, missing, template, unknown and withheld
href	string	-	The xlink:href value
role	string	-	The xlink:role value
arcrole	string	-	The xlink:arcrole value
title	string	-	The xlink:title value
show	string	-	The xlink:show value
actuate	string	-	The xlink:actuate value
remoteschema	string	-	The xlink:remoteschema value
id	string	-	The id of this object
uuid	string	-	The uuid of this object

verticalcrs table

Mapping file: gml/VerticalCRS.hbm.xml

Description: The VerticalCRS representation. You should define nilreason or href|role|arcrole|title|show|actuate|remoteschema for reference or the other fields for the full object

Name	Type	Constraint	Description
pkid	long	PK, Not Null	Table primary key, used for relations
nilreason	string	-	NilReason which would be set in XML if other fields are not defined. Values: inapplicable, missing, template, unknown and withheld
href	string	-	The xlink:href value
role	string	-	The xlink:role value
arcrole	string	-	The xlink:arcrole value
title	string	-	The xlink:title value
show	string	-	The xlink:show value
actuate	string	-	The xlink:actuate value
remoteschema	string	-	The xlink:remoteschema value
identifier	string	unique	the identifier of the object
codespace	long	FK	the codespace for the identifier, pointing to the entry in the codespace table
name	String	-	the name of the object
codespaceName	long	FK	the codespace for the name, pointing to the entry in the codespace table
description	string	-	the textual description of this object
remarks	string	-	Remarks of the object
verticalcs	ong	FK	The verticalCS of the verticalCRS, pointing to the entry in the verticalcs table
verticaldatum	ong	FK	The vertical datum of the verticalCRS, pointing to the entry in the verticaldatum table

verticalcrscope table

Mapping file: gml/VerticalCRS.hbm.xml

Description: The scopes of a verticalCRS

Name	Type	Constraint	Description
verticalcrsid	long	PK, FK, Not Null	Foreign Key (FK) to the related verticalCRS from the verticalCRS table. Contains "verticalcrs.pkid"
scope	string	PK, Not Null	Scope of the related verticalCRS

verticalcrsdomofval table

Mapping file: gml/VerticalCRS.hbm.xml

Description: Relation table for verticalCRS and domainOfValidity

Name	Type	Constraint	Description
verticalcrsid	long	PK, FK, Not Null	The verticalCRS object, pointing to the entry in the verticalcrs table
domainofvalidity	long	PK, FK, Not Null	The domainofvalidity of the verticalCRS object, pointing to the entry in the domainofvalidity table

verticalcs table

Mapping file: gml/VerticalCS.hbm.xml

Description: The representation of the VerticalCS objects. You should define nilreason or href|role|arcrole|title|show|actuate|remoteschema for reference or the other fields for the full object

Name	Type	Constraint	Description
pkid	long	PK, Not Null	Table primary key, used for relations
nilreason	string	-	NilReason which would be set in XML if other fields are not defined. Values: inapplicable, missing, template, unknown and withheld
href	string	-	The xlink:href value
role	string	-	The xlink:role value
arcrole	string	-	The xlink:arcrole value
title	string	-	The xlink:title value
show	string	-	The xlink:show value
actuate	string	-	The xlink:actuate value
remoteschema	string	-	The xlink:remoteschema value
identifier	string	unique	the identifier of the object
codespace	long	FK	the codespace for the identifier, pointing to the entry in the codespace table
name	String	-	the name of the object
codespaceName	long	FK	the codespace for the name, pointing to the entry in the codespace table
description	string	-	the textual description of this object
remarks	string	-	Remarks of the object
aggregation	string	-	The aggregation of the verticalCS: set, bag, sequence, array, record or table

verticalcscoodsysaxis table

Mapping file: gml/VerticalCS.hbm.xml

Description: Relation table for verticalCS and coordinateSystemAxis

Name	Type	Constraint	Description
verticalcsid	long	PK, FK, Not Null	The verticalCS object, pointing to the entry in the verticalcs table
coordinatesystemaxis	long	PK, FK, Not Null	The coordinateSystemAxis of the verticalCS object, pointing to the entry in the coordinatesystemaxis table

verticaldatum table

Mapping file: gml/VerticalDatum.hbm.xml

Description: The representation of the VerticalDatum objects. You should define nilreason or href|role|arcrole|title|show|actuate|remoteschema for reference or the other fields for the full object

Name	Type	Constraint	Description
pkid	long	PK, Not Null	Table primary key, used for relations
nilreason	string	-	NilReason which would be set in XML if other fields are not defined. Values: inapplicable, missing, template, unknown and withheld
href	string	-	The xlink:href value
role	string	-	The xlink:role value
arcrole	string	-	The xlink:arcrole value
title	string	-	The xlink:title value
show	string	-	The xlink:show value
actuate	string	-	The xlink:actuate value
remoteschema	string	-	The xlink:remoteschema value
identifier	string	unique	the identifier of the object
codespace	long	FK	the codespace for the identifier, pointing to the entry in the codespace table
name	String	-	the name of the object
codespaceName	long	FK	the codespace for the name, pointing to the entry in the codespace table
description	string	-	the textual description of this object
remarks	string	-	Remarks of the object
domainofvalidityid	long	FK	The domainofvalidity for the VerticalDatum, pointing to the entry in the domainofvalidity table
anchordefinition	string	-	The anchor definition of the VerticalDatum
codespaceanchordef	long	FK	The codespace for the anchordefinition, pointing to the entry in the codespace table
realizationepoch	timestamp	-	The realization epoch of the VerticalDatum

verticaldatumscope table

Mapping file: gml/VerticalDatum.hbm.xml

Description: The scopes of a verticalDatum

Name	Type	Constraint	Description
verticaldatumid	long	PK, FK, Not Null	Foreign Key (FK) to the related verticalDatumfrom the verticalDatumtable. Contains "verticalDatum.pkid"
scope	string	PK, Not Null	Scope of the related verticalCRS

verticalexextent table

Mapping file: gmd/VerticalExExtent.hbm.xml

Description: The representation of the VerticalExExtent objects. You should define nilreason or href|role|arcrole|title|show|actuate|remoteschema for reference or the other fields for the full object

Name	Type	Constraint	Description
pkid	long	PK, Not Null	Table primary key, used for relations
nilreason	string	-	NilReason which would be set in XML if other fields are not defined. Values: inapplicable, missing, template, unknown and withheld
href	string	-	The xlink:href value
role	string	-	The xlink:role value
arcrole	string	-	The xlink:arcrole value
title	string	-	The xlink:title value
show	string	-	The xlink:show value
actuate	string	-	The xlink:actuate value
remoteschema	string	-	The xlink:remoteschema value
id	string	-	The id of this object
uuid	string	-	The uuid of this object
minimumvalue	double	-	The minimum value
maximumvalue	double	-	The maximum value
verticalcrsid	long	FK	The VerticalCRS for the VerticalExExtent, pointing to the entry in the verticalcrs table

wmlmonitoringpoint table

Mapping file: wml/MonitoringPoint.hbm.xml

Description: The representation of the WaterML MonitoringPoint objects. If the featureofInterest .hibernatediscriminator is set to WmlMonitoringPoint, this table is required!

Name	Type	Constraint	Description
featureofinterestid	long	PK, FK, Not Null	The related featureofInterest entry, pointing to the entry in the featureofInterest table

wmlmprelatedparty table

Mapping file: wml/MonitoringPoint.hbm.xml

Description: Relation table for WaterML MonitoringPoint and RelatedParty

Name	Type	Constraint	Description
featureofinterestid	long	PK, FK, Not Null	The related featureofInterest entry, pointing to the entry in the featureofInterest table
responsiblepartyid	long	PK, FK, Not Null	The ResponsibleParty for the MonitoringPoint, pointing to the entry in the responsibleparty table

wmlmpverticaldatumtable

Mapping file: wml/MonitoringPoint.hbm.xml

Description: Relation table for WaterML MonitoringPoint and V!erticalDatum

Name	Type	Constraint	Description
featureofinterestid	long	PK, FK, Not Null	The related featureofInterest entry, pointing to the entry in the featureofInterest table
verticaldatumid	long	PK, FK, Not Null	The VerticalDatum for the MonitoringPoint, pointing to the entry in the responsibleparty table

DEPRECATED! Spatial Filtering Profile database model

The Spatial Filtering Profile database model is required to support the OGC SOS 2.0 Spatial Filtering Profile.

Add Spatial Filtering Profile model schema

spatialFilteringProfile table

Mapping file: SpatialFilteringProfile.hbm.xml

Description: Stores the sampling geometries of an OM_SpatialObservation. Used for the OGC SOS 2.0 Spatial Filtering Profile.

Name	Type	Constraint	Description
spatialFilteringProfileId	long	PK, Not Null	id of the spatialFilteringProfile
observation	long	FK, Not Null	id of the observation this spatial filtering profile relates to, pointing to the entry in the observation table
definition	string	Not Null	named value name to identify the sampling geometry, http://www.opengis.net/req/omxml/2.0/data/samplingGeometry
title	string	-	title of the geometry, e.g. the measurement location name
geom	org.hibernate.spatial.GeometryType	Not Null	the exact measurement location if the feature of interest describes an area, e.g. a lake

Old concept

At the beginning of the development of the 52°North SOS 4.x, the series concept was not yet supported. The series table was missing and the relation between procedure, observableProperty and featureOfInterest was directly stored in the observation table. This concept is still supported and here is the observation table for this concept.

Old concept database model

• SOS 4.3.x series concept database model as svg.
• SOS 4.3.x series concept database model as png.

Database model was created wiht DbVisualizer

observation table

Mapping file: Observation.hbm.xml, ObservationInfo.hbm.xml

Description:

Name	Type	Constraint	Description
observationId	long	PK, Not Null	id of the observation
featureOfInterestId	long	FK, Not Null	pointing to the measurement location, pointing to the entry in the featureOfInterest table
observablePropertyId	long	FK, Not Null	pointing to the parameter whose value is being observed, pointing to the entry in the observableProperty table
procedureId	long	FK, Not Null	pointing to the procedure that has generated the observed value, pointing to the entry in the procedure table
phenomenonTimeStart	timestamp	Not Null	timestamp for which the measured value applied
phenomenonTimeEnd	timestamp	Not Null	can be identical to phenomenonstarttime (unless the measured value belongs to a time period; in this case phenomenonTimeStart and phenomenonendtime can be different)
resultTime	timestamp	Not Null	the timestamp when the result was published, can be identical to phenomenonTimeStart
identifier	string	unique	identifier of the observation, queryable in GetObservationById
codespace	long	FK	the codespace for the identifier, pointing to the entry in the codespace table
deleted	org.hibernate.type.TrueFalseType	Not Null, default: 'F'	used for transactional DeleteObservation operation, indicates if the observation is deleted or not
description	string	-	description of this observation, e.g. keywords
validTimeStart	timestamp	-	starting time for the period this observation is valid, e.g. forecasting
validTimeEnd	timestamp	-	end time for the period this observation is valid , e.g. forecasting
unitId	long	FK	pointing to the unit of measurement of the observation, pointing to the entry in the unit table
samplingGeometry	org.hibernate.spatial.GeometryType	Not Null	the exact measurement location if the feature of interest describes an area, e.g. a lake

Complex Constraint: unique combination of featureOfInterestId, observablePropertyId, procedureId, phenomenonTimeStart, phenomenonTimeEnd, resultTime