CQL functions¶

Recode¶

This is similar to if_then_else function, except that there is no default clause. You have to specify a translation value for every vocabulary key.

Syntax:

Recode(COLUMN_NAME, key1, value1, key2, value2,...)

• COLUMN_NAME: column name to get values from

Example:

<AttributeMapping>
  <targetAttribute>gml:name</targetAttribute>
  <sourceExpression>
      <OCQL>Recode(ABBREVIATION, '1GRAV', 'urn:cgi:classifier:CGI:SimpleLithology:2008:gravel',
                                 '1TILL', 'urn:cgi:classifier:CGI:SimpleLithology:2008:diamictite',
                                 '6ALLU', 'urn:cgi:classifier:CGI:SimpleLithology:2008:sediment')
      </OCQL>
  </sourceExpression>
</AttributeMapping>

The above example will map gml:name value to urn:cgi:classifier:CGI:SimpleLithology:2008:gravel if the ABBREVIATION column value is 1GRAV.

Categorize¶

This is more suitable for numeric keys, where the translation value is determined by the key’s position within the thresholds.

Syntax:

Categorize(COLUMN_NAME, default_value, threshold 1, value 1, threshold 2, value 2, ..., [preceding/succeeding])

• COLUMN_NAME: data source column name

• default_value: default value to be mapped if COLUMN_NAME value is not within the threshold

• threshold(n): threshold value

• value(n): value to be mapped if the threshold is met

• preceding/succeeding:

  • optional, succeeding is used by default if not specified.

  • not case sensitive.

  • preceding: value is within threshold if COLUMN_NAME value > threshold

  • succeeding: value is within threshold if COLUMN_NAME value >= threshold

Example:

<AttributeMapping>
  <targetAttribute>gml:description</targetAttribute>
  <sourceExpression>
      <OCQL>Categorize(CGI_LOWER_RANGE, 'missing_value', 1000, 'minor', 5000, 'significant')</OCQL>
  </sourceExpression>
</AttributeMapping>

The above example means gml:description value would be significant if CGI_LOWER_RANGE column value is >= 5000.

Vocab¶

This function is more useful for bigger vocabulary pairs. Instead of writing a long key-to-value pairs in the function, you can keep them in a separate properties file. The properties file serves as a lookup table to the function. It has no header, and only contains the pairs in ‘’<key>=<value>’’ format.

Syntax:

Vocab(COLUMN_NAME, properties file)

• COLUMN_NAME: column name to get values from

• properties file: absolute path of the properties file

Example:

Properties file:

1GRAV=urn:cgi:classifier:CGI:SimpleLithology:2008:gravel
1TILL=urn:cgi:classifier:CGI:SimpleLithology:2008:diamictite
6ALLU=urn:cgi:classifier:CGI:SimpleLithology:2008:sediment

Mapping file:

<AttributeMapping>
  <targetAttribute>gml:name</targetAttribute>
  <sourceExpression>
      <OCQL>Vocab(ABBREVIATION, strconcat('${config.parent}', '/mapping.properties'))</OCQL>
  </sourceExpression>
</AttributeMapping>

The above example will map gml:name to urn:cgi:classifier:CGI:SimpleLithology:2008:gravel if ABBREVIATION value is 1GRAV.

This example uses the config.parent predefined interpolation property to specify a vocabulary properties file in the same directory as the mapping file. See Property Interpolation for details.

toDirectPosition¶

This function converts double values to DirectPosition geometry type. This is needed when the data store doesn’t have geometry type columns. This function expects:

Literal

'SRS_NAME' (optional)

Expression

expression of SRS name if 'SRS_NAME' is present as the first argument

Expression

name of column pointing to first double value

Expression

name of column pointing to second double value (optional, only for 2D)

ToEnvelope¶

ToEnvelope function can take in the following set of parameters and return as either Envelope or ReferencedEnvelope type:

Option 1 (1D Envelope):

ToEnvelope(minx,maxx)

Option 2 (1D Envelope with crsname):

ToEnvelope(minx,maxx,crsname)

Option 3 (2D Envelope):

ToEnvelope(minx,maxx,miny,maxy)

Option 4 (2D Envelope with crsname):

ToEnvelope(minx,maxx,miny,maxy,crsname)

toPoint¶

This function converts double values to a 2D Point geometry type. This is needed when the data store doesn’t have geometry type columns. This function expects:

Literal

'SRS_NAME' (optional)

Expression

expression of SRS name if 'SRS_NAME' is present as the first argument

Expression

name of column pointing to first double value

Expression

name of column pointing to second double value

Expression

expression of gml:id (optional)

toLineString¶

This function converts double values to 1D LineString geometry type. This is needed to express 1D borehole intervals with custom (non EPSG) CRS.

Literal

'SRS_NAME' (EPSG code or custom SRS)

Expression

name of column pointing to first double value

Expression

name of column pointing to second double value

toXlinkHref¶

This function redirects an attribute to be encoded as xlink:href, instead of being encoded as a full attribute. This is useful in polymorphism, where static client property cannot be used when the encoding is conditional. This function expects:

Expression

REFERENCE_VALUE (could be another function or literal)

FormatDateTimezone¶

A function to format a date/time using a SimpleDateFormat pattern in a time zone supported by Java. This function improves on dateFormat, which formats date/time in the server time zone and can produce unintended results. Note that the term “date” is derived from a Java class name; this class represents a date/time, not just a single day.

Syntax:

FormatDateTimezone(pattern, date, timezone)

pattern

formatting pattern supported by SimpleDateFormat, for example 'yyyy-MM-dd'. Use two single quotes to include a literal single quote in a CQL string literal, for example 'yyyy-MM-dd''T''HH:mm:ss''Z'''.

date

the date/time to be formatted or its string representation, for example '1948-01-01T00:00:00Z'. An exception will be returned if the date is malformed (and not null). Database types with time zone information are recommended.

timezone

the name of a time zone supported by Java, for example 'UTC' or 'Canada/Mountain'. Note that unrecognised timezones will silently be converted to UTC.

This function returns null if any parameter is null.

This example formats date/times from a column POSITION in UTC for inclusion in a csml:TimeSeries:

<AttributeMapping>
    <targetAttribute>csml:timePositionList</targetAttribute>
    <sourceExpression>
        <OCQL>FormatDateTimezone('yyyy-MM-dd''T''HH:mm:ss''Z''', POSITION, 'UTC')</OCQL>
    </sourceExpression>
    <isList>true</isList>
</AttributeMapping>