You are here: Wiki>SensorWeb Web>SensorObservationServiceIVDatabaseModel (05 Mar 2020, CarstenHollmann)Edit Attach

52°North Sensor Observation Service 4.x Database model

Page contents

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:

52n_sos_db_443.svg

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 (NEW 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 (NEW 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) (NEW 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.

TODO 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 (NEW since 4.2.0)

TODO

Series metadata database model (NEW 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 (NEW 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.

TODO 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_43_oldconcept.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

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

I Attachment Action Size Date Who Comment
52n_sos_db_443.svgsvg 52n_sos_db_443.svg manage 303 K 05 Jun 2018 - 09:33 CarstenHollmann SOS 4.4.3 series concept database model (svg)
52n_sos_db_443_core.svgsvg 52n_sos_db_443_core.svg manage 260 K 05 Jun 2018 - 09:34 CarstenHollmann SOS 4.4.3 core series concept database model (svg)
52n_sos_db_443_full.svgsvg 52n_sos_db_443_full.svg manage 555 K 05 Jun 2018 - 09:34 CarstenHollmann SOS 4.4.3 full series concept database model (svg)
SOS_43_oldconcept.pngpng SOS_43_oldconcept.png manage 293 K 19 Aug 2015 - 13:09 CarstenHollmann SOS 4.3.x old concept database model (png)
SOS_43_oldconcept.svgsvg SOS_43_oldconcept.svg manage 128 K 19 Aug 2015 - 13:09 CarstenHollmann SOS 4.3.x old concept database model (svg)
SOS_43_series.pngpng SOS_43_series.png manage 225 K 19 Aug 2015 - 13:07 CarstenHollmann SOS 4.3.x series concept database model (png)
SOS_43_series.svgsvg SOS_43_series.svg manage 133 K 19 Aug 2015 - 13:08 CarstenHollmann SOS 4.3.x series concept database model (svg)
sos_44_M1_series.pngpng sos_44_M1_series.png manage 474 K 18 Nov 2015 - 08:21 CarstenHollmann SOS 4.4.x M1 series concept database model (png)
sos_44_M1_series.svgsvg sos_44_M1_series.svg manage 162 K 18 Nov 2015 - 08:21 CarstenHollmann SOS 4.4.x M1 series concept database model (svg)
sos_44_dev_20160822_series.svgsvg sos_44_dev_20160822_series.svg manage 263 K 22 Aug 2016 - 10:43 CarstenHollmann  
Topic revision: r24 - 05 Mar 2020, CarstenHollmann
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