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>

Describe other named queries