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 |
- |
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:
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
featureRelation table (Optional)
Mapping file: FeatureOfInterest.hbm.xml
Description: the relation between features, e.g. parent and children
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
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
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
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 |
Mapping file: ProcedureDescriptionFormat.hbm.xml
Description: the format in which procedures shall be described
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
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
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
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 |
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 |
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 |
Mapping file: RelatedFeature.hbm.xml
Description: Realtion table between a related feature and one or more roles.
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)
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 |
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)
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
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
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!
Mapping file: wml/MonitoringPoint.hbm.xml
Description: Relation table for WaterML MonitoringPoint and RelatedParty
wmlmpverticaldatumtable
Mapping file: wml/MonitoringPoint.hbm.xml
Description: Relation table for WaterML MonitoringPoint and V!erticalDatum
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