Extended property selection with CQL2 expressions (like SQL SELECT)

[jerstlouis]

Similar to how you can use SELECT in SQL not only with existing fields from a table, but also derive new fields from existing properties using expressions, we discussed defining similar capabilities for the properties= query parameter (in a separate requirement class from basic property selection).

This could be for example to convert a temperature property from Fahrenheit to Celsius, or it use multiple properties to derive the new one e.g., properties=(temp_f - 32) * 5/9.

In OGC API - Coverages, we are planning to define this capability in a Part 2, also defining Filtering and Property selection as separate requirement classes. A classic example there is computing the NDVI from multispectral imagery e.g., properties=(B08 - B04) / (B08 + B04).

Some of the things to consider:

• How can derived properties be named? In coverages, some representations like GeoTIFF do not explicitly named fields (they only have an order). For these the name of the derived fields is not so important, so being able to just put the expressions makes sense (just like the AS {name} in SQL is optional). In representations that do have a field name, the server could generate some default names like e.g., field0. For Features, properties are always named (at least in GeoJSON) -- could this default derived property name approach also work? When the user does want to specify the name of the field, what should the syntax be? properties=(temp_f - 32) * 5/9 as temp_c? One other syntax we previously thought of was properties=temp_c:(temp_f - 32) * 5/9, but since CQL2 identifiers can contain colons this is ambiguous.
• We already define this capability in the draft OGC API - Processes - Part 3: Workflows in the Input/Output Field Modifiers ( Requirement 10). Currently, derived fields are an defined there as a JSON dictionary (object) where the key is the name of the derived property, and the value is the CQL2 expression.
• This is somewhat related to the Search/Queries extension, especially in the approach using the existing /items end-point (rather than the proposed stored query aproach POST to /search).
• We were also considering the joinCollections= query parameter allowing to perform joins (spatial or otherwise) between multiple collections, making the properties of all collections available for use in filter and properties. In these cases, we would need a mechanism to disembiguate identical property names from different collections (just like with SQL tables). Extending this further, this could also including with remote collections from other servers, and mixing OGC API - Features and OGC - Coverage collections

[cportele]

Meeting 2024-06-03: This sounds like a reasonable and useful extension. To be decided if it would be a separate requirements class or be moved to future work. This will also depend on the number of implementations. A code sprint may be a good idea to test this.

[cportele]

As discussed at the OGC Member Meeting, instead of properties=(temp_f - 32) * 5/9 as temp_c we should use a separate object-valued query parameter with the deepObject style for all derived queryables and write this as properties=temp_c&alias[temp_c]=(temp_f - 32) * 5/9.