52°North Sensor Observation Service 4.x Hibernate mapping

This page describes how you can adapt the 52°North Sensor Observation Service 4.x Hibernate mapping to map the SOS on your own database model.

Hibernate mapping options

The Hibernate mapping is described here: Hibernate mapping

Boolean values

Hibernate supports different types for boolean values. Due to Oracle, which does not have a boolean type, the boolean types in the mapping files are org.hibernate.type.TrueFalseType.

If you use a database which supports boolean values you can change the type to boolean or if your boolean values are numeric values you can use org.hibernate.type.NumericBooleanType.

For more details see the Hibernate types documentation;

Attributes insert="false" and update="false"

In some cases it is required to add the attributes insert="false" and update="false" to a property or relation element. It can not be said exactly where it is needed, but during the SOS start-up, an exception is thrown if these arttributes required.

discriminator

In some mapping files you can find an element named discriminator. This element is used by the SOS if the transactional operations are supported. In the adapted mapping files this element should look like this:

<discriminator formula="(SELECT DISTINCT 'F')" type="org.hibernate.type.TrueFalseType" />

Subselect

Hibernate description: "maps an immutable and read-only entity to a database subselect. This is useful if you want to have a view instead of a base table."

Hibernate example:

    1	...
    2	<subselect>
    3	     select item.name, max(bid.amount), count(*)
    4	     from item
    5	     join bid on bid.item_id = item.id
    6	     group by item.name
    7	</subselect>
    8	...

When a subselect is used, it is required that the result contains all elements with the same name as defined in the mapping file.

For example, the SOS Codespace mapping file has the two elements codespaceId and codespace. The SELECT clause in the formula should be

(SELECT 1 AS codespaceId, CAST('http://www.opengis.net/def/nil/OGC/0/unknown' AS VARCHAR) AS codespace)

Column (default)

All mapping files uses the column element to point to the column in the database table as default.

Formula

Instead of a column element you can define a formula element which can contain
  • a single value, e.g. <fomula>'F'</fomula> for a boolean field or <fomula>1</fomula> for a relation, e.g. featureOfInterestTypeId
  • no value, e.g. <fomula>null</fomula>
  • a SELECT clause which is enclosed with bracket, e.g. <fomula>(SELECT x FROM y)</fomula>

joined-subclass vs. subclass

The difference between joined-subclass (default in the SOS) and subclass is that at the joined-subclass the information is stored in different tables and at the subclasse the information is stored in one table. In the following two subsections you can find the mapping definitions for the NumericObservations.

joined-subclass

NumericObservation mapping defintion with separate table. More information can be found in the Hibernate documentation.

    1	<joined-subclass name="org.n52.sos.ds.hibernate.entities.NumericObservation" extends="org.n52.sos.ds.hibernate.entities.Observation" table="numericValue">
    2	    <key column="observationId" foreign-key="observationNumericValueFk" />
    3	    <property name="value" type="big_decimal" />
    4	</joined-subclass>

subclass

NumericObservation mapping defintion with one table. More information can be found in the Hibernate documentation.

    1	<discriminator column="discriminator" type="string" formula="NUMERIC_OBSERVATION" />
    2	...
    3	<subclass name="org.n52.sos.ds.hibernate.entities.NumericObservation" extends="org.n52.sos.ds.hibernate.entities.Observation" discriminator-value="NUMERIC_OBSERVATION">
    4	    <property name="value" type="big_decimal" column="numericvalue" />
    5	</subclass>

Named queries

Named queries are pre-defined queries with variables which can be added when the query is executed. In some cases the 52°North SOS 4.0 database model differs from a custom databse model so much so that it may lead to performance problems. To avoid this, the 52°North SOS 4.0 supports some named queries.

For more information, which named queries the 52°North SOS 4.0 supports, see Supported Named Queries

Attributes

Attribute like index, not-null, unique-key and foreign-key can be ignored because they have no meaning for querying the database.

Core mapping files

The core mapping files are the minimal required mapping files to run the 52°North SOS 4.0.

Codespace

The codespace mapping file should be look like this:
    1	<?xml version="1.0"?>
    2	<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
    3	<!-- Generated 10.07.2012 15:18:27 by Hibernate Tools 3.4.0.CR1 -->
    4	<hibernate-mapping>
    5	    <class name="org.n52.sos.ds.hibernate.entities.Codespace" table="codespace">
    6	        <subselect>(SELECT 1 AS codespaceId, CAST('http://www.opengis.net/def/nil/OGC/0/unknown' AS VARCHAR) AS codespace)</subselect>
    7	        <id name="codespaceId" type="long" />
    8	        <property name="codespace" type="string" insert="false" update="false" />
    9	    </class>
   10	</hibernate-mapping>

FeatureOfInterest

The featureOfInterest mapping contains a geometry that descripte the location of this feature. If your database or database model does not support geometries but rather has a longitude and a latitude field, then the SOS also supports this.

In that case you have to delete the geom property and uncomment the longitude and latitude properties. If you have an altitude and/or a srid field in your database model, you can map these fields, too. The srid data type should be integer and the longitud, latitude and altitude can be of type big_decimal or string.

FeatureOfInterestType

If only one featureOfInterest type is used, e.g. SF_SamplingPoint, the adapted mapping file should be look like this:
    1	<?xml version="1.0"?>
    2	<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
    3	<hibernate-mapping>
    4	    <class name="org.n52.sos.ds.hibernate.entities.FeatureOfInterestType" table="featureOfInterestType">
    5	        <subselect>(SELECT 1 AS featureOfInterestTypeId, CAST('http://www.opengis.net/def/samplingFeatureType/OGC-OM/2.0/SF_SamplingPoint' AS VARCHAR) AS featureOfInterestType</subselect>
    6	        <id name="featureOfInterestTypeId" type="long" />
    7	        <property name="featureOfInterestType" type="string" insert="false" update="false" />
    8	    </class>
    9	</hibernate-mapping>

ObservableProperty

Information about the phenomenon.

Series

A series consists of a featureOfInterest, a procedure and a observableProperty. This relation is defined in the series mapping file and used in the SeriesObservation mapping files.

SeriesObservation

This mapping file maps to the single observations. Next to the relations to series, and offering this file contains the values mapping. By default, the supports different value type which are stored in separate value tables. These tables are linked via joined-subclass elements in the Observation mapping file. In most custom database models, onyl one measure type (numeric values) is used and this value ist stored in the same table as the other observation informations (e.g. timestamp, procedure, ...). In that case you have to change the joind-subclass definition to a subclass definition

You have to add this element to the mapping file: <discriminator column="discriminator" type="string" formula="NUMERIC_OBSERVATION"/>

And the subclass element should contain the attribute discriminator-value="NUMERIC_OBSERVATION"

For more information see joined-subclass vs. subclass

The ...Observation mapping files contain a relation between the Observation and the Offering(s). This relations are described in the observationhasoffering table.

SeriesObservationInfo

Same as SeriesObservation but without values.

SeriesObservationTime

Same as SeriesObservation but contains only the observationId, the seriesId and the time informations.

Offering

Due to the SOS 2.0 specification restriction that each offering has only one procedure, the offering should map to the same table/columns as the procedure mapping file.

Procedure

Information about the procedure.

ProcedureDescriptionFormat

If only one procedure description format, e.g. SensorML 1.0.1 the adapted mapping file should be look like this:
    1	<?xml version="1.0"?>
    2	<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
    3	<hibernate-mapping>
    4	    <class name="org.n52.sos.ds.hibernate.entities.ProcedureDescriptionFormat" table="procedureDescriptionFormat">
    5	        <subselect>(SELECT 1 AS procedureDescriptionFormatId, CAST('http://www.opengis.net/sensorML/1.0.1' AS VARCHAR) AS procedureDescriptionFormat)</subselect>
    6	        <id name="procedureDescriptionFormatId" type="long" />
    7	        <property name="procedureDescriptionFormat" type="string" insert="false" update="false" />
    8	    </class>
    9	</hibernate-mapping>

Unit

The Unit mapping file contains the unit of measurement and is related in the Observation mapping files. It contains only two propteries, the id and the unit.

Optional Core mapping files

The following two tables are included in the core mapping folder but they are optional. This means that the tables and mapping files are not necessary to run the SOS 4.0.0. But without these tables the cache updates are slower and if you want to use the Hydrology-Profile it is currently required that the two mapping files ObservationConstellatio n and ObservationType are present.

If you do not want to use these mapping files, please remove them from [TOMCAT_HOME]\webapps\[SOS_NAME]\WEB-INF\classes\mapping\core.

ObservationConstellation

This mapping file is required for transactional operations but can eliminate som performance issues during the cache update. The hiddenChild property should always contain a formula with value 'F'.

ObservationType

If only one observation type is used, e.g. OM_Measurement, the adapted mapping file should be look like this:

    1	<?xml version="1.0"?>
    2	<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
    3	<hibernate-mapping>
    4	    <class name="org.n52.sos.ds.hibernate.entities.ObservationType" table="observationType">
    5	    	<subselect>(SELECT 1 AS observationTypeId, CAST('http://www.opengis.net/def/observationType/OGC-OM/2.0/OM_Measurement' AS VARCHAR) AS observationType)</subselect>
    6	        <id name="observationTypeId" type="long" />
    7	        <property name="observationType" type="string" insert="false" update="false" />
    8	    </class>
    9	</hibernate-mapping>

Old observation concept mapping files

Observation

The observation mapping file is used for the old observation concept and similiar to the series observation mapping file but instead of relations to the Series mapping/table it contains relations to featureOfInterest, procedure, and observableProperty

ObservationInfo

Identical to Observation mapping file without the value definition(s).

Supported named queries

Name queries can be added in each Hibernate mapping file after the elemen. The default structure is:
    1	<sql-query name="QUERY_NAME">
    2	     SELECT x FROM y WHERE x = (:parameter)
    3	</sql-query>

The benefit of named queries is, that you can define more performant queries than the queries which are generated by Hibernate from the mapping files.

Parameter list

parameter type definition in SQL query
feature String = (:feature )
procedure String = (:procedure )
observableProperty String = (:observableProperty )
offering String = (:offering )
features Collection IN (:features )
procedures Collection IN (:procedures )
observableProperties Collection IN (:observableProperties )
offerings Collection IN (:offerings )
series long = (:series )

Named queries

Query name parameter result Used for
getDataAvailabilityForFeatures features Data availability information GetDataAvailability operation
getDataAvailabilityForFeaturesProcedures features, procedures Data availability information GetDataAvailability operation
getDataAvailabilityForFeaturesObservableProperties features, observableProperties Data availability information GetDataAvailability operation
getDataAvailabilityForFeaturesProceduresObservableProperties features, observableProperties Data availability information GetDataAvailability operation
getDataAvailabilityForProcedures procedures Data availability information GetDataAvailability operation
getDataAvailabilityForProceduresObservableProperties procedures, observableProperties Data availability information GetDataAvailability operation
getDataAvailabilityForObservableProperties observableProperties Data availability information GetDataAvailability operation
getDataAvailabilityForSeries series Data availability information GetDataAvailability operation
getUnitForObservableProperty procedure Unit of measurement Procedure description generation
getUnitForObservablePropertyProcedure procedure, observableProperty Unit of measurement Procedure description generation
getUnitForObservablePropertyProcedureOffering procedure, observableProperty, offering Unit of measurement Procedure description generation
getFeatureOfInterestIdentifiersForOffering offering FeatureOfInterest identifiers Cache initialisation/update
getFeatureOfInterestIdentifiersForObservationConstellation procedure, observableProperty, offering FeatureOfInterest identifiers Cache initialisation/update
getOfferingTimeExtrema - Offering time extrema (min/max) Cache initialisation/update
getMinDate4Offering offering Min phenomenon time for offering Cache initialisation/update
getMaxDate4Offering offering Max phenomenon time for offering Cache initialisation/update
getMinResultTime4Offering offering Min result time for offering Cache initialisation/update
getMaxResultTime4Offering offering Max result time for offering Cache initialisation/update
getProceduresForFeatureOfInterest feature Procedure identifier Cache initialisation/update
getMinDate4Procedure procedure Min observation time for procedure Cache initialisation/update
getMaxDate4Procedure procedure Max observation time for procedure Cache initialisation/update
getFeatureForIdentifier features FeatureOfInterest identifier GetFeatureOfInterest operation
getFeatureForIdentifierProcedure features, procedures FeatureOfInterest identifier GetFeatureOfInterest operation
getFeatureForIdentifieObservableProperty features, observableProperties FeatureOfInterest identifier GetFeatureOfInterest operation
getFeatureForIdentifierProcedureObservableProperty features, procedures, observableProperties FeatureOfInterest identifier GetFeatureOfInterest operation
getFeatureForProcedure procedures FeatureOfInterest identifier GetFeatureOfInterest operation
getFeatureForProcedureObservableProperty procedures, observableProperties FeatureOfInterest identifier GetFeatureOfInterest operation
getFeatureForObservableProperty observableProperties FeatureOfInterest identifier GetFeatureOfInterest operation
getLatestObservationTime feature, procedure, observableProperty, offering Min observation time Subquery for GetObservation operation
getFirstObservationTime feature, procedure, observableProperty, offering Max observation time Subquery for GetObservation operation

getOfferingTimeExtrema

Query the time extrema for offering identifier. The result should be grouped by the offering identifier
  • parameter (where clause): -
  • result structure:
    • string (offering idenifier)
    • timestamp (min value of phenomenonTimeStart)
    • timestamp (max value of phenomenonTimeStart)
    • timestamp (max value of phenomenonTimeEnd)
    • timestamp (min value of resultTime)
    • timestamp (max value of resultTime)
  • hints: group by offering identifier
  • example:
    <sql-query name="getOfferingTimeExtrema">
         <return-scalar column="off" type="string" />
         <return-scalar column="minPhenStart" type="timestamp" />
         <return-scalar column="maxPhenStart" type="timestamp" />
         <return-scalar column="maxPhenEnd" type="timestamp" />
         <return-scalar column="minRes" type="timestamp" />
         <return-scalar column="maxRes" type="timestamp" />
             Select off.identifier as off,
             min(o.phenomenonTimeStart) as minPhenStart,
             max(o.phenomenonTimeStart) as maxPhenStart,
             max(o.phenomenonTimeEnd) as maxPhenEnd,
             min(o.resultTime) as minRes,
             max(o.resultTime) as maxRes
             FROM observation o
             INNER JOIN observationHasOffering oho ON o.observationid = oho.observationid
             INNER JOIN offering off ON oho.offeringid = off.offeringid
             GROUP BY off
    </sql-query>

TODO Describe other named queries

Topic revision: r13 - 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