CQL functions

CQL functions enable data conversion and conditional behaviour to be specified in mapping files. Some of these functions are provided by the app-schema plugin specifically for this purpose.

The uDig manual includes a list of CQL functions:
- http://udig.refractions.net/confluence/display/EN/Constraint%20Query%20Language.html
CQL string literals are enclosed in single quotes, for example 'urn:ogc:def:nil:OGC:missing'.
Single quotes are represented in CQL string literals as two single quotes, just as in SQL. For example, 'yyyy-MM-dd''T''HH:mm:ss''Z''' for the string yyyy-MM-dd'T'HH:mm:ss'Z'.

Vocabulary translation

This section describes how to serve vocabulary translations using some function expressions in application schema mapping file. If you're not familiar with application schema mapping file, read app-schema.mapping-file.

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 ''='' 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 app-schema.property-interpolation for details.

Geometry creation

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

Reference

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)

Date/time formatting

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>